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! :)
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! :)
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
Into one field that's like
This is not an "easy" problem, but try this. First, make a VBA function to expose the
Then, make your query like
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
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 queriesPublic 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
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
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
posted by thelonius at 8:56 AM on February 26, 2014
This thread is closed to new comments.
posted by oceanjesse at 6:10 AM on February 24, 2014