Query To Sum Daily Sales Figures By Weekend and Weekdays

[deluga.69]

I'm tracking box office grosses by movie, by date. I need a query that will sum these grosses by weekend (Fri/Sat/Sun) and Weekdays (Mon/Tue/Wed/Thu). I do have a separate table with the start and end dates for all weekend and weekday ranges going back to 2009.



I am so new to Access I don't know anything and I'm sure this is a simple thing. Please help!

[June7]

This is an aggregate GROUP BY (Totals) query. Use the query builder to help design. Access Help has more guidelines.

Do you want data aggregated to 2 values: Mon-Thu and Fri-Sun? Will need to use functions for manipulating date/time values.

A basic SQL statement for producing the 2 values:

SELECT Sum(IIf(Weekday([DateField],2)>4, [Receipts], 0)) AS Weekend, Sum(IIf(Weekday([DateField],2)<5,[Receipts], 0)) AS Weekday FROM tablename;

Add WHERE and/or GROUP BY clauses as needed. Such as:

SELECT Year([DateField]) AS Yr, Sum(IIf(Weekday([DateField],2)>4, [Receipts], 0)) AS Weekend, Sum(IIf(Weekday([DateField],2)<5,[Receipts], 0)) AS Weekday FROM tablename GROUP BY Year([Datefield]);

The only real issue is if change of year breaks one of the periods. If the period must be kept intact, which year should it fall in?

[deluga.69]

Thanks June! I will try this when I get home tonight.

[deluga.69]

I used your SQL statement last night and it worked. Thanks so much for the help. Now I need to figure out exactly what it's doing/saying so I can use it on other queries.

Thanks again!