Solved

Merging an unknow number of records

Posted on 2014-04-21
5
329 Views
Last Modified: 2014-04-21
I have a mastermember table in which each record represents a family. The key field is [membershipID]. I have a members table which represents individual people. The key field is [memberID]. Any number of people may represent a family. I need to develop a table which contains a single row for each family and each member of the family needs to be included.

I need this because I've been ask to produce a spreadsheet containing all family members which can be used in a mail merge.

Creating a query to select the [head_of_house] is simple but how do you select the other family members when there may be none, one or any number?

Using the members table I have the following fields.
membershipID ; used to group the family
memberID ; to identify each member of the family
membertype ; head, spouse, son, daughter

I know there needs to be a loop involved by I don't know how to execute it.
Loop through all [membertype = son] where [membershipID = head.membershipID] ?

Any suggestion on a concept, a direction to start?
Attached is a sample database and spreadsheet.
family.accdb
family.xlsx
0
Comment
Question by:garysamuels
  • 2
  • 2
5 Comments
 
LVL 34

Expert Comment

by:PatHartman
ID: 40012632
Here is a function written by Allen Browne that will do the concatenation for you.

http://allenbrowne.com/func-concat.html
0
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
ID: 40012770
0
 

Author Comment

by:garysamuels
ID: 40013113
As instructed I inserted a new module which was named Module 2. I also added the function to the end of Module 1, and the Global Module.

I tried it but all I could get was "Undefined function 'ConcatRelated' in function".
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 40013156
The procedure needs to be in ONE and ONLY ONE module.  That is because the procedure MUST be public in order to be referenced from a query.  If multiple modules contain the same procedure name, you will get compile errors.

So,
1. Put the procedure  in ONE module.
2. Make sure the procedure is defined as Public.
3. Make sure everything compiles.
4. Make sure that there are no typos.
0
 

Author Closing Comment

by:garysamuels
ID: 40013183
It Worked.
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now