By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,087 Members | 1,513 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,087 IT Pros & Developers. It's quick & easy.

Merging and deleting duplicate records

P: n/a
Hi,

I have an Access table with a number of records which refer to the
same person but with data in different fields. So for example the
table would look like this:

Name..............Field 1...................Field 2
Fred Smith........Red
John Brown........Blue
Fred Smith...................................Truck
John Brown...................................Car

What I'd like to have is a table with:
Name..............Field 1...................Field 2
Fred Smith........Red.......................Truck
John Brown........Blue......................Car

I'm not sure how to go about merging the data for each name into one
record. There are lots of very helpful posting about using SQL to
delete duplicate but I can't find a posting with addresses this issue.

Any suggestions for merging and purging would be appreciated. I'm
happy to be pointed to VB, SQL or whatever.

Thanks in advance.

Emmett Power
Nov 13 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
Emmett Power wrote:
Hi,

I have an Access table with a number of records which refer to the
same person but with data in different fields. So for example the
table would look like this:

Name..............Field 1...................Field 2
Fred Smith........Red
John Brown........Blue
Fred Smith...................................Truck
John Brown...................................Car

What I'd like to have is a table with:
Name..............Field 1...................Field 2
Fred Smith........Red.......................Truck
John Brown........Blue......................Car

I'm not sure how to go about merging the data for each name into one
record. There are lots of very helpful posting about using SQL to
delete duplicate but I can't find a posting with addresses this issue.

Any suggestions for merging and purging would be appreciated. I'm
happy to be pointed to VB, SQL or whatever.

Thanks in advance.

Emmett Power


I would create a table with all of the fields that will be a "final result".

Then I would create a query and append the name and field1 into the new
table. Then using the following code (modifying my table/field names
with your table/field names) update the table. ASSUMPTION. THere can
be one or many records of the same person but if there is a second
record it is has data for the field and the first is blank....like your
example above.

What it does is scans all fields and updates with data from your
existing current tables. At the end, delete the old table and rename
the new.

Sub td()
Dim tdf As TableDef
Dim fld As Field
Dim dbs As Database
Set dbs = CurrentDb
Dim strSQL As String
Dim sf As String

Set tdf = dbs.TableDefs("CurrentTable")
For Each fld In tdf.Fields
sf = fld.Name
If sf <> "Name" And sf <> "Field1" Then
'Name and Field1 already exist when you created
'the new table. Only update the other fields.
strSQL = "UPDATE NewTable INNER JOIN CurrentTable " & _
"ON NewTable." & sf & " = CurrentTable." & sf & _
" SET NewTable." & sf & " = CurrentTable." & sf & _
" WHERE ((Not (CurrentTable." & sf & ") Is Null));

dbs.Execute strSQL
End If
Next
Set tdf = Nothing

End Sub
Nov 13 '05 #2

P: n/a

Salad,

Thanks for the suggestion. I'll give it a go.

Regards

Emmett
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 13 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.