Query needed to list multiple values related to key field

[jawshark]

PLEASE HELP!!

I'm looking for a way to do a phone book that lists a family's last name, followed by the first name of each member of the family.

I see it in my mind this way:

ADAMS John, Sue, Bill                123-456-7890
CARNEGIE Andrew, Julie, Mabel        123-222-0000
SMITH Heather, Adrian                477-567-9874

My end result is a report that will be used as a directory. I have a Family table that indexes families and an individuals table that contains a persons 'Family ID' from the families table. I can use grouping in my report, but I can't get the format I want because the lengths of both the last names and first names change.

Thanks,
jawshark

11th Commandment - Love thy neighbor as thyself

 

[mikey69]

You should probably be using a Union Query. You'll have to write the SQL, because there is no grid for Union Queries. The simplest way to start is to use the grid for a simple query, then switch to SAL view and copy the SQL statemnet, add the keyword "UNION' at the start of the copy and then change fields as necessary. It's pretty intuyitive, one you understand the principle.

God luck!

 

[mikey69]

You should probably be using a Union Query. You'll have to write the SQL, because there is no grid for Union Queries. The simplest way to start is to use the grid for a simple query, then switch to SQL view and copy the SQL statemnet, add the keyword "UNION' at the start of the copy and then change fields as necessary. It's pretty intuyitive, one you understand the principle.

God luck!

 

[bustell]

I am not sure you can easily do (with Access) what you want to do, nor am I sure you would what you do it that way.

From your description, it sounds as if you have the phone number in the Families table. This would not be the best way to store it.

It would be better if the phone number was in the members table. In your example you would have to assume that all these people live under that same roof and all have the same phone number. A few years back this might have been acceptable, but with cell phones, a family of 3 could have 3 different phone numbers.

With this method you could order by Family Name and then by Phone Number while hiding duplicate Family Names and Phone Numbers. Additionally, you could display some other information on the members such as family role(Mother, Father, Son, Daughter, etc) or birthdays.

Then your report would look like:
ADAMS 123-456-7890 John
Sue
Bill

CARNEGIE 123-222-0000 Andrew
Julie
123-222-1111 Mabel

SMITH 477-567-9874 Heather
Adrian

Back to your request, If I think of a solution, I'll post it.
Pat B

 

[riny]

I am assuming you want two alignments, one for the people, one for the telephone numbers.

1>Make query1 (input tables that join family table and members table on "Family ID&quot, output fields:
[Family ID]:
FamilyName: UCase([Family] & " "
Member1: [First member]
Member2: [Second member]
etc for the max number of members foreseen.
[Telephone]:

2>Now make query2, input is query1, with fields:

[Family ID]:
[FamilyName]:
Memberssub: Iif(IsNull([Member2]),[Member1], Iif(IsNull([Member3]),[Member1] & ", " & [Member2]),[Member1] & ", " & [Member2] & ", " & [Member3])
[Telephone]:

3>You could nest the Iif's deeper and be finished, but if the list gets long, just make another query3, input is query2 and table members joined on "family id", with fields:
(we have now covered up to 3 members)

[Family ID]:
[FamilyName]:
MembersMore: Iif(IsNull([Member5]),Iif([Member4],[Memberssub], [Memberssub] & ", " & [Member4]),[Members] & ", " & [Member4] & ", " & [Member5])
[Telephone]:

4>If 5 members is enough then query3 is the input to your report, else make a query4 using the same method to go further.

Cheers!
Riny

 

[riny]

I am assuming you want two alignments, one for the people, one for the telephone numbers.

1>Make query1 (input tables that join family table and members table on "Family ID&quot, output fields:
[Family ID]:
FamilyName: UCase([Family] & " "
Member1: [First member]
Member2: [Second member]
etc for the max number of members foreseen.
[Telephone]:

2>Now make query2, input is query1, with fields:

[Family ID]:
[FamilyName]:
Memberssub: Iif(IsNull([Member2]),[Member1], Iif(IsNull([Member3]),[Member1] & ", " & [Member2]),[Member1] & ", " & [Member2] & ", " & [Member3])
[Telephone]:

3>You could nest the Iif's deeper and be finished, but if the list gets long, just make another query3, input is query2 and table members joined on "family id", with fields:
(we have now covered up to 3 members)

[Family ID]:
[FamilyName]:
MembersMore: Iif(IsNull([Member5]),Iif(IsNull([Member4]),[Memberssub], [Memberssub] & ", " & [Member4]),[Members] & ", " & [Member4] & ", " & [Member5])
[Telephone]:

4>If 5 members is enough then query3 is the input to your report, else make a query4 using the same method to go further.

Cheers!
Riny

 

[riny]

Sorry for double post, I corrected query3 regarding member4
Riny

 

[bustell]

To Riny, That really isn't the best database design because it assumes a maximun number of members. Additionally, I do not see how step 1 will work to reduce 3 Family/Member records down to one record per Family.
Pat B

 

[jawshark]

Thank you all so far!

The main problem I have is getting the first names to print on a report next to the family last name. I have the last name in a group header and the first names in the detail section. I want the first names to print next to the family last name. Columns don't work because there is no autosizing. I can get the following using columns, but its not the desired output:

ADAMS     John     Sue      Bill                123-456-7890
CARNEGIE  Andrew   Julie    Mabel               123-222-0000
SMITH     Heather  Adrian                       477-567-9874

I'd like this to be readable and have the first names appear next to the the family last name like the sample below:

ADAMS John, Sue, Bill                123-456-7890
CARNEGIE Andrew, Julie, Mabel        123-222-0000
SMITH Heather, Adrian                477-567-9874

I'm working on some code that reads a query to find out how many members are in each family and loop through by family ID and concatenate the members names and store that as a text field in the Family table, but it seems inefficient and poor database technique.

Thanks again,
Mike (jawshark)

 

[riny]

Hello Pat B,

Your first comment makes sense to me but does not give the presentation jawshark wanted, which is what the question was, so I just wanted to answer the exact question as a purely technical matter.

You pointed out family members could have their own phone numbers, I might add that if the directory is one of families living under one roof, these days families will rarely have more than say 10 members, the report design jawshark has in mind will look awful if the concept of family could go up to say 25 members (the telephone number would be way over to the right for all families whilst most only have 2-4 members)

You are absolutely right about query1 as presented.
I based myself on a simple environment (limited number of family members), so that there is only a need for one table, with family id (in case of duplicate family names), family name, member1, member2, member3 etc up to 10-15 members (after all members are only identified by their first name so no member ID needed).
So obviously there are no table joins (funnily I added that afterwards to be concise -how stupid of me), there is only one table as I conceived the method.
No excuse really, rereading jawsharks's post I realize he/she has 2 tables so I have not strictly answered his/her question.

It got me thinking about how to transpose from a list of member records to field names, I can't see how to do that with queries without creating a new table in vba


Meantime, saw Mike's (jawshark) new post.
Mike, how about adding an unbound text field in the report, and doing the joining-up in there (familyfield & " " & member1 (your col2) & ", " & so on).


Riny