Query Grouping Help

[athyeh]

Hi Folks,

e.g.
[User ID]: xxx01; [Status]: Incomplete; [Timestamp]: 11/01/2011 12:01:00.
[User ID]: xxx02; [Status]: Incomplete; [Timestamp]: 11/01/2011 14:30:00.
[User ID]: xxx03; [Status]: Complete; [Timestamp]: 11/01/2011 15:11:00.
[User ID]: xxx04; [Status]: Complete; [Timestamp]: 12/01/2011 17:12:00.

I am trying create a query that lists the number of users at each status for each date.

e.g. for the record above, I would like the query to return:
[Date]: 11/01/2011; [Status]: Incomplete; [CountOfUser ID]: 2.
[Date]: 11/01/2011; [Status]: Complete; [CountOfUser ID]: 1.
[Date]: 12/01/2011; [Status]: Complete; [CountOfUser ID]: 1.

As you can probably tell, the problem is when the query is run the date/time field is grouped as dd/mm/yyyy hh:mm:ss .

Is there a way I can group this field as dd/mm/yyyy without having to change datatype in the table?

DateValue([TimeStamp]) returns error: "Expression to complicated"


Thanks in advance for any help you can give.

[June7]

Try:

SELECT Status, Format(Timestamp, "mm/dd/yyyy"), Count([User ID]) AS CountUser GROUP BY Status, Format(Timestamp, "mm/dd/yyyy");