Access 2003 Copy a record when a field contains two entries

  • Thread starter Thread starter Céline Brien
  • Start date Start date
C

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
 
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,
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

KARL DEWEY said:
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 said:
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
Click to expand...
Click to expand...
 
Back
Top