No match query

  • Thread starter Thread starter Mick Horan
  • Start date Start date
M

Mick Horan

I have a Master table with the following structure.



Date

Unit (1S, 2S, 2W, 4W ect.)

Shift (DAY, EVENING, NIGHT)

(other field follow)



There are 11 Units and 3 Shifts.



In a perfect world each day there would be 3 records input for each Unit one
for each shift. For example:

6/1/4 1S DAY 6/1/4 2W DAY

6/1/4 1S EVENING 6/1/4 2W EVENING

6/1/4 1S NIGHT 6/1/4 2W NIGHT

6/1/4 2S DAY 6/1/4 4W DAY

6/1/4 2S EVENING 6/1/4 4W EVENING

6/1/4 2S NIGHT 6/1/4 4W NIGHT



The problem is different people will update this file at different times so
I need a query that will quickly tell me what records are missing. Using my
example above if: 6/1/4 1S EVENING and 6/1/4 4W DAY where missing I want my
query to list just those 2 missing records and not all the other records.



I created a SHIFT table that contains a record for each Unit and Shift
thinking I could use it to bump up against my Master table and pull the date
from my Master and the missing Unit and Shift from my Shift table.



That's what I thought I would do.problem is I don't know how.



Anyone have an idea? Mick
 
Mick,

When are you running this report, on the Date the entries are supposed to be
made, or the next day?

You might want to try:

SELECT T1.RptDate, T1.Unit, T1.Shift
FROM (SELECT DateAdd("d", Date(), -1) as RptDate , Unit, Shift FROM SHIFT)
as T1
LEFT JOIN yourTable T2
ON T1.RptDate = T2.RptDate
AND T1.Unit = T2.Unit
AND T1.Shift = T2.Shift
WHERE T2.RptDate IS NULL

Note that I have changed your [Date] field to RptDate, since Date is a
reserved word in Access and should not be used as a variable or field name.
What this does is creates a subquery (T1) that will give you yesterdays date
(if you are running the report on the same day the entries are made you can
change the DateAdd() syntax to Date()), and all the combinations of unit and
shift. By left joining this to your other table on all three fields and
then setting the criteria to T2.RptDate IS NULL, you are indicating to
Access that you only want those records in your subquery that do not have a
matching value in your main table.

HTH
Dale
 
Back
Top