Query of multiple Queries

[ambrn14]

I have a database that I want to be able to produce a report of total individual treatments received. For example,

Month/year Total IV tPA Total IA tPA Total IV & IA tPA
12/10 4 0 1
1/11 3 1 0
2/11 1 0 0
8 1 1

I have approxiately 35 treatments in all. I can't have 35 queries, can I? What is the best way to get this result. I have attached a test database if that helps.

Thank you in advance.

 

[stopher]

You can do this with one crosstab query which will create 35 columns if that's what you are after. Or you could have your periods as column headings and treatments down the side (again using a crosstab query). The challenge will be fitting it all on the paper.

hth
Chris

 

[ambrn14]

You can do this with one crosstab query which will create 35 columns if that's what you are after. Or you could have your periods as column headings and treatments down the side (again using a crosstab query). The challenge will be fitting it all on the paper.

hth
Chris

Can you show me a crosstab query using my test database that I posted? Sorry that I am a little slow.

 

[boostbyslinky]

In simple terms (broken down on treatment date, as opposed to months)


TRANSFORM Count(tblPatients.TreatmentActions) AS CountOfTreatmentActions
SELECT tblPatients.TreatmentDate
FROM tblTreatmentActions INNER JOIN tblPatients ON tblTreatmentActions.TreatmentActionID = tblPatients.TreatmentActions
GROUP BY tblPatients.TreatmentDate
PIVOT tblTreatmentActions.TreatmentAction;

 

[boostbyslinky]

Sorry, just updated it a touch to handle the month aggregation..


TRANSFORM Count(tblPatients.TreatmentActions) AS CountOfTreatmentActions
SELECT Month([TreatmentDate]) & "/" & Year([TreatmentDate]) AS [Month/Year]
FROM tblTreatmentActions INNER JOIN tblPatients ON tblTreatmentActions.TreatmentActionID = tblPatients.TreatmentActions
GROUP BY Month([TreatmentDate]) & "/" & Year([TreatmentDate])
PIVOT tblTreatmentActions.TreatmentAction;

 

[ambrn14]

In simple terms (broken down on treatment date, as opposed to months)


TRANSFORM Count(tblPatients.TreatmentActions) AS CountOfTreatmentActions
SELECT tblPatients.TreatmentDate
FROM tblTreatmentActions INNER JOIN tblPatients ON tblTreatmentActions.TreatmentActionID = tblPatients.TreatmentActions
GROUP BY tblPatients.TreatmentDate
PIVOT tblTreatmentActions.TreatmentAction;

Thank you! That is a great detailed report but I need one that gives me just the totals for each "TreatmentAction".

Any other suggestions?

 

[boostbyslinky]

Thank you! That is a great detailed report but I need one that gives me just the totals for each "TreatmentAction".

Any other suggestions?

You can add totals to the crosstab output by enabling the totals row on the datasheet, if that's what you're after?

 

[ambrn14]

You can add totals to the crosstab output by enabling the totals row on the datasheet, if that's what you're after?

I would like each intervention totaled then grouped by month and year. My dream would be to have this and then be able to specify which "TreatmentActions" I would like in the query-similar to the picture that I have attached.

 

[boostbyslinky]

I think I must be missing something..

The query I gave above gives the results in the attached image..
Can you mock up what you need in Excel to demonstrate the required output?

 

[ambrn14]

I copied and pasted you SQL into a new query and my results are not the same as seen in your picture. See my attachment. But this is exactly the results that I want!!

 

[boostbyslinky]

Sorry, I posted again with updated code to handle the aggregation.. here it is again..

TRANSFORM Count(tblPatients.TreatmentActions) AS CountOfTreatmentActions
SELECT Month([TreatmentDate]) & "/" & Year([TreatmentDate]) AS [Month/Year]
FROM tblTreatmentActions INNER JOIN tblPatients ON tblTreatmentActions.TreatmentActionID = tblPatients.TreatmentActions
GROUP BY Month([TreatmentDate]) & "/" & Year([TreatmentDate])
PIVOT tblTreatmentActions.TreatmentAction;

 

[ambrn14]

AWESOME!!!

Now any suggestion on running for a certain date range and for specified "TreatmentActions"?

 

[boostbyslinky]

You should be able to specify them as parameters within the crosstab query, alternatively, use a form to drive the query (probably preferable if you're doing it for the treatment actions!)

 

[ambrn14]

I tried to use the parameter [Enter Beginning Date] and [Enter Ending Date] in the parameters but it did not work. I entered "2/1/11" and "2/28/11". It gave me the same table that you posted in your "Capture" picture including Jan, Feb and Mar of 2011.

I am truly am not trying to be lazy. I just am not very familar with these functions. Could you help me out with a form that I can specify the date range and "TreatmentActions"? I would be forever gratefull!!

 

[boostbyslinky]

I'll have a quick look when I'm back in the office tomorrow..

 

[boostbyslinky]

It looks like I'm going to be rather busy today I'm afraid...

I have found this page, which is rather handy.. http://allenbrowne.com/ser-67.html

 

[ambrn14]

Thanks for the resource but I still can't get my crosstab query to work with my form.

I have attached the database. I want the form "frmCalTreatmentActions" to to be the parameters used when you run "Query1". I have the parameters in the criteria and in the parameters section but it still will not filter by what is selected in the form.

Appreciate any help given!