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


Best answer: 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

bacon
eggs
ham


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
UNION
SELECT split_at(colA, ",", 1) FROM tableA
UNION
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


Response by poster: 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.