Combining data that shares a record number in Access 2007 (or Excel)

HELP! I have three Excel files from a member database from our museum that I need to combine in Access and then create a mail merge letter. The three files are:
1. General Member Info
2. Spouse/Partner info
3. Children info

The common field among the three is MbrNo

The system manager who pulled the info can not give me anything but an excel spread that lists separate records as listed below and is being less than sympathetic to my problem. :(

So there's always a record in the general info, sometimes one in the spouse BUT sometimes multiple in the children one since each child has their own record (using the same MbrNo!). The children are the problem.

When I combine them in Access, it's fine with merging the files but creates duplicate listings if they have multiple children since  it treats each child record separately. So somehow I need to combine the information in the child records before doing the relationship/joining with the other two files or I get duplicate records.

How do I run a query or SQL to do this?

The three fields are

MbrNo (unique to member but if someone has multiple kids, it's the same number)

What I'm looking to do is have the child file with the same Mbr No have the names all on one line, creating Child1First Name, Child1Year, Child2First Name, Child2Year etc.

I saw two similar questions on this board from way back but they didn't translate -- one only had one or two possible values (English or Math) and the other, frankly, was way to technical for me to see how to customize/follow.


The third one is where the problem comes in.

Like records across the three files share a MbrNo  
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Aaron TomoskyDirector of Solutions ConsultingCommented:
With sqlserver you use concat(). I'm not sure if access supports this.
StumpedAccessAmateurAuthor Commented:
Thanks for writing. I'm stuck with finding something that can work directly. As my user name confesses, I am an Access amateur so really need specific help. Thanks again.
Looks like you need to first work on the table to get some normalisation.

My suggestion is that you have one table with all the entity (parent and children) each with separate member ID.

The MbrNo will eventually have to go but to start with we will use is to extablish the parent child relationship.

The end tables will be:

tblEntity which contains everybody's records. The fields of this table should be as follows:

we will also include MbrNo temporarily to help bring things together and delete that field once we have complete the setup.

You also need:

tblMember,tblSpouse and tblChild.

tblMember should have the following fields:

fldEntityID will be a lookup field which extract its value from tblEntity
fldMbrID is an autoNumber and is the primary key

tblChild should have the following field

Again fldEntityID and fldMbrID are lookup fields fldChildID is autonumber and primary key.fldMbrID is the member iD of the child's parent.

tblSpouse has the following fields:
fldSpouseID,fldEntityID,fldMbrID same logic as the child table.

As you can see most of the info are stored in the entity table. The other two tables just stores the relationship between the entities.

Now to get from excel spreadsheet to the above tables

Import all the spreadsheet into assess then we will use make table quires to create the three new tables.

First make table query is

Select * into tblEntity from [General Member Info]

Then view the new tblEntity in design view. Insert a new field call MbrID and make it auto number.

Select fldEntityID,MbrNo into tblMemeber from tblEntity

Insert an auto number field called fldMbrID to this table

Then create tblMember from tblEntity. This must be done before adding the spouse and children.

Now we will add records from Spouse/Partner info and Children info to the entity table.
Use the append query

INSERT INTO tblEntity ( fldName,MbrNO,relationship  )
select name,MbrNO,"child"  
FROM [Children info]

INSERT INTO tblEntity ( fldName,MbrNO,relationship )
select name,MbrNO,"spouse"
FROM [Spouse_Partner info] Notice that I have replace the front slash by an underscore to avoid error.

This completes the entity table

Notice that I have inserted a relationship field. This will be deleted after we create the spouse and children table. Please tell me if you like this approach and I will guide you through to complete the process

Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

StumpedAccessAmateurAuthor Commented:
Thank you for such a detailed response. If I'm following the logic correctly (forgive me if I'm not), is it assuming the member number isn't the same? The only constant is the member number to the member, spouse and child(ren). This member number also needs to be pulled out in the merge (meaning I can't lose the number). If this still works, I'd greatly appreciate the step by step. Thanks again!
Patrick MatthewsCommented:
>>With sqlserver you use concat().

