Subtracting running totals

[Mesanna]

This is probably idiotically simple, but I'm having a huge mental block. Say I have a table with some records on stock/financials. One of the fields in the table defines whether the record is a "Purchase" or a "Sale". I can create a report which lists my records grouped by "Purchase" and "Sale" and I can include a control which gives a running sum total for each heading. I can get a grand total at the bottom of the report which adds Sales + Purchases. But what if I want to do Sales - Purchases, i.e. subtract one from the other? The simplified table layout is below:

ID Item Price Sale/Purchase
1 Fruit 10.00 Purchase
2 Veg 5.00 Purchase
3 Meat 8.00 Purchase
4 Veg 8.00 Sale
5 Meat 10.00 Sale

On my report, I need to list and total all Purchases, list and total all Sales, then subtract Purchases figure from Sales figure. Appreciate any help anyone can give.

 

[dhookom]

I'm not sure why you didn't take the time to add just a little more to your data to display what you want on your report.
The total Purchase of the report is:

=Sum(Abs([Sale/Purchase] = "Purchase") * Price)

Duane
Hook'D on Access
MS Access MVP

 

[TheAceMan1]

How are ya Mesanna . . .

Perhaps:

[blue]=Sum(IIf([Sale/Purchase]="Sale",Price,-Price))[/blue]

[blue]Your Thoughts? . . .[/blue]

BTW: Welcome to [blue]Tek-Tips![/blue] Do have a look at one of the links at the bottom of my post. The links will help you [blue]ask better questions[/blue], get [blue]quick responses[/blue], [blue]better answers[/blue], and insite into [blue]etiquette[/blue] here in the forums. Again . . . Welcome to [blue]Tek-Tips![/blue] [blue]Its Worthy Reading![/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[Mesanna]

Hi AceMan!
Your response worked perfectly! Thanks so much
Mesanna

 

[TheAceMan1]

Mesanna . . .

BTW: Welcome to [blue]Tek-Tips![/blue] Do have a look at one of the links at the bottom of my post. The links will help you [blue]ask better questions[/blue], get [blue]quick responses[/blue], [blue]better answers[/blue], and insite into [blue]etiquette[/blue] here in the forums. Again . . . Welcome to [blue]Tek-Tips![/blue] [blue]Its Worthy Reading![/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]