Plz help with listing absent employees??

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Melissa Kay Beeline - 27 May 2004 05:31 GMT

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?? =(

Reply to this Message

Davis - 27 May 2004 16:15 GMT

> 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
[quoted text clipped - 26 lines]
>
> Am I attempting the impossible?? =(

The NOT IN query type is notoriously flaky and slow. Would it be
possible for you to create a temporary table of all employees, then
DELETE from that table based upon who came in. You would then have
the result set you want and the query would run much faster.

Reply to this Message

Todd B - 27 May 2004 17:26 GMT

> 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
[quoted text clipped - 8 lines]
> DEPARTMENT
> ACCESS_REG (raw entry/exit data)

So, a card can be shared by more than one person? If so, how will you
be able to identify who is absent. All you will be able to say is
that a particular card wasn't used on certain dates.

> 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)
[quoted text clipped - 13 lines]
>
> Am I attempting the impossible?? =(

If you want to see what card was absent on a particular day, use the
same query but modify it slightly (my changes are in lower case):

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 inner join access_reg on c_id WHERE
CARD.CARD_NUMBER NOT IN (SELECT CARD_NUMBER FROM access_reg where
access_reg.entrydate <> '5/4/2004' and access_reg.exitdate <>
'5/4/2004')

Now if you want a complete listing, I think you may need a 'where not
exists' in the query. I'm not sure, but will look into it.

Hope that helps,
Todd

Reply to this Message

--CELKO-- - 29 May 2004 19:31 GMT

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. I would also recommend that you start using mixed
case letters and aliases to make your code readable; it looks like you
are still using punch cards.

>> they are not satisfied (of course) and they want a list of ABSENT

employees. <<

Let's call the "raw data" of which you told us nothing a timeclock:

CREATE TABLE Timeclock
(card_nbr INTEGER NOT NULL,
entry_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
exit_time TIMESTAMP, -- null means still active
CHECK (entry_time < exit_time),
PRIMARY KEY (card_nbr, entry_time));

Then try something like this:

SELECT C1.card_number, P1.first_name, P1.last_name
FROM (Personnel AS P1
INNER JOIN
AccessCards AS C1
ON P1.p_id = C1.p_id) AS X -- get names
LEFT OUTER JOIN -- match people to cards
Timeclock AS T1
ON X.p_id = T1.p_id
WHERE X.entry_time BETWEEN :rpt_start_time AND:rpt_end_time;

Reply to this Message