calculating percents

[glorber]

I have a table containing the following fields :
Sex(text), Age(numeric), sodium concentration(numeric).

I am trying to write a query which will return the percentage of male patients have a sodium concentration of a certain range (ex:120-130) out of the TOTAL number of patients.
I am able to count the number of events which fit the criteria but I cannot figure out a way to calculate the percentage part (unless i manually add a field in which I devide the count result by total number of patients).

Any ideas?
Thanks

 

[Jon K]

In the query, you can use DCount() to get the total number of patients. See queries in the database.
.

 

[glorber]

Thanks for your swift reply and the sample db.
It seems like you were able to solved my problem.

Cheers!

 

[glorber]

I have a follow up question:
Is it possible to run one report which will calculate the percentage of patients having three different ranges (ie sodium concentration 100-110; 110-120; 120-130) based on the sample db Jon K wrote, or do I have to make 3 different queries and run the report on three of them?

 

[Jon K]

You can put three SQL statements in a Union Query.

SELECT [tblPatients].[Sex], "Sodium Concentration 100-110" AS Range, Count([tblPatients].[Sodium Concentration]) AS [Number of Patients], format([Number of Patients]/DCount("*","tblPatients"),"Percent") AS Percentage
FROM tblPatients
WHERE ((([tblPatients].[Sodium Concentration]) Between 100 And 110))
GROUP BY [tblPatients].[Sex], "Sodium Concentration 100-110"
UNION
SELECT [tblPatients].[Sex], "Sodium Concentration 101-120" AS Range, Count([tblPatients].[Sodium Concentration]) AS [Number of Patients], format([Number of Patients]/DCount("*","tblPatients"),"Percent") AS Percentage
FROM tblPatients
WHERE ((([tblPatients].[Sodium Concentration]) Between 101 And 120))
GROUP BY [tblPatients].[Sex], "Sodium Concentration 101-120"
UNION
SELECT [tblPatients].[Sex], "Sodium Concentration 121-130" AS Range, Count([tblPatients].[Sodium Concentration]) AS [Number of Patients], format([Number of Patients]/DCount("*","tblPatients"),"Percent") AS Percentage
FROM tblPatients
WHERE ((([tblPatients].[Sodium Concentration]) Between 121 And 130))
GROUP BY [tblPatients].[Sex], "Sodium Concentration 121-130";
.