CROSSTAB QUERY- Display Rows even if there is no data

[Guest]

Hi all,
I have following sample data from a Crosstab query:

PRODUCT CODE Q1 Q2 Q3 Q4
TEST1 01 10 2
TEST1 02 5 4 4
TEST1 03 2 5
TEST2 02 10 20
TEST3 01 2 5 3
TEST3 03 21 10
What I would like is for each product to have the same rows of CODE. So 01,
02, 03 should show even if there is no data for such item. So something like:

PRODUCT CODE Q1 Q2 Q3 Q4
TEST1 01 10 2
TEST1 02 5 4 4
TEST1 03 2 5
TEST2 01
TEST2 02 10 20
TEST2 03
TEST3 01 2 5 3
TEST3 02
TEST3 03 21 10

Is there a way to do this? I created a table with those 3 codes added this
table to the crosstab and join them but Show All rows from CODETABLE and only
those from TESTTABLE but doesnt' seem to work.
Any help would be very appreciated.
Thanks,
Juan

 

[Duane Hookom]

The easiest method for you might be to create a cartesian query of each
PRODUCT and CODE

SELECT PRODUCT, CODE
FROM tblProducts, tblCodes;

Then use this query in your crosstab with joins that select all records from
your cartesian query.

 

[Guest]

Hello Duane,
thanks for the info. Thought that was something I had to do.

Once again thanks alot.

Juan