Calculations on Null Values

[Guest]

I'm relatively new to Access and I have a question about doing calculations
for fields where they may or may not be null values. I initially had all the
blank records default to 0, but then I got Overflow errors, so I reverted
them back to blank null values.

Here's what I'm attempting to do. I have a list of clients and sales amounts
for the last 5 years. For each clint, there is sales
2000,2001,2002,2003,2004,and 2005. However, some clients may not have had
sales in some years, which are null values. I would like to do an overall
average sales for each client. Specifically, average all 6 years together and
get 1 average number. This works fine for people who have sales in all 6
years, but I get a black calculation for clients who have a blank sales
number in any year.

Is there any way to program the query to completely ignore years with blank
values. If someone has sales for all years, the averge is based on 6 numbers,
but if someone only has values for 3 years, the average is based only on 3
years (ignoring blank years, and not replacing blanks with 0 which messes up
the average)

Like I said, I'm new, but I do have some coding knowledge, so any help would
be great. Thanks.

 

[Vincent Johns]

What do you want to report for a client for whom you have records
beginning in November 2004? You might have sales of $2,000 in 2004 and
$24,000 in 2005. The average would be $13,000 per year. Is that what
you want?

What do you want to report for a client for whom you have records in
2000, 2001, 2004, and 2005? Would you include the zero amounts in 2002
and 2003, or omit them from the average? What do you want to do if only
2005 is missing?

How you should do the calculation will depend on how you want to treat
these cases.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[John Vinson]

I'm relatively new to Access and I have a question about doing calculations
for fields where they may or may not be null values. I initially had all the
blank records default to 0, but then I got Overflow errors, so I reverted
them back to blank null values.

Here's what I'm attempting to do. I have a list of clients and sales amounts
for the last 5 years. For each clint, there is sales
2000,2001,2002,2003,2004,and 2005. However, some clients may not have had
sales in some years, which are null values. I would like to do an overall
average sales for each client. Specifically, average all 6 years together and
get 1 average number. This works fine for people who have sales in all 6
years, but I get a black calculation for clients who have a blank sales
number in any year.

Well... first off... your table structure is incorrect. This is a good
spreadsheet design but not correct for a relational database. "Fields
are expensive, records are cheap" - a better design would be a
tall/thin table related one to many to your Clients table, with fields
ClientID, SalesYear, and SalesAmount. If the client had no sales for
2004, there'd simply be no record for that year.

A Totals query will average data from this table correctly - group by
the ClientID and Average the SalesAmount.

You can create such a table and use a Normalizing Union query to
migrate your existing data into it:

SELECT CustomerID, 2000 AS SalesYear, [2000] FROM yourtable WHERE
[2000] IS NOT NULL
UNION ALL
SELECT CustomerID, 2001 AS SalesYear, [2001] FROM yourtable WHERE
[2001] IS NOT NULL
UNION ALL
SELECT CustomerID, 2002 AS SalesYear, [2002] FROM yourtable WHERE
[2002] IS NOT NULL
UNION ALL
<etc>

Save this query and base a MakeTable query upon it to create the
tall-thin table.

Is there any way to program the query to completely ignore years with blank
values. If someone has sales for all years, the averge is based on 6 numbers,
but if someone only has values for 3 years, the average is based only on 3
years (ignoring blank years, and not replacing blanks with 0 which messes up
the average)

With your current structure, you'll need to use a rather snarky
expression: assuming your fieldnames are the year number,

(NZ([2000]) + NZ([2001]) + NZ([2002]) + NZ([2003]) + NZ([2004]) +
NZ([2005])) / (IIF(IsNull([2000]), 0, 1) + IIF(IsNull([2001]), 0, 1) +
IIF(IsNull([2002]), 0, 1) + IIF(IsNull([2003]), 0, 1) +
IIF(IsNull([2004]), 0, 1) + IIF(IsNull([2005]), 0, 1))

Of course next year you'll need to change your table structure, all
your queries, this expression, all your reports, ... another good
reason to normalize!


John W. Vinson[MVP]

 

[Pat Hartman\(MVP\)]

The problem is caused because your data is not normalized. If each year
were in a separate row as it should be, the Avg() function would work
exactly as you want. Null rows would be ignored so the average would be
based on the actual record count. With the data in columns, you essentially
have to work out the sum as well as the divisor and then do the division
yourself.
Select Client, (Nz(yr2000, 0) + Nz(yr2001, 0) + Nz(yr2002,) + .....) /
(Nz(yr2000, 1) + Nz(yr2001, 1) + .....) As AvgSales
From YourTable;

A totals query on normalized data would be:
Select Client, Avg(SalesAmt) As AvgSales
From YourTable
Group By Client;

Notice how the first query will need to be changed whenever the years
included in the calculation changes. Notice also that the query against
normalized data will never need to change regardless of how many years are
included. In fact if you want to use a range, you can specify the range in
the totals query so that each year it "rolls" if that is what you want.

A totals query on normalized data would be:
Select Client, Avg(SalesAmt) As AvgSales
From YourTable
Where Year(SalesDate) Between [Enter StartYear] And [Enter End Year]
Group By Client;

 

[Guest]

You guys are right, I do have this set up completely wrong. It does make much
more sense to have more records and less fields. However, I'm kind of stuck
with this format for now at least due to time limits. But thanks for the help
and when I get a chance to redo the database, it will definitely be like you
guys describe. Thanks a bunch.

J