Not In (sort of) query

R

R Fourt

This one is has been a real "bear" for me.

We have a database that tracks overtime for employees. Employees are
assigned to one of a dozen or so "departments".

Each overtime entry for an employee can be one of 3 types; overtime,
compensatory or holiday.

Each pay period has a unique number (eg, "08-11" for the eleventh period of
2008)

Each department processes and "submits" payroll information regarding
overtime for each payperiod for each of the 3 types.

When a department processess payroll they do so for each of the three OT
types separately (this is for admin reasons). When they process an OT type,
an entry is created in a table that gives the department ID, the pay period
ID and the type of overtime processed. So each of these 12 department should
have 3 entries (one for each OT type) for each pay period.

Finally, the question: I need to be able to query to see when a department
has not submitted all 3 types of OT for a pay period and which of the 3 types
they did not submit.

I have a master pay period table: [PayPeriod],[Start_date],[End_date]
a department submit table: [Dept],[PayPeriod],[OT_Type]

Any thoughts?
 
M

Michel Walsh

Have a table OT_Types, one field, OT_Type, 3 records (all the possible
ot_type).

Have a table Depts, one field, dept, listing all the possible departments.

Have a query: q1

SELECT dept, ot_type
FROM depts, ot_types


Have a query, q2:

SELECT *
FROM masterTable
WHERE payPeriod = whichPayPeriod


Make a third query:


SELECT q1.dept, q1.ot_type
FROM q1 LEFT JOIN q2
ON q1.dept=q2.dept AND q1.ot_type=q2.ot_type
WHERE q2.dept IS NULL
ORDER BY q1.dept, q1.ot_type


which should return what you want.


Vanderghast, Access MVP
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top