Query question

  • Thread starter Thread starter Jeffrey Marks
  • Start date Start date
J

Jeffrey Marks

I'm having difficulty with a query for a report. I want to report all
the volunteer records (by name) in the volunteer and when they most
recently participated in an event. This is done from a many-to-many
relationship table with events. I only want to pull the last record
for each volunteer (representing the most recently added event). I
also need to list those volunteers who have not ever worked an event
(no relationship table records at all.)

Thanks in advance.

Jeff
 
This would be simplest with 2 queries in tandem.

1. Create a new query, using the related table.

2. Depress the Total button on the toolbar.
Access adds a Total row to the query design grid.

3. In the Total row under the VolunteerID field accept Group By.

4. In the Total row under the date field, choose Max.

5. Test: it should show one row of each volunteer who has done anything,
with the latest date alongside. (Don't output any other fields.) Save the
query, and close it.

6. Create another new query, using your Volunteer table and the query you
just created as input "tables."

7. In the upper pane of table design, double-click the line joining the two
tables. Access pops up a dialog with 3 options. Choose the one that says:
All records from Volunteers, and any matches from Query1.
Technically, this is called an outer join, so you get all volunteers,
whether they have worked or not.

It would be possible (but probably less efficient) to do this in a single
query if you want to learn about subqueries. Here's an intro:
http://allenbrowne.com/subquery-01.html
 
Back
Top