Multiple Values Field

[cbrsix]

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.

[Robeen]

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.

[Bob Fitz]

I totally agree with Robeen. I don't think I've ever seen their use recommended in any forum.

[cbrsix]

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?

[Robeen]

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?

[cbrsix]

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

[cbrsix]

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..?

[Robeen]

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!