Calculating percentages in Access 2003 2

[commonskills]

Thank you,

I have a table that looks something like this:

Students Taken Test
1 20071201
2 20071201
3 20071205
4 20071205
5 20071205
6 20080108
7 blank
8 blank

This is multipled by 100 students though. My question is how do I create a query or field where it will calculate the percentage of student who have taken the test where the above example would read .75% and I am trying to do this in ACCESS 2003? Thank you again for your time.

 

[PHV]

SELECT Count([Take Test])/Count(*) AS [%]
FROM yourTable

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

[tt]
SELECT SUM(iif([Taken Test] is not null,1,0))/count(*)
FROM ...
[/tt]
Why do you have your dates as TEXT strings?

Skip,

_{Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty!}

 

[PHV]

Skip, the COUNT aggregate function doesn't count the null values unless you used *, so no need of the IIf function.

 

[commonskills]

Thank you for your time,

I'm doing the Count([Take Test])/Count(*) AS [%] expresion in design view of a query, under the Criteria tab, in expression builder, is that correct?

I made the database in about two days but Ive been working on the percentages for about a week now. I have a networking/ cisco router background. You guys are super human!!!

 

[dhookom]

The suggestions by both Skip and PH are what you might view in the SQL view of the query.

Duane MS Access MVP
Now help me support United Cerebral Palsy

 

[commonskills]

does this look right:

select Count([CLASSES DATABASE].TOTAL)/Count(*) AS [%] [COUNTOFTESTTAKEN]
FROM [MASTER CLASS DATABASE];

Thank you

 

[commonskills]

what does "enter a parameter" mean?

 

[dhookom]

Doesn't look correct. What's wrong with the syntax suggested by PH?

SELECT Count([Taken Test])/Count(*) AS [PctTaken] 
FROM [Your unkown table name];

If you can't figure this out, come back with your actual table and field names.

Duane MS Access MVP
Now help me support United Cerebral Palsy

 

[commonskills]

SELECT DISTINCTROW Count([HQ CO TRNG DATABASE].[EQUAL OPPORTUNITY]) AS [CountOfEQUAL OPPORTUNITY], Count([HQ CO TRNG DATABASE].TOTAL) AS CountOfTOTAL
FROM [HQ CO TRNG DATABASE];



Thanks Duane MS Access MVP, PHV, and Skip!!!!!!!!!!!!!

 

[commonskills]

CountOfEQUAL OPPORTUNITY CountOfTOTAL
35 211

This is what the fields look like, im not certain if this is helpful in solving this but it 35 have take the class out off a possible 211 people

 

[dhookom]

If you want the percent, you must look back at every SQL syntax provided to see the division "/".

Duane MS Access MVP
Now help me support United Cerebral Palsy

 

[commonskills]

SELECT DISTINCTROW Count([EQUAL OPPORTUNITY])/ Count(TOTAL) AS [%] FROM [HQ CO TRNG DATABASE];


got it Thanks!!!!