I have 2 tables I need to join on data from a field that is string concatenated in T1 and delimited with " ;" <-- there is a space before the semi-colon.
I first tried using the Split function in the query, but that's not available in MS Access from the query, just from VBA - so I wrote a small wrapper function:
Code: Select all
Public Function String_Split(sInput As String, _
Optional sDelim As String = ";") As String
If InStr(sInput, sDelim) Then
'String_Split = split(sInput, sDelim)
Else
String_Split = sInput
End If
End Function
Code: Select all
SELECT TOP 5,
Trim$(String_Split([T1].[Concatenated Item List]," ; ")) AS SplitItem
FROM SearchTable1 AS T1
First I need to get that working right because THEN I need to use the SplitItem field to join with SearchTable2 which has a Item field.
The tables are as such:
SearchTable1:
IssueNum | Concatenated Item List |
123 | KK123 ;234 ;RR345 |
124 | CC678 |
ActionNum | Item | IssueNum |
2404 | RR345 | 123 |
2405 | 234 | 123 |
2406 | CC678 | 124 |
Expected result set: All Items parsed out with their respective IssueNum and (if it exists) the ActionNum, blank space if no ActionNum
Item | IssueNum | ActionNum |
KK123 | 123 | |
234 | 123 | 2405 |
RR345 | 123 | 2404 |
CC678 | 124 | 2406 |
Thanks