Crosstab with Multiple Values 1

[cc143most]

I have the following code that works great (thanks Duane). I get columns such as "Declined Jan", "Obtained Jan", etc. However, my Link_Total doesn't have the month, which I need. Any ideas?

TRANSFORM Count([Qry_Orig].[Link]) AS [CountOfLink]
SELECT [Qry_Orig].Month, [Qry_Orig].Branch, [Qry_Orig].[Hospital], Count([Qry_Orig].[Link]) AS [Link_Total]
FROM [Qry_Orig]
GROUP BY [Qry_Orig].Month, [Qry_Orig].Branch, [Qry_Orig].[Hospital]
PIVOT [Qry_Orig].Consent & [Qry_Orig].Month;

My goal: I have a report with columns Jan-Dec and I get the appropriate total under the appropriate month. I looked at FAQ 703-5466; but, my months need to be static. New to SQL, it looked to me as if this FAQ would show the "last 12 months".

 

[dhookom]

I'm not sure why your Link_Total should have the month. Are you hoping to create three values for each month?

I am also confused by the [Month] field in the SELECT and the GROUP BY since you are pivoting on [Month].

Duane
Hook'D on Access
MS Access MVP

 

[cc143most]

I had the Month in the select field before you directed me to the Crosstab with multiple values FAQ. I simply forgot to remove it.

I need the counts by Hospital, by Month for every column (including Link_Total). By the time you break down all of my different "Consent" reasons, I'm not sure how many different columns I'll really end up with by the time I put in each month. This thing could "stretch a mile".

 

[dhookom]

stretch a mile" won't work in a report that is limited in width.

Can you step back and describe your data and specifications for your report?

Duane
Hook'D on Access
MS Access MVP

 

[cc143most]

Gladly. I am pulling data from our "very outdated" database. We are an eye bank and I need to analyze, by hospital and month the following:

Total Deaths
Consent Status

Consent Status tells us if the family declined or consented or if we ruled out with verbage such as:

Declined by Fam - Our Staff Approach
Declined by Fam - Hospital Approach
Declined by Fam - OPO Approach
Declined by Us/OPO - Rule Out
PKO
Obtained - By Hospital
Obtained - By Us
Obtained - By OPO

There are more, but this is a few of the main ones.

I have managed to get my crosstabs done to give me a count on each "Consent Status"; and, yesterday used your "& Month" to get a count for each month. But, again, I will run out of columns soon. I also ran an Update Query to get me counts based on "Declined", "RO", "Obtained". I then pulled those two queries together for my report.

My report needs to be as follows (one hospital per page):

Jan Feb Mar Apr May June July Aug Sept Oct Nov Dec Total
Total Deaths:
Med/Soc Rule Out: (This comes from Consent Status "Declined by Us-Rule Out"
ME/Coroner Rule Out: (This comes from Consent Status "Declined by Us-Can't Retrieve"
Suitable Eye Donor: (This is Total Deaths - Med/Soc Rule Out - ME/Coroner Rule Out)
Family Denied Consent: (Comes from Consent Status - "Declined")
Family Initiated - (Comes from Consent Status - Family Approach)
Hospital Approach - (Comes from Consent Status - Declined by Fam - Hospital Approach)
ETC (All "Declined" reasons will be listed so that we can see if there are issues in who is approaching, etc)
Actual Eye Donors: Suitable Eye Donor - Family Denied Consent
Obtained by Hospital (Consent Status - Obtained by Hospital)
Obtained by OPO: (Consent Status - Obtained by OPO)
Obtained by Us: (Consent Status - Obtained by Us)
Obtained FPC: (Obtained - On Donor Registry)
Overall Consent Rate: This is a percentage, which I was able to get to work when I had only one month of data.

Duane, again, I can't thank you enough for taking the time to help me and others. I was tossed into this a month ago; and, while I have built some basic things in Access, nothing to this degree. Your postings to others have helped me through to this point.

If you need to see anything else, just let me know.

 

[dhookom]

Can't you simply start with a query like:

TRANSFORM Count([Qry_Orig].[Link]) AS [CountOfLink]
SELECT [Qry_Orig].Branch, [Qry_Orig].[Hospital], [Qry_Orig].Consent, Count([Qry_Orig].[Link]) AS [Link_Total]
FROM [Qry_Orig]
GROUP BY [Qry_Orig].Branch, [Qry_Orig].[Hospital], [Qry_Orig].Consent
PIVOT [Qry_Orig].Month IN ("JAN","FEB","MAR", [red][b]..etc..[/b][/red], "DEC");

Duane
Hook'D on Access
MS Access MVP

 

[cc143most]

OMG! If you were here, I'd hug you! I didn't know you could do this; and, I never thought to switch my consent and Month. I went into Query Wizard to create my crosstab, thinking I would have to manually type in the MONTH statement. Then, I told it to COUNT my Call Date. It asked if I wanted to group it by Year, Quarter, Month, etc. I chose Month and it is BEAUTIFUL!

Thank you, again, so much for your time! I'm sure I'll have more questions, but for now, I'm OFF!!!!!![BIGSMILE]

 

[cc143most]

Well, that didn't take long, lol. Your suggestion for the Query worked well; but, I still don't understand how to get the right data in my report. I have created labels for all of my month columns and my "Consent Reason" type rows. All types need to be listed, even if 0. I cannot figure out how to get my "Jan" count in the right row.

 

[dhookom]

Aren't your months the columns? Aren't your Consent Reasons the values of [Qry_Orig].Consent?

Duane
Hook'D on Access
MS Access MVP

 

[cc143most]

Yes, and this is where I feel really dumb (or brain dead). I add available field "Jan".... it gives me the number for my first consent reason (whatever that may be). I can't, for the life of me, figure out how to tell "Jan" to list the 2nd - 17th reason count. Basically, it's my "intersecting cell" that I can't figure out. The count is "Jan" + "Declined by Fam - Hosp Approach" for example.

I have 17 total consent reasons and they all need to be listed (even if 0) so, I did labels for my rows. More like a blank form than a report.

I know this should be easy, but I just can't see it (I'm usually very logical). Due to my first deadline next week, I don't have time to "step back and relook at it later, which is what I need to do. Temporarily, I have done the crosstab with 2 values to get my Month/Consent in one column. However, we go back to "I'll run out of columns before the end of the year" that way.

 

[dhookom]

Each Consent reason will create a row in your report. So if you have 17 consent reasons, you will have 17 records. Each record will have a Consent, Branch, Hospital, Link_Total, as well as a field for each month (Jan- Dec).


Duane
Hook'D on Access
MS Access MVP

 

[cc143most]

Duh! Thank you, Duane. I was trying to pull in the Column instead of the row. I'm still not sure how all 17 of my consent types are going to show if a hospital only has a count on 4 of them; but, I'll play with it and see what I come up with.

 

[dhookom]

You can create a cartesian query that displays every distinct combination of Consent Type and Hospital. The SQL might look like:

SELECT Hospital, Consent
FROM tblHospitals, tblConsents

Then you can add this query to another query and join the Hospital and Consent fields. Change the join to include all of the records from the cartesian query.


Duane
Hook'D on Access
MS Access MVP

 

[cc143most]

Thanks! Something else new for me! I'll try it and let you know if/when I have problems! (My boss is VERY grateful, too!)