About Crosstab Query

[John]

I have a query:

Transform Round(Sum(Value)) AS [Sum]
SELECT Modal, market
FROM TAble
GROUP BY Modal, market
PIVOT Company ;

There are more than 20 companies returen from the query. I
want to only list four major company
sum in the columns. I want to put all other companies' sum
into "Other" column. How can I make the following
query work.

Transform Round(Sum(Value)) AS [Sum]
SELECT Modal, market
FROM TAble
GROUP BY Modal, market
PIVOT Company IN ('Company 1','Company 2', 'Company
3', 'Company 4','Other');


??????


Thanks
John

 

[Duane Hookom]

Try
PIVOT IIf(Instr("Company 1,Company 2, Company 3, Company 4",[Company])

0,[Company], "Other")

Ideally, you should add a column to your table of companies. For instance,
you could add a yes/no field name [Other]. Check all companies that you want
to lump together. Then your query would include
PIVOT IIf([Other], "Other", [Company])
This solution makes your application "data-driven".
--
Duane Hookom
MS Access MVP

I have a query:

Transform Round(Sum(Value)) AS [Sum]
SELECT Modal, market
FROM TAble
GROUP BY Modal, market
PIVOT Company ;

There are more than 20 companies returen from the query. I
want to only list four major company
sum in the columns. I want to put all other companies' sum
into "Other" column. How can I make the following
query work.

Transform Round(Sum(Value)) AS [Sum]
SELECT Modal, market
FROM TAble
GROUP BY Modal, market
PIVOT Company IN ('Company 1','Company 2', 'Company
3', 'Company 4','Other');


??????


Thanks
John