Group by Day

[bbeeching]

I have an application that tracks employee "absent from work" occurances. I would like to count and sum the number of instances and on which day of the week they occur. In other words, who many times was Johnny absent on a Monday, Tuesday, etc, etc. If I group by the date field, and format for "group by day", I get one record for each group, not the expected result. Any ideas?

Thanks!
Brad Beeching

 

[andrew93]

Hi Brad
If you wrap the Weekday() function around your date field in a query (e.g. Weekday([AbsentDate]), this will give you the day number of the week (from memory 1 = Sunday). You can then use this in a crosstab query to get a count of absences by employee by day of the week. Any totals would be done a report instead of the query.
HTH, Andrew

 

[MStef]

Hello Brad!
Look at "DemoGroupDayA2000.mdb".
Here you have got "Table1", "Query1", "Module1".
You can create a report on query1.

 

[bbeeching]

Thanks! I'll give both a try. If I run into any questions, as Arnold once said... "I'll be back"!