Query or Report

[alison.justice]

I have a crosstab query that has levels that look like this:

Level 1 Level 2 Level 3 Level 4
Wood Yellow Brown Green Blue

Tile Teal Tan Pink

I'm trying to get the report to acknowledge when the next category (i.e.
"Tile") I need the Level 2 Color to show under Level 1 if Level 1 is blank.
I will not be listing the Level's on t he report, so my end result is like
this:


Level 1 Level 2 Level 3 Level 4
Wood Yellow Brown Green Blue

Tile Teal Tan Pink

The two queries that look like this:

1st Query:

SELECT a.builderid, a.neighborhoodid, a.categoryID, a.categorydescription,
a.categorydetaildescription, a.price, a.levelid, a.color, COUNT(*) AS rank
FROM Query_Customer_Catalog_Crosstab AS a INNER JOIN
Query_Customer_Catalog_Crosstab AS b ON (a.color<=b.color) AND
(a.levelid=b.levelid) AND (a.price=b.price) AND
(a.categorydetaildescription=b.categorydetaildescription) AND
(a.categorydescription=b.categorydescription) AND (a.categoryID=b.categoryID)
AND (a.neighborhoodid=b.neighborhoodid) AND (a.builderid=b.builderid)
GROUP BY a.builderid, a.neighborhoodid, a.categoryID, a.categorydescription,
a.categorydetaildescription, a.price, a.levelid, a.color;


Second Query:

TRANSFORM Last(Query_Customer_Catalog_Crosstab_SQL.color) AS LastOfcolor
SELECT Query_Customer_Catalog_Crosstab_SQL.builderid,
Query_Customer_Catalog_Crosstab_SQL.neighborhoodid,
Query_Customer_Catalog_Crosstab_SQL.categoryid,
Query_Customer_Catalog_Crosstab_SQL.categorydetaildescription,
Query_Customer_Catalog_Crosstab_SQL.rank
FROM Query_Customer_Catalog_Crosstab_SQL
GROUP BY Query_Customer_Catalog_Crosstab_SQL.builderid,
Query_Customer_Catalog_Crosstab_SQL.neighborhoodid,
Query_Customer_Catalog_Crosstab_SQL.categoryid,
Query_Customer_Catalog_Crosstab_SQL.categorydetaildescription,
Query_Customer_Catalog_Crosstab_SQL.rank
PIVOT Query_Customer_Catalog_Crosstab_SQL.levelid In
("L1","L2","L3","L4","L5");

I have been working on this for days, and I'm so lost....I am hoping someone
can help....

Thank you so much