Delete Duplicates except newsest Date

[Jay]

I have a need to delete all duplicate records in a table except where
the EntryDate has a newest date. All other dates are older and need to
be deleted.

I have this in an OnClick Event procedure.
DoCmd.RunSQL "DELETE * FROM tblTest WHERE [TestDate]<>(SELECT
MAX([TestDate]);"

I get an syntax error and don't know what needs to be done to fix it.

The EntryDate field is Date formated.
Any help would be appreciated.


tblTest:

StudentName TestDate Score
Smith 1/12/07 90
Smith 12/1/06 56
Jones 1/12/06 95
Jones 12/10/06 85
Jones 12/25/06 90
Jackson 12/31/06 100

 

[John Vinson]

I have a need to delete all duplicate records in a table except where
the EntryDate has a newest date. All other dates are older and need to
be deleted.

I have this in an OnClick Event procedure.
DoCmd.RunSQL "DELETE * FROM tblTest WHERE [TestDate]<>(SELECT
MAX([TestDate]);"

I get an syntax error and don't know what needs to be done to fix it.

The EntryDate field is Date formated.
Any help would be appreciated.

No Totals query, nor any query containing any totals query (like this
one), is ever updateable - even if (as in this case) it logically
ought to be.

The getaround is to use the VBA DMax() function:

