Calculated column in Query

[davesmith202]

I'm creating a report that will show Jan-Dec in the first column. The next column shows total record counts for each month. Next, I want to show the record counts for each month but filtered by location.

i.e. first column is just the total, but second column is a filtered total.

Can I do that in the same query? e.g. have some kind of calculated column with a where statement or something?

Thanks,

Dave

 

[davesmith202]

Actually, I have just worked out a large part of it. I can use an iif function in a column and if it finds a particular value, set the field value 1 or 0 if not found. Then the report can sum to get a count.

I also need to calculate an average of a field, but only if there is a value in there. Can I do that too with an iif statement or in the report somehow? I can easily do an average but I need the average of only records that have entries.

 

[Alansidman]

I would approach this by running a query and in the criteria for the field you wish to average, put "not null." Save the query. Create a new query with the first query as the record source. Now change the query to an aggregate query and set the field in question as Average and group on whatever field you wish.

Alan