Tutorial :Looking for MISSING records


I'm a bit rusty when it comes to MS Access and I am hoping someone can help me out.....

I have a list of all items that have been scanned (for purchase) by each store, by UPC for a one month period. I also have a particular group of UPC's that I want data for. What I want to get is the items that DIDN'T get scanned. Obviously, the items that did not get a scan will not show up in list of scanned items.

First, I tried doing a crosstab query...which is great, but I only want to see the '0' values. Ideally I would like to put the '0' values from the crosstab into a simple table that lists the store and the UPC. I also tried doing an unmatched query, but that only returns the UPC....I need to know which store it didn't scan in....

I think I may be going about this a bit wrong. Like I said, I haven't used Access in years and I apologize if I am asking an uber easy question.

Anyone that can offer some assistance?

Thank you in advance!


I would use:

SELECT ul.upc FROM upc_list ul   LEFT JOIN upc_scanned us  ON ul.upc = us.upc  WHERE us.upc Is Null  

With your tables and fields:

SELECT [Master UPC List].UPC   FROM [Master UPC List] LEFT JOIN [No Scans]   ON [Master UPC List].UPC = [No Scans].UPC   WHERE [No Scans].UPC Is Null;   


I think you are looking for something like this. Obviously you will need to change table names and fields to match your schema.

select UL.UPC from UPC_LIST UL where UL.UPC not in (      select US.UPC from UPC_SCANNED US  )  


Could you give us some idea of the tables involved? If you only have two tables, one that contains each item scanned and which store it was scanned in, and one listing all the items, then any item returned by your unmatched query didn't scan in ANY store, is this what you want? You seem to be asking for it to tell you which store that it was meant to be scanned in :)


To answer your question pipTheGeek, I am looking for which UPC(s) (if any) were NOT scan in which stores.

For instance, if there are 100 stores, and Alpo Dog Food is supposed to be available in every store, I need to find out if there are any stores that did not sell any Alpo Dog Food during a one month period of time and what those store numbers are. Once I have those store numbers, I can investigate further as to why it did not sell.


Remou -

the edited sql you gave me:

SELECT [Master UPC List].UPC FROM [Master UPC List] LEFT JOIN [No Scans] ON [Master UPC List].UPC = [No Scans].UPC WHERE [No Scans].UPC Is Null;

also returns 0 records.....

Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Next Post »