G
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.
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.