Query results with semi-colons in them

[mrtauntaun]

I am importing an excel spreadsheet every month into one of my DBs. One of the fields can (but not always, and not even frequently) contain data with multiple values. These values are user IDs and are seperated by a semicolon.

I need to analyze each of these IDs individually, but when I run a query all I get is name1;name2;name3 on recordID1. Ideally, I'd like to see the query return three line items, each with recordID1 and one of the names. How can I accomplish this via query? The answer is in the back of my head, but for some reason I can't graps it. Thanks!

 

[dhookom]

I would create a table with just each unique UserID. You can then create a query with your imported record table and your UserID table. Don't create any joins. Add a where clause like:

WHERE ";" & [MultipleField] & ";" LIKE ";" & tblUserIDs![UserID] & ";"

This should create a record for each UserID from the multiple UserID field.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]