How to remove a bullet from one's foot ?

[john471]

Dear All,

I need some help !

I have a table that stores a (percentage) score for each of 10 categories, for evaluations done on employees. The table also details the employee, and the date of the evaluation; such that an extract of the table would look like this:

EmployeeID (long Integer)
EvalDate (Date)
ScoreCategory01 (Single)
ScoreCategory02 (Single)
ScoreCategory03 (Single)
.
.
ScoreCategory10 (Single)

Most of the time the scores will be in the range 0 to 100. It is possible for a ScoreCategory to be "Not Applicable" (through no fault of the employee). I very cleverly (or otherwise) decided to store this result as minus one (-1).

Now I would like to report averages on those values, and exclude the -1's from the calculation. Trouble is I cannot exclude entire records just because one of the ScoreCategories in that record is at -1.

Excel has lovely functions "SUMIF" and "COUNTIF" that would make such averaging readily achievable. Is there an easy way to get what I want, (especially in a report) or will I have to spend hours writing and arguing with code

My (AC97) project is way too far gone to redisgn the table structure at this point.

Any help greatly appreciated.

Rgds

John.

 

[WayneRyan]

John,

From what I can see, you're gonna need some VBA to do this.

If your categories were in a seperate table (I know, I know)
it would be a relatively simple matter to count them, sum them,
get averages, etc.

Records are easy to include/exclude in queries, but when your
data resides in fields, you end up with:

If ScoreCategory01 <> -1 And ScoreCategory01 <> -1 And ...

Hard to work with.

You could still export your data to Excel, but that kinda seems
weird to build something in Access (modeling Excel) only to export
it right back to Excel.

I think Mile posted a good sample of a questionairre, maybe a search
here would help.

Maybe someone can help, at least we'll promote it back to the top.

Sorry, but did you really expect a different answer?

Wayne

 

[john471]

Thanks Wayne for looking at it,

I eventualy bit the bullet and achieved the desired result with vba code.

regards

John.