Results 1 to 8 of 8
  1. #1
    cbrsix is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jul 2011
    Posts
    215

    Multiple Values Field


    Is it possible to have a text field in a table to store multiple values by using a comma or some other type of seperator? I do not want to set the field to a lookup field because the number of values that would be included would be to many to continuously look through to select. I would prefer to just manually type in what I need and use a seperator to create a multiple value field.

    Thanks.

  2. #2
    Robeen is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Mar 2011
    Location
    Tulsa, Oklahoma.
    Posts
    1,596
    You CAN. But it violates one of the basic tenets of database design, namely, 'thou shalt not have multiple values in one field'.

    You should consider a second Table in which you store each of your values in a separate row in the Table.

  3. #3
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows XP Access 2003
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,610
    I totally agree with Robeen. I don't think I've ever seen their use recommended in any forum.
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  4. #4
    cbrsix is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jul 2011
    Posts
    215
    I already have a table where each value I would be entering has its own line. The problem is that there are a couple thousand different values in that table. I know I can set up a lookup field and have that store multiple values but going through and finding each specific value I need each time would take longer than just entering it manually. You say I can accomplish this with a seperator, what is used to do this? Will I still be able to search for these through queries or any other means properly by storing the data like this? Will storing data like this affect the functionality of the database in any major way?

  5. #5
    Robeen is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Mar 2011
    Location
    Tulsa, Oklahoma.
    Posts
    1,596
    If there are certain sets of values that always go together, you could assign each of your values a 'code'.
    Then - if you know the code for the set of values you want to select - you can just select the code and it will pull in all your values.

    Perhaps you could give us an example of your data [what tables you have and what fields are in the tables] and we can use the example to discuss your options?

  6. #6
    cbrsix is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jul 2011
    Posts
    215
    Table 1 is an Account List. There is a field labeled AccountNumber. This number is a unique number (although not the primary key). The field AccountNumber would be the one used if I were to set the type as a lookup and allowed multiple values.

    Table 2 is an Incoming and Outgoing Mail Log. This tracks everything. Now if a piece of mail, whether incoming or outgoing, contains multiple account information for one client, it would be in the same piece and I put it on one line. There is an AccountNumber field in this table as well. This has a relationship going to table 1's AccountNumber field. Like I mentioned before, I would not want to create the lookup field and do the multiple values that way because it would take longer to find it than just typing in the respective AccountNumber since I would have them in front of me. For example, I want this AccountNumber field to be able to contain multiple AccountNumbers from the Account List and still have it relate to each specific account when I have to do a query or something else. Currently I am putting them is seperated by commas: XXXX, ZZZZ, AAAA

  7. #7
    cbrsix is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jul 2011
    Posts
    215
    I guess what I'm trying to ask is that if there is a way for that text string to still identify the specific relationships created, without creating the lookup field selecting allow multiple values..?

  8. #8
    Robeen is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Mar 2011
    Location
    Tulsa, Oklahoma.
    Posts
    1,596
    Yes. You can enter multiple values into one field of your Table [separated by commas - or any other character you choose].
    Then - when you want to search that one field for one or more of the values you have entered - you can create a query that searches that field for the values, and if the value[s] are found in the field - the query will display the record.

    All I was trying to tell you initially was that from the perspective of efficient database design, it is not a good practice to have multiple values in one field.
    To understand why it is not good, you can Google 'first normal form' - or 'database normalization'.

    That said . . . it is still YOUR database and has to serve YOUR needs. You can do what you want with it.

    Hope this helps - and all the best!!
    Let us know if you have more questions!

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

Similar Threads

  1. Replies: 5
    Last Post: 04-04-2012, 06:42 PM
  2. Update Query selecting multiple values in one field
    By Zipster1967 in forum Queries
    Replies: 1
    Last Post: 08-15-2011, 12:22 PM
  3. query field with multiple values
    By mknowles in forum Queries
    Replies: 1
    Last Post: 11-24-2009, 11:31 AM
  4. Query multiple values in a field
    By JAYgarti in forum Access
    Replies: 0
    Last Post: 07-09-2009, 09:52 AM
  5. Replies: 0
    Last Post: 12-19-2006, 09:44 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