Average Calculation

[Cherly Colon]

I am building a report that is based on cross tab queries.
Each column in the cross tab query is different - for
example - 1/1/03 Sales - 2/1/03 Sales. The problem is
when a run a query to do averages it is dividing by those
fields that contain $ figures and those that contain
zeros. I tried to change the fields that contain zeros to
nulls but it deletes the entire record.
I tried to do the average calculation manually in the
report but each page contains different data and of course
it is dividing the same number when the number does not
apply to each page in the report. Are their any
suggestions?

 

[[MVP] S. Clark]

Sometimes you just have to use VBA code to get you through the rough spots.
Typically in this situation, I try to make a set of field names that never
change, but change the label that describes them. So the field names may be
Val1, Val2, Val3, but the labels may display 1/1/03 Sales - 2/1/03 Sales,
3/1/03 Sales - 4/1/03 Sales, 5/1/03 Sales - 6/1/03 Sales.

I'm not sure how the rest of your issues are, especially the null and zero,
and division by zero, but once again, I'll suppose that some coding will
assist in working around the issues.


--
HTH,

Steve Clark, Access MVP
FMS, Inc.
Professional Solutions Group
http://www.FMSInc.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Is your Access database too slow?
Are you ready to upgrade to SQL Server?
Contact us for optimization and/or upsizing!
http://www.FMSInc.com/consulting
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-