Method for combining crosstab queries with same criteria from multiple fields? (1 Viewer)

[keen1598]

Hi all,

I have 3 cross tab queries that are completely identical with the exception of the field that they pivot. Each field is searching for the same values just in different columns, with the end goal being to get the sum of the values for each pivoted column. I'm wondering rather than having 3 almost identical queries is there a way to use a crosstab to sum the values from each of the three fields rather that having 3 queries which then have to be aggregated in a fourth?

QUERY1

TRANSFORM 
IIf(Count(PT_LEVEL.UNIT) Is Null,0,Count(PT_LEVEL.UNIT)) AS CountOfUNIT

SELECT 
PT_LEVEL.INF_YEAR, 
PT_LEVEL.INF_MONTH, 
PT_LEVEL.UNIT

FROM 
PT_LEVEL

WHERE 
(((PT_LEVEL.[ORGANISM 1])="STAPHYLOCOCCUS AUREUS - SA") AND 
((PT_LEVEL.[MDRO CLASS])="MRSA")) OR 
(((PT_LEVEL.[ORGANISM 1])="CLOSTRIDIUM DIFFICILE - CD"))

GROUP BY 
PT_LEVEL.INF_YEAR,
PT_LEVEL.INF_MONTH, 
PT_LEVEL.UNIT

ORDER BY 
PT_LEVEL.INF_YEAR, 
PT_LEVEL.INF_MONTH, 
PT_LEVEL.UNIT

PIVOT 
PT_LEVEL.[ORGANISM 1] In ('CLOSTRIDIUM DIFFICILE - CD','STAPHYLOCOCCUS AUREUS - SA');

QUERY2

TRANSFORM 
IIf(Count(PT_LEVEL.UNIT) Is Null,0,Count(PT_LEVEL.UNIT)) AS CountOfUNIT

SELECT 
PT_LEVEL.INF_YEAR, 
PT_LEVEL.INF_MONTH, 
PT_LEVEL.UNIT

FROM 
PT_LEVEL

WHERE 
(((PT_LEVEL.[ORGANISM 2])="STAPHYLOCOCCUS AUREUS - SA") AND 
((PT_LEVEL.[MDRO CLASS2])="MRSA")) OR 
(((PT_LEVEL.[ORGANISM 2])="CLOSTRIDIUM DIFFICILE - CD"))

GROUP BY 
PT_LEVEL.INF_YEAR, 
PT_LEVEL.INF_MONTH, 
PT_LEVEL.UNIT

ORDER BY 
PT_LEVEL.INF_YEAR, 
PT_LEVEL.INF_MONTH, 
PT_LEVEL.UNIT

PIVOT 
PT_LEVEL.[ORGANISM 2] In ('CLOSTRIDIUM DIFFICILE - CD','STAPHYLOCOCCUS AUREUS - SA');

QUERY3

TRANSFORM 
IIf(Count(PT_LEVEL.UNIT) Is Null,0,Count(PT_LEVEL.UNIT)) AS CountOfUNIT

SELECT 
PT_LEVEL.INF_YEAR, 
PT_LEVEL.INF_MONTH, 
PT_LEVEL.UNIT

FROM 
PT_LEVEL

WHERE 
(((PT_LEVEL.[ORGANISM 3])="STAPHYLOCOCCUS AUREUS - SA") AND 
((PT_LEVEL.[MDRO CLASS3])="MRSA")) OR 
(((PT_LEVEL.[ORGANISM 3])="CLOSTRIDIUM DIFFICILE - CD"))

GROUP BY 
PT_LEVEL.INF_YEAR, 
PT_LEVEL.INF_MONTH, 
PT_LEVEL.UNIT

ORDER BY 
PT_LEVEL.INF_YEAR, 
PT_LEVEL.INF_MONTH, 
PT_LEVEL.UNIT

PIVOT 
PT_LEVEL.[ORGANISM 3] In ('CLOSTRIDIUM DIFFICILE - CD','STAPHYLOCOCCUS AUREUS - SA');

THE AGGREGATING QUERY

SELECT 
V_GROUP_UNIT.GROUP, 
UNIT_DATA.INF_YEAR, 
UNIT_DATA.INF_MONTH, 

