Results 1 to 7 of 7
  1. #1
    Simbiose is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2015
    Posts
    35

    Retrieving Multiple Values into a Single Field

    Hello everyone.


    I'm not sure if my question has already been answered as I don't have a clue where to start.
    The company I work at needs to produce box labels for a School Campaign for hundreds of children from one of it's partners and I'm having a problem with automating the layout of the labels.
    To put it simple, let's say we have one box that will hold products that will be distributed to three children, so the logic database wise would be to group these three records into a single one, but the rows will not only have their names, as there will be other fields that need to be separate.

    I'd need to translate something like this:

    Click image for larger version. 

Name:	1.png 
Views:	21 
Size:	3.2 KB 
ID:	25542


    Into something like this:

    Click image for larger version. 

Name:	2.png 
Views:	21 
Size:	3.1 KB 
ID:	25543



    I'd need this, so that then when I run our label printing software, it'll read the Child's Name field value as single record, containing those three separate values.

    Is it possible to produce a query for this?

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    One field does not hold multiple values, you need multiple records to hold all the different values.
    do you mean 1 label for all values?

  3. #3
    Simbiose is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2015
    Posts
    35
    You're saying it's not possible to hold multiple values on a single field? If that's true I'd say that's odd, because I was able to do this on Excel with a function, but then I had to remove duplicates manually which was a pain
    In answer to your question, regarding the case I displayed above, I'd need a label that would contain the name of the three children, the name of three workers, their corresponding numbers, etc...

  4. #4
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    I don't know if this is what you are looking for, but versions of Access 2007 and after do offer multi-valued fields.
    See: https://support.office.com/en-us/art...c-6de9bebbec31
    and http://www.utteraccess.com/forum/Mul...-t1984616.html

    However, many programmers, including myself, will never use these, as Access is the only database program that supports them (so you could not easily convert the Access database to something like SQL), and they can be a pain to work with.

  5. #5
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Put your data into separate fields as you would with proper database design.
    If you need to print a string( eg: a label), concatenate the values from the individual fields in the order you need.

    example:

    tblStudent

    FirstName
    LastName
    SchoolYear

    Label: FirstName & "-" & LastName & "-" & SchoolYear

    Remember data storage and data presentation are very separate things.
    Don't put multiple values into a single field.

    Good luck.

  6. #6
    Bulzie is online now VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    As other said, you need to store the data in separate fields like your example shows. But for the output, not sure it will be easy to combine multiple records of fields onto a single label. I think you need to create a Label/Report to include those fields and in Sorting and Grouping on report, Sort by Company/ChildName and Group by Company to get all the records for that company. If that does not work, you might need to use code to loop through the company records and combine field values into a Temp table to use for the label.

  7. #7
    Simbiose is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2015
    Posts
    35
    Hi again.
    Thanks everyone for their suggestions. I've been back and fowards on those, but they don't achieve the result we're looking for.

    Since I had to have an answer today and I had return from vacation yesterday, I obviously didn't have time to prepare myself for such a request (yep! I get many little surprise like this one...)
    I went with the help of Excel, even though it's a pain to have to remove the duplicated records manually afterwards.
    Sorry and thanks everyone for your time.

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

Similar Threads

  1. Counting Multiple Dates in a single Field.
    By Woblypegs in forum Queries
    Replies: 2
    Last Post: 02-09-2015, 06:10 AM
  2. Replies: 1
    Last Post: 09-11-2014, 05:53 AM
  3. Replies: 1
    Last Post: 02-13-2014, 01:50 AM
  4. Multiple choice in a single field
    By fabads in forum Access
    Replies: 6
    Last Post: 05-21-2013, 11:45 AM
  5. Retrieving values from a Combo Box
    By dreamnauta in forum Programming
    Replies: 3
    Last Post: 01-16-2012, 10:18 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