Delete Query to Remove Duplicate Records

[Janice7]

First time user of this forum, hopefully someone can help me. I have a table that looks something like:

ClientID StatusChangeDate
001 06/25/2002
001* 06/21/2002
002 07/01/2002
003 06/29/2002
003* 06/25/2002
003* 05/14/2002
... ...

Its sorted w/ ClientID increasing & StatusChangeDate decreasing. I want to design a delete query (something I know nothing about) to remove the duplicate ClientID records. In other words, records marked with a star (*) needs to be deleted. Can anyone help me?

Thanks

Erin

 

[jjonesal]

What about using a subquery something like this:

SELECT Table1.clientid, Table1.thedate
FROM Table1
WHERE Table1.thedate <
(SELECT max(Table2.thedate)
FROM Table1 as Table2
Where Table1.clientid = Table2.clientid
Group by clientid);
J. Jones
jjones@cybrtyme.com

 

[Janice7]

What is Table2 though?

 

[jjonesal]

Basically, it's another copy of the same table...you are just calling it Table2

Notice in the inner SELECT statement (the subquery):

(SELECT max(Table2.thedate)
FROM Table1 as Table2
Where Table1.clientid = Table2.clientid
Group by clientid);

The FROM clause really references Table1 (your table) but just references it AS Table2.....So Access thinks it has two tables, but in fact it only has one (just using the same one multiple times).

There may be other ways to do this, but you are restricted by the fact that in SQL if a field is included in the select list and doesn't use a group function, it MUST be included in the GROUP BY clause. To get around this, we're basically breaking the problem apart, doing the inner query first to get one result (an interim result) and passing that result to the outer query to get your final answer...

Does that make sense?

Basically, you can copy and paste the SQL in and change all references to Table1 to be YOUR tablename and leave all the Table2 references alone because it really doesn't matter what that second table instance is called.




J. Jones
jjones@cybrtyme.com