OK, here's the sitch : we have an access control system at work that
registers ever entry/exit of every employee. I recently made some
queries in Access so the ppl in HR could make reports (who came in
late, how many ppl were undertimed, etc etc) Now they are not
satisfied (of course) and they want a list of ABSENT employees.

I have the following tables :

PERSONNEL
CARD
DEPARTMENT
ACCESS_REG (raw entry/exit data)

So I can easily list the employees (along with their card number and
department) that entered/exited on a given date (or range of dates)
.... but when I try to compare that with the list in PERSONNEL and have
Access return the ones that DON'T appear, it tells me the query is too
complex!! Help??

I have it phrased thusly : ("Sorted With Date Search" is a Query that
generates a report by range of dates)

SELECT CARD.CARD_NUMBER, PERSONNEL.FIRST_NAME, PERSONNEL.LAST_NAME,
DEPARTMENT.DEP_DESCRIPTION
FROM (PERSONNEL INNER JOIN CARD ON PERSONNEL.P_ID = CARD.P_ID) INNER
JOIN DEPARTMENT ON PERSONNEL.DEP_ID = DEPARTMENT.DEP_ID
WHERE CARD.CARD_NUMBER NOT IN (SELECT CARD_NUMBER FROM [Sorted With
Date Search])

Am I attempting the impossible?? =(

Ms. Beeline,

I'd suggest adding [Sorted With Date Search] via a left join on
card.card_number. Then add a WHERE for [Sorted With Date
Search].card_number IS NULL.

Try that... I'm not sure you're approaching this the same way I would, but
if what you have should give you what you want except for it being too
complex, this modification may do something similar...

--
Jeremy Shapiro
Asandia, Corp.
www.asandia.com
1.866.ASANDIA (272.6342)

"Melissa Kay Beeline" <melissakaybeeline (AT) hotmail (DOT) com> wrote