access SQL help desperatly needed

[Fistandantilis]

I have a datebase that is giving me some troubles, there a 5 fileds, P1-P5 and a totals field named Daily Percent.

P1 = 5
P2 = 5
P3 = 5
P4 = 5
P5 = 5
the total Daily Percent would be 5 (5 + 5 + 5 + 5 + 5 = 25 / 5)
That is all fine and dandy IF the employee works all 5 periods, for the most part, they wont be. How do I calculate the daily Percent with only the periods they work? PLEASE I am desperate and at my witts ends with this, can somebody show me the light?
thanks fellas.

 

[Neverm1nd]

The easiest way would be to refactor into something like:

tblPeriods (or whatever)
iEmployeeID (i assume you already have something like this)
iPeriodID (1-5 goes here, instead of a P1, P2, P3 etc. column each)
iPercent (where the data goes)

This query is totally of the top of my head and most likely wrong, but just to give you an idea of what it would look like:
SELECT SUM( iPercent ) / ( SELECT COUNT( * ) FROM tblPeriods WHERE iEmployeeID = x AND NOT IS NULL iPercent) AS DailyPercent FROM tblPeriods WHERE iEmployeeID = x
(it's not very efficient either, but it should work)

For your existing table,
SELECT (
(SELECT CASE WHEN P1 IS NULL 0 ELSE P1 END) +
(SELECT CASE WHEN P2 IS NULL 0 ELSE P1 END) +
(SELECT CASE WHEN P3 IS NULL 0 ELSE P1 END) +
(SELECT CASE WHEN P4 IS NULL 0 ELSE P1 END) +
(SELECT CASE WHEN P5 IS NULL 0 ELSE P1 END)
) / (
(SELECT CASE WHEN (SELECT P1 FROM tblPeriods iEmployeeID = x) IS NULL THEN 0 ELSE 1 END) +
(SELECT CASE WHEN (SELECT P2 FROM tblPeriods iEmployeeID = x) IS NULL THEN 0 ELSE 1 END) +
(SELECT CASE WHEN (SELECT P3 FROM tblPeriods iEmployeeID = x) IS NULL THEN 0 ELSE 1 END) +
(SELECT CASE WHEN (SELECT P4 FROM tblPeriods iEmployeeID = x) IS NULL THEN 0 ELSE 1 END) +
(SELECT CASE WHEN (SELECT P5 FROM tblPeriods iEmployeeID = x) IS NULL THEN 0 ELSE 1 END)
) AS DailyPercent FROM tblPeriods WHERE iEmployeeID = x

Actually looking at that, it sucks. Im sure there's a better way. I'm not sure all these constructs are supported in Access either.