Get multiple rows from related records

[Gina Maylone]

Good morning! I'm trying to put out a directory. The entries should look like this:
Doe, John and Jane
123 Some St.
Some City, ST 12345
555-555-1212 (John Work) 444-444-1414 (Jane Work)

My tables are set up like this:


Most members have more than one phone number, so my first attempt was to pull the telephone number table into a query twice. Which I thought worked, except it left out members that only have one phone number.

INSERT INTO directoryFINAL ( LASTNAME, ComboName, LotNumber, segment, Member, ADDRESS, CITY, STATE, ZIP, T1, TelType, T2 )
SELECT Members.LASTNAME, IIf([SpouseLastName] Is Null And Not ([Spouse] Is Null),[LASTNAME] & ", " & [FIRSTNAME] & " and " & [spouse] & " " & [LASTNAME],IIf([spouselastname]<>[lastname],[lastname] & ", " & [firstname] & " and " & [spouse] & " " & [spouselastname],[lastname] & ", " & [firstname])) AS ComboName, primarylots.LotNumber, primarylots.segment, IIf(Right([comboname],13)=' and  , ',Left([comboname],Len([comboname])-8),[comboname]) AS Member, Members.ADDRESS, Members.CITY, Members.STATE, Members.ZIP, TelephoneNumbers.TelephoneNumber AS T1, TelephoneNumbers.TelType, IIf(Not IsNull([telephonenumbers_1].[TelephoneNumber]),[telephonenumbers_1].[TelephoneNumber],"") AS T2
FROM ((Members INNER JOIN TelephoneNumbers ON Members.MemberID_PK = TelephoneNumbers.MemberID_FK) INNER JOIN primarylots ON Members.MemberID_PK = primarylots.MemberID_PK) INNER JOIN TelephoneNumbers AS TelephoneNumbers_1 ON Members.MemberID_PK = TelephoneNumbers_1.MemberID_FK
WHERE (((TelephoneNumbers.TelephoneNumber) Is Not Null) AND ((IIf(Not IsNull([telephonenumbers_1].[TelephoneNumber]),[telephonenumbers_1].[TelephoneNumber],""))<>[telephonenumbers].[TelephoneNumber]) AND ((Members.Inactive)=False))
ORDER BY Members.LASTNAME;

Then I tried this:

INSERT INTO directoryFINAL ( LASTNAME, ComboName, LotNumber, segment, Member, ADDRESS, CITY, STATE, ZIP, T1, TelType, T2 )
SELECT Members.LASTNAME, IIf([SpouseLastName] Is Null And Not ([Spouse] Is Null),[LASTNAME] & ", " & [FIRSTNAME] & " and " & [spouse] & " " & [LASTNAME],IIf([spouselastname]<>[lastname],[lastname] & ", " & [firstname] & " and " & [spouse] & " " & [spouselastname],[lastname] & ", " & [firstname])) AS ComboName, primarylots.LotNumber, primarylots.segment, IIf(Right([comboname],8)=' and  , ',Left([comboname],Len([comboname])-8),[comboname]) AS Member, Members.ADDRESS, Members.CITY, Members.STATE, Members.ZIP, TelephoneNumbers.TelephoneNumber AS T1, TelephoneNumbers.TelType, IIf(Not IsNull([telephonenumbers_1].[TelephoneNumber]),[telephonenumbers_1].[TelephoneNumber],"") AS T2
FROM ((Members INNER JOIN TelephoneNumbers ON Members.MemberID_PK = TelephoneNumbers.MemberID_FK) INNER JOIN primarylots ON Members.MemberID_PK = primarylots.MemberID_PK) LEFT JOIN TelephoneNumbers AS TelephoneNumbers_1 ON Members.MemberID_PK = TelephoneNumbers_1.MemberID_FK
WHERE (((TelephoneNumbers.TelephoneNumber) Is Not Null) AND ((IIf(Not IsNull([telephonenumbers_1].[TelephoneNumber]),[telephonenumbers_1].[TelephoneNumber],""))<>[telephonenumbers].[TelephoneNumber]) AND ((Members.Inactive)=False))
ORDER BY Members.LASTNAME;

Which ends up leaving the second phone number blank on everyone.

How can I get 2 phone numbers into the export table when they exist and just one when only one exists. Thank you in advance.

[CJ_London]

suggest look at allen browne's concatrelated function - http://allenbrowne.com/func-concat.html

then no need to include your telephone numbers table

perhaps something like

concatrelated("TelephoneNumber","TelephoneNumbers" ,"MemberID_FK=" & MemberID_PK)

[orange]

Please show all fields in the tables. It would also be helpful if you showed some sample data from the tables.

[Gina Maylone]

That's a fantastic idea Ajax...wish I would have thought of that earlier! Thanks!!

[Gina Maylone]

So I concatenated the phone numbers easy enough. BUT, I also need the telephone type after each one (i.e.

555-555-1212 (John Work) 444-444-1414 (Jane Work)) and to complicate the issue, I only need 2 phone numbers, some people have >2 and some people have 1 which are fine with the concatenation. I am at a loss as to what to do next. Thoughts please? Here's my concat statements

TelNum: ConcatRelated("telephonenumber","telephonenumbers","memberid_FK = " & [members.memberid_PK])

and

Ttype: ConcatRelated("teltype","telephonenumbers","memberid_FK = " & [members.memberid_PK])

TIA!

[Gina Maylone]

I've managed to get the tel type included in the concatenation by using additional queries. And it concatenates properly, I just need to know now how to limit the number of phone numbers to two please?

[CJ_London]

you will need to modify the concat related code and you only need to call it once

TelNum: ConcatRelated("[telephonenumber] & ' (' & [teltype] & ')'","telephonenumbers","memberid_FK = " & [members.memberid_PK])

the modification to the code - find this line

'Build SQL string, and get the records.
strSql = "SELECT " & strField & " FROM " & strTable


and change to


strSql = "SELECT TOP 2 " & strField & " FROM " & strTable

if this is not what you want, you will need to modify the sqlstr to meet your requirements - for example if your top 2 is supposed to be one per person, you would need to group by the person name (excluding work/home etc) and select max or min for work/home depending on whether you wanted to prioritise Work or Home to show. Use the query builder to build your requirement then translate that to use in the concatrelated function - recommend copy this function and give it another name since it will then become specific to this specific requirement.

[Gina Maylone]

ajax!!! You are a rockstar!!!!!!!! I can't thank you enough!