Can I search identical fields from multiple tables?

[Mr A C Ryder]

I have a database to log details about staff in my school. I have a tables
for staff details, courses, absence, educational visits etc.

In each table I have a date field to show when the member of staff is not
present.

I want to produce a query to search all tables by entering a date - e.g. to
bring back all results for staff who are not in school on a certiain day
because they are either ill, on a course or on an educational visit. The
query will work by entering a date once - it will then search all the other
tables and bring back the results.

Is this possible? Do I restructure?

 

[Golfinray]

I would start by bringing all the tables into one query. You may have to
relate them first by going into tool/relationships and hooking them together
by primary and foreign key. I don't know how you have set your tables but you
have have a primary key of something like teacher ID number. When you bring
them into a query link them again and choose the fields you need from each
table. You may only need teacher id# and the reasons for absense and date of
absence. Then in the query you can add any date to the criteria of the date
field, like Between 3/5/2008 and 3/6/2008 and see who was absent and the
reason.

 

[Mr A C Ryder]

All the tables in my database are related together by staff Initial. Each
table will then have a date field within it.

If I add all the tables into a query it will mean that the field "date" will
appear more than once. What I want to do is not have to enter the date more
than once in a parameter box - i.e. not for each field in each table.

I have tried using an SQL query but have been unsucceful in getting this to
work.

 

[John Spencer]

Take a look at using a UNION query.

SELECT TeacherID, DateOut, "Course" as Reason
FROM Courses
WHERE DateOut = [What Date?]
UNION
SELECT TeacherID, DateOut, "ILL"
FROM Illness
WHERE DateOut = [What Date?]
UNION
SELECT TeacherID, DateOut, "EVisit"
FROM [Educational Visit]
WHERE DateOut = [What Date?]

That said, you probably should roll all of this into one table - Absences
(?) with three fields
TeacherID
DateOut
Reason

The nice thing about this structure is if you come up with another reason
for a teacher to be out, then you just add a reason. Then the query becomes
really easy to find out who is absent on a specific date or a range of
dates.



--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..