Normalizing Monthly Sales Data

  • Thread starter Thread starter Phil Smith
  • Start date Start date
P

Phil Smith

Ok, what I have:

A Crosstab query which returns each item, and in yyyy-mm format a
monthly sales totals in crosstab itself.


Item 2007-1 2007-2 2007-3 2007-4 2007-5 2007-6
Coat 10 13 5
Hat 12 15 22 24
Socks 10 21 45 20

What I want would look more like:


Item Month1 Month2 Month3 Month4 Month5 Month6
Coat 10 13 5
Hat 12 15 22 24
Socks 10 21 45 20 24

We did not sell socks until February, and Hats until March but I want to
line up monthly sales based on the first month each product sold.

This will be the basis for some graphs.

Any Ideas?
 
Here it is, with extraneous stuff removed.

TRANSFORM Sum(invoice_d.ship_qty) AS SumOfMonth_ship_qty1
SELECT invoice_d.item_id, Sum(invoice_d.ship_qty) AS SumOfship_qty,
Min(invoice_h.invoice_date) AS MinOfinvoice_date
FROM invoice_h INNER JOIN invoice_d ON invoice_h.invoice_id =
invoice_d.invoice_id
GROUP BY invoice_d.item_id
ORDER BY invoice_d.item_id
PIVOT Year([invoice_h]![invoice_date]) & "-" &
IIf(Len(Month([invoice_h]![invoice_date]))=1,"0","") &
Month([invoice_h]![invoice_date]);
 
You left out Invoice. Try this ---
TRANSFORM Sum(invoice_d.[ship_qty]) AS SumOfship_qty
SELECT invoice_d.[item_id]
FROM invoice_d INNER JOIN (invoice_h INNER JOIN invoice_min ON
invoice_h.item_id = invoice_min.item_id) ON invoice_d.Invoice =
invoice_h.Invoice
GROUP BY invoice_d.[item_id]
PIVOT "Month " & DateDiff("m",[MinOfinvoice_date],[invoice_date])+1;

--
KARL DEWEY
Build a little - Test a little


Phil Smith said:
Here it is, with extraneous stuff removed.

TRANSFORM Sum(invoice_d.ship_qty) AS SumOfMonth_ship_qty1
SELECT invoice_d.item_id, Sum(invoice_d.ship_qty) AS SumOfship_qty,
Min(invoice_h.invoice_date) AS MinOfinvoice_date
FROM invoice_h INNER JOIN invoice_d ON invoice_h.invoice_id =
invoice_d.invoice_id
GROUP BY invoice_d.item_id
ORDER BY invoice_d.item_id
PIVOT Year([invoice_h]![invoice_date]) & "-" &
IIf(Len(Month([invoice_h]![invoice_date]))=1,"0","") &
Month([invoice_h]![invoice_date]);



KARL said:
Post the SQL of the crosstab you are presently using.
Click to expand...
Click to expand...
 
Ok, by the numbers.

1) Thank you. I appreciate the response.
2) I am not sure what you mean by "left out Invoice." The query works
as is, giving me the information I need, just not 'lined up" as I need.
I wonder if you are seeing something I am not, or misinterpreting
something.
3) Your query appears to treat Min(Invoice_h.[invoice_date]), an
aggregated data field, as a table called invoice_min. Since there is no
such table, trying to join to invoice_min.item_id is not going to work.

However, I think I see where you are going, and if I did pull that MIN
into another query, so that I could reference it in this manner, I think
I might get what I am looking for.

I will play around with it.
 
I forgot to post a query that is needed ---
invoice_min --
SELECT invoice_h.item_id, Min(invoice_h.invoice_date) AS MinOfinvoice_date
FROM invoice_h
GROUP BY invoice_h.item_id;

--
KARL DEWEY
Build a little - Test a little


Phil Smith said:
Ok, by the numbers.

1) Thank you. I appreciate the response.
2) I am not sure what you mean by "left out Invoice." The query works
as is, giving me the information I need, just not 'lined up" as I need.
I wonder if you are seeing something I am not, or misinterpreting
something.
3) Your query appears to treat Min(Invoice_h.[invoice_date]), an
aggregated data field, as a table called invoice_min. Since there is no
such table, trying to join to invoice_min.item_id is not going to work.

However, I think I see where you are going, and if I did pull that MIN
into another query, so that I could reference it in this manner, I think
I might get what I am looking for.

I will play around with it.


KARL said:
You left out Invoice. Try this ---
TRANSFORM Sum(invoice_d.[ship_qty]) AS SumOfship_qty
SELECT invoice_d.[item_id]
FROM invoice_d INNER JOIN (invoice_h INNER JOIN invoice_min ON
invoice_h.item_id = invoice_min.item_id) ON invoice_d.Invoice =
invoice_h.Invoice
GROUP BY invoice_d.[item_id]
PIVOT "Month " & DateDiff("m",[MinOfinvoice_date],[invoice_date])+1;
Click to expand...
Click to expand...
 
Back
Top