CODEWITHSUNDEEP
sqlms-accesssplit
John Smith
John Smith

Reputation: 13

Microsoft Access SQL Comma Delimited Values

I have a dataset with a primary key called authors and a field called books.

The table looks something like this:

Authors (primary key)     Books
John                      Fathom,Mouseover,Keycard
Kyle                      Teachers,Carl,Phone

I know this violates 1NF, and I'm attempting to create a new table (one to many relationship) with the primary key (authors) occurring more than once for each book. ie

Authors                   Books
John                      Fathom
John                      Mouseover
John                      Keycard
Kyle                      Teachers
Kyle                      Carl
Kyle                      Phone

I need to use SQL in microsoft access, and I think it involves the split function, but I'm at a loss for how to perform this query.

Any insight would be greatly appreciated!

Thank you very much

Upvotes: 1

Views: 683

Answers (1)

Erik A
Erik A

Reputation: 32682

Well, since the code is rather difficult to implement, and you only have 2, I guess I can share an implemented solution:

(replace MyTable with your tablename, and find and replace all field names with the correct one):

SELECT * Into NewTable FROM (
SELECT Author, IIf(InStr(1, [Books], ',') = -1, [Books], Left([Books], InStr(1, [Books], ',') - 1)) As Book
FROM MyTable
WHERE IIf(InStr(1, [Books], ',') = -1, [Books], Left([Books], InStr(1, [Books], ',') - 1)) <> ""
UNION ALL
SELECT Author, IIf( InStr(1,[Books],',')> 0, IIf(InStr(InStr(1,[Books],',') + Len(','), [Books] , ',') < 1, Mid([Books], InStr(1,[Books],',') + Len(',')), Mid([Books], InStr(1,[Books],',') + Len(','), InStr(InStr(1,[Books],',') + Len(','), [Books] , ',') - InStr(1,[Books],',') - Len(','))), "")  As Book
FROM MyTable
WHERE IIf( InStr(1,[Books],',')> 0, IIf(InStr(InStr(1,[Books],',') + Len(','), [Books] , ',') < 1, Mid([Books], InStr(1,[Books],',') + Len(',')), Mid([Books], InStr(1,[Books],',') + Len(','), InStr(InStr(1,[Books],',') + Len(','), [Books] , ',') - InStr(1,[Books],',') - Len(','))), "")  <> ""
UNION ALL
SELECT Author, IIf( InStr(InStr(1,[Books],',') + Len(','), [Books],',')> 0, IIf(InStr(InStr(InStr(1,[Books],',') + Len(','), [Books],',') + Len(','), [Books] , ',') < 1, Mid([Books], InStr(InStr(1,[Books],',') + Len(','), [Books],',') + Len(',')), Mid([Books], InStr(InStr(1,[Books],',') + Len(','), [Books],',') + Len(','), InStr(InStr(InStr(1,[Books],',') + Len(','), [Books],',') + Len(','), [Books] , ',') - InStr(InStr(1,[Books],',') + Len(','), [Books],',') - Len(','))), "")  As Book
FROM MyTable
WHERE IIf( InStr(InStr(1,[Books],',') + Len(','), [Books],',')> 0, IIf(InStr(InStr(InStr(1,[Books],',') + Len(','), [Books],',') + Len(','), [Books] , ',') < 1, Mid([Books], InStr(InStr(1,[Books],',') + Len(','), [Books],',') + Len(',')), Mid([Books], InStr(InStr(1,[Books],',') + Len(','), [Books],',') + Len(','), InStr(InStr(InStr(1,[Books],',') + Len(','), [Books],',') + Len(','), [Books] , ',') - InStr(InStr(1,[Books],',') + Len(','), [Books],',') - Len(','))), "") <> ""
)

Upvotes: 1

Related Questions