day query

  • Thread starter Thread starter JIM.H.
  • Start date Start date
J

JIM.H.

Hello,
I have a table with two fields: id and date (dd/mm/yyyy
format). I need to write a query that counts id for
Monday, Tuesday, . How can I do that?
Thanks,
Jim.
 
Hello,
I have a table with two fields: id and date (dd/mm/yyyy
format). I need to write a query that counts id for
Monday, Tuesday, . How can I do that?
Thanks,
Jim.
Click to expand...

Jim,
To count how many Mondays, Tuesdays, etc., you can use a Select
Count(*) query within the Select query.
Assuming Sunday is the first day of the week, Monday is Day2:

SELECT DISTINCT (Select Count(*) From YourTable Where
WeekDay([ADate])=2;) AS Day2, (Select Count(*) From YourTable Where
WeekDay([ADate])=3;) AS Day3, (Select Count(*) From YourTable Where
WeekDay([ADate])=4;) AS Day4
FROM YourTable;

You can get each additional days count simply by adding on to the
query.
 
Hello,
I have a table with two fields: id and date (dd/mm/yyyy
format). I need to write a query that counts id for
Monday, Tuesday, . How can I do that?
Thanks,
Jim.
Click to expand...

Create a Query based on the table, and include a calculated field:

DayOfWeek: Weekday([Date])

This will have 1 for Sunday, 2 for Monday, and so on. If you want to
see the day name include a second calculated field

DayName: Format([Date], "dddd")

Change it to a Totals query by clicking the Greek Sigma icon (like a
sideways W); Group By DayOfWeek and DayName, and use Count as the
totals function for ID.

You may also want to add the [Date] field to the query, change its
Total function to WHERE, and put a criterion

BETWEEN [Enter start date:] AND [Enter end date:]

in order to limit the count to a particular week or other range of
dates.
 
Back
Top