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)

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.

 

[humour]

I hate coded solutions myself when an elegant table based or SQL based solution might work. I had to write something similar. I wanted a query for when employee's were absent on a working day. The application & context is completely different to yours but the problem is similar (finding missing records).

I created a Working days table. Using Excel as a quick way to create DATE data I created a days record with 700+ records. Each row represents a working day, I had a field for Day (Mon, Tue, Sat, Sun etc) and another for holiday so I basically had data where I could SQL every working day.

Select from Days where dayoftheweek is (M,T,W,TH,F) and holiday is ='' - Joining this to emplooyee working records I could determine when people didn't have a working record. (I would join on date).

Perhaps something similar could work for you. Create a table that represents when you should have a tape record. Another table represents SITES that do backups. The cartesian set (Sites * BackupDays) is your table you Left-outer join to.

Left Join (Sites * Backups) TO Tape-Records where Tape-Record is Null or (= '') and you will have a list of missing tapes.

 

[clickster]

I think this might work. Let me work on the SQL and see if I can get it to work in my situation. Do you happen to have the SQl for the query that you used? It might be easier for me to figure out how to set it up for my data if I see how you set it up.

 

[humour]

Sorry for the late reply but here it is...

When I replied first I had designed what I wanted to do, when I actually did it I found the SQL to be slightly more complex than anticipated but certainly workable.

At a high level this is what I am doing....

Select "Records of days the employee worked"
Union
Select "Records representing Calendar Days (I made a 365 row table representing a year (easy to do with EXCEL) where days are not in (select of days the employee worked)...

** I may have missed formatting out some ASP syntax but you should get the idea... Lemme know if this helps... The KEY to the Problem an the solution is the POWERFUL "UNION" keyword.

SELECT distinct
Days.DAYOFTHEWEEK AS [Day],
Days.DATE, Days.Holiday,
TimeReporting_a.[Start Time],
TimeReporting_a.[End Time],
TimeReporting_a.Comment,
Days.yyyymmdd,
TimeReporting_a.EmpId
FROM
Days LEFT JOIN TimeReporting_a ON Days.yyyymmdd = TimeReporting_a.yyyymmdd "
WHERE
(TimeReporting_a.EmpId) = " & cstr(empid)
and TimeReporting_a.yyyymmdd >= '2004/01/01'
and TimeReporting_a.yyyymmdd <= '2004/04/30'
UNION
Select
dayoftheweek,
date,
holiday,
' ',
' ',
' ',
yyyymmdd,
' '
from
days
where
yyyymmdd
not in
(
SELECT yyyymmdd FROM TimeReporting

WHERE (TimeReporting_a.EmpId)= "& cstr(empid) & ")
and yyyymmdd >= '2004/01/01' and yyyymmdd <= '2004/04/30'
ORDER BY 7

 

[clickster]

Any idea what's wrong with this? It looks like it might work, but there is something wrong with the JOIN syntax. I'm fairly new to SQL, so I'm probably missing something obvious.

SELECT tBranches.Branch, tDates.Dates
FROM tBranches B, tDates D
LEFT JOIN tTapesReceived T ON D.date = T.date
AND B.Branch = T.Branch
WHERE T.Branch IS NULL
AND D.Date Between [FROM] AND [TO];

 

[tracy1234]

SELECT tBranches.Branch, tDates.Dates
-->use your alias B.Branch, D.Dates

FROM tBranches B, tDates D
LEFT JOIN tTapesReceived T ON D.date = T.date
-->this does not match with your original post so I'm not sure what you're getting at here.

AND B.Branch = T.Branch
-->this is out of place sql first condition is 'where' then use 'and' for further conditions.

WHERE T.Branch IS NULL
-->seems to conflict with the above

AND D.Date Between [FROM] AND [TO];

If you're looking to find records that exist in one table but don't exist in another table, you might try something like this:

select * from mytable_a a
where not exists (select * from mytable_b b where a.my_id = b.my_id);

-Tracy