Results 1 to 6 of 6
  1. #1
    Remster is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Sep 2010
    Posts
    317

    List all values in one cell?

    Folks

    I'm wondering whether it's possible, instead of embedding a subdatasheet, to list all values that would otherwise appear in a subdatasheet in one cell in the main query.



    The scenario is this. I'm creating a database of documents 99.9% of which have only one author. But because 0.1% have more than one author, I have a separate authors table that stands in a many-one relationship to my documents table. I need to display authors in my documents query, but because so few have more than one author a subdatasheet is an undesirable complication. That's why I'd like, if possible, to list authors in one cell. Is such a thing possible?

    Thanks

    Remster

  2. #2
    ajetrumpet is offline VIP
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694
    I don't think in a query it is. but what's the ultimate display object? dynamic display through that might be easier. regardless though, I would assume that at least some code writing would be required.

  3. #3
    Remster is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Sep 2010
    Posts
    317
    Actually, this looks as if it's supposed to do what I want to do:

    http://allenbrowne.com/func-concat.html

    I'll give it a go if I can make sense of the instructions. What d'ya reckon?

  4. #4
    ajetrumpet is offline VIP
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694
    yep. that'll work! just remember to use "vbcrlf" as the delimiter instead of a comma. you'll have to change it a bit too, as some of the args don't apply in your situation.

  5. #5
    Remster is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Sep 2010
    Posts
    317
    Splendid. It could be a little while, but I'll report back when I've had a chance to give it a go.

  6. #6
    Remster is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Sep 2010
    Posts
    317
    Just a quick note to confirm that this works (I needed to set the query property Unique Values or Unique Records to 'Yes').

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

Similar Threads

  1. Combo Box - Field List Values
    By jennyaccord in forum Forms
    Replies: 5
    Last Post: 07-29-2011, 01:49 PM
  2. Replies: 3
    Last Post: 11-26-2010, 12:38 PM
  3. Master list table with changing values.
    By evander in forum Database Design
    Replies: 11
    Last Post: 06-24-2010, 07:40 AM
  4. Replies: 1
    Last Post: 03-09-2006, 12:12 PM
  5. Can we post Access cell data to Excel cell properties?
    By Zethro in forum Import/Export Data
    Replies: 1
    Last Post: 12-13-2005, 08:42 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