Query question

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

--
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.


"Jeffrey Marks" wrote in message
...
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

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

Jeffrey Marks wrote:
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

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/201005/1