summarize by week.

[ChristineMD]

I feel like there has to be a way to do this, but I sure can't figure it out. I am using MS ACCESS 2007, linking through ODBC to a MySQL database that contains order detail.


I've written a query to summarize product sales data for a single week, via parameter entry. So user puts in their starting and ending date (so it doesn't HAVE to be a week, but it usually will be) It sums the quantity ordered and the sales price of the product. No problem.

SELECT Sum(orddetail.QuantityOrdered) AS Qty, Sum([quantityordered]*[price]) AS Demand
FROM orddetail
WHERE (((orddetail.OrderDate) Between [Start Date] And [End Date]) AND ((Left([displaysku],5)) Like "*" & [Enter Item] & "*"));

NOW, user wants to show the same info for the previous week and the following week in the same query.

I thought this would get me what I'm after, but I'm still getting just one row of results. It's also losing results. If I do individual weeks, my total qty is 251. With the query below, I'm only getting 237. Weird.

 SELECT Sum(orddetail.QuantityOrdered) AS Qty, Sum([quantityordered]*[price]) AS Demand
FROM orddetail
WHERE (((orddetail.OrderDate) Between [Start Date] And [End Date]) AND ((Left([displaysku],5)) Like "*" & [Enter Item] & "*")) OR (((orddetail.OrderDate) Between (DateAdd("w",-7,[start date])) And (DateAdd("w",-7,[end date])))) OR (((orddetail.OrderDate) Between (DateAdd("w",7,[start date])) And (DateAdd("w",7,[end date]))));

So then I tried, this... getting somewhere, but in the QBE it will not allow me to specify the first day of the week as Monday, it is treating "vbMonday" as a parameter. If I go directly to SQL view, and and try to modify the Where clause below by adding the vbMonday parameter I get an "aggregate function" error. GRRR. I think something like this, where the week number is shown would work for her, but I have to be able to say the week starts on Mon not Sun.

SELECT Sum(orddetail.QuantityOrdered) AS Qty, Sum([quantityordered]*[price]) AS Demand, DatePart("ww",[orderdate]) AS [Wk#]
FROM orddetail
WHERE (((orddetail.OrderDate) Between [start] And [end]) AND ((Left([displaysku],5)) Like "*" & [Enter Item] & "*"))
GROUP BY DatePart("ww",[orderdate])

Any suggestions will be much appreciated.