Count number of distinct weekdays in Group By (1 Viewer)

[troubledesire]

Hi all,

I have a database that tracks the number of visits to a museum--each time someone visits I add an entry to tblVisit with the User's ID and a Date/timestamp. Now I want to calculate the average number of visitors by the day of the week. I can calculate total number of visits by day easily, using a Group By for the day of the week:
DayOfWeek: Format([VisitDatetime],"ddd")

This plus a Count in my query yields summaries:
Monday 314
Tuesday 400
Wed 323
and so on. But I want the AVERAGE number of visitors by each week day, which means I need to know how many (different) Mondays were counted, how many Tuesdays, etc. So if the above figure included 10 dates which were Mondays, my average number of visitors for a Monday would be 31.4.

Any ideas how I would do that?

Many thanks in advance for any help!

 

[khawar]

Can you describe your table columns

 

[Brianwarnock]

If your dates span complete weeks then you just need to calculate the number of weeks, but difficulties arise if there are partial weeks.

Brian

 

[troubledesire]

Yes, it's complete weeks. I'll try counting the number of weeks, that seems the simplest solution--thanks!

 

[mwtex]

Not enough coffee yet, but hopefully this is helpful.

Everything i can think of takes two query steps:
query A: group(date) sum(visits)

query B: group(DayOfWeek(A.day) count(DayOfWeek(A.date) sum(A.visits) average: visits/count....

The key is to retain info that keeps each day of the week unique. Query A gets you a summary of visits per date. Query B uses A as the source table. You format the date as DayOfWeek & use it twice - once to group by, then as a count(DayOfWeek) that gives you the number of mondays.. sum(visits) gives you what you need to calculate AVERAGE = sum(visits)/count(DayOfWeek)



You could also concatenate the Day & date to get your unique day of week. IE ["MONDAY" & "_" & DATE] ---> Monday_3/31/10


Hope this helps (or at least makes sense), I'll be interested to see the elegantly simple answers the experts around here always seem to have!
Now off to find that coffee!
-marc