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

[DarrenNotts]

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!

 

[Jerry Whittle]

What is the primary key field for this table? Without one, it could be
difficult to do properly.

 

[DarrenNotts]

Hi Jerry,

The field [PaymentID] is the primary

Cheers

Darren

 

[Jerry Whittle]

PaymentID can not be the primary key unless your data examples are wrong. A
primary key must be unique and all the PaymentID's shown are 1's.

 

[DarrenNotts]

Hi Jerry,

Sorry, I did write the wrong info, PaymentID is unique and the primary
key, the example above was incorrect, all the PaymentID fields are
diiferent. Sorry for the confusion.

Darren

 

[Jerry Whittle]

OK then! Much better Here's what I used for your data example. I'm assuming
heavily that the Date Paid field is of the Date/Time datatype. You also need
to check that the table and field names are correct.

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

DELETE *
FROM DDPayments
WHERE NOT EXISTS (SELECT "X"
FROM DDPayments AS DD2
GROUP BY DD2.DDID, DD2.BankID
HAVING DD2.DDID = DDPayments.DDID
AND DD2.BankID = DDPayments.BankID
AND Max(DD2.[Date Paid]) = DDPayments.[Date Paid]) ;

STANDARD WARNING: Make a backup of any tables that you change first or even
the entire database.

 

[DarrenNotts]

OK then! Much better Here's what I used for your data example. I'm assuming
heavily that the Date Paid field is of the Date/Time datatype. You also need
to check that the table and field names are correct.

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

DELETE *
FROM DDPayments
WHERE NOT EXISTS (SELECT "X"
FROM DDPayments AS DD2
GROUP BY DD2.DDID, DD2.BankID
HAVING DD2.DDID = DDPayments.DDID
AND DD2.BankID = DDPayments.BankID
AND Max(DD2.[Date Paid]) = DDPayments.[Date Paid]) ;

STANDARD WARNING: Make a backup of any tables that you change first or even
the entire database.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Hi Jerry,

Sorry, I did write the wrong info, PaymentID is unique and the primary
key, the example above was incorrect, all the PaymentID fields are
diiferent. Sorry for the confusion.

Darren- Hide quoted text -

- Show quoted text -

Cheers Jerry, this was exactly what was needed.

Darren