Query to Split a field into parts?

[EricRex]

Hey there,



I don't think this is possible in SQL and requires VBA, but thought I'd ask.

I have a column that contains various values including NULL, a single word or acronym, or a comma-separated list of those words and acronyms. These entries indicate various mistakes I've made on those particular stock trades.

Sample data:
NULL
Entry
Exit
Exit, Shares
BRB, 4R

Currently, when I do a query, it returns the whole string which confuses the issue of how many of each type of mistake I've made over a period of time.

I'm hoping for a simple query to split up those comma-separates values and present a total for each word or acronym.
Currently, I have a query that is a union of several queries that each look for one of those strings. It works fine but it's clunky and if I add new mistake types, I have to create a new, specific query to add to the union.

The other solution I've thought of is to write some VBA or a query that will split up those values and create a Mistake types table that I can use for some kind of join to generate the separated results.

I'd appreciate your thoughts.

Cheers,
Eric

[Minty]

Look at the Split() function. You can't use it in a query but you can in a function.

Ideally you should do this once and normalise and store your data in a proper related table.

[xps35]

Storing multiple values in one field is a bad idea. And now you know why. Better use the splitting to set up a better datastructure.

How can you (or Access) identify mistakes? Do you have a list/table of valid values?

[orange]

I agree with Minty re use of Split function. However, I suggest you give us a 30000 ft overview of what you need to correct eg from what - To what with example(s). Also, to provide more details on setting up to do a "split", I recommend you give us more data/records to show context. ?? It isn't clear (yet) what correction is needed, so to offer any detail info on HOW to do it (especially csv lists) is pure guesswork at this time.

[June7]

In this case, a multi-value field or a long text field with Column History (as bad as both of these options are) would probably have been preferable to a CSV string.

Another vote for normalizing your data structure. You need a child table of the mistakes with foreign key field for TradeID.

[Micron]

see if this helps
http://allenbrowne.com/func-concat.html

[June7]

Allen's ConcatRelated function will be useful after OP gets data normalized but don't think it is a benefit at this point. OP needs to parse CSV string, not concatenate.

[Micron]

OP needs to parse CSV string

I'm aware of that - just thought it might be a jumping off point. I probably should have refreshed my memory first to see if that might be the case.

[Gicu]

Depending on how many words or acronyms you have you could create a query that counts individual words using the InStr() function by adding calculated fields using this pattern:
Exit:Iif(Instr([MistakeType],"Exit")>0,1,0)
Entry:Iif(Instr([MistakeType],"Entry")>0,1,0)
Cheers,

[orange]

I think it's time to hear from the OP.

[EricRex]

Hey guys,

Thanks for all of the good responses above. I understand the challenge with having a single field with CSV's. The normalized option would be to have multiple fields with individual words or acronyms, most of which would be empty because I don't make multiple mistakes in a single trade. That option doesn't appeal to me very much.

Of all of the options presented above, the one that makes the most sense is to write some VBA that will read that column, split out the individual values, and populate a table with those values. That way, if I add a new value, I can simply update the Mistakes table by running that routine.

Cheers,
Eric

[June7]

The normalized option would be a dependent table where each mistake would be a record. This is what your VBA procedure should accomplish. This is actually a fairly common topic. Here is one discussion https://www.access-programmers.co.uk...ecords.242856/

Then all future data entry would be into this table.