Average calculation automatically not manually manipulated

[lschuh]

I originally added this on 10/15/13 but did not attach a file to explain and now I don't know how to attach a file to that post so it is the same question of calculating an average based on 12 fields. The fields are the months (Jan-Dec) I want the blood average of the year. Depending on the result an employee might only have to provide a sample 4 times a year or quarterly. If their result is greater than 15 a monthly sample must be provided. The employees on the quarterly draws only will have 4 samples to average whereas a monthly draw can have 12 or fewer. I am not getting a correct answer using /12 as there are 0 in the fields and the right answer would be on samples >0 So I am attaching a mini mdb so I can get a syntax to use to provide a right answer. Thanks

[Dal Jeanis]

Assuming that your database had been properly normalized, where each blood sample had a unique record, then the answer would be pretty simple. This will average the values for a year:

tlbEmployees
  EmpID
  EmpLName
  EmpFName
  EmpMI
  EmpSSN
  EmpDept
tlbSamples
  SampPK     Autokey
  SampEmpID  FK to EmpID
  SampDt     Date
  SampPB     Number
SELECT SampEmpID, Count(SampDt), Avg(SampPB)
FROM tblSamples
WHERE SampDate Between DateAdd("d",1,DateAdd("yyyy",-1,Date)) AND Date
GROUP BY SampEmpID;

On the other hand, since your samples are laid out in a single unnormalized record that holds twelve months, then you have a more complicated problem, especially if you want to do a year (including records for Nov and Dec of last year).
Here's the simple, each-year-only version:

Query:
SELECT  
   SampYear, 
   SampEmpID, 
   SampCountPB, 
   SampSumPB, 
   SampSumPB/IIF(SampCountPB>0,SampCountPB,1) AS AvgPB
FROM 
  (SELECT SampYear, SampEmpID.
      IIF(SampPBJan>0,1,0) + IIF(SampPBFeb>0,1,0) + 
      IIF(SampPBMar>0,1,0) + IIF(SampPBApr>0,1,0) + 
      IIF(SampPBMay>0,1,0) + IIF(SampPBJun>0,1,0) + 
      IIF(SampPBJul>0,1,0) + IIF(SampPBAug>0,1,0) + 
      IIF(SampPBSep>0,1,0) + IIF(SampPBOct>0,1,0) + 
      IIF(SampPBNov>0,1,0) + IIF(SampPBDec>0,1,0) AS SampCountPB,
      SampPBJan + SampPBFeb + SampPBMar + 
      SampPBApr + SampPBMay + SampPBJun +
      SampPBJul + SampPBAug + SampPBSep + 
      SampPBOct + SampPBNov + SampPBDec AS SampSumPB
   FROM tblSamples);

With your current structure, to get a running year, you'd need to replace each of the twelve terms in the year-only query like this:
replace "IIF(SampPBJan>0,1,0)" in the SampCountPB calculation with

IIF(DateSerial(SampYear,01,01)<=DateAdd("yyyy",-1,Date),0,
    IIF(DateSerial(SampYear,01,01)>Date),0,
        IIF(SampPBJan>0,1,0)))

and replace "SampPBJan" in the SampSumPB calculation with

IIF(DateSerial(SampYear,01,01)<=DateAdd("yyyy",-1,Date),0,
    IIF(DateSerial(SampYear,01,01)>Date),0,SampPBJan)

Technically, the Dateserial()>Date isn't needed when you're using current date to determine the one-year, but I've included it so that if you wanted one-year-back from some prior date, the code would work.
The final query would look something like this...assuming it fits in the limit of number of characters for a query...

Query:
SELECT  
   SampEmpID, 
   SUM(SampCountPB), 
   SUM(SampSumPB), 
   SUM(SampSumPB)/IIF(SUM(SampCountPB)>0,SUM(SampCountPB),1) AS AvgPB
FROM 
  (SELECT SampYear, SampEmpID.
       IIF(DateSerial(SampYear,01,01)<=DateAdd("yyyy",-1,Date),0,
          IIF(DateSerial(SampYear,01,01)>Date),0,
              IIF(SampPBJan>0,1,0))) + 
       IIF(DateSerial(SampYear,02,01)<=DateAdd("yyyy",-1,Date),0,
          IIF(DateSerial(SampYear,02,01)>Date),0,
              IIF(SampPBFeb>0,1,0))) + 
       (...repeat for other ten months...)
                                     AS SampCountPB,
       IIF(DateSerial(SampYear,01,01)<=DateAdd("yyyy",-1,Date),0,
          IIF(DateSerial(SampYear,01,01)>Date),0,SampPBJan) +
       IIF(DateSerial(SampYear,02,01)<=DateAdd("yyyy",-1,Date),0,
          IIF(DateSerial(SampYear,02,01)>Date),0,SampPBFeb) +
       (...repeat for other ten months...)
                                     AS SampSumPB,
   FROM tblSamples)
GROUP BY SampEmpID;

[Dal Jeanis]

Sorry, didn't mean to make it hard for you.

Let's try this way instead. The following code can be pasted into SQL view of a query on your test database, and will run as posted.

QueryU1:
SELECT DateSerial(Year,01,01) AS PBDate, empid, pbjan AS PBVal
FROM tester1
WHERE pbjan > 0
UNION
SELECT DateSerial(Year,02,01), empid, pbfeb
FROM tester1
WHERE pbfeb > 0
UNION
SELECT DateSerial(Year,03,01), empid, pbmar
FROM tester1
WHERE pbmar > 0
UNION
SELECT DateSerial(Year,04,01), empid, pbapr
FROM tester1
WHERE pbapr > 0
UNION
SELECT DateSerial(Year,05,01), empid, pbmay
FROM tester1
WHERE pbmay > 0
UNION
SELECT DateSerial(Year,06,01), empid, pbjune
FROM tester1
WHERE pbjune > 0
UNION
SELECT DateSerial(Year,07,01), empid, pbjuly
FROM tester1
WHERE pbjuly > 0
UNION
SELECT DateSerial(Year,08,01), empid, pbaug
FROM tester1
WHERE pbaug > 0
UNION
SELECT DateSerial(Year,09,01), empid, pbsept
FROM tester1
WHERE pbsept > 0
UNION
SELECT DateSerial(Year,10,01), empid, pboct
FROM tester1
WHERE pboct > 0
UNION
SELECT DateSerial(Year,11,01), empid, pbnov
FROM tester1
WHERE pbnov > 0
UNION
SELECT DateSerial(Year,12,01), empid, pbdec
FROM tester1
WHERE pbdec > 0

That query creates a single "table" that is normalized. You could use the output from that to create a new table, then delete all the twelve columns of data out of the tester1 table. Don't do that yet, though.

This next one selects from that query to average the most recent one year's worth of data:

QueryU2:
SELECT PBEmpID, Count([PBVal]) AS PBCount,AVG([PBVal]) AS PBAvg
FROM QueryU1
WHERE PBDate BETWEEN DateAdd("yyyy",-1,Date()) AND Date() 
GROUP BY PBEmpID;

Unfortunately, your fictional sample data only went through Dec 2012, so there's only a single return value for each employee.

By the way, my prior post (now corrected) was wrong to use "y", which won't give you any results at all. The proper parm to add/subtract a year in DateAdd is "YYYY".

[lschuh]

I thank you for all your help. I have everything printed out. I took some of your suggestions and code and added someother stuff and made my query work. This is a great site and have used it on and off for several years. I couldn't have managed in any of my code without the help of this site. Thank you so much.

[Dal Jeanis]

No problem. Please mark the thread "solved". Top of page, under "thread tools"