Sum or AVG from derived textboxes

[pctaz]

I have a form with header and detail. the detail section is populated with results from a query. there are two columns at the end that are derived from data in the results. One is a percent [pct] of two columns [settlement]/[balance]. this works

now in the header I have a box that counts the number of settlements, this works correctly.

I am trying to add a box that will give me the avg of the [pct] column. I can sum the settlements and sum the balances then divide and this will give me the % of the total. but I am trying to get the avg of the pct.

an example

[balance]     [settlement]   [pct](rounded up to 0 decimal)
458.15              229.07        50
365.91              195.00        53
2331.48             550.00       24

total
3155.54             974.07       31

the avg of the percentages would be 42 <= this is the one I can't figure out. I tried adding a column in the query that would calculate the pct then I could avg that, but it told me it was too complex or typed wrong?

[pctaz]

put the table on sql server, made my query and it works (having the pct column as settlement/balance.

copied it to access and i now get a popup Overflow

SELECT     [Date], Client_Name, Creditor_Name, [Payment_#], Balance, Settlement, ModifiedDate, ModifiedBy, SUM(Settlement / Balance) AS pct
FROM         tbl_settlements
WHERE     (ModifiedBy = 'fred') AND (YEAR([Date]) = 2009) AND (MONTH([Date]) = 11)
GROUP BY [Date], Client_Name, Creditor_Name, [Payment_#], Balance, Settlement, ModifiedDate, ModifiedBy

so I must be missing something access wants that sql doesn't need

[pctaz]

DOH!!!!!!!!!!
can't divide by 0!!!
ok so I put an if in the query to avg only if field>0
seems to be working now

[GolferGuy]

The sooner you learn to always check for zero (0) before dividing, the happier you will be. (Voice of way too much experience) Good job!