Using a query to find missing records

[clickster]

I have a database that tracks tape backups. We, like most people, have many sites (68) that do tape backups each night to a tape. There are 20 tapes - Mon-Fri in 4 different colors, one color for each week. Blue Monday, Pink Friday, etc. The sites then send the tapes to me for temporary offsite storage and I have to catalogue them. I enter the data into a table called tTapeReceived. The fields are as follows:

Date (Date I received the tape)
Site (Which site the tape belongs to)
TapeReceived (Which tape I received - i.e. Blue Monday)
CorrectTape (What tape I SHOULD have received)

The Date, Site, and TapeReceived are collectively the Primary Key

I have another table called tSites that has a single field [site] that lists the sites.

I have no problem running queries to find out about the tapes that I HAVE received. The problem is that I may need to know on what days I DIDN'T received a correct tape. That means I need to find out on what date a record does not exist that includes the following:
The date and branch that I specify where the TapeReceived and CorrectTape are equal.
Since I have the tSites table, there should be a way using SQL to check each date against tSites and report any time it does not find a record with that date, site, and identical TapeReceived and CorrectTape fields, retuning Site and Date.
I need to find a way in SQL (not VBA if it can be avoided)to do this. Thank you in advance for any assistance.

 

[lespaul]

In the query design grid (of the query that returns all those that were turned in) there should be a relationship line between the two tables (tTapeReceived and tSites). If you right click on that relationship line and edit the relationship, you will have three choices:

Only select those records from the tables where the values are equal (INNER JOIN)

Select all the records from tTapeRecieved and only those that match from tSites (LEFT or RIGHT JOIN)

Select all the records from tSites and only those that match from tTapeRecieved. (RIGHT or LEFT JOIN)

You will want to select the one that says select all the records from tSite and only those from tTapeRecieved that match (which gives you all the sites regardless if they have any tapes recieved)

HTH

Leslie

 

[clickster]

I'm not sure if that will work. I also may not be explaining myself right. I need a query that prompts me for a date range (usually use "between [from] and [to]&quot. Once I've given it a date range (we'll say 02/01/04 - 02/04/04 for this example) it says, site A didn't receive a correct tape (TapeReceived and CorrectTape are equal) for 02/01/04 and 02/03/04, site B didn't receive a correct tape on 02/04/04, site F didn't receive a correct tape for...

In other words, for each date in the date range, there should be a record for each site that has the TapeReceived field and CorrectTape field equal. I need it to tell me which sites don't and on which dates they don't. So, essentially, I am looking for the absense of a "Correct" record for each branch on each date.

 

[BNPMike]

Set up a calendar ie a table that holds days only. Maybe you can put colours against days it there is a common pattern across sites. (Use Excel to create the table)

Now left join from the calendar to tTApeReceived. Selecting on nulls, this will pick up all the sites that didn't make any days within your range.

The ones that sent the wrong tape you can get from a simple table scan.