Access 2003 Copy a record when a field contains two entries

[Céline Brien]

Hi everybody,
Is it possible to copy a record when a field contains two entries and obtain
2 records, one with each entrie.
The table is Colors :
Id Color
1 blue
2 green
3 red
4 green;red
5 blue;green
The result I would like after the query :
Id Color
1 blue
2 green
3 red
4 green
4 red
5 blue
5 green
Many thanks,
Céline

 

[Guest]

Try this --
SELECT Brien.ID,
IIf(InStr([Color],";")=0,[Color],Left([Color],InStr([Color],";")-1)) AS
Color_List
FROM Brien
UNION SELECT Brien.ID,
IIf(InStr([Color],";")=0,[Color],right([Color],len([color])-InStr([Color],";"))) AS Expr1
FROM Brien;

 

[Céline Brien]

Hi everybody,
Hi Karl,
Thank you for your answer.
It is working perfectly.
Now, I will try to adjust the codes to my real table.
Many thanks again !
Céline

Try this --
SELECT Brien.ID,
IIf(InStr([Color],";")=0,[Color],Left([Color],InStr([Color],";")-1)) AS
Color_List
FROM Brien
UNION SELECT Brien.ID,
IIf(InStr([Color],";")=0,[Color],right([Color],len([color])-InStr([Color],";")))
AS Expr1
FROM Brien;

Hi everybody,
Is it possible to copy a record when a field contains two entries and
obtain
2 records, one with each entrie.
The table is Colors :
Id Color
1 blue
2 green
3 red
4 green;red
5 blue;green
The result I would like after the query :
Id Color
1 blue
2 green
3 red
4 green
4 red
5 blue
5 green
Many thanks,
Céline