Compare two fields in table and delete oldest record

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

Guest

I want to compare two fields of data in my table (tblhistory) - dateID and
projectID and if they are the same for more than one historyID (record) then
I want to delete the record with the lowest historyID number. Thanks in
advance for your guidance/suggestions.
 
DELETE FROM MyTable
WHERE EXISTS (SELECT 'X' FROM MyTable B
WHERE B.IDField = myTable.IDField
AND B.DateField > myTable.DateField)

HtH

Pieter
 
Hello, thanks for the quick response. I am still having trouble but I am most
certain its due to my lack of knowledge. Please see the code below that I put
in my query

Funding_history is the table from which the query was built and from which I
want the records deleted. I want to eliminate records where the DateID and
proj_exec_id are the same, however I want to leave the latest entry which
would be record with the highest hist_id


DELETE FROM funding_history
WHERE EXISTS (SELECT 'X' FROM funding_history B
WHERE B.proj_exec_id = funding_history.proj_exec_id
WHERE B.DateID = funding_history.DateID
AND B.histid < funding_history.histid)
 
You have 2 WHERE clauses in the set of (), I believe you need an "AND" or an
"OR".

(SELECT 'X' FROM funding_history B
WHERE B.proj_exec_id = funding_history.proj_exec_id
WHERE B.DateID = funding_history.DateID
AND B.histid < funding_history.histid)
Click to expand...

Hello, thanks for the quick response. I am still having trouble but I am most
certain its due to my lack of knowledge. Please see the code below that I put
in my query

Funding_history is the table from which the query was built and from which I
want the records deleted. I want to eliminate records where the DateID and
proj_exec_id are the same, however I want to leave the latest entry which
would be record with the highest hist_id

DELETE FROM funding_history
WHERE EXISTS (SELECT 'X' FROM funding_history B
WHERE B.proj_exec_id = funding_history.proj_exec_id
WHERE B.DateID = funding_history.DateID
AND B.histid < funding_history.histid)
DELETE FROM MyTable
WHERE EXISTS (SELECT 'X' FROM MyTable B
Click to expand...
[quoted text clipped - 10 lines]
Click to expand...
 
An AND, Brain to keyboard, just before bed thingy, thx for spotting that

DELETE FROM funding_history
WHERE EXISTS (SELECT 'X' FROM funding_history B
WHERE B.proj_exec_id = funding_history.proj_exec_id
AND B.DateID = funding_history.DateID
AND B.histid < funding_history.histid)

Pieter

Hoardling via AccessMonster.com said:
You have 2 WHERE clauses in the set of (), I believe you need an "AND" or
an
"OR".

(SELECT 'X' FROM funding_history B
WHERE B.proj_exec_id = funding_history.proj_exec_id
WHERE B.DateID = funding_history.DateID
AND B.histid < funding_history.histid)
Click to expand...

Hello, thanks for the quick response. I am still having trouble but I am
most
certain its due to my lack of knowledge. Please see the code below that I
put
in my query

Funding_history is the table from which the query was built and from which
I
want the records deleted. I want to eliminate records where the DateID and
proj_exec_id are the same, however I want to leave the latest entry which
would be record with the highest hist_id

DELETE FROM funding_history
WHERE EXISTS (SELECT 'X' FROM funding_history B
WHERE B.proj_exec_id = funding_history.proj_exec_id
WHERE B.DateID = funding_history.DateID
AND B.histid < funding_history.histid)
DELETE FROM MyTable
WHERE EXISTS (SELECT 'X' FROM MyTable B
Click to expand...
[quoted text clipped - 10 lines]
I want to delete the record with the lowest historyID number. Thanks
in
advance for your guidance/suggestions.
Click to expand...
Click to expand...
Click to expand...
 
Thank you so much! Works beautifully! What if I wanted this to run in a form
and not show the message boxes before the delete.

Pieter Wijnen said:
An AND, Brain to keyboard, just before bed thingy, thx for spotting that

DELETE FROM funding_history
WHERE EXISTS (SELECT 'X' FROM funding_history B
WHERE B.proj_exec_id = funding_history.proj_exec_id
AND B.DateID = funding_history.DateID
AND B.histid < funding_history.histid)

Pieter

Hoardling via AccessMonster.com said:
You have 2 WHERE clauses in the set of (), I believe you need an "AND" or
an
"OR".

(SELECT 'X' FROM funding_history B
WHERE B.proj_exec_id = funding_history.proj_exec_id
WHERE B.DateID = funding_history.DateID
AND B.histid < funding_history.histid)
Click to expand...

Hello, thanks for the quick response. I am still having trouble but I am
most
certain its due to my lack of knowledge. Please see the code below that I
put
in my query

Funding_history is the table from which the query was built and from which
I
want the records deleted. I want to eliminate records where the DateID and
proj_exec_id are the same, however I want to leave the latest entry which
would be record with the highest hist_id

DELETE FROM funding_history
WHERE EXISTS (SELECT 'X' FROM funding_history B
WHERE B.proj_exec_id = funding_history.proj_exec_id
WHERE B.DateID = funding_history.DateID
AND B.histid < funding_history.histid)

DELETE FROM MyTable
WHERE EXISTS (SELECT 'X' FROM MyTable B
[quoted text clipped - 10 lines]
I want to delete the record with the lowest historyID number. Thanks
in
advance for your guidance/suggestions.
Click to expand...
Click to expand...
Click to expand...
 
CurrentDb.Execute TheSQL,DAO.dbFailOnError
in code
setwarnings false
runSQL
setwarnings true
in a macro

Pieter

SMT said:
Thank you so much! Works beautifully! What if I wanted this to run in a
form
and not show the message boxes before the delete.

Pieter Wijnen said:
An AND, Brain to keyboard, just before bed thingy, thx for spotting that

DELETE FROM funding_history
WHERE EXISTS (SELECT 'X' FROM funding_history B
WHERE B.proj_exec_id = funding_history.proj_exec_id
AND B.DateID = funding_history.DateID
AND B.histid < funding_history.histid)

Pieter

Hoardling via AccessMonster.com said:
You have 2 WHERE clauses in the set of (), I believe you need an "AND"
or
an
"OR".

(SELECT 'X' FROM funding_history B
WHERE B.proj_exec_id = funding_history.proj_exec_id
WHERE B.DateID = funding_history.DateID
AND B.histid < funding_history.histid)


SMT wrote:
Hello, thanks for the quick response. I am still having trouble but I
am
most
certain its due to my lack of knowledge. Please see the code below that
I
put
in my query

Funding_history is the table from which the query was built and from
which
I
want the records deleted. I want to eliminate records where the DateID
and
proj_exec_id are the same, however I want to leave the latest entry
which
would be record with the highest hist_id

DELETE FROM funding_history
WHERE EXISTS (SELECT 'X' FROM funding_history B
WHERE B.proj_exec_id = funding_history.proj_exec_id
WHERE B.DateID = funding_history.DateID
AND B.histid < funding_history.histid)

DELETE FROM MyTable
WHERE EXISTS (SELECT 'X' FROM MyTable B
[quoted text clipped - 10 lines]
I want to delete the record with the lowest historyID number.
Thanks
in
advance for your guidance/suggestions.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top