Access 97 question 3

[tdpman]

I have a Access table that contains many duplicate records. One of the fields within this table is a date field. I am needing some code (or something) that will delete all the duplicate records except the one with the earliest date. Any ideas? THX!

Examples:

SO NUMBER LOA LOC SO VERSION LOA OUTPUT CODE DATE LOA SENT
1959066 WNCH.CPT 99 F 1/7/02
1959066 WNCH.CPT 98 F 1/7/02
1959066 WNCH.CPT 99 F 1/7/02
1959066 WNCH.CPT 98 F 1/7/02
1959066 WNCH.CPT 98 F 1/7/02
1959066 WNCH.CPT 99 F 1/7/02
1959066 WNCH.CPT 99 F 1/10/02
1959066 MGTW.CPT 98 F 1/10/02

 

[tdpman]

NOTE: I need it to look at duplicates based upon the first field only. The other fields are duplicates as well but do not matter for my purpose.

 

[DanJR]

Hi there, with your sample data, there are 6 records that have a 'minimum date'. Therefore, do you want delete only the records that don't have a date of 1/7/02, which leaves 6 records....or do you want all records deleted except the first?

Cheers

 

[DanJR]

....if you want all records assoicated with the minimum date kept then try this..

--First, make a backup of your table (very important )....


1. Do you have a field that uniquely identifies each record (primary key)? If not, then create an autonumber field and call it UniqueID.