[B]IIF(Sum(PT_INF_CROSSTAB.[CLOSTRIDIUM DIFFICILE - CD]) IS NULL, 0, SUM(PT_INF_CROSSTAB.[CLOSTRIDIUM DIFFICILE - CD])) + 
IIF(Sum(PT_INF_CROSSTAB_2.[CLOSTRIDIUM DIFFICILE - CD]) IS NULL, 0, SUM(PT_INF_CROSSTAB_2.[CLOSTRIDIUM DIFFICILE - CD])) +
IIF(Sum(PT_INF_CROSSTAB_3.[CLOSTRIDIUM DIFFICILE - CD]) IS NULL, 0, SUM(PT_INF_CROSSTAB_3.[CLOSTRIDIUM DIFFICILE - CD])) AS CDIFF, 

IIF(Sum(PT_INF_CROSSTAB.[STAPHYLOCOCCUS AUREUS - SA]) IS NULL, 0, SUM(PT_INF_CROSSTAB.[STAPHYLOCOCCUS AUREUS - SA])) +
IIF(Sum(PT_INF_CROSSTAB_2.[STAPHYLOCOCCUS AUREUS - SA]) IS NULL, 0, SUM(PT_INF_CROSSTAB_2.[STAPHYLOCOCCUS AUREUS - SA])) +
IIF(Sum(PT_INF_CROSSTAB_3.[STAPHYLOCOCCUS AUREUS - SA]) IS NULL, 0, SUM(PT_INF_CROSSTAB_3.[STAPHYLOCOCCUS AUREUS - SA])) AS MRSA,[/B]

IIF(Sum(PT_SPEC_CROSSTAB.[CLABSI]) IS NULL, 0, Sum(PT_SPEC_CROSSTAB.[CLABSI]))  AS CLABSI, 
IIF(Sum(PT_SPEC_CROSSTAB.[CAUTI]) IS NULL, 0, Sum(PT_SPEC_CROSSTAB.[CAUTI])) AS CAUTI, 
IIF(Sum(PT_MAJ_CROSSTAB.[VAE]) IS NULL, 0, Sum(PT_MAJ_CROSSTAB.[VAE])) AS VAE, 

IIF(Sum(PT_SPEC_CROSSTAB.[VAC]) IS NULL, 0, Sum(PT_SPEC_CROSSTAB.[VAC])) AS VAC, 
IIF(Sum(PT_SPEC_CROSSTAB.[IVAC]) IS NULL, 0, Sum(PT_SPEC_CROSSTAB.[IVAC]))  AS IVAC, 
IIF(Sum(PT_SPEC_CROSSTAB.[POSSIBLE VAP]) IS NULL, 0, Sum(PT_SPEC_CROSSTAB.[POSSIBLE VAP])) AS POSSIBLE, 
IIF(Sum(PT_SPEC_CROSSTAB.[PROBABLE VAP]) IS NULL, 0, Sum(PT_SPEC_CROSSTAB.[PROBABLE VAP])) AS PROBABLE, 

IIF(Sum(UNIT_DATA.[PT_DAYS]) IS NULL, 0, Sum(UNIT_DATA.[PT_DAYS])) AS PT_DAYS, 
IIF(Sum(UNIT_DATA.[CVC_DAYS]) IS NULL, 0, Sum(UNIT_DATA.[CVC_DAYS])) AS CVC_DAYS, 
IIF(Sum(UNIT_DATA.[IUC_DAYS])  IS NULL, 0, Sum(UNIT_DATA.[IUC_DAYS])) AS IUC_DAYS, 
IIF(Sum(UNIT_DATA.[VENT_DAYS]) IS NULL, 0, Sum(UNIT_DATA.[VENT_DAYS])) AS VENT_DAYS, 
IIF(Sum(UNIT_DATA.[APV_DAYS]) IS NULL, 0, Sum(UNIT_DATA.[APV_DAYS])) AS APV_DAYS, 

Avg(NHSN_BM.CLABSI_BM) AS CLABSI_BM, 
Avg(NHSN_BM.CLABSI_UR_M) AS CLABSI_UR_M, 
Avg(NHSN_BM.CAUTI_BM) AS CAUTI_BM, 
Avg(NHSN_BM.CAUTI_UR_M) AS CAUTI_UR_M, 
Avg(NHSN_BM.VAP_BM) AS VAP_BM, 
Avg(NHSN_BM.VAP_UR_M) AS VAP_UR_M

