Crosstab query w/ multiple values....

[MSherfey]

I've been trying to create a crosstab query with multiple values with no luck.

As was suggested in threads in this forum, as well as following Microsoft's suggestion, I first created two different crosstab queries; 1 for count and 1 for sum. I then made a third query and joined the first two based on Geo (one of my fields). I then added the year field from each to the query and ran.

Instead of getting the count followed by the sum, I received a record for each Geo followed by all the combinations of counts and sums.

Geo1 C1 S1
Geo1 C1 S2
Geo1 C1 S3
Geo1 C2 S1
Geo1 C2 S2
Geo1 C3 S3
Geo2 C1 S1
Geo2 C1 S2
Geo3 C1 S3
etc.....

Instead of 18 rows (3 geos and 6 sub categories) I end up with 108 (each combination). What am I missing here? yeah yeah, I know get in line

 

[MSherfey]

Sorry to bother the thread, but i just figured it out.

My join was only on one of the rows in the crosstab. I went ahead and did a join for each category in the crosstab queries and it is working great!

Live and learn (embarrassed smile)