Delete the last entry that is found

[LED]

Hi
I was wondering if anyone could help me. I have a query that finds the last
entry of a certain field.

Is there a way for me to create a query that would delete this last entry
that the above query found?

Thanks for any help you can give me.

 

[Allen Browne]

This example assumes a primary key field named ID, and the maximum value is
found in SomeField:

DELETE FROM Table1
WHERE ID =
(SELECT TOP 1 ID
FROM Table1 AS Dupe
ORDER BY Dupe.SomeField DESC,
Dupe.ID DESC)

If subqueries are new, here's an introduction:
http://allenbrowne.com/subquery-01.html

 

[LED]

Hi Allen,

Thank you for helping me with this. I really appreciate. Since I am new, I
will start over. Disregard what I said before.

OK...I have a query that reads like this:

DELETE [Schedule Enrollment].[Schedule #], [Schedule Enrollment].[Customer #]
FROM [Schedule Enrollment]
WHERE ((([Schedule Enrollment].[Schedule #])=[Forms]![Schedule Enrollment
subform]![Schedule #]) AND (([Schedule Enrollment].[Customer
#])=[Forms]![Schedule Enrollment subform]![Customer #]));


So, if it finds Schedule # 677 in Schedule Enrollment and 677 in Schedule
Enrollment subform AND Customer # 5511 in Schedule Enrollment and 5511 in
Schedule Enrollment subform,..............

it deletes every instance of when that is found. Where I just want it to
delete the last instance of that criteria when it is found.

Do you know how I can change that?

Thanks Again

 

[Allen Browne]

You will need to add another condition to the WHERE clause if you want it to
delete a particular one rather than them all.

The extra condition will need a way to determine which is the one to delete.
To do this, your table must have a primary key (since that guarantees you a
way of selecting a particular record.) You will then use a subquery in the
WHERE clause to delete that one rather than all.

The example shown previously illustrates how to use a subquery to select the
latest record. You will need to adjust it to work with your table and
primary key name.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hi Allen,

Thank you for helping me with this. I really appreciate. Since I am new,
I
will start over. Disregard what I said before.

OK...I have a query that reads like this:

DELETE [Schedule Enrollment].[Schedule #], [Schedule Enrollment].[Customer
#]
FROM [Schedule Enrollment]
WHERE ((([Schedule Enrollment].[Schedule #])=[Forms]![Schedule Enrollment
subform]![Schedule #]) AND (([Schedule Enrollment].[Customer
#])=[Forms]![Schedule Enrollment subform]![Customer #]));


So, if it finds Schedule # 677 in Schedule Enrollment and 677 in Schedule
Enrollment subform AND Customer # 5511 in Schedule Enrollment and 5511 in
Schedule Enrollment subform,..............

it deletes every instance of when that is found. Where I just want it to
delete the last instance of that criteria when it is found.

Do you know how I can change that?

Thanks Again

This example assumes a primary key field named ID, and the maximum value
is
found in SomeField:

DELETE FROM Table1
WHERE ID =
(SELECT TOP 1 ID
FROM Table1 AS Dupe
ORDER BY Dupe.SomeField DESC,
Dupe.ID DESC)

If subqueries are new, here's an introduction:
http://allenbrowne.com/subquery-01.html

 

[LED]

Allen, Thank you very much for the answer you provided me and for taking the
time to explain it so that I can understand it.

I understand what you are saying and now I will try it.

Again thank you for your time and your advice.

You will need to add another condition to the WHERE clause if you want it to
delete a particular one rather than them all.

The extra condition will need a way to determine which is the one to delete.
To do this, your table must have a primary key (since that guarantees you a
way of selecting a particular record.) You will then use a subquery in the
WHERE clause to delete that one rather than all.

The example shown previously illustrates how to use a subquery to select the
latest record. You will need to adjust it to work with your table and
primary key name.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hi Allen,

Thank you for helping me with this. I really appreciate. Since I am new,
I
will start over. Disregard what I said before.

OK...I have a query that reads like this:

DELETE [Schedule Enrollment].[Schedule #], [Schedule Enrollment].[Customer
#]
FROM [Schedule Enrollment]
WHERE ((([Schedule Enrollment].[Schedule #])=[Forms]![Schedule Enrollment
subform]![Schedule #]) AND (([Schedule Enrollment].[Customer
#])=[Forms]![Schedule Enrollment subform]![Customer #]));


So, if it finds Schedule # 677 in Schedule Enrollment and 677 in Schedule
Enrollment subform AND Customer # 5511 in Schedule Enrollment and 5511 in
Schedule Enrollment subform,..............

it deletes every instance of when that is found. Where I just want it to
delete the last instance of that criteria when it is found.

Do you know how I can change that?

Thanks Again

This example assumes a primary key field named ID, and the maximum value
is
found in SomeField:

DELETE FROM Table1
WHERE ID =
(SELECT TOP 1 ID
FROM Table1 AS Dupe
ORDER BY Dupe.SomeField DESC,
Dupe.ID DESC)

If subqueries are new, here's an introduction:
http://allenbrowne.com/subquery-01.html

Hi
I was wondering if anyone could help me. I have a query that finds the
last
entry of a certain field.

Is there a way for me to create a query that would delete this last
entry
that the above query found?