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