Therefore your table (i've called it 'Table1') would have the following fields:
UniqueID
SO NUMBER
LOA LOC
SO VERSION
LOA OUTPUT CODE
DATE LOA SENT

-i've listed the table names just incase i've got it wrong.

2. The following query uses a subquery to list all UniqueID values taht you want to keep. The query then deletes all records that do not have a UniqueID value in the subquery list.

---
SELECT Table1.*
FROM Table1
WHERE (((Table1.UniqueID) Not In (SELECT Table1.UniqueID
FROM Table1
WHERE (((Table1.[DATE LOA SENT])=(SELECT min([temp].[Date loa sent]) FROM table1 as temp WHERE temp.[so number] = [table1].[so number]))))));
---

I hope this helps. Please let me know if i've mis-interpreted your question.

Cheers,
Dan

 

[tdpman]

Actually I would like it delete all but one of the duplicates. Only one of the duplicates with the earliest date. I really appreciate your help with this. Thanks!

 

[DanJR]

Hi tdpman,

Sorry for my slow response. If I understand correctly, from your sample data you want only one record left:

1959066 WNCH.CPT 99 F 1/7/02

Here one way to do it, although it may not be the most efficient way, but it should do the job.

<Back up your table>

1. If you don't already have one, create a field that uniquely identifies a row e.g. an autonumber field called &quot;UniqueID&quot;.

2. Create a query that lists the minimum &quot;UniqueID&quot;'s for each set of duplicates

SELECT DISTINCT (SELECT min(UniqueID) FROM table1 as temp WHERE ([temp].[so number] = table1.[so number] and temp.[date loa sent] = table1.[date loa sent])) AS minUniqueID
FROM table1
WHERE (((table1.[DATE LOA SENT])=(SELECT min([date loa sent]) FROM table1 as temp2 WHERE table1.[so number] = temp2.[so number])));

*You may want to turn this query into a table, if you have a lot of data.

I called this query &quot;Query2&quot;


3. Create a query that does the deletion

DELETE myTable.*
FROM table1 AS myTable
WHERE not Exists (Select * FROM query2 WHERE query2.minUniqueID =mytable.UniqueID)


see if this does the trick...

Cheers
Dan

 

[tdpman]

DanJr,

I really appreciate your help on this, but I'm trying to run the query and it doesn't appear to be working right. I run the first query and it basically runs forever. I left it running for 2 hours with no change. Perhaps I'm doing something wrong.

I am creating a new query based on my table and adding all the fields to the query. Do I put your code in the query as &quot;criteria&quot; or what? That is what I was doing. I have created the autonumber field like you said.

Any ideas?

 

[shelron]

okay, I don't know how to do this in sql, but there is a fairly easy workaround.

dump the table to excel,

do a compound sort by your duplicate number and then date field.

then do a simple comparison like this

if (a2=a1,1,&quot;&quot


('a' being the column the duplicat number is in)

copy the formula down, filter on the non blanks, delete the filtered rows, remove the filter and you should have a new list of unique numbers to import back into your application.

ron

 

[SgtJarrow]

I put together some code that will leave at most 1 entry per SO per day found......I am currently about to leave the office, so I can't tweak it any more right now......hope this will help you out.....Just save this in a module, and change any reference to Table1 to your table name.....

Public Function RemoveEntries()

Dim db As DAO.Database
Dim rsList As DAO.Recordset
Dim rsTable As DAO.Recordset
Dim dteDateCheck As Date

Set db = CurrentDb
Set rsList = db.OpenRecordset(&quot;SELECT DISTINCT Table1.[SO NUMBER] FROM Table1;&quot;, dbOpenDynaset)
Set rsTable = db.OpenRecordset(&quot;SELECT Table1.[SO NUMBER], Table1.[LOA LOCATION], Table1.[SO VERSION], Table1.[LOA OUTPUT CODE], Table1.[DATE LOA SENT] &quot; _
& &quot;FROM Table1 &quot; _
& &quot;ORDER BY Table1.[DATE LOA SENT] DESC;&quot;, dbOpenDynaset)

With rsList
.MoveFirst
Do While Not .EOF
dteDateCheck = Date
rsTable.MoveFirst
Do While Not rsTable.EOF
If rsTable.Fields(&quot;SO NUMBER&quot = .Fields(&quot;SO NUMBER&quot Then
If rsTable.Fields(&quot;DATE LOA SENT&quot < dteDateCheck Then
dteDateCheck = rsTable.Fields(&quot;DATE LOA SENT&quot
Else
rsTable.Delete
End If
End If
rsTable.MoveNext
Loop
.MoveNext
Loop
End With

Set rsTable = Nothing
Set rsList = Nothing
Set db = Nothing

End Function
Please remember to give helpful posts the stars they deserve!
This makes the post more visible to others in need!

Robert L. Johnson III, A+, Network+, MCP
Access Developer/Programmer
robert.l.johnson.iii@citigroup.com

 

[DanJR]

My sincere apologies tdpman, i created the query based on dummy data and didn't consider how it would perform under a large(r) dataset. Thats the second time i've done this... I realise this causes more headaches than help.

Robert is on to it...much better solution.


sorry,
Dan

 

[cmmrfrds]

If you have a unique id in the table you can use the following sql statement. This will leave only the minumum sequence number per SO.

Delete from YourTable A
where A.rowid >
(Select min(B.rowid)
from YourTable B
where A.soNumber = B.soNumber)

 

[SgtJarrow]

OK....the code below (modified slightly from the above code) will give finally end with a table called Temp, that holds a list of the [SO NUMBER] and the [DATE LOA SENT]. The date is the earliest for that SO. Let me know if this is what you need....

''''''''''''''''''''start code''''''''''''''''''''''
Public Function RemoveEntries()

Dim db As DAO.Database
Dim rsList As DAO.Recordset
Dim rsTable As DAO.Recordset

DoCmd.DeleteObject acTable, &quot;Temp&quot;

Set db = CurrentDb
Set rsList = db.OpenRecordset(&quot;SELECT Table1.[SO NUMBER], Min(Table1.[DATE LOA SENT]) AS [MinOfDATE LOA SENT] &quot; _
& &quot;FROM Table1 &quot; _
& &quot;GROUP BY Table1.[SO NUMBER];&quot;, dbOpenDynaset)
Set rsTable = db.OpenRecordset(&quot;SELECT Table1.[SO NUMBER], Table1.[LOA LOCATION], Table1.[SO VERSION], Table1.[LOA OUTPUT CODE], Table1.[DATE LOA SENT] &quot; _
& &quot;FROM Table1 &quot; _
& &quot;ORDER BY Table1.[DATE LOA SENT] DESC;&quot;, dbOpenDynaset)

With rsList
.MoveFirst
Do While Not .EOF
rsTable.MoveFirst
Do While Not rsTable.EOF
If rsTable.Fields(&quot;SO NUMBER&quot = .Fields(&quot;SO NUMBER&quot Then
If rsTable.Fields(&quot;DATE LOA SENT&quot <> .Fields(&quot;MinOfDATE LOA SENT&quot Then
rsTable.Delete
End If
End If
rsTable.MoveNext
Loop
.MoveNext
Loop
End With

DoCmd.RunSQL (&quot;SELECT DISTINCT Table1.[SO NUMBER], Table1.[DATE LOA SENT] INTO Temp FROM Table1;&quot

Set rsTable = Nothing
Set rsList = Nothing
Set db = Nothing

End Function
'''''''''''''''''''''End Code'''''''''''''''''' Please remember to give helpful posts the stars they deserve!
This makes the post more visible to others in need!

Robert L. Johnson III, A+, Network+, MCP
Access Developer/Programmer
robert.l.johnson.iii@citigroup.com

 

[tdpman]

I might be doing something wrong, but when I try to run this code, I get a Run Time error. It says, &quot;Too Few Parameters, Expected 1.&quot;.

This is the point in the code that it says that:

Set rsTable = db.OpenRecordset(&quot;SELECT Table1.[SO NUMBER], Table1.[LOA LOCATION], Table1.[SO VERSION], Table1.[LOA OUTPUT CODE], Table1.[DATE LOA SENT] &quot; _
& &quot;FROM Table1 &quot; _
& &quot;ORDER BY Table1.[DATE LOA SENT] DESC;&quot;, dbOpenDynaset)

 

[SgtJarrow]

Which version of Access are you using??? Please remember to give helpful posts the stars they deserve!
This makes the post more visible to others in need!

Robert L. Johnson III, A+, Network+, MCP
Access Developer/Programmer
robert.l.johnson.iii@citigroup.com

 

[tdpman]

Access '97

 

[SgtJarrow]

Not to sound like a wierdo or something...but first try to repair and compact the database then compile all modules in your database. After that, if you are still unsuccessful, try deleting the module you created and then create a new one..... Please remember to give helpful posts the stars they deserve!
This makes the post more visible to others in need!

Robert L. Johnson III, A+, Network+, MCP
Access Developer/Programmer
robert.l.johnson.iii@citigroup.com

 

[SgtJarrow]

Then, if you are STILL having difficulties, send me an email and I will send you the sample db I put together for this.... Please remember to give helpful posts the stars they deserve!
This makes the post more visible to others in need!

Robert L. Johnson III, A+, Network+, MCP
Access Developer/Programmer
robert.l.johnson.iii@citigroup.com

 

[shelron]

How many records are we talking about?

Ron

 

[tdpman]

k, will do

 

[tdpman]

Roughly 18K