Access 2003 Copy a record when a field contains three entries

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Céline Brien - 31 Oct 2006 14:45 GMT

Hi everybody,
Is it possible to copy a record when a field contains three entries and
obtain
3 records, one with each entry.
The table is Colors :
Id Color
1 blue
2 green
3 red
4 green;red;blue
5 blue;green;red
The result I would like after the query :
Id Color
1 blue
2 green
3 red
4 green
4 red
4 blue
5 blue
5 green
5 red
Many thanks,
Céline
-------------------------------
Here are the codes for two entries : green;blue
For three entries can you use Middle ???
-------------------------------
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;

Reply to this Message

kingston - 31 Oct 2006 15:24 GMT

I think you're better off creating a subroutine to handle this so it doesn't
matter whether you have two, three, four, or any number of colors:

ID = Recordset![ID]
string = Recordset![Field] & ";"

While Instr(string,";")>0
Color = Left(string,Instr(string,";")-1)
SQL = "INSERT INTO Target (ID,Color) SELECT " & ID & ", '" & Color & "';"
DoCmd.RunSQL SQL
string = Mid(string,Instr(string,";")+1)
Wend

Run that for every record instead of a series of queries. HTH

>Hi everybody,
>Is it possible to copy a record when a field contains three entries and
[quoted text clipped - 32 lines]
>AS Expr1
>FROM Brien;

Reply to this Message

Céline Brien - 31 Oct 2006 16:03 GMT

Hi everybody,
Hi HTH,
Thank you very much for your answer.
I need a little more help here.
How do I create a subroutine ?
Where do I paste those codes ? In a Modules ?
How to I run those codes for every record instead of a series of queries ?
Many thanks for any addotionnal help,
Céline

>I think you're better off creating a subroutine to handle this so it
>doesn't
[quoted text clipped - 49 lines]
>>AS Expr1
>>FROM Brien;

Reply to this Message

kingston - 31 Oct 2006 16:46 GMT

Since I don't think that I could do a good job of teaching you how to code in
this forum, I'll try to help you design some queries that will do what you
described. Before you do this, make a copy of your original data table.

1) Create a table with the fields ID and Color. Do not allow Access to
create any keys or set uniqueness.
2) Create an update query for the original data: SET [Color] = [Color] & ";"
3) Create an append query to append data to the table you created in step 1:
[ID] = [OriginalData].[ID] and [Color] = Left([OriginalData].[Color],Instr(
[OriginalData].[Color],";")-1)
4) Create a modify query to modify data in the original data table: [Color]
= Mid([Color],Instr([Color],";")+1)

Run the query in step 2 once. Run the queries in steps 3 and 4 over and over
until no new data is added (run 3, then 4, then 3, then 4, then 3, then 4...).
The new table you created in step 1 will contain the parsed data.

Hope
That
Helps

>Hi everybody,
>Hi HTH,
[quoted text clipped - 11 lines]
>>>AS Expr1
>>>FROM Brien;

Reply to this Message

Céline Brien - 31 Oct 2006 20:00 GMT

Hi,
Thank you so much for your answer.
I will keep both solutions and try to find info on how to code.
I will try this solution first, since it is more simple.
Many thanks again !
Céline

> Since I don't think that I could do a good job of teaching you how to code
> in
[quoted text clipped - 39 lines]
>>>>AS Expr1
>>>>FROM Brien;

Reply to this Message