How to group by and sort by a date

[Rick Brandt]

I need to GROUP BY a date and also SORT BY the date.

But the sort order in Access treats the date as a string rather than
a date.
For example, I have this query:

SELECT Format([createdate],'short date') AS [Date],
Count(stats.statsid) AS CountOfstatsid
FROM stats
WHERE stats.URL = 'z.asp'
GROUP BY Format([createdate],'short date')
ORDER BY Format([createdate],'short date') desc;

Which reurns records in the following order:

12/1/2005 55
11/6/2005 1
11/29/2005 28
11/27/2005 33
11/26/2005 77
11/19/2005 1

How can I write query that groups by day and sorts by date?

Include an additional version of the date that will sort in chronological order.

SELECT Format([createdate],'short date') AS [Date],
Format([createdate, 'yyyymmdd') AS SortVal,
Count(stats.statsid) AS CountOfstatsid
FROM stats
WHERE stats.URL = 'z.asp'
GROUP BY Format([createdate],'short date'), Format([createdate, 'yyyymmdd')
ORDER BY Format([createdate, 'yyyymmdd') desc;

 

[Dave]

I need to GROUP BY a date and also SORT BY the date.

But the sort order in Access treats the date as a string rather than a date.

For example, I have this query:

SELECT Format([createdate],'short date') AS [Date],
Count(stats.statsid) AS CountOfstatsid
FROM stats
WHERE stats.URL = 'z.asp'
GROUP BY Format([createdate],'short date')
ORDER BY Format([createdate],'short date') desc;

Which reurns records in the following order:

12/1/2005 55
11/6/2005 1
11/29/2005 28
11/27/2005 33
11/26/2005 77
11/19/2005 1

How can I write query that groups by day and sorts by date?

Thanks
Dave

 

[Dave]

Yes this works fine

Thanks Rick

SELECT Format([createdate],'Short Date') AS [Date],
Format([createdate],'yyymmdd') AS [Date2],
Count(stats.statsid) AS CountOfstatsid
FROM stats
GROUP BY Format([createdate],'Short Date'),Format([createdate],'yyymmdd')
ORDER BY Format([createdate],'yyymmdd') DESC;

 

[John Spencer]

Add another column with just CreateDate in it. SORT by that column

Order By CreateDate

The problem is that applying the FORMAT function to the field forces its
type to a string (text) versus a datetime.

 

[Guest]

Rick, I have the same problem and tried you solution with my data:

SELECT Format([completion_date],"ww") & "-" & Format([completion_date],"yy")
AS [Completion week-year],
Format([completion_date],"yyyymmdd") AS SortVal,
Count(qry_master_data_list.[OLP Salesbundle ID]) AS [CountOfOLP Salesbundle
ID]
FROM qry_master_data_list
WHERE ((Not (qry_master_data_list.completion_date) Is Null))
GROUP BY Format([completion_date],"ww") & "-" & Format([completion_date],"yy")
ORDER BY Format([completion_date],"yyyymmdd");

but get the error:

"You tried to execute a query that does not include the specified expression
'Format([completion_date],"yyyymmdd")' as part of an aggregate function."

Can you tell me where I'm going wrong

Thanks

I need to GROUP BY a date and also SORT BY the date.

But the sort order in Access treats the date as a string rather than
a date.
For example, I have this query:

SELECT Format([createdate],'short date') AS [Date],
Count(stats.statsid) AS CountOfstatsid
FROM stats
WHERE stats.URL = 'z.asp'
GROUP BY Format([createdate],'short date')
ORDER BY Format([createdate],'short date') desc;

Which reurns records in the following order:

12/1/2005 55
11/6/2005 1
11/29/2005 28
11/27/2005 33
11/26/2005 77
11/19/2005 1

How can I write query that groups by day and sorts by date?

Include an additional version of the date that will sort in chronological order.

SELECT Format([createdate],'short date') AS [Date],
Format([createdate, 'yyyymmdd') AS SortVal,
Count(stats.statsid) AS CountOfstatsid
FROM stats
WHERE stats.URL = 'z.asp'
GROUP BY Format([createdate],'short date'), Format([createdate, 'yyyymmdd')
ORDER BY Format([createdate, 'yyyymmdd') desc;

 

[Guest]

Just spotted my error. I omitted ",
Format([qry_master_data_list].[completion_date],'yyyymmdd') " from GROUP BY.

Thanks for your previous post anyway

Rick, I have the same problem and tried you solution with my data:

SELECT Format([completion_date],"ww") & "-" & Format([completion_date],"yy")
AS [Completion week-year],
Format([completion_date],"yyyymmdd") AS SortVal,
Count(qry_master_data_list.[OLP Salesbundle ID]) AS [CountOfOLP Salesbundle
ID]
FROM qry_master_data_list
WHERE ((Not (qry_master_data_list.completion_date) Is Null))
GROUP BY Format([completion_date],"ww") & "-" & Format([completion_date],"yy")
ORDER BY Format([completion_date],"yyyymmdd");

but get the error:

"You tried to execute a query that does not include the specified expression
'Format([completion_date],"yyyymmdd")' as part of an aggregate function."

Can you tell me where I'm going wrong

Thanks

I need to GROUP BY a date and also SORT BY the date.

But the sort order in Access treats the date as a string rather than
a date.
For example, I have this query:

SELECT Format([createdate],'short date') AS [Date],
Count(stats.statsid) AS CountOfstatsid
FROM stats
WHERE stats.URL = 'z.asp'
GROUP BY Format([createdate],'short date')
ORDER BY Format([createdate],'short date') desc;

Which reurns records in the following order:

12/1/2005 55
11/6/2005 1
11/29/2005 28
11/27/2005 33
11/26/2005 77
11/19/2005 1

How can I write query that groups by day and sorts by date?

Include an additional version of the date that will sort in chronological order.

SELECT Format([createdate],'short date') AS [Date],
Format([createdate, 'yyyymmdd') AS SortVal,
Count(stats.statsid) AS CountOfstatsid
FROM stats
WHERE stats.URL = 'z.asp'
GROUP BY Format([createdate],'short date'), Format([createdate, 'yyyymmdd')
ORDER BY Format([createdate, 'yyyymmdd') desc;