datepart

[mikk]

I have a query that has a date range from 1/1/02 to
11/1/03. I would like to group these dates by the week
ending on a Sunday. So all transactions would be summed
by 1/5/03, 1/12/03, 1/19/03 and so on. Any thoughts on
how to write the query to pull dates like this?

 

[Brian Camire]

You might try a query whose SQL looks something like this:

SELECT
DateValue([Your Table].[Your Date Field] + 7 - Weekday([Your Table].[Your
Date Field] - 1)) AS [Week Ending],
Sum([Your Table].[Transaction Amount]) AS [Total Transaction Amount]
FROM
[Your Table]
GROUP BY
DateValue([Your Table].[Your Date Field] + 7 - Weekday([Your Table].[Your
Date Field] - 1))