troubleshooting Question

Trouble with <>

Avatar of Steve B
Steve B asked on
SQL
2 Comments1 Solution90 ViewsLast Modified:
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
Join our community to see this answer!
Unlock 1 Answer and 2 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 2 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros