Normalizing Monthly Sales Data

[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?

 

[KARL DEWEY]

Post the SQL of the crosstab you are presently using.

 

[Phil Smith]

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 DEWEY]

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

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]);

Post the SQL of the crosstab you are presently using.

 

[Phil Smith]

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 DEWEY]

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

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.

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;