Crosstab showing too many rows

[Linda RQ]

Hi Everyone,

Using Access 2003. SQL for my query is below in case it helps but I use the
query grid. My ultimate goal is to get a total count of each type of
therapy in each area. My query is showing all the appropriate records but I
don't have a total of each therapy, instead if there is a repeat of each
therapy for each area. Hope my example shows up looking
right....................Thanks, Linda


This is what my crosstab query shows....

Therapy type ICU
PROG MEDT
Aerosol
1
Aerosol Protocol 1
Aerosol 1
Aerosol Protocol
1
Aerosol
1
Aerosol Protocol 1
Aerosol Protocol 1
Aerosol 1

This is how I want it to look...

Therapy type ICU
PROG MEDT
Aerosol 2
1 1
Aerosol Protocol 2
1 1


TRANSFORM Count(tblTherapyType.TherapyTypeID) AS CountOfTherapyTypeIDSELECT
tblTherapyType.TherapyDescFROM tblTherapyType INNER JOIN ((tblPatients INNER
JOIN tblPtThpy ON tblPatients.PtID = tblPtThpy.PtID_fk) INNER JOIN
(tblAreaList INNER JOIN tblPtLocation ON tblAreaList.AreaID =
tblPtLocation.AreaID_fk) ON tblPatients.PtID = tblPtLocation.PtID_fk) ON
tblTherapyType.TherapyTypeID = tblPtThpy.ThpyTypeID_fk
WHERE (((tblPtThpy.ThpyEndDtTm) Is Null))GROUP BY tblPtThpy.ThpyEndDtTm,
tblPatients.PtID, tblPtThpy.PtThpyID, tblTherapyType.TherapyDesc PIVOT
tblAreaList.AreaName;

 

[Linda RQ]

Ummm the example didn't work. Can someone tell me how I can get columns and
rows to show up in text style for posting?

Thanks,
Linda

 

[Chris2]

Ummm the example didn't work. Can someone tell me how I can get columns and
rows to show up in text style for posting?

Thanks,
Linda

Linda

Use a monospace font like Courier/New Courier, and use only spaces to separate columns (no
tabs).


Sincerely,

Chris O.

 

[Chris2]

Hi Everyone,

Using Access 2003. SQL for my query is below in case it helps but I use the
query grid. My ultimate goal is to get a total count of each type of
therapy in each area. My query is showing all the appropriate records but I
don't have a total of each therapy, instead if there is a repeat of each
therapy for each area. Hope my example shows up looking
right....................Thanks, Linda


This is what my crosstab query shows....

Therapy type ICU
PROG MEDT
Aerosol
1
Aerosol Protocol 1
Aerosol 1
Aerosol Protocol
1
Aerosol
1
Aerosol Protocol 1
Aerosol Protocol 1
Aerosol 1

This is how I want it to look...

Therapy type ICU
PROG MEDT
Aerosol 2
1 1
Aerosol Protocol 2
1 1


TRANSFORM Count(tblTherapyType.TherapyTypeID) AS CountOfTherapyTypeIDSELECT
tblTherapyType.TherapyDescFROM tblTherapyType INNER JOIN ((tblPatients INNER
JOIN tblPtThpy ON tblPatients.PtID = tblPtThpy.PtID_fk) INNER JOIN
(tblAreaList INNER JOIN tblPtLocation ON tblAreaList.AreaID =
tblPtLocation.AreaID_fk) ON tblPatients.PtID = tblPtLocation.PtID_fk) ON
tblTherapyType.TherapyTypeID = tblPtThpy.ThpyTypeID_fk
WHERE (((tblPtThpy.ThpyEndDtTm) Is Null))GROUP BY tblPtThpy.ThpyEndDtTm,
tblPatients.PtID, tblPtThpy.PtThpyID, tblTherapyType.TherapyDesc PIVOT
tblAreaList.AreaName;

Linda,

I don't have any table structures or sample data to go on, so the following is a shot in
the dark that I cannot test.

Here is your query straightened up.

TRANSFORM Count(tblTherapyType.TherapyTypeID) AS CountOfTherapyTypeID
SELECT tblTherapyType.TherapyDesc
FROM tblTherapyType
INNER JOIN
((tblPatients
INNER JOIN
tblPtThpy
ON tblPatients.PtID = tblPtThpy.PtID_fk)
INNER JOIN
(tblAreaList
INNER JOIN
tblPtLocation
ON tblAreaList.AreaID = tblPtLocation.AreaID_fk)
ON tblPatients.PtID = tblPtLocation.PtID_fk)
ON tblTherapyType.TherapyTypeID = tblPtThpy.ThpyTypeID_fk
WHERE (((tblPtThpy.ThpyEndDtTm) Is Null))
GROUP BY tblPtThpy.ThpyEndDtTm
,tblPatients.PtID
,tblPtThpy.PtThpyID
,tblTherapyType.TherapyDesc
PIVOT tblAreaList.AreaName;


The way I am reading your message is that you are getting too many row-headers (too many
therapies).

I see that there is one non-aggregate column named on the SELECT clause,
tblTherapyType.TherapyDesc.

However, there are four non-aggregate columns named on the GROUP BY clause.

Most often, the non-aggregate columns named on the SELECT clause match the non-aggregate
columns name on the GROUP BY clause.

I would try changing the GROUP BY to:

GROUP BY tblTherapyType.TherapyDesc


If that does not work, try providing the relevant portions of your table structures and
some relevant sample data for each of the tables. (If those requests don't make sense,
let me know.)


Sincerely,

Chris O.

 

[Linda RQ]

Linda

Use a monospace font like Courier/New Courier, and use only spaces to
separate columns (no
tabs).


Sincerely,

Chris O.
Thanks...Linda