Is it possible to add dummy row headings to a crosstab query in MS Access when the data doesn't exist in the source table/query? I'd like to indicate that I'm aware that a category exists, even though nothing currently falls under that category.
To illustrate, I'd like to take this output...
| Category | Total | Class 1 | Class 2 | Class 3 | Class 4 |
| PARTK2130 | 0.10 | 0.00 | 0.00 | 0.10 | 0.00 |
| PARTK3140 | 0.29 | 0.01 | 0.24 | 0.04 | 0.00 |
| PARTK4150 | 30.98 | 0.33 | 12.22 | 18.44 | 0.00 |
And make it look like this (notice the * New * rows)...
| Category | Total | Class 1 | Class 2 | Class 3 | Class 4 |
| PARTK1120 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | *New*
| PARTK2130 | 0.10 | 0.00 | 0.00 | 0.10 | 0.00 |
| PARTK3140 | 0.29 | 0.01 | 0.24 | 0.04 | 0.00 |
| PARTK4150 | 30.98 | 0.33 | 12.22 | 18.44 | 0.00 |
| PARTK5160 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | *New*
| PARTK6170 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | *New*
| PARTK7180 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | *New*
The only way that I can think of doing this is by creating a new dummy source-table/query, populate it with some dummy records, then modifying my crosstab query as a UNION query that looks at this dummy source along with the original. I got it to work, but this seems like a like a lot of monkey business.
Indeed, I learned how to add extra columns when there is no data (see the Class 4 column and this post), so I would think there's a trick to add extra rows too. Hoping anyway.
Here's the SQL (Access SQL dialect):
TRANSFORM Format(Nz(Sum(qryPartK.MILES_OF_PHYS_LENGTH),0),"Fixed")
AS SumOfMILES_OF_PHYS_LENGTH
SELECT IIf([PCT_SMYS]<=30,"PARTK2130",
IIf([PCT_SMYS]<=40,"PARTK3140", IIf([PCT_SMYS]<=50,"PARTK4150",
IIf([PCT_SMYS]<=60,"PARTK5160",IIf([PCT_SMYS]<=72,"PARTK6170",
IIf([PCT_SMYS]>72 And [PCT_SMYS]>=80,"PARTK7180",
IIf([PCT_SMYS]>80,"PARTK80MORE"))))))) AS Category,
Sum(qryPartK.MILES_OF_PHYS_LENGTH) AS Total
FROM qryPartK
GROUP BY IIf([PCT_SMYS]<=30,"PARTK2130",
IIf([PCT_SMYS]<=40,"PARTK3140",IIf([PCT_SMYS]<=50,"PARTK4150",
IIf([PCT_SMYS]<=60,"PARTK5160",IIf([PCT_SMYS]<=72,"PARTK6170",
IIf([PCT_SMYS]>72 And [PCT_SMYS]>=80,"PARTK7180",
IIf([PCT_SMYS]>80,"PARTK80MORE")))))))
PIVOT qryPartK.CLASS_LOC_text In ("Class 1","Class 2","Class 3","Class 4");
Aucun commentaire:
Enregistrer un commentaire