Transpose Query into new Query

[skhaliq]

Query View
Code	Dist	RSM	Brand1	Brand2	Brand3	Brand4	Brand5	Brand6	Brand7	Brand8	Brand9	Brand10
320002	Ali Traders	Asser	2	3	5	6	7	8	9	10	4	66

<colgroup><col><col><col><col><col span="8"><col></colgroup><tbody>
</tbody>

Result
Code	Dist	RSM	SKU	Sales
320002	Ali Traders	Asser	Brand1	2
320002	Ali Traders	Asser	Brand2	3
320002	Ali Traders	Asser	Brand3	5
320002	Ali Traders	Asser	Brand4	6
320002	Ali Traders	Asser	Brand5	7
320002	Ali Traders	Asser	Brand6	8
320002	Ali Traders	Asser	Brand7	9
320002	Ali Traders	Asser	Brand8	10
320002	Ali Traders	Asser	Brand9	4
320002	Ali Traders	Asser	Brand10	66

<colgroup><col style="text-align: center;"><col style="text-align: center;"><col style="text-align: center;"><col style="text-align: center;"><col style="text-align: center;"></colgroup><tbody>
</tbody>

 

[SydneyGeek]

Is the first query a crosstab?
If so, do you just want to group and total the original data?

Denis

 

[skhaliq]

Is the first query a crosstab?
If so, do you just want to group and total the original data?

Denis

This is Simple Query, yes i just want it transpose as mentioned as result.

 

[alansidman]

If this is a simple "Select" Query, then I believe that you have a normalization issue. You should have your table holding the data set up differently.

TableName
----------
Code (PK)
Dist
RSM
Brand
Data

With your table set up this way, you can run the report as you wish. With your current set up, you will have to run a query for each Brand and then join all of the queries together in a Union Query.

 

[SydneyGeek]

As Alan points out, your data structure is going to cause you trouble.

To create the query using your current data structure, you would need to do something like:

SELECT Code, Dist, RSM, "Brand1" AS Brand, Brand1 As Amount
UNION
SELECT Code, Dist, RSM, "Brand2" AS Brand, Brand2 As Amount
UNION
SELECT Code, Dist, RSM, "Brand3" AS Brand, Brand3 As Amount
FROM YourTable

... Extending the pattern for all of the brands. When a new brand is added you will need to rewrite / adjust the query to suit.
By using Alan's suggested structure you just need to do the following:
1. Add all fields to the grid
2. Click the big Sigma button to make it a Totals query
3. In the Totals row, change Data from Group By to Sum.

When new brands are added, the query just handles it. No rewriting required.

Denis