StumpedAccessAmateur
asked on
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)
ChildFirstName
ChildYear
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.
HELP!
The third one is where the problem comes in.
Like records across the three files share a MbrNo
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)
ChildFirstName
ChildYear
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.
HELP!
The third one is where the problem comes in.
Like records across the three files share a MbrNo
Aaron Tomosky
With sqlserver you use concat(). I'm not sure if access supports this.
ASKER
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:
fldEntityID,fldName,fldDOB
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:
fldMbrID,fldEntityID.
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
fldChildID,fldEntityID,fld
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,fl
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
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:
fldEntityID,fldName,fldDOB
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:
fldMbrID,fldEntityID.
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
fldChildID,fldEntityID,fld
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,fl
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
ASKER
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!
>>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.
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.
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.
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.
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.
ASKER
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.
Thanks again.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.