Calculations on reports

[holysepulchre]

I am running access 2010 with a report that has a dollar value field in it. I used the sum feature to get a total of the dollar value. I have another field i am pulling in for payments received. I want to make a calculation that takes the sum of the value on the report less payment received and gives a remaining balance. To date I tired a text box and made a formula but didn't work. Any thoughts can this be done? my basic formula =accesstotal field - payment recevived

[alansidman]

If you are using a query as your record source for your report, you can create a new field with an expression (your formula) and bring the value of that expression/field directly into your report with the other fields.

Alan

[holysepulchre]

I am using a query on the report. It asks for a specific vendor and displays tyhe info. One of the fields is a price field. I have tried to modify the query and am getting errors with the query now. I clicked on a empty column and created Expr1: Sum([FOUND11]![PRICE]) to get a basic sum function going. Which is now telling me you have tried to execute a query that does not include the specified expression date as part of an aggregate function

[alansidman]

I think that you need to create an aggregate query to get the sum of a particular field.

Look here for instructions on how to create it.

http://www.techonthenet.com/access/f...umeric/sum.php

Alan

[holysepulchre]

I must be missing something obvious. I went and added a seperate field and told it to sum found11.price field. When I run the query now the prevoius message does not display and the expression field appears to sim each record and not a running total which is more of what I am looking for. Here is the sql code:
SELECT FOUND11.Date, FOUND11.DEALER, MONDEA11.ADDRESS, MONDEA11.CITY__STAT, MONDEA11.ZIP, FOUND11.FAMILY, FOUND11.TP, FOUND11.POS, FOUND11.LOT, FOUND11.SECT, FOUND11.LIST, FOUND11.COMPLETED, FOUND11.PRICE, MONDEA11.PAYMENT_RE, Sum(FOUND11.PRICE) AS SumOfPRICE
FROM FOUND11 INNER JOIN MONDEA11 ON FOUND11.[DEALER] = MONDEA11.[DEALER_COD]
GROUP BY FOUND11.Date, FOUND11.DEALER, MONDEA11.ADDRESS, MONDEA11.CITY__STAT, MONDEA11.ZIP, FOUND11.FAMILY, FOUND11.TP, FOUND11.POS, FOUND11.LOT, FOUND11.SECT, FOUND11.LIST, FOUND11.COMPLETED, FOUND11.PRICE, MONDEA11.PAYMENT_RE
HAVING (((FOUND11.DEALER)=[Enter Dealer Name]));

[alansidman]

Suggest you try this with two queries. First query will aggregate on Dealer and sum on Payment. No other fields in your query.

Second query will inner join the original query you have with out the payment field and without the aggregation to the aggregate query and join them on the dealer name.

Alan