Group column by day...

[Aerodyne]

Hi all,

I want to group a "Date/Time" Column by the date not time... the problem is I can group them, but because the time is envloved they don't group by the day.

I want a query that can produce a table with 31 days in it and I want to group each day seperatley w/o the time. And find the total row count of another column by date.

Also if it can be done in the Query design view... that would be helpfull.

MS Access 2002

Thanks In Advance

 

[KenHigg]

Use the datepart() function to extract the day then use this group on.

kh

 

[DLB]

In the Totals query, you can group by DateValue([yourDateTimefield])

 

[Aerodyne]

Group by day

Thanks for both your replies...

Hi DLB, How would I accomplish this in SQL statemant since I can't find the DateValue in the drop down list?

 

[KenHigg]

See example.

kh

 

[DLB]

Put this in a column in the query grid:

Field: Date: DateValue([DateTimeField])

Total: Group By


See the query in the attached database.

 

[KenHigg]

DLB - Your example is probably what he really wants. It's just that he said 31 days and I figured he wanted all the 1st day of the months to be counted as one...

Guess we'll see...

kh

 

[Aerodyne]

Group by day

Thanks you both for the examples...
Yeah, DLB's one is closer to what I wanted to do, but atleast I know how to use the DatePart() function if I need to use that in the future.

Much obliged to both of you.