Calculating an average number from multiple fields in one record

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Scenario: I have a table where each record is an employee. There is a field
for each month and the total number of sales the employee makes is entered
into that month.

So... Tom Jones: Jan=10, Feb=12, Mar=0, Apr=18, May=0, Jun=4, Jul=15,
Sep=0, Oct=10, Nov=13, Dec=0

I want to calculate the total number of sales for Tom and average it out not
by total months, but by months for which there WERE sales. So I need to add
all the sales together and then divide by 8 instead of 12, and then place
that total so it is visible on the form somewhere.

How can I tell it to disregard those fields that have a null value when
averaging? And only for this record and not ALL records.

Thank you for your help!
 
Hi HWhite,

If each month is a field in your table, you could do it like this:

NumMonthsWithSales: iif(isnull([Jan]), 0, 1) + iif(isnull([Feb]), 0, 1) +
iif(isnull([Dec]), 0, 1) (remembering to put all months in)

Then calculate the total sales in a similar fashion:

TotalSales: iif(isnull([Jan]), 0, [Jan]) + ...

Then calculate the values on your form (or wherever) using
[TotalSales]/[NumMonthsWithSales] (remembering to check if there were no
months with sales, otherwise you will end up with a divide by zero error)

Hope this helps.

Damian.
 
It is possible to get the total with a convoluted expression like this:
=(Nz([Jan],0) + Nz([Feb],0) + ...) / (-[Jan] Is Not Null) - ([Feb] Is
Not Null) - ...)

The better solution would be to redesign the table so you don't have
repeating fields like Jan, Feb, ... Instead, your table should have fields:
SalesmanID relates to a field in the Salesman table.
TheYear Number (4-digit year)
TheMonth Number (1 - 12)
Amount Currency

It is now dead-easy to query, compare salesmen, compare months, sum,
average, and do pretty much anything else you want.

And if you do need to create a report with the months as column headings,
you can do that with a crosstab query.
 
Ok. I tried what you said and I'm getting an error (#Name?). So, I should
tell you the exact scenario so we're talking about the same thing. Instead
of months and sales figures, it actually is tracking claim reports sent to us
from an insurance company and calculating the average time between reports.
Some may have 1 entry, some may have 3, some may have 7, so we have to
account for those with null entries and not calculate them in the averages.
Each record is one claim.

Here are my fields (in Date format):
First_Report
Subsequent_Report1
Subsequent_Report2
Subsequent_Report3
Subsequent_Report4
Subsequent_Report5
Subsequent_Report6
Subsequent_Report7

Our assumption at this point is that there won't be more than 8 total
reports. That could change, but if I can get the formula right, it won't
matter.

Based on what you wrote "NumMonthsWithSales: iif(isnull([Jan]), 0, 1) +
iif(isnull([Feb]), 0, 1) + iif(isnull([Dec]), 0, 1) (remembering to put all
months in)" we entered:

iif(isnull([First_Report]), 0, 1) + iif(isnull(subsequent_report1]), 0, 1) +
iif(isnull([subsequent_report2]) 0, 1) + iif(isnull([subsequent_report3]) 0,
1) + iif(isnull([subsequent_report4]) 0, 1) +
iif(isnull([subsequent_report5]) 0, 1) + iif(isnull([subsequent_report6]) 0,
1) + iif(isnull([subsequent_report7]) 0, 1)

into the control source of a text field called "NumberOfReports" and the
result comes back #Name?

Any ideas where we've gone wrong?

Thanks so much for your assistance!



Damian S said:
Hi HWhite,

If each month is a field in your table, you could do it like this:

NumMonthsWithSales: iif(isnull([Jan]), 0, 1) + iif(isnull([Feb]), 0, 1) +
iif(isnull([Dec]), 0, 1) (remembering to put all months in)

Then calculate the total sales in a similar fashion:

TotalSales: iif(isnull([Jan]), 0, [Jan]) + ...

Then calculate the values on your form (or wherever) using
[TotalSales]/[NumMonthsWithSales] (remembering to check if there were no
months with sales, otherwise you will end up with a divide by zero error)

Hope this helps.

Damian.

HWhite said:
Scenario: I have a table where each record is an employee. There is a field
for each month and the total number of sales the employee makes is entered
into that month.

