Results 1 to 6 of 6
  1. #1
    Trojnfn is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Sep 2011
    Posts
    46

    Multivalue List

    I created a multivalue list in my table. It contains a list of names and a checkbox. I designed the form based on this and am able to check off multiple names. When I go back to look at the table, it stores in each name separated by a comma. So far so good.



    When I create a report to display the data, it displays it as 1,2,3..etc., instead of the names separated by a comma.

    What do I need to do to display the names ?

  2. #2
    NoellaG's Avatar
    NoellaG is offline VIP
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    Hi,

    first of all I wouldn't use multivalue lists in a table, it doesn't comply with the first normal form and almost surely will cause trouble later on.
    If there are numbers stored in the list it means that the primary key values of the dependant table are stored in the multi-value column, not the name values. So you're probably dealing with a one to many relationship.
    My advise: use a normalised datastructure and show the data on the many side of the relationship in a subform.

    succes
    NG

  3. #3
    Trojnfn is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Sep 2011
    Posts
    46
    Quote Originally Posted by NoellaG View Post
    Hi,

    first of all I wouldn't use multivalue lists in a table, it doesn't comply with the first normal form and almost surely will cause trouble later on.
    If there are numbers stored in the list it means that the primary key values of the dependant table are stored in the multi-value column, not the name values. So you're probably dealing with a one to many relationship.
    My advise: use a normalised datastructure and show the data on the many side of the relationship in a subform.

    succes
    NG
    I am very new to access 2007 so I am learning. I do not know or understand what you just described.

    I took the easy way by creating the lookup column in my table and then just have access build the form for me. When I go to that field, it has all the names and a checkbox and I just check the names I want. Works fine. When I go look in the table, the checked names are stored separated by a comma. So far, so good, exaclty what I want.

    However, when I run a report to display that field, it comes out as numbers (the id value) instead of the name, which was stored in the field. That is what I cannot figure out.

    The names are in a master table along with a primary id key. I used this master table in the lookup when I built the lookup column (the wizard asked what table the data was coming from).

    I tested and everythign looks good, fuctions propery, and does what I want. Just the report does not come out with the names, just the id numbers.

  4. #4
    boblarson is offline --------
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2011
    Posts
    1,272
    Perhaps it would be good for you to read this:
    http://office.microsoft.com/en-us/ac...33722.aspx#BM7

  5. #5
    Trojnfn is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Sep 2011
    Posts
    46
    Quote Originally Posted by boblarson View Post
    Perhaps it would be good for you to read this:
    http://office.microsoft.com/en-us/ac...33722.aspx#BM7

    I looked at that link already, found it on goolge. That is how I learned to create the multivalue list and was able to create my form checkboxes based on that list.

    The problem I am running into is on an access report. The value comes out as a number (id) instead of the name.

    I view the table and it is stored as a name (names separated by comma) and when I run a query agaist the table using the field, a name comes out.

    So looks like it is working, but the report keeps diplaying the id number instead of the name.

    That is where I am running into the problem.

  6. #6
    boblarson is offline --------
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2011
    Posts
    1,272
    Post the SQL of the report. You haven't based the report on the table instead of a query have you?

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

Similar Threads

  1. Multivalue field sort order
    By bdaun in forum Forms
    Replies: 0
    Last Post: 09-05-2011, 12:27 PM
  2. Getting the sum from a multivalue combobox
    By polis in forum Programming
    Replies: 4
    Last Post: 08-31-2011, 03:47 PM
  3. Replies: 4
    Last Post: 06-16-2011, 09:30 PM
  4. Append into multivalue field help
    By iwantatransam in forum Import/Export Data
    Replies: 0
    Last Post: 04-26-2011, 08:11 AM
  5. List box to populate other list boxes
    By Nathan in forum Forms
    Replies: 0
    Last Post: 03-03-2009, 07:22 PM

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