Merge Like Records

[theoldskoolninja]

Hi I hope someone can help.

I have a table with records in like the follwowing examples.

Record 1
ID - 1
AccountID - 123
Name - Mr Smith
Address - 38 The Close
PetName - Tilly

Record 2
ID - 2
AccountID - 123
Name - Mr Smith
Address - 38 The Close
PetName - Chloe

Record 3
ID - 3
AccountID - 124
Name - Mr Jones
Address - 49 The Avenue
PetName - Francis

I would like to be able to run an update query that would merge the records with the same Account ID by PetName so that my table would now look like this

Record 1
ID - 1
AccountID - 123
Name - Mr Smith
Address - 38 The Close
PetName - Tilly & Chloe

Record 3
ID - 3
AccountID - 124
Name - Mr Jones
Address - 49 The Avenue
PetName - Francis

So record 2 would have been removed but the Pet that belongs merged with an & into record 1.

It's had me puzzled me for some time.

Thanks to any replies in advance.

theoldskoolninja

 

[Simon_MT]

I would create a new Table with:

ID
PetName

Simon

 

[theoldskoolninja]

hi thanks for your response, i dont quite follow tho.

how would i go about the & merge of data

 

[The_Doc_Man]

Read up on normalization. Merging two fields into one is a form of denormalization. Now if this is a one-time thing and the table isn't that large, do it by hand. If you plan to do this frequently, don't do it this way. That's where "Database Normalization" becomes your friend.

Doing this via query is surprisingly tricky since it would require you to do a form of what we call a self-join. That is because you want to act on a record based on the contents of another record in the same table.

Doing it with VBA is easier but requires you to write a VBA loop based on multiple clones of a recordset. If you are a programming beginner, this is also not going to be your favorite way to dive into Access.

Doing this via normalization and a query would be a piece of cake compared to directly appending pet names into a single field. Not to mention size allowances because of some of the crazy names you run across.

 

[Simon_MT]

There would be multiple records for each Client with multiple Pets but a unique record in the Client Table. This is the normalised way of handling this siutation. You could use a CrossTab query to flatten the Client Pet File. The non normalised is to create a new Table Copy Definitions only i.e. the same as your existing table. Create an Append query grouping on ID but don't update the PetName Field. An update Query with Joins into the Original Table using the following rationale:

iif(IsNull([New Table].PetName)

Set [New Table].PetName = [Original Table].[PetName]

else

Set [New Table].PetName = [New Table].PetName & " & " & [Original Table].PetName

Basically you want to concatenate the PetName when required. I do perfer the normalised way. The syntax is not correct but that is how I would approach the problem in a non normalised way.

Simon

 

[rsmonkey]

as Doc man said read up on normalization otherwise you will find youself 6ft under in complex queries / vba scripting and then you will hate access forever. from wikipedia

Database normalization is a technique for designing relational database tables to minimize duplication of information and, in so doing, to safeguard the database against certain types of logical or structural problems. For example, when multiple instances of a given piece of information occur in a table, the possibility exists that these instances will not be kept consistent when the data within the table is updated, leading to a loss of data integrity

now you know how important it is

 

[theoldskoolninja]

hi guys

thanks very much for your responses.

i should of mentioned the data does not need to be stored in the database i recive it monthly in csv with seperate records for all the pets regardless of the pet owner and essentially have to cancatonate the Pet Names manually and then run a mail merge. once the letter has been printed the list is not needed. i am just looking to import the list into the database join the pet names together then export my new list to excel for mail merge.

also the info on normalistation is interesting i have just started to get into databases and will find it very useful.

thanks
theoldskoolninja

 

[Cosmos75]

I agree with The_Doc_Man that the data is not normalized and it would be better to have it normalized. Learning to normalize your data is the foundation of learning databases since almost everything else follows from how you normalize your data. It was one of the hardest things for me to learn (and I'm still learning).

That being said, it is possible to accomplish what you want with the data the way it is. It not efficient nor speedy.
DConcat function - Concatenating field values for records matching a query's GROUP / SORT BY or WHERE clause . (old link).

 

[GaryPanic]

I think we have the message on this one

if you are getting these by CSV files then there might be something you can do

import your account into a new table
now import pets name and the Account ID
Now essential you have "Normalisation"

we now need to join the account id (Priamry key) and your Petname tie to the Foreighn Key in this table (which is the Accountid)

2 tables one with name addres etc the other with pets name

this allows 1 account to have more than 1 pet

run your merge (Why merge stay in access) based ona the account id - with a join on the fk in the pet table

once you have wrap your head round this you can look at automation of this process

-