So... Tom Jones: Jan=10, Feb=12, Mar=0, Apr=18, May=0, Jun=4, Jul=15,
Sep=0, Oct=10, Nov=13, Dec=0

I want to calculate the total number of sales for Tom and average it out not
by total months, but by months for which there WERE sales. So I need to add
all the sales together and then divide by 8 instead of 12, and then place
that total so it is visible on the form somewhere.

How can I tell it to disregard those fields that have a null value when
averaging? And only for this record and not ALL records.

Thank you for your help!
Click to expand...
Click to expand...
 
PMFJI but I think you went wrong with your table structure. I would place
each date in its own record in a related table. If you can't change your
structure, you could use a UNION query like:

SELECT ClaimID, First_Report as ReportDate, 0 as RptNum
FROM tblTooWide
UNION ALL
SELECT ClaimID, Subsequent_Report1, 1
FROM tblTooWide
WHERE Subsequent_Report1 Is not Null
UNION ALL
SELECT ClaimID, Subsequent_Report2, 2
FROM tblTooWide
WHERE Subsequent_Report2 Is not Null
--etc--
UNION ALL
SELECT ClaimID, Subsequent_Report7, 7
FROM tblTooWide
WHERE Subsequent_Report7 Is not Null;

You can then perform average, total, min, and max using basic SQL
statements.

--
Duane Hookom
MS Access MVP



HWhite said:
Ok. I tried what you said and I'm getting an error (#Name?). So, I
should
tell you the exact scenario so we're talking about the same thing.
Instead
of months and sales figures, it actually is tracking claim reports sent to
us
from an insurance company and calculating the average time between
reports.
Some may have 1 entry, some may have 3, some may have 7, so we have to
account for those with null entries and not calculate them in the
averages.
Each record is one claim.

Here are my fields (in Date format):
First_Report
Subsequent_Report1
Subsequent_Report2
Subsequent_Report3
Subsequent_Report4
Subsequent_Report5
Subsequent_Report6
Subsequent_Report7

Our assumption at this point is that there won't be more than 8 total
reports. That could change, but if I can get the formula right, it won't
matter.

Based on what you wrote "NumMonthsWithSales: iif(isnull([Jan]), 0, 1) +
iif(isnull([Feb]), 0, 1) + iif(isnull([Dec]), 0, 1) (remembering to put
all
months in)" we entered:

iif(isnull([First_Report]), 0, 1) + iif(isnull(subsequent_report1]), 0, 1)
+
iif(isnull([subsequent_report2]) 0, 1) + iif(isnull([subsequent_report3])
0,
1) + iif(isnull([subsequent_report4]) 0, 1) +
iif(isnull([subsequent_report5]) 0, 1) + iif(isnull([subsequent_report6])
0,
1) + iif(isnull([subsequent_report7]) 0, 1)

into the control source of a text field called "NumberOfReports" and the
result comes back #Name?

Any ideas where we've gone wrong?

Thanks so much for your assistance!



Damian S said:
Hi HWhite,

If each month is a field in your table, you could do it like this:

NumMonthsWithSales: iif(isnull([Jan]), 0, 1) + iif(isnull([Feb]), 0, 1) +
iif(isnull([Dec]), 0, 1) (remembering to put all months in)

Then calculate the total sales in a similar fashion:

TotalSales: iif(isnull([Jan]), 0, [Jan]) + ...

Then calculate the values on your form (or wherever) using
[TotalSales]/[NumMonthsWithSales] (remembering to check if there were no
months with sales, otherwise you will end up with a divide by zero error)

Hope this helps.

Damian.

HWhite said:
Scenario: I have a table where each record is an employee. There is a
field
for each month and the total number of sales the employee makes is
entered
into that month.

So... Tom Jones: Jan=10, Feb=12, Mar=0, Apr=18, May=0, Jun=4, Jul=15,
Sep=0, Oct=10, Nov=13, Dec=0

I want to calculate the total number of sales for Tom and average it
out not
by total months, but by months for which there WERE sales. So I need
to add
all the sales together and then divide by 8 instead of 12, and then
place
that total so it is visible on the form somewhere.

How can I tell it to disregard those fields that have a null value when
averaging? And only for this record and not ALL records.

Thank you for your help!
Click to expand...
Click to expand...
Click to expand...
 
Back
Top