Creating a report...can I do this and how?

[John]

I have 2 tables, one with dates and information about those dates, and
one with people information. I want to create a report listing each
date and the people who attended on that date (who have that date under
the date column in their table). Can I do it and how?

Also another problem I'm running into is what if the person is on more
than one date? How can I work that out in the tables?

[Jim Carter]

If you only want a report of people that attended on which dates, then
why don't you just sort the people table by dates, and select the
records (dates) you want to print?

-----Original Message-----
From: John [mailto:jo****** *@yahoo.com]
Posted At: Monday, May 01, 2006 6:03 AM
Posted To: comp.databases. ms-access
Conversation: Creating a report...can I do this and how?
Subject: Creating a report...can I do this and how?

I have 2 tables, one with dates and information about those dates, and
one with people information. I want to create a report listing each
date and the people who attended on that date (who have that date under the date column in their table). Can I do it and how?

Also another problem I'm running into is what if the person is on more
than one date? How can I work that out in the tables?

[John]

Say I have a people table, and what if they attend more than 1 date...

Like Bob showed up on 12/5/05 and 12/6/05 and 1/3/06
Jimmy on 12/2/05 and 1/6/06....

etc.

And these dates are unknown at the moment, until they are created in
the other table.

[pietlinden]

the standard way of doing this is your classic Students-Classes
database.

Student---(1,M)----Attendance---(M,1)---Meeting

Then this is a walk. Base the report on your query of the 3 tables,
and you're off and running.

CREATE TABLE Attendance(
StudentID FOREIGN KEY REFERENCES Student(Student ID),
MeetingID FOREIGN KEY REFERENCES Meeting(Meeting ID),
Present BOOLEAN),
PRIMARY KEY(StudentID, MeetingID));

CREATE TABLE Meeting(
MeetingID,
MeetingDate...) ;

IOW, I think your design doesn't allow for what you're trying to do.
the attendance/roster table is missing. Once you have that, you should
be all set.

[Jim Carter]

It sounded to me like he was trying to do this from the Access GUI
itself. If that is the case then how would do you a multi-table join at
the GUI level? I also thought he only wanted the list of attendees for
unknown dates. Since he said the date attended was in the attendee
table, then why wouldn't he just sort the attendee table by date
attended, highlight the date(s) he wanted to report, and then print the
selected rows? I didn't get the impression that he was trying to write
some SQL code to do this for him.

-----Original Message-----
From: pi********@hotm ail.com [mailto:pi****** **@hotmail.com]
Posted At: Monday, May 01, 2006 9:12 PM
Posted To: comp.databases. ms-access
Conversation: Creating a report...can I do this and how?
Subject: Re: Creating a report...can I do this and how?

the standard way of doing this is your classic Students-Classes
database.

Student---(1,M)----Attendance---(M,1)---Meeting

Then this is a walk. Base the report on your query of the 3 tables,
and you're off and running.

CREATE TABLE Attendance(
StudentID FOREIGN KEY REFERENCES Student(Student ID),
MeetingID FOREIGN KEY REFERENCES Meeting(Meeting ID),
Present BOOLEAN),
PRIMARY KEY(StudentID, MeetingID));

CREATE TABLE Meeting(
MeetingID,
MeetingDate...) ;

IOW, I think your design doesn't allow for what you're trying to do.
the attendance/roster table is missing. Once you have that, you should
be all set.

[John]

This looks like it is going to work very well, thank you.
I haven't had a chance to play with it too much but the little I did it
looks like just what I need.