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 qptHisAttendance

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_SiteMostVisited1") looks like this:

SELECT qptHisAttendance.MemberId, qptHisAttendance.AtDate AS AttendDate, qptHisAttendance.SiteID
FROM qptHisAttendance
GROUP BY qptHisAttendance.MemberId, qptHisAttendance.AtDate, qptHisAttendance.SiteID
HAVING (((qptHisAttendance.MemberId)=13500741) AND ((qptHisAttendance.AtDate)>=Now()-31));

Query 2 (called "qryBP_SiteMostVisited2") looks like this:

SELECT [qryBP_SiteMostVisited1].MemberId, [qryBP_SiteMostVisited1].SiteID, Count([qryBP_SiteMostVisited1].SiteID) AS CountOfSiteID
FROM [qryBP_SiteMostVisited1]
GROUP BY [qryBP_SiteMostVisited1].MemberId, [qryBP_SiteMostVisited1].SiteID;

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.

SELECT First([qryBP_SiteMostVisited2].MemberId), First([qryBP_SiteMostVisited2].SiteID), Max([qryBP_SiteMostVisited2].CountOfSiteID)
FROM qryBP_SiteMostVisited2
 

Mary