FROM 
((((((UNIT_DATA 
LEFT JOIN PT_SPEC_CROSSTAB ON 
(UNIT_DATA.[UNIT] = PT_SPEC_CROSSTAB.[UNIT]) AND 
(UNIT_DATA.[INF_YEAR] = PT_SPEC_CROSSTAB.[INF_YEAR]) AND 
(UNIT_DATA.[INF_MONTH] = PT_SPEC_CROSSTAB.[INF_MONTH])) 

LEFT JOIN PT_MAJ_CROSSTAB ON 
(UNIT_DATA.[UNIT] = PT_MAJ_CROSSTAB.[UNIT]) AND 
(UNIT_DATA.[INF_YEAR] = PT_MAJ_CROSSTAB.[INF_YEAR]) AND 
(UNIT_DATA.[INF_MONTH] = PT_MAJ_CROSSTAB.[INF_MONTH])) 

LEFT JOIN PT_INF_CROSSTAB ON 
(UNIT_DATA.[UNIT] = PT_INF_CROSSTAB.[UNIT]) AND 
(UNIT_DATA.[INF_YEAR] = PT_INF_CROSSTAB.[INF_YEAR]) AND 
(UNIT_DATA.[INF_MONTH] = PT_INF_CROSSTAB.[INF_MONTH])) 

LEFT JOIN PT_INF_CROSSTAB_2 ON 
(UNIT_DATA.[UNIT] = PT_INF_CROSSTAB_2.[UNIT]) AND 
(UNIT_DATA.[INF_YEAR] = PT_INF_CROSSTAB_2.[INF_YEAR]) AND 
(UNIT_DATA.[INF_MONTH] = PT_INF_CROSSTAB_2.[INF_MONTH])) 

LEFT JOIN PT_INF_CROSSTAB_3 ON 
(UNIT_DATA.[UNIT] = PT_INF_CROSSTAB_3.[UNIT]) AND 
(UNIT_DATA.[INF_YEAR] = PT_INF_CROSSTAB_3.[INF_YEAR]) AND 
(UNIT_DATA.[INF_MONTH] = PT_INF_CROSSTAB_3.[INF_MONTH])) 

INNER JOIN NHSN_BM ON 
(UNIT_DATA.[UNIT] = NHSN_BM.[UNIT_LIST]) AND 
(UNIT_DATA.[INF_YEAR] = NHSN_BM.[NHSN_BM_YEAR])) 

LEFT JOIN V_GROUP_UNIT ON 
UNIT_DATA.[UNIT] = V_GROUP_UNIT.[UNIT]

GROUP BY 
V_GROUP_UNIT.GROUP, 
UNIT_DATA.INF_YEAR, 
UNIT_DATA.INF_MONTH

ORDER BY 
V_GROUP_UNIT.GROUP, 
UNIT_DATA.INF_YEAR, 
UNIT_DATA.INF_MONTH;

 

[plog]

First, cross tabs shouldn't be used as sub-queries. You can't rely on them to have the same columns every time (sometimes there's no data for one unit and they don't get a column, sometimes a new unit appears that you aren't expecting). Because of this, you shouldn't build upon cross-tabs. If you truly need to pivot one column, it should be done in the last query of the process.

2nd, your data isn't properly structured. When you numerate fields ([ORGANISM 3], [MDRO CLASS2]) its usually time for a new table. That's true in your case as well. That data should go into a new table and if the suffixed number (1, 2, 3, ...) is pertinent to the data then you need to add a field to that table to capture that number. This is 90% of your issue. If your data was in 1 table, you could create a subquery to get all the totals and then use that subquery in your final query instead of trying to combine the data in those 3 cross-tab queries.

Your UNIT_DATA is also improperly structured. Like numerated fields, you are storing relevant data in your field names in this table. All those day fields (PT_DAYS, CVC_DAYS, etc.) shouldn't be fields in that table, instead they should be in a new table that has a field to hold what type of days each record represents (PT, CVC, etc.)

Again, the big issue is your improperly structured [ORGANISM X] and [MDRO CLASSX] fields. Break them out into a new table.

 

[keen1598]

Not necessarily true about cross tabs. If you read the code, the cross tabs are left joined to data that is wholly comprehensive of all months and years since a starting date.

The format of my tables is as such of necessity. I was required to keep a single table of individual transactional data, a running cumulative table of every unit for every month and year for patient and device days, and annualized data for benchmark metrics.

The way I have the system now, it is fully functional.

I was just wondering out of curiosity if there was a way to aggregate several columns with the exact same criteria and values from multiple crosstab queries to a single to eliminate duplicitous efforts.