Melissa Kay Beeline wrote:
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?? =(
A suggestion to help keep it simple.
Select the above SQL string and select Edit/Copy from the menu. In
Access, click Queries/New/Design. When the AddTables is presented,
press Close. From the menu select View/SQL and from the menu select
Edit/Paste. (If you copy the SQL string from my message, be sure to
remove the > lines after pasting)
Now wipe out anything from the word "Where" including Where. IOW, you
have no where clause.
Now select, from the menu, View/Design. You are back in the query builder.
From the menu, select the AddTable button (has a + sign). Select your
[Sorted With Date Search] query. Now drag a line from the card number
in the table CARD to the CARD number field in [Sorted With Date Search].
This sets a relationship between the two tables.
Dbl-click on the relationship line. Select option 2; All records from
Card and those that match in [Sorted With Date Search].
Now drag the Card field from [Sorted With Date Search] into a blank
column in the query builder. Uncheck the Show so it won't be displayed.
In the criteria row for this column enter the words
IS NULL
so that only records where card #s not in the query are displayed.
Close and save the query. Maybe call it AbsentEmps.
This looks like a lot of work, it really isn't.
Of course, you can always use the FindUnmatched query wizard. But if
you know how to do this yourself, you have added another skill set to
your belt.
<rant on>
I HATE spaces in table field names. I HATE spaces in Table, Query,
Form, Report names. HATE, HATE, HATE. Although you can do it, I see
absolutely NO REASON to do so. Ex: [Sorted With Date Search].
If you stay on programming in Access, sooner or later you are going to
kick yourself in the butt for ever using spaces in object names. I
would suggest you desist immediately. However, it's your perogative.
In fact, I even detest naming conventions were people put "tbl" in front
of table names, "qry" in front of query names, "frm" in front of
reports, etc. Why do it...especially if you have a lot of them? To
exercise the eyeballs more?
I STRONGLY support and use putting identifiers in code, as in
Dim intNumber As Integer.
We are talking code tho. In the database window, why do I need to see a
bunch of objects preceeded by tbl, qry, frm, rpt, etc?
Thank you for permitting me to get this off my chest.
<rant off>.
