Convert multiple records into a single record

[JudyR]

OK, let me see if I can phrase this in an understandable manner.

I am working on a membership database.

I have a table that contains a single record for each person, with a unique Individual ID.
Each person has a 'Family ID' - all people with the same ID are part of one family.
In each family, there can be up to two adults (Member and Spouse), and any number of children (let's say no more than 6 for now).

So, for example, I have the three families below:

Family ID	Individual ID	First Name	Last Name	Family Position
ABC123	100001	John	Doe	Member
ABC123	100002	Jane	Doe	Spouse
ABC123	100003	David	Doe	Child
ABC123	100004	Lucy	Doe	Child
BCD234	100005	Bill	Jones	Member
CDE345	100006	Kelsey	Smith	Member
CDE345	100007	Eva	Smith	Spouse

What I need is to convert those seven records into three records, like so:

Family ID	Last Name	Adult 1	Adult 2	Child 1	Child 2	Child 3	Child 4	Child 5	Child 6
ABC123	Doe	John	Jane	David	Lucy
BCD234	Jones	Bill
CDE345	Smith	Kelsey	Eva

My first instinct is that I'd need to write a module to do this - a query/report is probably not going to do it. But I'm so rusty at VBA I'm not sure where to start.

I did manage to get the first four columns of my result, with a bunch of queries. But a variable number of children has me stuck.

Ideally, the resulting data would spin out to a text file, but a table is fine, if that's easier.

Any thoughts (other than "You've lost your mind" - I have that one already )?

Thanks!

 

[theDBguy]

Hi. You might be able to get that using some helper queries and one final Crosstab query. Can you post a sample db with test data?

 

[plog]

Think this is going to be easier than you think. 2 words and 1 link:

Crosstab query:

Make summary data easier to read by using a crosstab query - Microsoft Support

Crosstab queries are useful for summarizing data, calculating statistics and then grouping the results.

support.microsoft.com

You'll first need a subquery to order the adults (1&2) and the children (1-6)--you'll use a DCount or correlated subquery to get that. But once you have a query that can turn the Family Position values into the corect values to have as column headings, you will then be able to use that query as a basis for another query which will be your crosstab.

Give it a shot and then post back here any issues you have.

 

[JudyR]

Ah! Crosstabs queries are a weak spot for me.

I did manage to build a crosstab query that gave me a count of each type of family member by their 'Role' code (Member=1, Spouse=2, Child=4). So now I need to use that number of Role '4' records to assign data to appropriate columns?

 

[plog]

Again, you need to build a query before the crosstab to assign the correct values that will become field names. Perhaps 2 queries.

Converting "Spouse" and "Member" is an easy IIf statement: if "Member"->"Adult 1", if "Spouse"->"Adult 2". The real trick is the kids. For them you will need to use a DCOUNT (or correlated subquery--google that). In that DCount you will count how many [Individual ID] values are lower than the one in the current record for each [Family ID] and suffix that to the string "Child".

Something like (but definitely not exactly because I am freehand writing) this:

ChildCount: 1 + DCount("[Individual ID]", "YourTableNAmeHere", "[Family ID]=" & [Family ID] & " AND [Family Position]='Child' AND [Individual ID]<" & [Individiaul ID])

Again, again--you are not ready for the crosstab part of this until you can figure out the above. You need to use a regular query to assign the appropriate family position to each record before you start to mess with the cross tab portion of this.

 

[JudyR]

Thanks! I'll plod along and see what I can work out.