Max of Count

[Bobwhosmiles]

Hi,

I use access to query a Jet database full of info about members of a club.

Each attendance by each member at any of 18 sites is recorded in a table called qptHisAttendanc e

I'm trying to create a query that will list each member ID in the first column and the site ID for the site they've visited most in the last 30 days.

So far I've got two queries. The first lists all the visits made by a member in the past 31 days. The next one takes that list and gives a count by site ID

I want a third that just shows the Member ID and the Site ID (for the site that has the highest count in query 2).

Query 1 (called "qryBP_SiteMost Visited1") looks like this:
[CODE=sql]SELECT qptHisAttendanc e.MemberId, qptHisAttendanc e.AtDate AS AttendDate, qptHisAttendanc e.SiteID
FROM qptHisAttendanc e
GROUP BY qptHisAttendanc e.MemberId, qptHisAttendanc e.AtDate, qptHisAttendanc e.SiteID
HAVING (((qptHisAttend ance.MemberId)= 13500741) AND ((qptHisAttenda nce.AtDate)>=No w()-31));[/CODE]
Query 2 (called "qryBP_SiteMost Visited2") looks like this:
[CODE=sql]SELECT [qryBP_SiteMostV isited1].MemberId, [qryBP_SiteMostV isited1].SiteID, Count([qryBP_SiteMostV isited1].SiteID) AS CountOfSiteID
FROM [qryBP_SiteMostV isited1]
GROUP BY [qryBP_SiteMostV isited1].MemberId, [qryBP_SiteMostV isited1].SiteID;[/CODE]

Can anyone help with what query 3 should look like to just display the member ID and the Site ID for the site with the highest count of attendances please?

Cheers

Bob

[Rabbit]

I only read the last few lines but can't you just use Max()?

[MMcCarthy]

Hi Bob,

Try this and see if it gives you the result you want.
[CODE=sql]
SELECT First([qryBP_SiteMostV isited2].MemberId), First([qryBP_SiteMostV isited2].SiteID), Max([qryBP_SiteMostV isited2].CountOfSiteID)
FROM qryBP_SiteMostV isited2
[/CODE]

Mary