One to many on single row

debugcn Published at Dev

user2486993

I have an access database with a contact and phone number table. Each contact can have between 0 and 10 phone numbers. I'm trying to get all of the number for each contact on the same row in order to import as a .csv to another system. I've tried using a pivot but ran into issues since each contact has a varying number of phone numbers.

PaulFrancis

So based on your description, you have a Contacts table like,

tbl_Contacts
````````````
contactID   |   contactName
------------+---------------
1           |   Jones
2           |   Jackson

A phone record of customers like,

tbl_PhoneRecords
````````````````
phoneID |   contactID   |   contactNumber
--------+---------------+-----------------
1       |   1           |   555-555-5555
2       |   1           |   555-444-7777
3       |   2           |   111-111-1111

And your desired output be like,

qry_Output
``````````
contactID   |   contactName |   joinedContact
------------+---------------+-------------------------------
1           |   Jones       |   555-555-5555, 555-444-7777
2           |   Jackson     |   111-111-1111

You might be able to make use of Allen Browne's Code Concat Related. So your SQL would be something like,

SELECT 
    tbl_Contacts.contactID,
    tbl_Contacts.contactName,
    ConcatRelated("contactNumber", "tbl_PhoneRecords", "contactID = " & tbl_Contacts.contactID) As joinedContact
FROM
    tbl_Contacts;

Hope this helps !

Collected from the Internet

Please contact [email protected] to delete if infringement.