Crosstab?

[Guest]

I have many products, each which can appear in multiple catalogs. I want to
show the product numbers down the left side and then show each catalog that
the product appears in all in one row. It should look similar to this:

Product Catalog Prefixes
1 DD HH YY
2 AA DD GG
3 AA DD YY
4 BB DD EE

I'm using Access 2003. I may not have explained this very well. Sorry.
Thanks in advance for any help.

 

[Guest]

Yes but with only two fields you have to do something extra.
One way outputs the data like this --
Product AA BB DD EE GG HH YY
1 X X X
2 X X X
3 X X X
4 X X X

TRANSFORM First(IIf([CATALOG] Is Null,"","X")) AS Expr1
SELECT PHIL.PRODUCT
FROM PHIL
GROUP BY PHIL.PRODUCT
PIVOT PHIL.CATALOG;

The other way is to use a make table query that outputs a number for each
catalog that a product has so it looks like this --
PRODUCT CATALOG COUNT
1 AA 1
1 BB 2
1 GG 3
2 AA 1
2 DD 2
2 HH 3
3 HH 1
3 YY 2
4 AA 1
Then this table will have the necessary three fields.

 

[Guest]

Thank you very much Karl. These are both excellent solutions. I'll play a
little and see which the user prefers.

Yes but with only two fields you have to do something extra.
One way outputs the data like this --
Product AA BB DD EE GG HH YY
1 X X X
2 X X X
3 X X X
4 X X X

TRANSFORM First(IIf([CATALOG] Is Null,"","X")) AS Expr1
SELECT PHIL.PRODUCT
FROM PHIL
GROUP BY PHIL.PRODUCT
PIVOT PHIL.CATALOG;

The other way is to use a make table query that outputs a number for each
catalog that a product has so it looks like this --
PRODUCT CATALOG COUNT
1 AA 1
1 BB 2
1 GG 3
2 AA 1
2 DD 2
2 HH 3
3 HH 1
3 YY 2
4 AA 1
Then this table will have the necessary three fields.

--
KARL DEWEY
Build a little - Test a little

I have many products, each which can appear in multiple catalogs. I want to
show the product numbers down the left side and then show each catalog that
the product appears in all in one row. It should look similar to this:

Product Catalog Prefixes
1 DD HH YY
2 AA DD GG
3 AA DD YY
4 BB DD EE

I'm using Access 2003. I may not have explained this very well. Sorry.
Thanks in advance for any help.