delete oldest dates

B

Bart

I have a table that looks like this

Account Number Creation Date Status
01-029-009 10/23/1992 A
01-029-009 10/20/2000 A
01-029-009 6/13/2001 A
01-035-123 8/15/1998 I
01-035-123 11/25/2004 A

I need to find all duplicate Account Numbers and delete all except the
latest Creation Date.

thanks
 
M

MGFoster

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Before trying this solution backup your table (just in case ;-) ), or
substitute SELECT for DELETE. The SELECT will show all records that
will be deleted. Be sure to substitute your table name for "Accounts."
Do not remove the alias "A."

DELETE *
FROM Accounts AS A
WHERE A.[Creation Date]<>(SELECT Max([Creation Date]) FROM Accounts
WHERE [Account Number] = A.[Account Number])

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQ9/gCIechKqOuFEgEQLHdQCdFat13MJ79g9o8miIUkrL7WA6fjkAoL6m
rL26pReAOfFAXT1znJfb7ncY
=+NEU
-----END PGP SIGNATURE-----
 
B

Bart

I'm getting an "Enter Parameter Value" for Accounts.Map Number




MGFoster said:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Before trying this solution backup your table (just in case ;-) ), or
substitute SELECT for DELETE. The SELECT will show all records that
will be deleted. Be sure to substitute your table name for "Accounts."
Do not remove the alias "A."

DELETE *
FROM Accounts AS A
WHERE A.[Creation Date]<>(SELECT Max([Creation Date]) FROM Accounts
WHERE [Account Number] = A.[Account Number])

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQ9/gCIechKqOuFEgEQLHdQCdFat13MJ79g9o8miIUkrL7WA6fjkAoL6m
rL26pReAOfFAXT1znJfb7ncY
=+NEU
-----END PGP SIGNATURE-----

I have a table that looks like this

Account Number Creation Date Status
01-029-009 10/23/1992 A
01-029-009 10/20/2000 A
01-029-009 6/13/2001 A
01-035-123 8/15/1998 I
01-035-123 11/25/2004 A

I need to find all duplicate Account Numbers and delete all except the
latest Creation Date.
Click to expand...
Click to expand...
 
M

MGFoster

What's is Map Number? It wasn't in the original post. Post your SQL.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
I'm getting an "Enter Parameter Value" for Accounts.Map Number




-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Before trying this solution backup your table (just in case ;-) ), or
substitute SELECT for DELETE. The SELECT will show all records that
will be deleted. Be sure to substitute your table name for "Accounts."
Do not remove the alias "A."

DELETE *
FROM Accounts AS A
WHERE A.[Creation Date]<>(SELECT Max([Creation Date]) FROM Accounts
WHERE [Account Number] = A.[Account Number])

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQ9/gCIechKqOuFEgEQLHdQCdFat13MJ79g9o8miIUkrL7WA6fjkAoL6m
rL26pReAOfFAXT1znJfb7ncY
=+NEU
-----END PGP SIGNATURE-----

I have a table that looks like this

Account Number Creation Date Status
01-029-009 10/23/1992 A
01-029-009 10/20/2000 A
01-029-009 6/13/2001 A
01-035-123 8/15/1998 I
01-035-123 11/25/2004 A

I need to find all duplicate Account Numbers and delete all except the
latest Creation Date.
Click to expand...
Click to expand...
Click to expand...
 
B

Bart

Sorry, I mean Account Number and I found my error.

thanks for your help, it worked great


MGFoster said:
What's is Map Number? It wasn't in the original post. Post your SQL.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
I'm getting an "Enter Parameter Value" for Accounts.Map Number




-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Before trying this solution backup your table (just in case ;-) ), or
substitute SELECT for DELETE. The SELECT will show all records that
will be deleted. Be sure to substitute your table name for "Accounts."
Do not remove the alias "A."

DELETE *
FROM Accounts AS A
WHERE A.[Creation Date]<>(SELECT Max([Creation Date]) FROM Accounts
WHERE [Account Number] = A.[Account Number])

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQ9/gCIechKqOuFEgEQLHdQCdFat13MJ79g9o8miIUkrL7WA6fjkAoL6m
rL26pReAOfFAXT1znJfb7ncY
=+NEU
-----END PGP SIGNATURE-----


Bart <bbailey@ wrote:

I have a table that looks like this

Account Number Creation Date Status
01-029-009 10/23/1992 A
01-029-009 10/20/2000 A
01-029-009 6/13/2001 A
01-035-123 8/15/1998 I
01-035-123 11/25/2004 A

I need to find all duplicate Account Numbers and delete all except the
latest Creation Date.
Click to expand...
Click to expand...
Click to expand...
 
Top