Crosstab Query Question

[DLIP]

I have created a crosstab query that shows in column A the SKU of a product
(ex. AAA011/3) The cross tab columns are different distribution locations,
and the values being portrayed are the number of items in stock at each
location. My current query lists a single SKU multiple times because it is
in many different locations (i.e. multiple records for each SKU) I want to
list only the SKU once and show the total number of items in each warehouse
on a single row.

Currently, the SKU column is set up as follows:
Field: SKU
Table: Table1
Total: Group By
Crosstab: Row Heading

Any ideas?

 

[KARL DEWEY]

Open the query in design view, click on menu VIEW - SQL View, highlight all,
copy, and paste in a post.

 

[DLIP]

TRANSFORM Sum([June Aging Report].Value) AS SumOfValue
SELECT [June Aging Report].[Banner Name], [June Aging Report].Item
FROM [June Aging Report]
WHERE ((([June Aging Report].Site) Like "3216US" Or ([June Aging
Report].Site)="3205US" Or ([June Aging Report].Site)="3210US" Or ([June Aging
Report].Site)="3211US" Or ([June Aging Report].Site)="3212US" Or ([June Aging
Report].Site)="3220US" Or ([June Aging Report].Site)="3222US" Or ([June Aging
Report].Site)="3226US" Or ([June Aging Report].Site)="3231US" Or ([June Aging
Report].Site)="3800US" Or ([June Aging Report].Site)="3805US") AND (([June
Aging Report].Line) Like "1702") AND (([June Aging Report].[Months
Remaining]) Like "3"))
GROUP BY [June Aging Report].[Banner Name], [June Aging Report].Item, [June
Aging Report].Quantity
PIVOT [June Aging Report].Site;

 

[DLIP]

Oh - And instead of SKU - the column Title is Item

 

[KARL DEWEY]

Group By, not Order By.
--
KARL DEWEY
Build a little - Test a little

TRANSFORM Sum([June Aging Report].Value) AS SumOfValue
SELECT [June Aging Report].[Banner Name], [June Aging Report].Item
FROM [June Aging Report]
WHERE ((([June Aging Report].Site) Like "3216US" Or ([June Aging
Report].Site)="3205US" Or ([June Aging Report].Site)="3210US" Or ([June Aging
Report].Site)="3211US" Or ([June Aging Report].Site)="3212US" Or ([June Aging
Report].Site)="3220US" Or ([June Aging Report].Site)="3222US" Or ([June Aging
Report].Site)="3226US" Or ([June Aging Report].Site)="3231US" Or ([June Aging
Report].Site)="3800US" Or ([June Aging Report].Site)="3805US") AND (([June
Aging Report].Line) Like "1702") AND (([June Aging Report].[Months
Remaining]) Like "3"))
GROUP BY [June Aging Report].[Banner Name], [June Aging Report].Item, [June
Aging Report].Quantity
PIVOT [June Aging Report].Site;

Open the query in design view, click on menu VIEW - SQL View, highlight all,
copy, and paste in a post.

 

[KARL DEWEY]

The problem is Quanity in the Group By.
--
KARL DEWEY
Build a little - Test a little

TRANSFORM Sum([June Aging Report].Value) AS SumOfValue
SELECT [June Aging Report].[Banner Name], [June Aging Report].Item
FROM [June Aging Report]
WHERE ((([June Aging Report].Site) Like "3216US" Or ([June Aging
Report].Site)="3205US" Or ([June Aging Report].Site)="3210US" Or ([June Aging
Report].Site)="3211US" Or ([June Aging Report].Site)="3212US" Or ([June Aging
Report].Site)="3220US" Or ([June Aging Report].Site)="3222US" Or ([June Aging
Report].Site)="3226US" Or ([June Aging Report].Site)="3231US" Or ([June Aging
Report].Site)="3800US" Or ([June Aging Report].Site)="3805US") AND (([June
Aging Report].Line) Like "1702") AND (([June Aging Report].[Months
Remaining]) Like "3"))
GROUP BY [June Aging Report].[Banner Name], [June Aging Report].Item, [June
Aging Report].Quantity
PIVOT [June Aging Report].Site;

Open the query in design view, click on menu VIEW - SQL View, highlight all,
copy, and paste in a post.