DELETE * FROM tblTest WHERE [TestDate] < DMax("[TestDate]",
"[tblTest]", "[StudentName] = """ & [StudentName] & """")

I use all the doublequotes because you need either ' or " as a text
delimiter and you might have a student named O'Brien - the apostrophe
would mess you up.

Note that using student's last names as a unique identifier will NOT
work well: surely you might happen to have two students (siblings or
otherwise) who happen to have the same last name! Even full names
won't work; I know three men named Fred Brown.

John W. Vinson[MVP]

 

[Jay]

I have a need to delete all duplicate records in a table except where
the EntryDate has a newest date. All other dates are older and need to
be deleted.

I have this in an OnClick Event procedure.
DoCmd.RunSQL "DELETE * FROM tblTest WHERE [TestDate]<>(SELECT
MAX([TestDate]);"

I get an syntax error and don't know what needs to be done to fix it.

The EntryDate field is Date formated.
Any help would be appreciated.

No Totals query, nor any query containing any totals query (like this
one), is ever updateable - even if (as in this case) it logically
ought to be.

The getaround is to use the VBA DMax() function:

DELETE * FROM tblTest WHERE [TestDate] < DMax("[TestDate]",
"[tblTest]", "[StudentName] = """ & [StudentName] & """")

I use all the doublequotes because you need either ' or " as a text
delimiter and you might have a student named O'Brien - the apostrophe
would mess you up.

Note that using student's last names as a unique identifier will NOT
work well: surely you might happen to have two students (siblings or
otherwise) who happen to have the same last name! Even full names
won't work; I know three men named Fred Brown.

John W. Vinson[MVP]

I can use the other field I forgot to put there. StudenID and replace the
StudentName with the StudentID. Would that work?

StudentId StudentName TestDate Scores
23 smith
23 smith
54 jones
54 jones
54 jones
54 jones
33 smoe
33 smoe

and so on

 

[Jay]

I have a need to delete all duplicate records in a table except where
the EntryDate has a newest date. All other dates are older and need to
be deleted.

I have this in an OnClick Event procedure.
DoCmd.RunSQL "DELETE * FROM tblTest WHERE [TestDate]<>(SELECT
MAX([TestDate]);"

I get an syntax error and don't know what needs to be done to fix it.

The EntryDate field is Date formated.
Any help would be appreciated.

No Totals query, nor any query containing any totals query (like this
one), is ever updateable - even if (as in this case) it logically
ought to be.

The getaround is to use the VBA DMax() function:

DELETE * FROM tblTest WHERE [TestDate] < DMax("[TestDate]",
"[tblTest]", "[StudentName] = """ & [StudentName] & """")

I use all the doublequotes because you need either ' or " as a text
delimiter and you might have a student named O'Brien - the apostrophe
would mess you up.

Note that using student's last names as a unique identifier will NOT
work well: surely you might happen to have two students (siblings or
otherwise) who happen to have the same last name! Even full names
won't work; I know three men named Fred Brown.

John W. Vinson[MVP]

I can use the other field I forgot to put there. StudenID and replace the
StudentName with the StudentID. Would that work?

StudentId StudentName TestDate Scores
23 smith
23 smith
54 jones
54 jones
54 jones
54 jones
33 smoe
33 smoe

and so on

Sorry forgot again. there is a letter with the number.

StudentId StudentName TestDate Scores
B23 smith
B23 smith
A54 jones
A54 jones
F54 jones
F54 jones
G33 smoe
G33 smoe

and so on

 

[Jay]

I have a need to delete all duplicate records in a table except where
the EntryDate has a newest date. All other dates are older and need to
be deleted.

I have this in an OnClick Event procedure.
DoCmd.RunSQL "DELETE * FROM tblTest WHERE [TestDate]<>(SELECT
MAX([TestDate]);"

I get an syntax error and don't know what needs to be done to fix it.

The EntryDate field is Date formated.
Any help would be appreciated.

No Totals query, nor any query containing any totals query (like this
one), is ever updateable - even if (as in this case) it logically
ought to be.

The getaround is to use the VBA DMax() function:

DELETE * FROM tblTest WHERE [TestDate] < DMax("[TestDate]",
"[tblTest]", "[StudentName] = """ & [StudentName] & """")

I use all the doublequotes because you need either ' or " as a text
delimiter and you might have a student named O'Brien - the apostrophe
would mess you up.

Note that using student's last names as a unique identifier will NOT
work well: surely you might happen to have two students (siblings or
otherwise) who happen to have the same last name! Even full names
won't work; I know three men named Fred Brown.

John W. Vinson[MVP]

I can use the other field I forgot to put there. StudenID and replace
the StudentName with the StudentID. Would that work?

StudentId StudentName TestDate Scores
23 smith
23 smith
54 jones
54 jones
54 jones
54 jones
33 smoe
33 smoe

and so on

Sorry forgot again. there is a letter with the number.

StudentId StudentName TestDate Scores
B23 smith
B23 smith
A54 jones
A54 jones
F54 jones
F54 jones
G33 smoe
G33 smoe

and so on

I tried this and it failed. The problem area is the first qoute ".
I don't understand the right of the DMax code.
I will be studying DMax. The StudentID is Text Format.


DELETE * FROM tblTest WHERE [TestDate] < DMax("[TestDate]",
"[tblTest]", "[StudentID] = """ & [StudentId] & """")

 

[John Vinson]

I tried this and it failed. The problem area is the first qoute ".
I don't understand the right of the DMax code.
I will be studying DMax. The StudentID is Text Format.


DELETE * FROM tblTest WHERE [TestDate] < DMax("[TestDate]",
"[tblTest]", "[StudentID] = """ & [StudentId] & """")

Since it's a date field you might need # date delimiters. I have no
idea what you mean by "the problem area" nor did you post the actual
error message, so I'm guessing in the dark... but try

DELETE * FROM tblTest WHERE [TestDate] < "#" Format(DMax("[TestDate]",
"[tblTest]", "[StudentID] = """ & [StudentId] & """"), "mm/dd/yyyy") &
"#"

This will be a bit of a mess with all the embedded quotes if you try
to build it in code; maybe save it as a stored Query and execute it by
query name.

John W. Vinson[MVP]

 

[Jay]

I tried this and it failed. The problem area is the first qoute ".
I don't understand the right of the DMax code.
I will be studying DMax. The StudentID is Text Format.


DELETE * FROM tblTest WHERE [TestDate] < DMax("[TestDate]",
"[tblTest]", "[StudentID] = """ & [StudentId] & """")

Since it's a date field you might need # date delimiters. I have no
idea what you mean by "the problem area" nor did you post the actual
error message, so I'm guessing in the dark... but try

DELETE * FROM tblTest WHERE [TestDate] < "#" Format(DMax("[TestDate]",
"[tblTest]", "[StudentID] = """ & [StudentId] & """"), "mm/dd/yyyy") &
"#"

This will be a bit of a mess with all the embedded quotes if you try
to build it in code; maybe save it as a stored Query and execute it by
query name.

John W. Vinson[MVP]

It says syntax error. I had to change the " to the '.

This is where I am now:

DoCmd.RunSQL "DELETE * FROM tblTest WHERE [TestDate] < '#'
Format(DMax('[TestDate]', '[tblTest]', '[StudentID] = """ & [StudentID] &
""""), "mm/dd/yyyy") & '#';"
W

I now get expected end of statement at the third parenthases above the W
above.

 

[Wolfgang Kais]

Hello Jay.

I have a need to delete all duplicate records in a table except
where the EntryDate has a newest date. All other dates are older
and need to be deleted.

I have this in an OnClick Event procedure.
DoCmd.RunSQL "DELETE * FROM tblTest WHERE [TestDate]<>(SELECT
MAX([TestDate]);"

I get an syntax error and don't know what needs to be done to fix it.

The EntryDate field is Date formated.
Any help would be appreciated.


tblTest:

StudentName TestDate Score
Smith 1/12/07 90
Smith 12/1/06 56
Jones 1/12/06 95
Jones 12/10/06 85
Jones 12/25/06 90
Jackson 12/31/06 100

The query could look like this:
DELETE * FROM tblTest WHERE EXISTS(SELECT * FROM tblTest AS tmp
WHERE tmp.StudentID = tblTest.StudentID AND
tmp.EntryDate > tblTest.EntryDate)

 

[John Vinson]

It says syntax error. I had to change the " to the '.

Please try what I suggested: rather than using RunSQL, create and save
the Query and just execute it.

John W. Vinson[MVP]

 

[Jay]

Please try what I suggested: rather than using RunSQL, create and save
the Query and just execute it.

John W. Vinson[MVP]

Ok

 

[Jay]

Please try what I suggested: rather than using RunSQL, create and save
the Query and just execute it.

John W. Vinson[MVP]

I paste it into the sql view in a simple query and syntax error
happens and the Format is highlighted. I tried to edit it in other ways and
it fails.

I am not sure where you want it put at. I tried in the criteria too.

 

[Jay]

I paste it into the sql view in a simple query and syntax error
happens and the Format is highlighted. I tried to edit it in other ways
and it fails.

I am not sure where you want it put at. I tried in the criteria too.

I just tried the first Delete you gave me and it maybe working. I will
check it out later.

 

[John W. Vinson]

I just tried the first Delete you gave me and it maybe working. I will
check it out later.

I see that I did have a typo: left out an ampersand. Does

DELETE * FROM tblTest WHERE [TestDate] < "#" &
Format(DMax("[TestDate]",
"[tblTest]", "[StudentID] = """ & [StudentId] & """"), "mm/dd/yyyy") &
"#"

work?

John W. Vinson [MVP]

 

[Jay]

I just tried the first Delete you gave me and it maybe working. I will
check it out later.

I see that I did have a typo: left out an ampersand. Does

DELETE * FROM tblTest WHERE [TestDate] < "#" &
Format(DMax("[TestDate]",
"[tblTest]", "[StudentID] = """ & [StudentId] & """"), "mm/dd/yyyy") &
"#"

work?

John W. Vinson [MVP]

I will try later at work

 

[Jay]

I just tried the first Delete you gave me and it maybe working. I will
check it out later.

I see that I did have a typo: left out an ampersand. Does

DELETE * FROM tblTest WHERE [TestDate] < "#" &
Format(DMax("[TestDate]",
"[tblTest]", "[StudentID] = """ & [StudentId] & """"), "mm/dd/yyyy") &
"#"

work?

John W. Vinson [MVP]

I will try later at work

no it failed to work. Doesn't look like an easy one to do.