Query based Report with One-to-Many Table Relationship

[Todd D. Levy]

I have a primary table (containing basic contact information) and a
number of subsidiary tables (containing various additional information)
for employees.

Most of the subsidiary tables have a One-to-One relationship with the
primary table.

There is an exception. The employees attend various events and I have a
subsidiary table that tracks the Start Date and End Date of these
events.

Since each employee can (and frequently does) attend more than 1 event
over the course of time, this subsidiary table has a One-to-Many
relationship to the primary table.

I am setting up a query (used as the basis for a report) that grabs
fields from the primary table and a number of subsidiary tables,
including the Event table with the One-to-Many relationship to the
primary table.

What I want is to be able to (for a given employee) grab the Start Date
and End Date of only the most recent (by Start Date) event from the
Events table.

How do I do this?

[Allen Browne]

See:
http://www.mvps.org/access/queries/qry0020.htm
for 4 possible approaches.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Todd D. Levy" <us************ **@spamex.com> wrote in message
news:MP******** *************** *@nyc.news.spea keasy.net...

I have a primary table (containing basic contact information) and a
number of subsidiary tables (containing various additional information)
for employees.

Most of the subsidiary tables have a One-to-One relationship with the
primary table.

There is an exception. The employees attend various events and I have a
subsidiary table that tracks the Start Date and End Date of these
events.

Since each employee can (and frequently does) attend more than 1 event
over the course of time, this subsidiary table has a One-to-Many
relationship to the primary table.

I am setting up a query (used as the basis for a report) that grabs
fields from the primary table and a number of subsidiary tables,
including the Event table with the One-to-Many relationship to the
primary table.

What I want is to be able to (for a given employee) grab the Start Date
and End Date of only the most recent (by Start Date) event from the
Events table.

How do I do this?

[Todd D. Levy]

Thanks Allen.

This was very helpful; I adapted one of these solutions to my needs.

In article <40************ **********@free news.iinet.net. au>,
Al*********@See Sig.Invalid says...

See:
http://www.mvps.org/access/queries/qry0020.htm
for 4 possible approaches.