Access Report for Hospitals

[JamesC3]

Need some serious assistance with this. I have been trying to research in my various books how to do this, with no success.

Trying to get the general output as follows in a report format...

Hospital 1 Hospital 2 Hospital 3

Heart Cath (Category)
% Cases for Hosp
Cases
Days
Charges
Actual Payments
Expected Payments
ALOS
Charges/Case
Actual/Case
Expected/Case
Actual/Charges
Expected/Charges

Open Heart Surgery
% Cases for Hosp
Cases
Days
Charges
Actual Payments
Expected Payments
ALOS
Charges/Case
Actual/Case
Expected/Case
Actual/Charges
Expected/Charges

I have 13 "Categories", which may not be at all hospitals.

I have 6-9 Hospitals I will be needing to include in the report.

I would like to illustrate in a columnar format so that the hospitals can compare across one another. All Hospitals should show all 13 categories, if no data then 0 would appear in the various fields. I would also like to have a total column at the end of the hospitals.

Anyone have any goods ideas?

 

[nfusee]

Assuming your problem is getting all 6-9 hospitals to show on one report....

You can simply create a separate report for each hospital with all 13 categories, then in another report put all 6-9 reports in it, as subreports.....

 

[JamesC3]

Problem isn't getting all on one report, necessarily.

What I end up getting is the "Category" and Row labels (i.e. Cases, Days, etc.) showing up for each hospital.

For Example.

Hospital 1 Hospital 2
Cath Cath
Cases 'Data' Cases 'Data'
Days 'Data' Days 'Data'

And so on.

What I would like to get is....

Hospital 1 Hospital 2
Cath
Cases 'Data' 'Data'
Days 'Data' 'Data'

Hope thi helps with understanding. Let me know if you have any other ideas. Thank you.

 

[nfusee]

So, along the same theme...just take the labels off the text box fields....make one report, then copy that one and change the Control Source to the table or query for the next hospital, ect...

Then on the main report, you just have the Category and ROw labels and the 6-9 subreports...

 

[GingerR]

why not a crosstab query, with column headings set for each hospital, and plunked into the report?

usually a bad idea cause hospitals could change, but if they do not, or it is rare, maybe that is your best bet.

 

[nfusee]

I like the Crosstab query Idea too.....it really depends on how you have your database setup, though...

 

[JamesC3]

Can't get the crosstab to do more than one data element for the value. I need 12 data elements in the value section. Is there some way to develop a SQL crosstab that would be a work around on this? Not real fluent on SQL, but I'm not scared of it either.

 

[GingerR]

tell us your table structure. i don't see why it can't work for you.

 

[nfusee]

Yep..what is the structure of the database?

 

[JamesC3]

Can you be more specific by what you mean about the structure of the database?

 

[nfusee]

You cna just zip it and send it to me if you want...my public e-mail address is nfusee@aol.com

That way I can see what you got going....

 

[JamesC3]

Reports contain information that is not available for public viewing. Any other way?

 

[nfusee]

You can make a copy of it and strip out the data in the copied version...add some bogus data maybe

 

[GingerR]

structure = what are the tables, and the fields in your table(s)?

seems it should be something like this:

tblHospitals
fields: HospitalID, HospitalName, HospitalAddress,etc

tblCategory
fields: CategoryID, CategoryDesc
examples:

1 Heart Cath
2 Open Heart Surgery

tblSubCategory
fields: SubCatID, SubCatDesc
examples:
1 Cases
2 Days
3 Charges

tblHospitalData
fields: HospitalID, CategoryID, SubCategoryID, Data
examples:

1 1 1 45
1 1 2 8800
1 2 3 77

once it's in this kind of format or even without the ID's just

Hospital1 HeartCath Cases 45
Hospital1 HeartCath Days 8800

just make a crosstab query from that. "category" and "subcat" will both be row headings. in the report you can group them on Category.

 

[JamesC3]

Here's what I have....

Flat file with following data fields:

Hospital, Category, Cases, Days, Charges, expected Payment, Actual Payment, patient type (qualifying field)

Would like to add calculated fields to do:

Average Length of stay, average charges/case, actual payment/case, etc.

Need to essentially create a report with...

Hospital as a column header
Category as a row header
All Else as values