Access 2003 Copy a record when a field contains two entries

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
 
K

KARL DEWEY

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

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

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top