Crosstab Query Help

[aageorge]

How can I use this table:

Date A B C D
7/22 10 20 15 30
7/23 0 19 0 40

To run a crosstab query to get the following:


Line 7/22 7/23
A 10 0
B 20 19
C 15 0
D 30 40

Thanks.

 

[sxschech]

This will work, although tedious and somewhat hard coded, meaning that if the dates change, the union query would have to be modified. You will need to create a separate query for each letter and then do a union on the results.

First Query--
TRANSFORM First(Table1.A) AS Data_A
SELECT "A" AS Category
FROM Table1
GROUP BY "A"
PIVOT Format([Period],"Short Date"

Second Query--
TRANSFORM First(Table1.B) AS Data_B
SELECT "B" AS Category
FROM Table1
GROUP BY "B"
PIVOT Format([Period],"Short Date"

Third and beyond...--
(Same as above but with next letter)

Final Query Union-- (Union all the queries)
SELECT qryCrossTabA.Category, qryCrossTabA.[7/22/2003], qryCrossTabA.[7/23/2003]
FROM qryCrossTabA
UNION
SELECT qryCrossTabB.Category, qryCrossTabB.[7/22/2003], qryCrossTabB.[7/23/2003]
FROM qryCrossTabB
UNION
SELECT qryCrossTabC.Category, qryCrossTabC.[7/22/2003], qryCrossTabC.[7/23/2003]
FROM qryCrossTabC;

 

[uscitizen]

though not a reply, perhaps my question has a bearing on this thread (regarding crosstabulations)...

i have created a report that uses the results of a query.

in simple terms the report looks like this

severity
side_effect 1 2 3 4 5
a # # # # #
b # # # # #
c # # # # #
. . . . . .
. . . . . .
. . . . . .

(note: the side_effects can/will vary from one user of this database to another and the frequency (#) with which the different levels of severity found by the query will vary as well)

i used a manufactured datafile which i guaranteed would have every level of severity with which to create the underlying query. subsequently after having made some minor changes to the data table underlying that query i began to receive error messages when attempting to run the report that used the query, notably that the variable '[1]' was not found (or words to this effect). on looking at the results of running the query i found that there were no columns having the value '1'. at that point i began to get the idea that access is not sufficiently 'smart' to know to plug the quantity zero (0) into any cells in the report which the query finds to be missing and when an entire column does not exist in the underlying query that the report won't work. am i wrong? is there not a workaround?