Deleting records based on sum of certain fields

[Paul Fenton]

I have a table (created by a query) of customer records listing name,
task, StartDate, PaymentDue. Because of the way the client keeps his
records, his customer may have a balance due of $1,000.00 for some
task, but he'll record another task cancelling that $1,000.00 balance
due. It looks like this in the table:

Name Task Start BalDue

Smith Install 11/10/03 1000.00
Smith Cancel 11/20/03 (1000.00)

We run a report from this table listing all customers with a balance
due and we don't want to show those with a net due of 0 or less than
zero on that report.

I need a way to delete records like those above from that table before
running the report.


Paul Fenton
(e-mail address removed)

 

[Nikos Yannacopoulos]

Paul,

Are you sure you want to delete these records? I would
have thought it's preferable to just not show them in the
report somehow rather than delete them from the table, so
you don't lose history. One way to do this is to:
1. create a totals query (say, qryCustPositiveBal), group
by customer, sum on baldue, filter on baldue > 0, then
2. create a second query (say, qryCustBalReport), linking
your original table to qryCustPositiveBal on Name (only
where field values are equal - Access query design
default), and retrieving the fields you want form the
original table. This will only return records for
customers with positive balance.
3. Use qryCustBalReport as the recordsource foir your
report rather than the original table.

Nikos

 

[Paul Fenton]

Thank you Nikos. I was working on this yesterday and blundered into a
solution that works that is very similar to what you suggested. You
gave me an idea though that will make it work better.

Thanks again

Paul Fenton