Link to home
Start Free TrialLog in
Avatar of Steve B
Steve B

asked on

Trouble with <>

Hi all. I have a table, called tblRaces with fields ID, Event, Bikes, Bike_Shop, Bags, Event_IDFK. Event_IDFK is foreign key to contacts table where the athletes name is stored.

I have a query taken off of another temp table called qry_Temp with fields ID, SKU, Bikes, Bike_Shop, Bags, Customer_IDFK.

tblRaces has data:

ID     Event                       Bikes    Bike_Shop    Bags    Event_IDFK
1        Chattanooga           1          Steve's                          124
2         Louisville                 2          Joe's                1             47
3         Louisville                 1          Joe's                               24

qry_Temp has data:

ID       SKU                        Bikes     Bike_Shop   Bags     Customer_IDFK
1       Chattanooga            1           Steve's                           124
2        Louisville                  1           Steve's          1               32

I am trying to make another query to find the entry in qry_Temp that doesn't match tblRaces (ID 2)

I have tried numerous things, but can't find the answer. They all return all the entries in qry_Temp.

 In this case I ONLY want ID 2 from the query, where the Event =SKU but the IDFK is <>

SELECT qry_Temp.SKU, qry_Temp.Bikes, qry_Temp.Bike_Shop, qry_Temp.Bags, qry_Temp.Customer_IDFK
FROM qry_Temp, tblRaces
WHERE (((qry_Temp.SKU)=[tblRaces].[Event]) AND ((qry_Temp.[Customer_IDFK]) <> [tblRaces].[Event_IDFK]))

And I have tried

SELECT qry_Temp.SKU, qry_Temp.Bikes, qry_Temp.Bike_Shop, qry_Temp.Bags, qry_Temp.Customer_IDFK
FROM [qry_Temp] LEFT JOIN tblRaces ON [qry_Temp].[SKU] = tblRaces.[Event]
WHERE (((tblRaces.Event)=[qry_Temp].[SKU]) AND ((tblRaces.[Customer IDFK])<>[qry_Temp].[Customer IDFK]))
GROUP BY [qry_Temp].SKU, [qry_Temp].[Bikes], [qry_Temp].[Bike_Shop], [qry_Temp].[Bags], [qry_Temp].[Customer IDFK];

And..

SELECT qry_Temp.SKU, qry_Temp.Bikes, qry_Temp.Bike_Shop, qry_Temp.Bags, qry_Temp.Customer_IDFK
FROM [qry_Temp] LEFT JOIN tblRaces ON [qry_Temp].[SKU] = tblRaces.[Event]
WHERE (((tblRaces.[Customer IDFK])<>[qry_Temp].[Customer IDFK]))
GROUP BY [qry_Temp].SKU, [qry_Temp].[Bikes], [qry_Temp].[Bike_Shop], [qry_Temp].[Bags], [qry_Temp].[Customer IDFK];

HELP!
ASKER CERTIFIED SOLUTION
Avatar of Terry Woods
Terry Woods
Flag of New Zealand image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Steve B
Steve B

ASKER

Thank You Terry, That looks like a winner!  And FAST too!