It would be really quick to answer this if you posted the design of the tables, as rpeare asked. The SQL of the query that is working would also help.
I'm going to create you a quick fake query based on two made-up tables. You said you have an entry for each time a visitor was logged into the system. I'm going to assume that those visits are logged by each club, and have a date-timestamp, which may or may not have the exact time on it.
Code:
tblVisits
VisitID
ClubID
VisitTime
tblClub
ClubID
ClubName
Here's a query that will calculate your visitors for each month for each club.
Code:
SELECT Month(TV.VisitTime), TC.ClubName, Count(TV.VisitID)
FROM tblClubs AS TC, tblVisits AS TV
WHERE TC.ClubID = TV.ClubID
GROUP BY Month(TV.VisitTime), TC.ClubName
You could also add lines after the WHERE clause to limit the reported data to the prior month or to a limited date range
Code:
limit to prior month:
AND Month(TV.VisitTime) = Month(DateAdd("M",-1,Date))
AND Year(TV.VisitTime) = Year(DateAdd("M",-1,Date))
limit to prior three months:
AND TV.VisitTime >= DateSerial(Year(Date),Month(date)-3,1)
AND TV.VisitTime < DateSerial(Year(Date),Month(date),1)
This query should provide a Sum Report listing something like this
Code:
03 Club Bazanga 127
03 Club Chartreuse 412
03 Club Nowhere 12
...
04 Club Bazanga 222
04 Club Chartreuse 112
04 Club Nowhere 3
...