Finding unmatched records

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

This one seems like a no brainer but I am having problems getting the result
I need. I have 2 queries, one returns all of the possible records lets call
them 1 through 14 and the other returns the pertinent records that are
entered daily in the db. What I want to do is compare the 2 queries and the
records that do not match the 1 - 14 query reveal themselves in the third
query. The following is the code in the 2 queries:

First query.

SELECT DISTINCTROW tblScans.PickListNum, tblScans.TicketNum
FROM tblScans
WHERE (((tblScans.TicketNum)>1 And (tblScans.TicketNum)<16))
GROUP BY tblScans.PickListNum, tblScans.TicketNum;

Second query.

SELECT qry1CountPickListNums.PickListNum, tblStatusCodes.StatusCode
FROM qry1CountPickListNums, tblStatusCodes
WHERE (((tblStatusCodes.StatusCode)>1 And (tblStatusCodes.StatusCode)<16))
GROUP BY qry1CountPickListNums.PickListNum, tblStatusCodes.StatusCode;

In the third query I tried to use the query wizard and find the unmatched
records. The following results in zero return. The problem is I know that the
two queries do not match each other therefore the queries are not working as
I wish. The following is the third query.

SELECT qry1CountPickListNums.PickListNum, qry1CountPickListNums.TicketNum
FROM qry1CountPickListNums LEFT JOIN [qry2CountPickListNum<16] ON
qry1CountPickListNums.TicketNum = [qry2CountPickListNum<16].StatusCode
WHERE ((([qry2CountPickListNum<16].StatusCode) Is Null));

As mentioned there is no returned records. Any Ideas?

Thanks,

Dennis
 
Why not just put the results of the two queries into temporary tables and use
an outer join on the two tables with your third query?
 
Back
Top