Combining Field Data

[Stacy]

Hi!
I am working with Access 2000 and I am trying to combine data for "same
customer" records into one customer record...
I receive a file from someone that looks similar to this:

lastname firstname address phone
smith john 123 St 555-1111
smith john 567 Dr 555-1212

I need to export a file showing only ONE John Smith but with both
Addresses and both phone numbers.
Something like this:

lastname firstname address phone
smith john 123 St; 567 Dr 555-1111; 555-1212

Is this possible?
If so, how do I go about getting my data to look like the above?

Thanks for any advice.
Stacy

 

[Guest]

Hi, Stacy.

This *sounds* like a bad idea because it's trying to represent a one-to-many
relationship in a flat file. For example, how many John Smith's can there
be? 2, 1000? Does your file need to be in a specific format for computer
processing, or is it just a written report for someone to review? If it's
the latter, the easy way to do it would be to create a report, grouping on
the name, and putting the address and phone fields in the Detail section.
That would give you something like:

Smith John
123 St. 555-1111
567 Dr. 555-1212

Hope that helps.
Sprinks

 

[Stacy]

There are usually 3 - 5 records per name, and after I import this file
into Access for some updating, I need to export the file as a flat file
for importing into another program. In my process, Access is acting
basically as a middle-man, not as a true database.

 

[Admiral Access via AccessMonster.com]

You gonna need to write a module for this one. Create a recordset of the
table sorting by last name and first name. Store the address and phone
number of the current record in 2 separate variables. Cycle through the
table with a loop. If the current name is the same as the previous recrods
name, append the address and phone to the variable.

 

[Stacy]

Great Thanks!

 

[Stacy]

Ok, so now I am stuck in a do...loop nightmare.
Not exceptional with vb, can someone just walk me through how the code
should look?
Thanks!

 

[John Vinson]

Ok, so now I am stuck in a do...loop nightmare.
Not exceptional with vb, can someone just walk me through how the code
should look?
Thanks!

ummm...

Stacy, you're assuming that either we can see your database and your
code, or that you're the only person posting questions here, or
possibly that we can all remember all of your previous postings.

Please post enough context and samples of your code that someone not
intimately familiar with your database might have a chance of
answering. I'm not going to google for your name just to save you the
few seconds it would have taken to repost.

John W. Vinson[MVP]

 

[uriel]

Great Thanks!

 

[Stacy]

Ok, so I have most of my code sketched out, but I am still having
issues....
My Code (below) adds the records to my new table fine, however, only
the first name on my new table is looked at for potential duplicates,
and I need it to scan the whole table for duplicate names, not just the
first one. Any ideas?

Plus, how do I get around the initial new table being empty. I keep
getting a "no current record" error.

Any help is appreciated.
I am not great with VB code, just a novice trying to get my code to
create miracles.
Have a good day.
Stacy


Dim Str As String
Dim rst As DAO.Recordset
Dim rst2 As DAO.Recordset
Dim db As DAO.Database
Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT * FROM tblMasterPeople order by
full_name")
Set rst2 = db.OpenRecordset("SELECT * FROM tblFinCENCustomers order by
full_name")
rst.MoveFirst

Do Until rst.EOF
If rst!full_name = rst2!full_name Then
With rst2
.Edit
!newdob = !dob & " ; " & rst!dob
.Update
End With
rst.MoveNext
Else
With rst2
.AddNew
!tracking_number = rst!tracking_number
!Entity = rst!Entity
!last_name = rst!last_name
!first_name = rst!first_name
!middle_name = rst!middle_name
!suffix = rst!suffix
!full_name = rst!full_name
!dob = rst!dob
.Update
End With
rst.MoveNext
End If
Loop

rst.Close
rst2.Close
MsgBox "Complete"