Report Format Possible? 1

[shelby55]

Hello

I'm using Access 2003 and though I've used it for a long time, I haven't delved into anything other than using Access as a place to store data. But now I need to report from it....

I would like to create a report that looks like:
[tt] Apr May
Disposition # Cases # Days Avg Days # Cases # Days Avg Days
Directly Home 8 80 10.0 12 48 4.0
Tsfr to Acute 10 80 8.0 12 48 8.0
Tsfr to Rehab 5 50 10.0 6 24 4.0
[/tt]

The dispositions noted are from a single data element. I would like to show across an entire fiscal year (Apr to Mar). All data is from a single data table.

Thanks for any assistance.

 

[MajP]

Yes you can do that, but you would have to provide the fields in your table for us to be helpful. From the example can not tell what is an aggregate value and what already exists in the table.

If you want this will print the needed information
FAQ700-6905

But the likely solution is you create an aggregate query grouping by month. This would give the fields and values above grouped by month. Then from that query you make 12 seperate queries filtered by month. Then you would join the 12 queries in a final query linked by disposition. Sounds a lot but basically copy and paste.

 

[shelby55]

Hi MajP

Thanks so much for the speedy reply!

Okay, the table has one inpatient visit per line of data. The data elements required for this report would be:
chartno, disdate, days, disposition

I can aggregrate the data in a query because disposition text is actually in another table that I would link on.

# cases is aggregrated by count of chartno, disdate provides the month, days is aggregrated to total days and average length of stay.

Is this all the information you require? Thanks very much.

 

[shelby55]

Hi MajP

I have a question: when linking the 12 am I linking them from the 12 month tabkes to disposition table so 12 links coming into the disposition table? Or month 1 linked to month 2 which is linked to month 3 etc.?

Thanks.

 

[MajP]

If you look at the faq I provided it shows a format for describing your table structure. It makes it a lot easier to discuss, and provide you some working queries. Need to really know the relevant table fields, not just those in the report.

However, can you already make a query like this?

Disposition   # Cases # Days  Avg Days  YearMonth
Directly Home     8      80   10.0       Jan 2010
Tsfr to Acute     10     80    8.0       Jan 2010
Tsfr to Rehab     5      50   10.0       Jan 2010
Directly Home     x      x      x        Feb 2010
Tsfr to Acute     x      x      x        Feb 2010
Tsfr to Rehab     x      x      x        Feb 2010
....

If you already have than assume it is called "qrySummaryDataAll"

Then you could make your seperate queries
qryJan2010:

Select * from qrySummaryDataAll where yearMonth = "Jan 2010"
(Assume yearmonth is a text field from a formatted date field)

Then you would inner join each month query on disposition.

 

[shelby55]

Hi

Sorry but I didn't understand the link....but yes I can make a table like you described (and have done so).

The only part where you are missing me is the part about inner joining on each month....so as per my question above, are all 12 months in the query connecting to the disposition table (to convert the disposition code to text) OR am I daisy chaining each month to each other?

And I'm not sure how I take this to make the report i.e. how do I take the results and make the report format shown? Thanks.

 

[MajP]

The only part where you are missing me is the part about inner joining on each month....so as per my question above, are all 12 months in the query connecting to the disposition table (to convert the disposition code to text) OR am I daisy chaining each month to each other?

And I'm not sure how I take this to make the report i.e. how do I take the results and make the report format shown? Thank

Just link the 12 months by disposition code. Link the disposition text in one time to show the field. This would be a pain to write the sql but in the query developer it would be simple

You may be able to do a crosstab as well.

Join tblDisposition to qryJan (by caseID) then qryJan to qryFeb then qryFeb to ...

actually your queries are apr to march.

Once you are done the query will almost look like the report. you will have fields like

tblDisposition.Disposition, qryJan.CaseCount,qryJan.DayCount,qryJan.Avg,.....qryDec.CaseCount,qryDec.DayCount.

 

[shelby55]

Hi

Thanks very much! I'll give it a try and let you know!

 

[shelby55]

Hi

Okay this works great....except for partial years. So I have data from Apr to Sep 2010 but since I won't have the month match in each group of 12, those cases won't show up.

Any way to work around this?

Thanks.

 

[MajP]

A left outer join for all the queries should work.

Also once you get this working, then you probably want to modify the 12 queries so they are not specific months but they are a month based off of a start month. You would have qryMonth1 to qryMonth12

Then you could prompt the user for a start month and build the report for any 12 month period

So you would have in your criteria of qryMonth2 something like
"where yearMonth = getYearMonth(1)."

getYearMonth(1) would be a vb function to add a month to your startmonth.

 

[shelby55]

Hi

Thanks very much but outer joins create the error message of "could not be executed because it contains ambiguous outer joins." Suggestions?

Also, I like the sound of your other idea but not sure how to execute...if you could give sample of vb code that would be great.

Thanks.