Display totals for each week

[codegirl]

To simplify my question, say I have a table called Invoices and the only two fields are Invoice_Date and Total. I want to display a list which lists the week ending date and the sum of the invoice totals for the week. The list needs to contain every week, not just the current week. Is there a way to do this in SQL, or should I do it in my VB code? Thanks!!

 

[PaulBricker]

You will need a Totals query. In your query put this expression in the first column
MyWeek:[Invoice_Date] - Weekday([Invoice_Date]) +1
then in the next column add your Total field
Turn on the Totals, View...Totals on the menu bar. Group By MyWeek and Sum the Total field.

This should get you close.

Paul

 

[codegirl]

Actually I need a SQL command to put in my VB program... do you know how to convert that to SQL? Thanks!

 

[r937]

if you want a week to be shown even if there aren't any invoices for that week, you need a left outer join

see Grouping output by week

http://tinyurl.com/4ble

rudy

http://rudy.ca/

 

[PaulBricker]

Dim strSQL as String
strSQL = "SELECT [Invoice_Date]-Weekday([Invoice_Date])+7 AS MyWeek, Sum([TableName].Total) AS SumOfTotal
FROM [TableName]
GROUP BY [Invoice_Date]-Weekday([Invoice_Date])+7"

Sorry I read it as Beginning of Week at first. To get the end of week you add 7 instead of 1.

This will not show weeks where there were no invoices.

Paul

 

[codegirl]

Thanks, Paul! Just what I needed!