Results 1 to 5 of 5
  1. #1
    Sck is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Jul 2010
    Posts
    82

    Post One to many query but only show the one for first returned record


    I have a query that pulls from two tables (one to many relationship). i need to export this to excel but I only want the ONE side of the relationship to be listed on the first row (basically look like a report) I need it like below.
    NAME PHONE
    John ###=####
    ###-####
    Sue ###-####
    Jeff ###-####
    ###-####
    sorry about the alignment, the ###-#### should all be aligned but i couldn't get it to post that way

    instead of
    NAME PHONE
    John ###=####
    John ###-####
    Sue ###-####
    Jeff ###-####
    Jeff ###-####

    I know i can do it via Excel automation after the export but i am wondering if i can get the query to list this way

  2. #2
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    honestly, excel automation as you say might be the most straight forward way to accomplish what you've asked for.

    You could use Allen Browne's ConcatRelated function or something similar and list the name once and a string of all their phone numbers like this
    Code:
    +------+--------------------+
    | Name |   Phone Numbers    |
    +------+--------------------+
    | John | ###-####, ###-#### |
    | Sue  | ###-####           |
    | Jeff | ###-####, ###-#### |
    +------+--------------------+
    Quote Originally Posted by Sck View Post
    sorry about the alignment, the ###-#### should all be aligned but i couldn't get it to post that way
    I used this to quickly generate the table I posted above: https://ozh.github.io/ascii-tables/
    Just wrap it in [code ] and [/code ] tags, the little button in the reply toolbar with a hashmark.

  3. #3
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Have a look at this code, replace the comma with Chr(13) & Chr(10) as the delimiter:
    http://www.accessmvp.com/thedbguy/codes.php?title=simplecsv
    If you're dealing with lots of records it can get quite slow, so Excel automation would better.

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  4. #4
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    Another idea. If your phone numbers table has something like an IsPrimary field then you could sort by this field then use an IIF to decide to display the name or not. BUT you would have to guarantee that each person has a primary number or you'll get phone numbers with blank names and you wouldn't know who they belong to, or rather you'd think they belong to the wrong person... This is probably a bad idea.

    Code:
    SELECT IIf([IsPrimary], [PersonName], "") AS Person, 
           PhoneNumber.PhoneNumber 
    FROM   Person 
           INNER JOIN PhoneNumber 
                   ON Person.PersonId = PhoneNumber.PersonId 
    ORDER  BY Person.PersonName, 
              PhoneNumber.IsPrimary; 

  5. #5
    Sck is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Jul 2010
    Posts
    82
    thanks think i will just go with Excel Automation Just wanted to see if there was a better way to handle it.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 6
    Last Post: 05-14-2018, 01:14 PM
  2. Replies: 4
    Last Post: 04-06-2016, 05:37 AM
  3. Replies: 3
    Last Post: 11-16-2014, 01:53 AM
  4. Replies: 1
    Last Post: 04-12-2011, 05:19 PM
  5. Replies: 1
    Last Post: 09-22-2010, 08:03 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums