473,320 Members | 2,006 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Query based Report with One-to-Many Table Relationship

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
2 2115
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: jwad01 | last post by:
I have a stored procedure that gets executed by a Crystal Report. The report runs in about a minute. Using profiler, I get the SP that was executed by the report and run it in Query Analyzer. In...
4
by: Minitman | last post by:
I have an address database and I want to have the query prompt for me to enter an expression that will bring up ALL names begining with same first ltr for individual page on my report. I am...
2
by: Jur Schagen | last post by:
I have this weird sorting problem. I have a report that is based on a query. The query is sorted on field A, and if I run it seperately it is decently sorted on that field. However, if I call the...
1
by: novice_in_ny | last post by:
I have a simple form that takes in a UPC code of an item and after I enter the record, I would like to do an inner join to get a couple of fields from another table that is joined on the UPC code...
2
by: starke1120 | last post by:
I have a query that has a prompt for date as Between And This database is hosted on a share drive. About half of my user's are ok with the report that has this query the other half when the...
4
by: Simon | last post by:
Dear reader, The syntax for Docmd.OpenReport is: OpenReport(ReportName, View, FilterName, WhereCondition, WindowMode, OpenArgs) Example The following example prints Sales Report while...
12
by: bhipwell via AccessMonster.com | last post by:
Hello, I have hit the "Cannot open any more databases" and "System resource exceeded" errors. Knew this was coming, but as I got closer to finishing the database, I hoped it wouldn't be an...
5
by: margot | last post by:
Hello, I created a listbox with companies names and when the user selects a company a report is generated based on the company name. I created a Function below and then I created a button that will...
5
by: bobh | last post by:
Hi All, Is there a difference in preformance between the two; TblNme has 36 fields across a record a query that selects all fields and the report only uses 75% of the fields, ie; Select...
2
by: cpStar | last post by:
I have a report grouped by a location field. In the detail section it shows several columns such as 1st name, last name, city, st, zip & company name. My user would like his report sorted based on...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.