Looking for MISSING records

Question

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!

Answer 1

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 :)

Answer 2

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
)

Answer 3

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.

Answer 4

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;

Answer 5

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.....