Query - Crosstab ?

[rob4465 via AccessMonster.com]

Hi Good Morning,

I currently have a query that displays six columns:

Company | Product1 | Product2 | Product3 | Product4 | Product5

So one row displays one company with five different products.

What I need is some kind of query that will display the data like this:


Company | Product1

Company | Product2

Company | Product3

Company | Product4

Company | Product5


Basically the same company would be shown five times (on different rows) with
the different Products on each.

I'm sure there's a way to do this with a crosstab but I could do with some
new ideas as I'm in a rut !

Thanks very much in advance for your assistance.

 

[Allen Browne]

You need the reverse of a crosstab, so perhaps a UNION query.

Something like this:

SELECT Company, Product1 AS Product, "1" AS Source FROM Table1
UNION ALL
SELECT Company, Product2 AS Product, "2" AS Source FROM Table1
UNION ALL
SELECT Company, Product3 AS Product, "3" AS Source FROM Table1
UNION ALL
SELECT Company, Product4 AS Product, "4" AS Source FROM Table1
UNION ALL
SELECT Company, Product5 AS Product, "5" AS Source FROM Table1;

Ultimately, the result you are looking for is the way the table should have
been designed. (You probably know that.)

 

[rob4465 via AccessMonster.com]

Thanks very much Allen,

That's got me back on track.
Rob

You need the reverse of a crosstab, so perhaps a UNION query.

Something like this:

SELECT Company, Product1 AS Product, "1" AS Source FROM Table1
UNION ALL
SELECT Company, Product2 AS Product, "2" AS Source FROM Table1
UNION ALL
SELECT Company, Product3 AS Product, "3" AS Source FROM Table1
UNION ALL
SELECT Company, Product4 AS Product, "4" AS Source FROM Table1
UNION ALL
SELECT Company, Product5 AS Product, "5" AS Source FROM Table1;

Ultimately, the result you are looking for is the way the table should have
been designed. (You probably know that.)

Hi Good Morning,

[quoted text clipped - 24 lines]

Thanks very much in advance for your assistance.