Attendance Dates for Each Month of Year 2

[clem]

I have 41 people that need to attend a meeting every month for the year 2001. There are two meetings a month, and they should attend one of these meetings. Initially, I have set up a personal table that has their names, autonumber (1-41), whether their salaried or hourly, etc. Then I thought I could set up an attendance table; in this table I have listed their name(actually the autonumber that was assigned them when I entered the info in the personal table)and their meeting attendance date. It worked well for January and I was able to retrieve who attended what meeting date and ultimately, who didn't attend either date in January. I got the non-attender information by querying with the criterion AttendanceDate "is null". In this query I have the personal table and the attendance table and they are linked by the autonumber. But now it's February, and February attendance dates are now in the attendance table along with January dates. The people who attended in February may not have attended in January and I've lost the ability to recall the January non-attenders or to pull out who didn't attend in February. How can I get this to work so I can, at all times, be able to tell who did not attend in Jan, Feb, Mar, etc. Thanks in advance for any help anyone may have.

 

[Qaroven]

Hrm, bear with me cos i haven't seen your actual table structures, but how bout something like ..
userTable (UID autonumber pri key, other fields, etc)
attendanceTable (UID pri key, AttendanceDate datetime pri key, Attended boolean)
such that every person has an entry in the attendanceTable for each meeting, even if it is just to note that they weren't there .. then simply query for selected year/month or date range
Does that help?
Q

 

[jgoodman00]

A simple, but slightly inefficient way to do this would be to create a field for each month. Then you just query whichever months you want.... James Goodman
j.goodman00@btinternet.com

 

[MichaelRed]

Or, just join the tables in a Crosstab qyery, where the Column headings are the Month of the attendance date(s) and Row headings are the Individual names. Each Col is then a visual guide to the months attendees, while each row is the individual's attendance. Additional queries from this to the endpoint (e.g. Non Attendance reports) should be simple.

MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over

 

[clem]

MichaelRed - You're the greatest!! I think this approach will work the best for what I need to accomplish. Thanks so very very much. Looks like I'm going to have to do some reading on crosstab queries.

Also thanks to Qaroven and jgoodman00!!