Crosstab Query?

[Guest]

Hi,

Struggling badly with this and would appreciate some help. I have 20
different sales items and I want to monitor the weekly total sales quantity
of each of these items over the last 13 weeks. I want to be able to generate
trend graphs for weekly sales of each item so I think I need a (crosstab?)
query with each row being an individual item and each column being a week
number. I want the value shown at the intersection of each row and column to
be the weekly sales of that item expressed as a percentage of the total sales
of all 20 items in that week. I don't know how to do this - can anyone help?

TIA

 

[Guest]

You have weeknumbers in your table or just dates or are dates only available
in another table (header table)?

- Raoul

 

[Guest]

My table has the date each sale was made. I use DatePart() in my query to
determine week number from the actual sale date.

 

[Guest]

I think this is what you want

TRANSFORM Sum(Qty)/[Total Of Qty] AS SumOfQty
SELECT Product, Sum(Qty) AS [Total Of Qty]
FROM YourTable
WHERE Week Between 200510-13 And 200510
GROUP BY Product
PIVOT Week

The weeks you can parameterize with a function or a reference to a form.

- Raoul

 

[Guest]

Thanks Raoul,

I have a problem with the top line in this - when I try to run the query it
won't run because it does not recognise [Total Of Qty] as a valid field. Can
you explain where this field comes from?

I think this is what you want

TRANSFORM Sum(Qty)/[Total Of Qty] AS SumOfQty
SELECT Product, Sum(Qty) AS [Total Of Qty]
FROM YourTable
WHERE Week Between 200510-13 And 200510
GROUP BY Product
PIVOT Week

The weeks you can parameterize with a function or a reference to a form.

- Raoul

My table has the date each sale was made. I use DatePart() in my query to
determine week number from the actual sale date.

 

[Guest]

it comes from this line

SELECT Product, Sum(Qty) AS [Total Of Qty]


try this



TRANSFORM Sum(Qty)/TotalOfQty AS SumOfQty
SELECT Product, Sum(Qty) AS TotalOfQty
FROM YourTable
WHERE Week Between 200510-13 And 200510
GROUP BY Product
PIVOT Week

Thanks Raoul,

I have a problem with the top line in this - when I try to run the query it
won't run because it does not recognise [Total Of Qty] as a valid field. Can
you explain where this field comes from?

I think this is what you want

TRANSFORM Sum(Qty)/[Total Of Qty] AS SumOfQty
SELECT Product, Sum(Qty) AS [Total Of Qty]
FROM YourTable
WHERE Week Between 200510-13 And 200510
GROUP BY Product
PIVOT Week

The weeks you can parameterize with a function or a reference to a form.

- Raoul

My table has the date each sale was made. I use DatePart() in my query to
determine week number from the actual sale date.

:

You have weeknumbers in your table or just dates or are dates only available
in another table (header table)?

- Raoul

:

Hi,

Struggling badly with this and would appreciate some help. I have 20
different sales items and I want to monitor the weekly total sales quantity
of each of these items over the last 13 weeks. I want to be able to generate
trend graphs for weekly sales of each item so I think I need a (crosstab?)
query with each row being an individual item and each column being a week
number. I want the value shown at the intersection of each row and column to
be the weekly sales of that item expressed as a percentage of the total sales
of all 20 items in that week. I don't know how to do this - can anyone help?

TIA