Query question

[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

 

[Allen Browne]

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

 

[KenSheridan via AccessMonster.com]

Try something like this:

SELECT FirstName, LastName, Event, EventDate
FROM Volunteers, EventVolunteers AS EV1, Events
WHERE EV1.VolunteerID = Volunteers.VolunteerID
AND EV1.EventID = Events.EventID
AND EventDate =
(SELECT MAX(EventDate)
FROM EventVolunteers AS EV2 INNER JOIN Events
ON EV2.EventID = Events.EventID
WHERE EV2.VolunteerID = EV1.VolunteerID)
UNION ALL
SELECT Firstname, LastName, "None", NULL
FROM Volunteers LEFT JOIN EventVolunteers
ON Volunteers.VolunteerID = EventVolunteers.VolunteerID
WHERE EventVolunteers.VolunteerID IS NULL;

Ken Sheridan
Stafford, England