Report question.

[PuJo]

Afternoon all,
I am trying to generate a report based off a query. The query works fine and the report is working correct.
I really don't know what kind of report this is called, but I will do my best to explain what I am trying to do.
Example:
If I keep track of 10 different tennis clubs, and everyday I list the total number of visitors visit each facility.
At the end of the month I want to make the report for each location. All I can get now is an entry for each time a visitor was logged into the system.
So at the end of the month, I would like the visitors to be totaled and listed under only one heading. Location XYZ 687 for the month.
I hope I explained it correctly.
Thanks,


Pujo

[rpeare]

what does your table look like? what are the fields?

[Dal Jeanis]

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.

tblVisits
  VisitID
  ClubID
  VisitTime

tblClub
  ClubID
  ClubName

Here's a query that will calculate your visitors for each month for each club.

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

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

03   Club Bazanga     127
03   Club Chartreuse  412
03   Club Nowhere      12
...
04   Club Bazanga     222
04   Club Chartreuse  112
04   Club Nowhere       3
...