Help with query

[RussG]

Hi - I have a query to return the number of transactions by each member at various venues. I have grouped by member no and added a count to return the number of attendances by each member at each venue.

What I need to do now , for each member, is to filter this further so only the venue most visited is returned (highest count for each member).

Any help would be greatly appreciated.

Thanks
RussG

 

[Rabbie]

Look up the MAX and DMAX functions in Access Help.

 

[RussG]

Thanks for your suggestions, I had already tried the Max function and have now had a look at DMax but am no further forward. I can return the max number for the domain but I need the max number for each member.

for example my query returns the count of attendances at each venue for each member.

MemNo VenNo CountOfVisits
Member1 Venue1 5
Member1 Venue2 1
Member2 Venue1 1
Member2 Venue2 3

I want to return
Member1 Venue1 5
Member2 Venue2 3

I am stuck on how to stipulate "Member" in my DMAX statement so I only return the MAX for each member.

RussG

 

[RussG]

I have solved my problem.
I created another query to find the max CountOfVisits without including the venue field and then another query where I joined the 2 queries together by joining on the MemNo and CountOfVisits fields. I knew there would be a simple solution.............

Cheers