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