separating multiple entries in field into separate fields

[chriswrcg]

In excel it is possible to enter information into a cell separated by commas and then have another column break it down into multiple cells in the column. In other words if this is in a cell [cm,ok,lp,ij] excel separates the information and creates the following.
[cm]
[ok]
[lp]
[ij]



In access is this possible? Enter all the information in one field and then have access separate it with a query or something or would I have to create a separate field for each entry? I did it like this in excel to save space as I have 15 possible codes to enter so that would be one field or 15. any Guidance on this would be appreciated.

[Minty]

If they are separate records, then they should be stored as such.
I'm not sure of your terminology, a field in access stores a (single) piece of data, each piece of data is a record.

There are such things in Access as Multivalued fields, but anyone with any experience avoids them like the plague, as they are very difficult to work with.

If you wanted to enter data in the format of value1, value2, value3 - you could build a simple function to take that from a form and insert appropriate individual records, but it makes validating the data a lot harder, and isn't something you will see done very often.

[chriswrcg]

Is there a tutorial on how to build the function to separate the values entered on a form and place them in an individual record?

I am entering data from an associates day as he works in a warehouse. during that day he my be asked to perform one task or many. We separate the task by task codes to save time and space. So If he unloads a truck, then puts away the product, then is asked to pick an order we enter it in as the three codes in one cell. uww.at,and AR instead of having three separate columns. I am just worried that in the end my table will be extremely long with 40 or more fields and the entry form would look a mess.

[Minty]

Your data should be stored vertically - a field shouldn't be called a data type (No fields called UWW or AT etc) so your data should be stores along the lines of,

EventID, EmpID, EventDate, EventType

You would have a table for your event types and store the short code and a probably a fuller description.

By doing this if you add an event type you simply add it to your EventTypes table and then use it in your existing Events table. No need to change anything to accommodate the added process.
This is a fundamental of database design - Normalisation of your data. It's key to making your design useable and manageable going forwards.