Access Query to Provide Totals and % of Total 1

[EBox]

Hello All:

I am trying to run a query that will allow me to view the total of several numeric fields in a query, and then create a column for each that calculates the % of each row of the total.

The numeric fields are [CountofDoortoFP],[CountofDoortoRoom],[CountofDoortoTriage],[CountofDoortoReg], which are all grouped by [Path].

So, if my results query looked like this:

Path CountofDoortoFP
A 5
B 4
C 2
D 2

I would want a field that provides 5/13 for Path A, 4/13 for Path B, 2/13 for Path C, etc....

Can anyone help? Here is the SQL statement...

SELECT [All Times].Path, Avg([All Times].DoortoFP) AS AvgOfDoortoFP, Count([All Times].DoortoFP) AS CountOfDoortoFP, Avg([All Times].DoortoRoom) AS AvgOfDoortoRoom, Count([All Times].DoortoRoom) AS CountOfDoortoRoom, Avg([All Times].DoortoTriage) AS AvgOfDoortoTriage, Count([All Times].DoortoTriage) AS CountOfDoortoTriage, Avg([All Times].DoortoReg) AS AvgOfDoortoReg, Count([All Times].DoortoReg) AS CountOfDoortoReg
FROM [All Times]
GROUP BY [All Times].Path;

 

[PHV]

A starting point:
SELECT ..., Count(DoortoFP) AS CountOfDoortoFP, Count(DoortoFP)/DCount('*','[All Times]') AS PercentOfDoortoFP, ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[EBox]

Thanks for the lead, however, there is a problem. Embedded underneath the query are tables that use prompts for a [StartDate] and [EndDate] range. When I run this query, I get the following error:

The expression you entered as a query parameter produced this error: 'Microsoft Office Access can't find the name 'StartDate' you entered in the expression'

Any thoughts of how to work around this?

 

[PHV]

So, [All Times] is a parametized query ?
Another way:
SELECT ..., Count(DoortoFP) AS CountOfDoortoFP, Count(DoortoFP)/CountAll AS PercentOfDoortoFP, ...
FROM [All Times], (SELECT Count(*) AS CountAll FROM [All Times]) AS T
GROUP BY Path;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[EBox]

Thanks. I get an error of "You tried to execute a query that does not include the specific expression 'Count([All Times].DoortoFP)/CountAll' as part of an aggregate function.


Also - the total I want to use is different for each of the 4 numeric fields, and is the sum of the Count([DoortoFP]) etc. fields. So, if the sum of all DoortoFP fields is 100, I want to divide by 100.

I hope that makes sense. And thank you so much for your help!!!

 

[PHV]

SELECT ..., Count(DoortoFP) AS CountOfDoortoFP, Count(DoortoFP)/CountAllFP AS PercentOfDoortoFP, ...
FROM [All Times], (SELECT Count(DoortoFP) AS CountAllFP, Count(DoortoRoom) AS CountAllRoom, Count(DoortoTriage) AS CountAllTriage, Count(DoortoReg) AS CountAllReg FROM [All Times]) AS T
GROUP BY Path, CountAllFP, CountAllRoom, CountAllTriage, CountAllReg;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[EBox]

Thanks for the timely and accurate solutions! If I could, I'd leave you 10 stars!