By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,880 Members | 2,443 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,880 IT Pros & Developers. It's quick & easy.

Query based Report with One-to-Many Table Relationship

P: n/a
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?
Nov 12 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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.speakeasy .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?

Nov 12 '05 #2

P: n/a
Thanks Allen.

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

In article <40**********************@freenews.iinet.net.au> ,
Al*********@SeeSig.Invalid says...
See:
http://www.mvps.org/access/queries/qry0020.htm
for 4 possible approaches.

Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.