Copying records to a new table excluding dups

[BrazilTechie]

Hi everyone:

I have one table called COMPANIES, which was entered companies and addresses multiple times for some of the records.

How can I programatically copy only the unique records (having only one entry for field COMPANYNAME to a new table (COMPANIES_NEW)?

Thanks for your help.

 

[Dale3]

Copy a recordset based on a query using the keyword DISTINCT in a SQL statement. That assumes that you have a field that really is unique. If so, then your SQL query would be something like: SELECT DISTINCT FieldA, FieldB, FieldC FROM Table1

 

[BrazilTechie]

Dale3, I got it!

Thanks for your help and for taking the time.

 

[BrazilTechie]

Hi Dale3 and all:

I do have a problem, I only wanted to get rid of the duplicates based in one field, [company].

If I use DISTINCT it gives all the unique records based on all fields, giving more than one record with the same company.

Please advise.
Thanks.

 

[Dale3]

What fields are different? If the records are really distinct, then you first need to decide which information you are losing and which record(s) you want to keep. For example if the company field was identical but the address was different, then do you want to keep one of the addresses but lose the other? Or do you just want the company field without either address?

 

[BrazilTechie]

Hi Dale3:
Thanks for your response.

The file I have have multiple records for the same company. What I am trying to do is to get the first record of a company to be the only one copied to the new DB.

Let me show how far I have gone and the problem I am still having. Let me know if I am on the right track and, if I am, what wrong with my code.

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

Private Sub SearchForDups_Click()
'==========================================================
' This routine will browse the records of table tblCompanies and copy only the ones with unique company name to
' another table called tblCompanies_NEW
'========================================================== Dim MyRst1 As DAO.Recordset
Dim MyRst2 As DAO.Recordset
Dim stDocName, LastRecord As String
Dim NotDupsCtr As Long
Dim TotRecsCtr As Long

Set MyRst1 = CurrentDb.OpenRecordset("tblCompanies&quot
Set MyRst2 = CurrentDb.OpenRecordset("tblCompanies_NEW&quot
Dim fld As DAO.Field

Set fld = MyRst1("[Company]&quot

MyRst1.MoveLast
MyRst1.MoveFirst

MyRst1.MoveFirst ' Ensure pointer is at first record
LastRecord = fld

' Browse all records looking for duplicate company names
Do While Not MyRst1.EOF
If fld <> LastRecord Then
DoCmd.RunSQL &quot;SELECT &quot; & MyRst1 & &quot; INTO &quot; & MyRst2 & &quot; FROM &quot;&quot; & MyRst2&quot;
LastRecord = fld
NotDupsCtr = NotDupsCtr + 1
End If

TotRecsCtr = TotRecsCtr + 1

MyRst1.MoveNext

Loop

MsgBox &quot;Total number of unique records: &quot; & NotDupsCtr & &quot; &quot; & vbCrLf & _
&quot;Total number of records source tblCompanies: &quot; & TotRecsCtr

MyRst1.Close
MyRst2.Close

Set Rst1 = Nothing
Set Rst2 = Nothing
End Sub

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

I am having problem in the line:
DoCmd.RunSQL &quot;SELECT &quot; & MyRst1 & &quot; INTO &quot; & MyRst2 & &quot; FROM &quot;&quot; & MyRst2&quot;

The word MyRst1 gets highlighed in yellow and this message comes up:
Compile Error
Type Mismatch.



Thanks again.

 

[Dale3]

I think you have to use syntax more like this to insert records into a table:

Docmd.RunSql &quot;Insert into &quot; & rst2 & &quot; select &quot; & rst1

I don't think this solves your whole problem, but give this a try and let me know if it fixes the imeediate syntax error

 

[joshuar]

If you have an autonumber field (like CompanyID) in your table, this will probably be a solution. If you don't, you could add one...

Select * from tblCompanies where CompanyID in (Select SELECT Min(tblCompanies.CompanyID) AS MinOfCompID
FROM tblCompanies
GROUP BY tblCompanies.Company)

Another solution is

SELECT First(tblCompanies.CompanyPhone) AS FirstOfCompanyPhone, First(tblCompanies.CompanyFax) AS FirstOfCompanyFax, ..., tblCompanies.Company
FROM tblCompanies
GROUP BY tblCompanies.Company;

-Joshua

 

[BrazilTechie]

Thank you all very much.

I have gotten the solution based on your help.

Thanks again.