running total by customer

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have been using excel to do this problem, but I think it would be faster in
Access...

I have these fields FUND, ACCTNO, OBJCODE, ACCT, DR, SUBCODE, DESC, AMOUNT,
SSN, NAME, EFF_DATE, TERM, REF

I have to seperate them by term, then Total AMOUNT by SSN, reference the
total back to all records for that SSN, delete all records whose Total nets
out to zero, and keep all records for each SSN that don't. Each SSN can have
between 1 and 30 or so records and there are anywhere between 4 and 8 TERMs.

Any ideas will be greatly appreciated.
 
You can do what you describe by creating a query to get the totals by SSN
and then using this to identify which SSNs you need to delete:

SSNtotal:
SELECT AmountTable.SSN, Sum(AmountTable.AMOUNT) AS SumOfAMOUNT
FROM AmountTable
GROUP BY AmountTable.SSN;

DeleteZeros:
DELETE FROM AmountTable
WHERE SSN in (SELECT SSN FROM SSNtotal WHERE SSNtotal.SumOfAMOUNT=0);
 
Back
Top