% for Cases by Year by month

[error123]

Hi,
I would liek to know how many CigaretteSmoker (Current, Former, Never) did we have and the percentage of all of them.

This is whta I have so far:
SELECT DISTINCTROW Count(Event_Cath.SS_Patient_ID) AS Total, Cath_Registry_Common.CigaretteSmoker, [Total]*100 AS [%of cases]
FROM Event_Cath LEFT JOIN Cath_Registry_Common ON Event_Cath.SS_Event_Cath_ID = Cath_Registry_Common.SS_Event_Cath_ID
WHERE (((Event_Cath.Date_of_Cath) Between [Start_Date] And [End_Date]))
GROUP BY Cath_Registry_Common.CigaretteSmoker
ORDER BY Cath_Registry_Common.CigaretteSmoker;

Maybe I should make a cross tab query?..

Thanks You!!!

 

[AlexCuse]

I think you need to use an iif to sum the number of smokers, and then calculate. This assumes that your 'Smoker' column is populated with 'Y' and 'N'. If this does not work, please provide some sample data.

formatpercent(sum(iif(Cath_Registry_Common.CigaretteSmoker = 'Y', 1, 0))/count(Event_Cath.SS_Patient_ID), 2)

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[fneily]

Here's a way to get a matrix with the smokers classification as row headings and dates as column headings with the percentage as data.
If you can get the input to have two columns, CigaretteSmoker and Date then you can do a little Pivot table magic. (So, a column that has the Never, Former, None and a column that has Date). This is for Access 2000 but the steps should approximately be the same for other versions.
Go to Forms, click New and select Pivot Table Wizard. Select the input at the bottom. Click Next (explanation stuff). Select the fields CigaretteSmoker and Date then click Next.
Select Layout. Move CigaretteSmoker to Row, Move Date to Column, Move CigaretteSmoker to Data.
Double Click "Count of CigaretteSmoker".
Change Name to Percentage of CigaretteSmoker.
Click Number button. Select Percentage and change decimal places to 0. Click OK.
Click Options button (the last button).
Click Dropdown arrow under Show Data As and select
% of Column. Click OK.
Click Ok in PivotTable Wizard pane.
Click Option (second button from left).
Deselect Grand Total for Row. Click Ok
Click Finish.

You'll now have a nice little table showing your percentages per date.

 

[error123]

Hi,
I used a crosstab query, and I calculated the %.
I will try it out your way too, and definitelly tell you how it came out.
Thank you Alex and fneily!!