How do I create a multivalue column from delimited text in Access 2010?
February 24, 2014 5:44 AM   Subscribe

I want to take a comma delimited list of values e.g. "bacon, sausages, eggs" stored as a single text field and store these as selected values in a single multi-value lookup field.

Basically I want a query that goes something like this

INSERT INTO MainTable ( Meats.[Value] )
SELECT Split(MeatsList,", ");

Unfortunately, Split does not seem to work within a query. Does anyone have any ideas? Thanks! :)
posted by tsh to Computers & Internet (6 answers total) 2 users marked this as a favorite
Isn't this what join operations are for? I'm not sure what you're going for exactly because you didn't provide an example of your desired output.
posted by oceanjesse at 6:10 AM on February 24, 2014

I'm not quite sure what you mean by "multi-value lookup field", but I assume you mean that you want one field that's like

bacon, eggs, ham

Into one field that's like


This is not an "easy" problem, but try this. First, make a VBA function to expose the split function in queries

Public Function split_at(str As String, delim As String, num As Integer) As String
  split_at = Trim(Split(str, delim)(num))
End Function

Then, make your query like

INSERT INTO tableB (colB)
SELECT split_at(colA, ",", 0) FROM tableA
SELECT split_at(colA, ",", 1) FROM tableA
SELECT split_at(colA, ",", 2) FROM tableA

That'll work if your data has e.g. 3 comma delimited values. If you have more, add more unions. If you have a variable amount of comme delimited values, you'll have to put some error handling into the VBA script to return a NULL, and then deal with the NULL in your query (in fact, if you make a column NOT NULL in Access I'm pretty sure the insert just drops the insert instead of returning an error).

Hope this helps!
posted by Mons Veneris at 7:22 AM on February 24, 2014

Is using something like Python or R a feasible option for you? I think this sort of operation might be more natural in such a language, but if this is a one-off the learning curve is probably not worth it.
posted by leopard at 8:02 AM on February 24, 2014

Well, multi-valued fields break 1nf, so I'd say, fix that and your problem disappears.
First normal form (1NF) is a property of a relation in a relational database. A relation is in first normal form if the domain of each attribute contains only atomic values, and the value of each attribute contains only a single value from that domain.
posted by j_curiouser at 4:08 PM on February 24, 2014

Thanks everyone for the help. In the end it was easier to just place the columns side-by-side and put the information in manually, but I very much appreciate all of you giving your time to try help.
posted by tsh at 1:59 AM on February 25, 2014

Access 2007 added these "multi-valued fields", which, as noted above, should not exist.
posted by thelonius at 8:56 AM on February 26, 2014

« Older iTunes Gracenote Server Error   |   I've Gotta Tell You What I'm Feeling Inside Newer »
This thread is closed to new comments.