GROUP BY month and year

[madt]

Hello,

I'm trying to build a SQL statement with which I can group the table entries according to month and year, and not according to the whole date. Eg, all entries entered in September 2004 must be in one group. At the moment I can only get them to group by the whole date, thus entries entered on 13 September 2004 will be grouped, but entries on 13 and 24 September will not be grouped together.
I'm trying to build links for each month of a year, so that users can click on a link and view all the entries for that specific month and year.

Am I being clear here? I'm getting more and more confused....

I'm using ASP with an Access database.

This is the SQL code:

SELECT format(arcDate,'mmmm yyyy') AS fdate, format(arcDate,'dd/mm/yyyy') AS ndate
FROM archive
WHERE arcCatagory = 2
GROUP BY arcDate
ORDER BY arcDate DESC

which groups by the whole date.

fdate is for the links, and ndate is for the querystring value that gets passed when a link is clicked.
arcDate is the date the entry got entered into the database.

Now when I try to group by only the month and year:

SELECT format(arcDate,'mmmm yyyy') AS fdate, format(arcDate,'dd/mm/yyyy') AS ndate
FROM archive
WHERE arcCatagory = 2
GROUP BY format(arcDate,'mmmm yyyy')
ORDER BY arcDate DESC

I get this lovely error:

[Microsoft][ODBC Microsoft Access Driver] You tried to execute a query that does not include the specified expression 'format(arcDate,'dd/mm/yyyy')' as part of an aggregate function.

Helep? What am I doing wrong?
Helep Helep!

 

[Jon K]

SELECT format(arcDate,'mmmm yyyy') AS fdate, format(arcDate,'dd/mm/yyyy') AS ndate
FROM archive
WHERE arcCatagory = 2
GROUP BY format(arcDate,'mmmm yyyy')
ORDER BY arcDate DESC

Access was complaining that you used arcDate in the Order By clause but arcDate was not in the Group By clause.


And it's not clear exactly what you wanted the query to return. If you want to group 13 September 2004 and 24 September 2004 as one record of "September 2004", then you just won't be able to return format(arcDate,'dd/mm/yyyy') AS ndate in the record.
.