Counting Days of the Week

[losingmymind]

Hi there. A few months ago I asked this question and tried the suggestions but got confused...then reassigned so I haven't worked on this in awhile. Anyway, here's what I'm trying to do. I want to count how many events took place on a "Monday" or a "Tuesday" etc. Doesn't matter the actual date, I just want to be able to count how many events (records) were created on a Monday, Tuesday, etc. I got the query to group by date and count how many events took place on that date but I need to add up all the "Monday" events and so on. I did create a column on my table to convert the date to the day. That seems to work fine but why can't I count how many "Mondays" are in that column? It's because it's still a "date" and not text, huh? Anyway around this? Any help would be greatly appreciated. :-)

[AlexHedley]

If you create a Query.
Add a new column using Weekday() passing in your Date Field.

Weekday ( date_value, [firstdayofweek] )

Constant     ValueExplanation
vbUseSystem0Use the NLS API settings
vbSunday1Sunday (default used)
vbMonday2Monday
vbTuesday3Tuesday
vbWednesday4Wednesday
vbThursday5Thursday
vbFriday        6Friday
vbSaturday7Saturday

Then use an Aggregate Query to count how many of Each number.

[losingmymind]

Thanks for the reply but I'm sorry, it makes no sense to me. I understand adding a new column in my query but where exactly do I type in Weekday()? And what is all that other stuff? Does it go in the query? If so, where? I don't understand code yet, I just work with Macros and Expressions. Thank you :-)

[AlexHedley]

More info here:
http://office.microsoft.com/en-gb/ac...001228933.aspx

The mini table was a list you could use for firstdayofweek argument.

For the Query

  WD: Weekday([YourDateField])

Replace [YourDateField] with the name of your Date Field from you Table.

The WD: is called an Alias which is giving this new Field a name. Call it what you wish.

[June7]

Losing, what expression did you use to calculate day of week?

Post the SQL statement of the query you attempt.

I think you will need to use that query as source for another query that sums the count of events by day of week.

Or use that query as the source for a report that groups by day of week and do the sum in a textbox in group footer.

A report will allow listing detail records as well as summary calcs.

[losingmymind]

Hey, I finally figured it out!! OMG, that was torture...lol... This is what I did, I created a query that pulled only records between a date range specified on a form. Then in the query design I "counted" those records and finally in a separate column in the query design I typed in the field row "Day of Week: Format([Date_of_Event],"dddd")" and selected "group by" in the Total row. And it worked... :-) But now I have one problem which I hope is minor. I want the results to be listed in day order (Sunday, Monday, Tuesday, etc.). Right now I can either have no sorting which puts them alphabetically, or I can sort by the "count" (highest to lowest or vice versa). Any idea how to sort by day order so my report will look normal instead of Friday, Monday, Saturday, etc.)??? Thank you :-)

[June7]

As Alex suggested earlier, extract the day number and sort by that. Include it in the GROUP BY as well.

WeekDay([Date_of_Event])

[losingmymind]

Very cool, that worked...THANK YOU SO MUCH!!!!! :-) I thought I'd never finish this darn thing...lol...

[losingmymind]

One more question if you have time....Do you know why when I total the results in the report footer the sort order defaults back to alphabetical instead of the way the query had it? Thanks :-)

[June7]

Not really, except that report sort order overrides the SORT BY in query, but no idea why a simple calc would cause that. Try setting the sort in the report.

[losingmymind]

That didn't seem to work either. I ended up creating a new query and combining the two queries to get the results I wanted. Thanks so much for your help :-)