days query

[HarryCohen]

A field in my table holds date information. I want to create a query that returns the number of times each day has an entry.

eg.
Monday 20
Tuesday 15
Wednesday 10
Thursday 24
etc

 

[jhall156]

If you mean a count of records by date try this:

SELECT yourdatefield, COUNT(yourdatefield) as CntDate
FROM yourtable
GROUP BY yourdatefield
ORDER BY yourdatefield;

If you want it counted by day of the week that can be done with the same principle, group by the day using the appropriate function (I think it's DatePart()). JHall

 

[HarryCohen]

Thanks, but no, that's not what I mean.

I want to know how many records there are for Monday, how many for Tuesday, etc. For instance I am using this to return a count by hour (for every day).

SELECT DatePart('h',mydatefield), COUNT(DatePart('h',mydatefield)) as CntHour
FROM mytable
GROUP BY DatePart('h',mydatefield);

I want the same thing, but returning the count for each day of the week. I can't just change the 'h' to a 'd' because that gives me for each day of the month (ie. 1 to 31). I want 1 to 7 (Monday - Sunday).

 

[jhall156]

you can use the datepart with "w" for weekday.

datepart("W",Date(),vbMonday)

the vbMonday tells the function to use monday as the first day of the week. If you want the output column to be text i.e. "Monday" etc, you can use a combination of summing an iif based on the datepart. The group by column can then be followed by a max of a switch function to return the day text into a column or alternatively you can just group by the numeric output and then handle the text conversion in your reports or other output.
JHall

 

[HarryCohen]

Thanks jHall.

You are using VBA to do that whereas I'm wanting pure SQL, but it was enough to point me in the right direction. SQL also has a datepart function which is mostly the same (doesn't allow to choose first day of week).

I'm running my query through Visual Basic so I can easily format my results as I needed them. I guess I should have made that clear from the start.

Anyway, got it working now, so thanks again.
Harry.