Formula question and book recommendation needed.

[Dan S]

I would like to calculate a monthly average by Customer ID in a query by
using the Expression Builder and without having to resort to SQL. Here are
my fields:

Customer Name| Customer ID| Billing Month| Qty Bought| Monthly Avg

The Qty Bought is the number of units purchased for a given Billing Month.
The Monthly Avg is the total Qty Bought/#of Billing Months. In other words,
if I am running a report for 6 billing months and the customer has only been
buying our products for 3 months, I want the average of 3 Billing Months,
not 6.

Also, can you recommend a good book which deals heavily with MS Access
queries?

TIA.

 

[Michel Walsh]

Hi,

You mean using the query designer? .

Bring the table, click on the toolbar button with summation symbol, a
capital Sigma (a kind of M rotated by 90 degree), a new line, total, appear
in the grid. Bring the client field, keep the proposed Group By. Bring the
Billing Month field in the grid, change the GROUP BY to WHERE, and add the
criteria:

<= Month( Date() ) - 6


Bring the QtyBought field in the grid, change the Group By to Avg
(average).


That assumes there is just one QtyBought by month, for a given client



Hoping it may help,
Vanderghast, Access MVP