How do you delete duplicate records?

[aageorge]

I have a query that gives me the following table(Thanks Mike Pastore):

Date Line Downtime Reason
07/10 A 2 d1
07/10 B 1 d2
07/10 C 3 d3
07/10 C 3 d1
07/10 C 3 d2

I need to get the following table

Date Line Downtime Reason
07/10 A 2 d1
07/10 B 1 d2
07/10 C 3 d3

How can I do this? I only need one occurence for particular set of date, line and downtime combination. In the above example line C had three downtime reasons with the same amount of downtime. In this case I need only one occurence and ideally club all the reasons in one entry:

07/10 C 3 d1,d2,d3

Crosstab queries won't work for my use. If it is not possible to club all the reasons, how do I just get one occurence.

 

[GingerR]

ok here you go. this takes your orig table and puts final data into another table.

1) i didnt know what your table name was, so i called it LineDown. you'll have to sub in your table name in this code.

2) create a table called LineDownFinal, with the same structure as your orig table (Date, Line, Downtime, Reason). dont know how big you need the reason field (255?)

3) put this function into a new module. change the table name (LineDown) if you need to. run the function - it will make your final data.

===================================


Function MakeNewTable()
Dim rsReason, rsUnique, rsFinal As Recordset
Dim strSQLReason, strSQLUnique, strSQLFinal As String
Dim strReasonFinal As String 'Final string of all reasons with commas in between

'Delete old records from final table
CurrentDb.Execute "Delete * from LineDownFinal"


'This gets unique combination of Date + Line + Downtime
strSQLUnique = "SELECT LineDown.Date, LineDown.Line, LineDown.Downtime from LineDown GROUP BY [LineDown].Date, [LineDown].Line, [LineDown].Downtime"
Set rsUnique = CurrentDb.OpenRecordset(strSQLUnique)

'Opens FINAL table (which is empty at first)
strSQLFinal = "Select * from LineDownFinal"
Set rsFinal = CurrentDb.OpenRecordset(strSQLFinal)

'Go to first record in UNIQUE recordset
rsUnique.MoveFirst

'Loop thru UNIQUE records, calculate REASON string, and plop all the data into the FINAL table
While Not rsUnique.EOF
rsFinal.AddNew
rsFinal!Date = rsUnique!Date
rsFinal!Line = rsUnique!Line
rsFinal!Downtime = rsUnique!Downtime
strReasonFinal = ""
strSQLReason = "Select LineDown.Reason from LineDown WHERE Linedown.Date = #" & rsUnique!Date & "# and Linedown.Line = '" & rsUnique!Line & "' and linedown.Downtime = " & rsUnique!Downtime
Set rsReason = CurrentDb.OpenRecordset("SELECT [LineDown].Reason FROM [LineDown] WHERE [LineDown].Line = '" & rsUnique!Line & "' ORDER BY LineDown.Reason&quot

rsReason.MoveFirst
While Not rsReason.EOF
If Len(strReasonFinal) = 0 Then
strReasonFinal = rsReason!reason
Else
strReasonFinal = strReasonFinal & ", " & rsReason!reason
End If
rsReason.MoveNext
Wend
rsFinal!reason = strReasonFinal

rsFinal.Update
rsUnique.MoveNext
Wend
End Function

 

[scriverb]

Here's another method:
Copy and paste this Function into a database module, change the red table name to your table name, and call it basReasonString:

Function ReasonString(vDate As Date, vLine As String, vDT As Integer) As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim vReasonString As String, strSQL As String
Set db = CurrentDb
strSQL = "Select * from tblDT as A Order By A.Date, A.Line, A.DownTime, A.Reason;"
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
vReasonString = ""
rs.FindFirst "[Date] = #" & vDate & "# and [Line] = '" & vLine & "' and [DownTime] = " & vDT
Do
If Not rs.NoMatch Then
vReasonString = vReasonString & IIf(vReasonString = "", "", ",&quot & rs("Reason&quot
rs.FindNext "[Date] = #" & vDate & "# and [Line] = '" & vLine & "' and [DownTime] = " & vDT
Else
vReasonString = ""
End If
Loop Until rs.EOF Or rs.NoMatch
ReasonString = vReasonString
rs.CLOSE
db.CLOSE
End Function

Copy this SQL into a new query, change the red table name to your table name, save and run it. This is a make table query that will group and create the reason string using the function and make a new table called tblDownTimeReasons. You change the name of table to be created by modifying it in the below SQL.

SELECT A.Date, A.Line, A.Downtime, ReasonString([Date],[Line],[Downtime]) AS Reasons INTO tblDownTimeReasons
FROM tblDT as A
GROUP BY A.Date, A.Line, A.Downtime;

Post back with any question that you might have.


Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[aageorge]

Thanks for the replies. I figured out to do it using some delete queries.

 

[scriverb]

aageorge: Please do not take this the wrong way I am responding sincerely but, either you didn't communicate correctly what you wanted and both GingerR and myself followed down a completely incorrect path or your last response was written because you didn't understand our responses. Please don't get this wrong but you asked for a Select Query of sorts to group on certain fields and create a linear string of another field. How in the world could you possibily have solved this with a series of delete queries. Either we missed the boat in understanding your needs or you simply don't understand that the code given to you gives you exactly what you want.

We want to help you solve this but I would like to see the SQL for the series of delete queries that solves your problem. Maybe GingerR and I can learn something here in the way of a new technique.

Waiting for your response.

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???