Breaking out sales data

[Fanew3]

Hello, I am trying to build a weekly sales report using data from several
different table. I have a style table (basic style info), a sku table
(multiple records for each style in the style table), and a sales table (data
is at the sku level). I'm not having any trouble getting sales rolled up to
the style level. My issue is getting that sales data broken out for multiple
time frames (each sales record includes a Beginning of week date).

Here's an example of my data sets:
Style Table:

Style Number Style Description Vendor Name
10-207 Candle Foust Industries
60-671 "Fitted T-Shirt" Raglan Brothers

Sku Table:

Sku ID Sku Description Cost Retail Style ID Color Color2 Size
10-207/IVO Candle Ivory $8.40 $24.00 10-207 Ivory
10-207/WHT Candle White $8.40 $24.00 10-207 White
60-671/PNK/L Pink Large $10.75 $29.00 60-671 Pink L
60-671/PNK/M Pink Medium $10.75 $29.00 60-671 Pink M
60-671/PNK/S Pink Small $10.75 $29.00 60-671 Pink S
60-671/PNK/XL Pink Extra Large $10.75 $29.00 60-671 Pink XL
60-671/WHT/L White Large $10.75 $29.00 60-671 White L
60-671/WHT/M White Medium $10.75 $29.00 60-671 White M
60-671/WHT/S White Small $10.75 $29.00 60-671 White S
60-671/WHT/XL White Extra Large $10.75 $29.00 60-671 White XL

And the Sales Table:

Date Sku ID Unit Sales Dollar Sales Cost Sales
6/3/2007 10-207/IVO 1 $24.00 $8.40
6/10/2007 10-207/IVO 1 $24.00 $8.40
6/17/2007 10-207/IVO 1 $24.00 $8.40
6/3/2007 10-207/WHT 2 $48.00 $16.80
6/10/2007 10-207/WHT 1 $24.00 $8.40
6/17/2007 10-207/WHT 2 $48.00 $8.40

Any suggestions?

 

[KARL DEWEY]

What do you want to be in the output? Layout the fields.

 

[Fanew3]

Karl,

Here's the layout I'm invisioning:

Style Style Descrp Week 1... Week 4 Ttl Ttl $ Ttl Cost

So the product specific data followed by the last 4 weeks of sales (in
units) and then a 4 week total in units, sales $, and cost $ (I'll calc a
margin from those components). Each style will be grouped by department and
class (though I have represented that info in the example, I can do that no
problem). Let me know if you need any additional info.

Thanks,
Frank

 

[KARL DEWEY]

Your SKU ID field contains three sets of data so it can not be joined in a
query without parsing first.
I'll work on it later.

 

[KARL DEWEY]

The first two queries parse the SKU from the SKU ID.
Sales_SKU ---
SELECT Left([Sku ID],6) AS SKU, Sales.BeginDate, Sales.[Sku ID], Sales.[Unit
Sales], Sales.[Dollar Sales], Sales.[Cost Sales]
FROM Sales;

SKU_1 ---
SELECT Left([Sku ID],6) AS SKU, SKU.[Sku ID], SKU.[Sku Description],
SKU.Cost, SKU.Retail, SKU.[Style ID], SKU.Color, SKU.Color2, SKU.Size
FROM SKU;

SELECT Date()-Weekday(Date()) AS [Week Ending], Style.[Style Number],
Style.[Style Description], Sum(IIf([BeginDate] Between
DateAdd("d",-6,Date()-Weekday(Date())) And Date()-Weekday(Date()),[Unit
Sales],0)) AS [Last Week], Sum(IIf([BeginDate] Between
DateAdd("d",-13,Date()-Weekday(Date())) And
DateAdd("d",-7,Date()-Weekday(Date())),[Unit Sales],0)) AS [Week 1 before],
Sum(IIf([BeginDate] Between DateAdd("d",-20,Date()-Weekday(Date())) And
DateAdd("d",-14,Date()-Weekday(Date())),[Unit Sales],0)) AS [Week 2 before],
Sum(IIf([BeginDate] Between DateAdd("d",-27,Date()-Weekday(Date())) And
DateAdd("d",-21,Date()-Weekday(Date())),[Unit Sales],0)) AS [Week 3 before],
Sum(Sales_SKU.[Unit Sales]) AS [Total Unit Sales], Sum(Sales_SKU.[Dollar
Sales]) AS [Total Dollar Sales], Sum(Sales_SKU.[Cost Sales]) AS [Total Cost]
FROM (Style LEFT JOIN Sales_SKU ON Style.[Style Number] = Sales_SKU.SKU)
LEFT JOIN SKU_1 ON Style.[Style Number] = SKU_1.SKU
WHERE (((Sales_SKU.BeginDate)<=Date()-Weekday(Date())))
GROUP BY Date()-Weekday(Date()), Style.[Style Number], Style.[Style
Description];