Comparing Information

[Guest]

Heya im pretty new to access.

I have a table which is a list of names (all the people in my office) Called
'Names'
I have another table which has a list of dates for a month. Its filled in
with people who are absent on what days for that month. This is called
'Absence'

I want to create a way where it subtracts the names that are absent on that
day, for the whole to leave me who isn't absent.....

E.G.
Table 1 Table 2
'Names' 'monday 1st' 'Tuesday 2nd'
A A B
B B
C
D


So i would get another table that shows

Monday 1st , Tuesday 2nd
C A
D C
D

Is it possible , if so how?

 

[Michel Walsh]

If table2 is like:

table2 ' table name
TheDate Who ' fields name
monday 1st, A
monday 1st, B
thuesday 2nd, B ' data


Just to be on the safe side, we also need a table with all the dates covered
by the exercise:


table3 ' table Name
theDate ' field name
monday 1st
thuesday 2nd
wednesday 3rd ' data



Then


SELECT a.Name, a.theDate
FROM (SELECT name, theDate FROM table1, table3) AS a
LEFT JOIN table2
ON a.name=table2.name AND a.theDate=table2.theDate
WHERE table2.name IS NULL
ORDER BY a.theDate


should do. The ORDER BY clause is just to 'regroup' the data in order of
date.



Hoping it may help,
Vanderghast, Access MVP