Access SQL - Most recent child record

[PAK]

I have two tables. One for members and one for the events that members
attended. I need to write a query that returns one row for each member with
the most recent event attended. Here are the tables/fields below. Thank you.

Regards,

Drew

Members Table:
ID
First
Last

Event Table:
MemberID
Event
Date

 

[Dale Fye]

Select M.ID, M.First, M.Last, E.Event, E.Date
FROM tbl_Members as M
INNER JOIN tbl_Events as E
ON M.ID = E.MemberID
WHERE E.Date = DMAX("Date", "tbl_Events", "[MemberID = " & M.ID)

This particular query will not give you those members who have not attended
any events.

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

 

[Tom van Stiphout]

On Mon, 6 Oct 2008 07:28:01 -0700, PAK <[email protected]>
wrote:

Create a totals query on tblEvent, group by memberid and event, and
max of date.

Btw, Date is not a great field name, since it is also a reserved word.
Change it to EventDate.
Event is perhaps not great either; it suggests a text field, whereas I
was expecting an ID field linked to an Events table. That would make
it easy to keep other event-related info (e.g. Location, Chairman) and
also supports recurring events. Your current Events table could then
be renamed to EventInstances.

-Tom.
Microsoft Access MVP

 

[Duane Hookom]

Try:

SELECT M.*, (SELECT TOP 1 E.Event FROM tblEvent E WHERE E.MemberID = M.ID
ORDER BY E.Date DESC) as MostRecentEvent
FROM tblMembers M;

BTW: Date is the name of a function and should not be used as the name of a
field. Similarly First and Last should not be used. Consider EventDate,
Firstname, and Lastname.

 

[John Spencer]

One more version. If this works it should be the fastest version.

SELECT Members.*
, Ea.Event
, Ea.Date
FROM (Members INNER JOIN Event as Ea
on Members.Id = Ea.MemberID)
INNER JOIN
(Select E.MemberID, Max(E.Date) as LastDate
FROM Event as E
GROUP BY E.MemberID) as Eb
On Ea.MemberID =Eb.MemberID
AND Ea.[Date] = Eb.LastDate

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[PAK]

Thanks Duane,

You put me on the right track. Here is the final query.

Regards,

Drew

SELECT Members.[First Name], Members.[Last Name], (SELECT TOP 1
EventInstances.TypeEvent
FROM EventInstances
WHERE (((EventInstances.TypeEvent) In
("Prospective","Probationary","Active","Inactive"))) AND ( [Members]![ID] =
[EventInstances]![MemberID] )
ORDER BY EventInstances.EventDate DESC AS RecentEvent
FROM Members
WHERE (((Members.Membership)="Active" Or (Members.Membership)="Probationary"
Or (Members.Membership)="Prospective" Or (Members.Membership)="Inactive"))
ORDER BY Members.[Last Name];