I need to delete oldest record so that table only contains latest record

[d.barson]

Hi,

I have a table called DDPayments which contains 4 fields: PaymentID,
DDID, BankID and Date Paid. I need a query that will delete all
records, so the rows will only contain the most recent Date when a
transaction occured.

I currently have the following in the table:

ROW 1 - PaymentID: 1 DDID: 9 BankID: 1 Date Paid: 13/08/07
ROW 2 - PaymentID: 1 DDID: 9 BankID: 1 Date Paid: 13/09/07
ROW 3 - PaymentID: 1 DDID: 26 BankID: 1 Date Paid: 10/08/07
ROW 4 - PaymentID: 1 DDID: 26 BankID: 1 Date Paid: 13/09/07
ROW 5 - PaymentID: 1 DDID: 6 BankID: 1 Date Paid: 13/08/07

(This table is updated with an append query.)

I therefore need to delete rows1 and 3 as these are the oldest records
and not needed.

The reason I need this is because I have written a database for my
bank accounts and I have a form to show regular payments such as
direct debits. This form needs to show when a payment was last paid
and when it is due to be paid again. This is why I have created the
table above, to show when the payment was last paid. Hope all of this
makes sense!

 

[John W. Vinson]

Hi,

I have a table called DDPayments which contains 4 fields: PaymentID,
DDID, BankID and Date Paid. I need a query that will delete all
records, so the rows will only contain the most recent Date when a
transaction occured.

I currently have the following in the table:

ROW 1 - PaymentID: 1 DDID: 9 BankID: 1 Date Paid: 13/08/07
ROW 2 - PaymentID: 1 DDID: 9 BankID: 1 Date Paid: 13/09/07
ROW 3 - PaymentID: 1 DDID: 26 BankID: 1 Date Paid: 10/08/07
ROW 4 - PaymentID: 1 DDID: 26 BankID: 1 Date Paid: 13/09/07
ROW 5 - PaymentID: 1 DDID: 6 BankID: 1 Date Paid: 13/08/07

(This table is updated with an append query.)

I therefore need to delete rows1 and 3 as these are the oldest records
and not needed.

The reason I need this is because I have written a database for my
bank accounts and I have a form to show regular payments such as
direct debits. This form needs to show when a payment was last paid
and when it is due to be paid again. This is why I have created the
table above, to show when the payment was last paid. Hope all of this
makes sense!

You certainly should NOT store the latest record redundantly in a second
table. YOu can do this in a Query. To show the most recent payment for each
value of DDID you can use a Subquery: create a query based on your table, and
in the Criteria line under Date Paid put

=(SELECT Max([Date Paid]) FROM yourtable AS X WHERE X.DDID = yourtable.DDID)


John W. Vinson [MVP]

 

[DarrenNotts]

Thank you so much John, this worked a treat!

Cheers

Darren