Percentage Query

[anthony.carter]

Hi,

I have a table (table1) which records scores of 0 to 4 for about 70
people. The scores are in a field (F4).

Is it possible to build a query which will return the percentage of
people who attained a score of, say, 3 ?

If so, how would an Access congenital idiot (me) go about it?

Cheers,

Tony Carter

 

[Stefan Hoffmann]

hi Anthony,

I have a table (table1) which records scores of 0 to 4 for about 70
people. The scores are in a field (F4).
Is it possible to build a query which will return the percentage of
people who attained a score of, say, 3 ?

You may use

DCount("*", "table1", "F4=3") / DCount("*", "table1")

either in a query or in VBA, e.g.

SELECT F4, DCount("*", "table1", "F4=" & F4) / DCount("*", "table1")
FROM table1
GROUP BY F4


mfG
--> stefan <--

 

[anthony.carter]

Hi Stefan,

I put the following SQL into a query but it didn't quite work.....

SELECT [Table1].F4, DCount("*","Table1","F4=3")/DCount("*","Table1")
AS Expr1
FROM [Table1]
GROUP BY [Table1].F4;

The result was a list of the scores (ie all of them individually) and
a new field with a zero next to each score.

Have I made a typing error?

Cheers,

Tony Carter

 

[Stefan Hoffmann]

hi Anthony,

SELECT [Table1].F4, DCount("*","Table1","F4=3")/DCount("*","Table1")
AS Expr1
FROM [Table1]
GROUP BY [Table1].F4;

The result was a list of the scores (ie all of them individually) and
a new field with a zero next to each score.
Have I made a typing error?

Nope, just a reading error. The example query is slightly different then
the basic approach first given:

SELECT Table1.F4, DCount("*","Table1","F4=" & [F4])/DCount("*","Table1")
AS Expr1
FROM Table1
GROUP BY Table1.F4;



mfG
--> stefan <--

 

[anthony.carter]

Cheers Stefan!

Regards,

Tony Carter