Calculating percentages

[Guest]

I have a table which contains the result of a test (Text, List Box, Value
List "pass";"fail";"borderline") together with who took the test & when. The
associated form is accessed via a switchboard form which prompts users to
enter their ID - this means that users only see their results, a command
button on the form prompts the user to either input 2 dates - to search for
their results between two dates or to input one date - to search for their
results from that date onwards - these dates are parameters for a query
(samples query 2) - what gets returned is a subset of the table showing all
the results ie
John Smith 01-jun-06 pass
John Smith 31-mar - 06 pass

and so on

I need to be able to total the results and express as a percentage

John Smith pass 30 15%
John Smith fail 40 20%
John Smith borderline 130 65%

it would also be nice if there was a total row
John Smith 200 (ie 30+40+130)

I've got halfway with a 2nd query based on the first

SELECT [Samples Query 2].Result, Count(*) AS [Count]
FROM [Samples Query 2]
GROUP BY [Samples Query 2].Result;

this shows totals
pass 30
fail 40
borderline 130

but no grand totals nor percentages

Any help gratefully received

 

[Michel Walsh]

Hi,


SELECT name, result, result/(SELECT SUM(result) FROM myTable)
FROM myTable


should list the name, the result, and the percentage (as the result over the
sum of the results)



Hoping it may help,
Vanderghast, Access MVP

 

[Guest]

Thank you

Hi,


SELECT name, result, result/(SELECT SUM(result) FROM myTable)
FROM myTable


should list the name, the result, and the percentage (as the result over the
sum of the results)



Hoping it may help,
Vanderghast, Access MVP

I have a table which contains the result of a test (Text, List Box, Value
List "pass";"fail";"borderline") together with who took the test & when.
The
associated form is accessed via a switchboard form which prompts users to
enter their ID - this means that users only see their results, a command
button on the form prompts the user to either input 2 dates - to search
for
their results between two dates or to input one date - to search for their
results from that date onwards - these dates are parameters for a query
(samples query 2) - what gets returned is a subset of the table showing
all
the results ie
John Smith 01-jun-06 pass
John Smith 31-mar - 06 pass

and so on

I need to be able to total the results and express as a percentage

John Smith pass 30 15%
John Smith fail 40 20%
John Smith borderline 130 65%

it would also be nice if there was a total row
John Smith 200 (ie 30+40+130)

I've got halfway with a 2nd query based on the first

SELECT [Samples Query 2].Result, Count(*) AS [Count]
FROM [Samples Query 2]
GROUP BY [Samples Query 2].Result;

this shows totals
pass 30
fail 40
borderline 130

but no grand totals nor percentages

Any help gratefully received

 

[Guest]

thank you

Hi,


SELECT name, result, result/(SELECT SUM(result) FROM myTable)
FROM myTable


should list the name, the result, and the percentage (as the result over the
sum of the results)



Hoping it may help,
Vanderghast, Access MVP

I have a table which contains the result of a test (Text, List Box, Value
List "pass";"fail";"borderline") together with who took the test & when.
The
associated form is accessed via a switchboard form which prompts users to
enter their ID - this means that users only see their results, a command
button on the form prompts the user to either input 2 dates - to search
for
their results between two dates or to input one date - to search for their
results from that date onwards - these dates are parameters for a query
(samples query 2) - what gets returned is a subset of the table showing
all
the results ie
John Smith 01-jun-06 pass
John Smith 31-mar - 06 pass

and so on

I need to be able to total the results and express as a percentage

John Smith pass 30 15%
John Smith fail 40 20%
John Smith borderline 130 65%

it would also be nice if there was a total row
John Smith 200 (ie 30+40+130)

I've got halfway with a 2nd query based on the first

SELECT [Samples Query 2].Result, Count(*) AS [Count]
FROM [Samples Query 2]
GROUP BY [Samples Query 2].Result;

this shows totals
pass 30
fail 40
borderline 130

but no grand totals nor percentages

Any help gratefully received