Crosstab Query does not return all records

[alison.justice]

I have created a table that looks like this:

Category Type Level Color
CARPET LEVEL 1 SAND STONE
CARPET LEVEL 1 BEIGE
CARPET LEVEL 2 GREEN
CARPET LEVEL 3 RED
CARPET LEVEL 3 BLUE

I am trying to create a query for the results to look like this:
Category Type LEVEL 1 LEVEL 2 LEVEL 3
CARPET SAND STONE GREEN RED
CARPET BEIGE BLUE

But when I create a crosstab query I get this:

Category Type LEVEL 1 LEVEL 2 LEVEL 3
CARPET SAND STONE GREEN RED

The query is not returning all of the results, can someone help, thank you
so much.


Alison

 

[vanderghast]

Rank the color, by level and category, in a query, then, in the crosstab,
GROUP on the category AND on the rank.


If there is no dup, in each sub-group, a relatively fast way to get the
required rank is:



SELECT a.category, a.level, a.color, COUNT(*) AS rank
FROM tableName AS a INNER JOIN tableName AS b
ON a.category = b.category
AND a.level = b.level
AND a.color <= b.color
GROUP BY a.category, a.level, a.color


(you can use >= instead of <=, to get the reverse rank ordering)

saved as qr, the crosstab will become:


TRANSFORM LAST(color)
SELECT category, rank
FROM qr
GROUP BY category, rank
PIVOT level




Vanderghast, Access MVP

 

[alison.justice]

I am so sorry, but I am completely cluess as to what you are saying to do.
That is all way too foriegn to me

 

[vanderghast]

Well, create the first query, in SQL view:

SELECT a.[category type] AS category, a.level, a.color, COUNT(*) AS rank
FROM tableName AS a INNER JOIN tableName AS b
ON a.[category type]= b.[category type]
AND a.level = b.level
AND a.color <= b.color
GROUP BY a.[category type], a.level, a.color


save it under the name: qr.

Then, make a second query, still in SQL view:


TRANSFORM LAST(color)
SELECT category, rank
FROM qr
GROUP BY category, rank
PIVOT level


and run it.



Vanderghast, Access MVP

 

[alison.justice]

You are the BEST!!!! Thank you so much.
--
Alison

Well, create the first query, in SQL view:

SELECT a.[category type] AS category, a.level, a.color, COUNT(*) AS rank
FROM tableName AS a INNER JOIN tableName AS b
ON a.[category type]= b.[category type]
AND a.level = b.level
AND a.color <= b.color
GROUP BY a.[category type], a.level, a.color


save it under the name: qr.

Then, make a second query, still in SQL view:


TRANSFORM LAST(color)
SELECT category, rank
FROM qr
GROUP BY category, rank
PIVOT level


and run it.



Vanderghast, Access MVP