Crosstab?

  • Thread starter Thread starter Guest
  • Start date Start date
G

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.
 
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.
 
Thank you very much Karl. These are both excellent solutions. I'll play a
little and see which the user prefers.

KARL DEWEY said:
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


Phil Trumpy said:
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.
Click to expand...
Click to expand...
 
Back
Top