There is no such function for SQL Server.  SQL Server uses + as a concatenation operator, although you can also use FOR XML PATH as a concatenator in SQL Server 2005 and later.  (Perhaps STUFF also, but that is just a guess.)

MySQL has a Concat() function, and of course MySQL <> SQL Server.
Yes I am assuming that a member can share their member number with the spouse and children.
The DB structure will look like the diagram below. Note that I have made a slight change from my original post. The children and spouse table has been replaced by the family table. A field in that table is used to identify the relationship of that person to the member.
 BD relationship diagram
It is best that you do not have MbrNo for the Spouse and Children. I can ellaborate on this if you want but for know I will live it at that.

By using queries you can construct mail merge for any entity in the database. I note that I am assuming that the children are living with the member. In real life this may not be always true. A member may get a divorce and loose custody of the child but still want the child to access the museum under his or her MbrNo. However, in this case the child's address will be different. For know don't worry about that. Just take care of the basic and we'll deal with that later.
StumpedAccessAmateurAuthor Commented:
Looks like this will work just fine. I am so grateful that you understand the question and, even better, have a solution, any walk through guide would be greatly appreciated.

Thanks again.

OK, I have create a demo to help walk you through the process.

The General Member Info, Spouse_Partner info and Children info is based on what I believe your spreadsheet roughly looks like.

I have a number of queries that will be used to create the new database from the above tables. The tables that they create is already in the database so that you can have a look at them before you start. But you will have to delete them before you go through the following steps so as to replicate the creation of a DB from excel spreadsheets.

Now we start stepping through

Step 1: Delete every table in my sample except General Member Info, Spouse_Partner info and Children info

Step 2: Run qryMakeNewEntityTable

Step 3: Open tblEntity in design view. Create an autonumber field called fldEntityID. make this field the primary key. Create two other fields tempChildID, tempSpouseID both number data type.

Step 4: Run qryMakeMemberTable

Step 5: Open tblMembers in design view. Change fldEntityID from autonumber to number.Close and save tblMember

Step 6: Open tblMembers in design view again.Create an autonumber field called fldMemberID. Make this field the primary key

Step: 7: Open Children info in design view. Create a field called tempChildID (autonumber).In Spouse_Partner info create similar field called tempSpouseID

Step 8: Run qryAppendChildEntities and qryAppendSpouseEntities

Step 9 Create a table called tblRelationship with the following fields fldRelationshipID (autonumber), fldRelationship (text). Save this table. Then open it and add the records child and spouse. The relationshipID should be 1 and 2 respectively.

Step 10: Create a table called tblFamily with the following fields fldFamilyID (autonumber, primary key),fldEntityID (number), fldMemberID (number), fldRelationshipID (use the lookup wizard to lookup to the fldRelationshipID in tblRelationship)

Step 11: Run qryAppendMembersChildren and qryAppendMembersSpouse

Step 12: Delete MbrNo field from tblEntity

That's IT.

 Check frmMembers for confirmation that the families are linked together

Off course there is still a lot of work to do but you now have a relational database. Enjoy


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
StumpedAccessAmateurAuthor Commented:
Person was amazing. Not only answered it but created a full step by step on how to do it. Truly appreciated.
Thanks for your nice comments. Glad to here that it is all working for you.

However, before you go further, remember this statement:

"I note that I am assuming that the children are living with the member. In real life this may not be always true. A member may get a divorce and loose custody of the child but still want the child to access the museum under his or her MbrNo. However, in this case the child's address will be different. For know don't worry about that. Just take care of the basic and we'll deal with that later."

I strongly suggest that you do address these situation so that your application can handle real life situations. An extra yes/no field in the relationship table to specify if the child/spouse is living with the member plus union query will do the job. If you need help with that just ask on expert exchange and me or some other experts will be too happy to help.

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.