Fixed columns for crosstab query?

[lorirobn]

Hi,

I have created a report based on a Crosstab Query which displays counts of the condition of items in our guest rooms, along with the inventory item. For example, for Beds, it would look like this:

Bed: Condition 1 Cond 2 Cond 3 Cond 4 Cond 5
------------------------------------------------------------
BedABC 5 0 2 50 100
BedDEF 2 5 0 25 115

As you can see by the chart, we have condition 1 through 5. The problem I am having is, because we don't have records for all conditions (ie, there are no recs for Condition 2 for BedABC), these columns don't show up in my report. I would like to ALWAYS have 5 columns, with the exact headings each time, and have zero counts for items that are not on the tables. Is there a way to do this with Crosstab Query, or do I need to manually do this report somehow instead? Sorry if I am repeating posts already out there; I have tried to find this issue but to no avail...

Thanks!
Lori

 

[Remou]

Hi lorirobn
Have you looked at the Column Heading property?
I set up two mock tables: Conditions, Beds
Then created a cross tab with the following Column Heading propert for the query: "c1","c2","c3","c4","c5"
The crosstab showed all five Conditions for all Beds:

TRANSFORM Sum(Conditions.Cond) AS SumOfCond
SELECT Beds.Beds
FROM Beds LEFT JOIN Conditions ON Beds.Beds = Conditions.Beds
GROUP BY Beds.Beds
PIVOT Conditions.CondNo In ("c1","c2","c3","c4","c5");

 

[lorirobn]

Thanks once again for your help, Remou. I got the column headings to show up fixed format. HOWEVER - now I get no counts or totals listed in the query. Don't know what i am doing wrong? I tried it with both my original logic, and with the logic you included above (minus the join statement, because my data is all included in one table). I get column headings, but no counts. Any ideas???

By the way, since my data all comes from only one table, is it still correct to use a CrossTab query?

Thanks!
Lori

 

[lorirobn]

PS - Here is the code I am using in my query, where all values are blank. When I remove the column headings, the counts are listed.

TRANSFORM Count(tblGuestRoom.DeskOrTableCond) AS SumOfCond
SELECT tblGuestRoom.DeskOrTable
FROM tblGuestRoom
WHERE (((tblGuestRoom.DeskOrTableCond)<>" "))
GROUP BY tblGuestRoom.DeskOrTable
PIVOT tblGuestRoom.DeskOrTableCond In ("c1","c2","c3","c4","c5");

 

[lorirobn]

Oops, sorry, I just figured out that the "IN" was looking for conditions 'C1', etc., which I do not have, which is why it's all blank! D-uh. My values are simply '1', '2', '3', '4', '5'. Is there a way to make the column heading different than the actual values? For example: 'Cond 1', 'Cond 2', etc?

THANK YOU!!
Lori

 

[Remou]

No, I don't think so, I was guessing (bad habit). I'll do what I should have done in the first place and ask you for the sql you were originally using. By the way, column heads must match the data:

Note The column headings you specify for the ColumnHeadings property must exactly match the data values in the Column Heading field in the query design grid. Otherwise, the data won't appear in the columns.

 

[lorirobn]

Oh, OK, that answers the question of how to change column headings. BUT - you did get me to have fixed column headings, and that is a huge help! THANKS!


By the way, it'd help me to know how you got this Microsoft quote. I get very confused looking up information on Access.

Thanks,
Lori

 

[Remou]

Ah, the big secret is to know the answer first!
In this case I hit F1 when the cursor was on the Column Heading property for the query.

 

[lorirobn]

WOW!!!!!!! That is soooo great to know. Thanks for sharing your secret!