Duplicate multiple records

[WaltW]

I have an application where I want to use the contents of one table to create another new table. The catch is I want to use a numeric field in Table 1 to create multiple identical records in Table 2, modifying one of the fields in Table 2 to keep things unique. Example: Table 1 contains Field1 thru Field9, and Field5 is a numeric field that can contain anything from 0 to 9. Record 1 in Table 1 has a 3 in Field5, so we need to append 3 identical records to the new Table 2, with the same structure. In the process, we append an “A” to the end of Field1 when we add the first of the 3 records to Table 2, a “B” when we append the 2nd of the 3 records, and a “C” when we append the 3rd of the 3 records. Same occurs for records 2 thru whatever in Table 1. Field5 could also have a zero, in which case no records at all would be appended to Table 2. What’s the best way to accomplish this? Do we have to use Visual Basic?

Thanks for any help you can offer!

Walt Wright

[sig][/sig]

 

[DMac]

Is this something you want to run once to create all the table2 records from existing data in table1? If so you could achieve this with three inserts along the lines of:

insert into table2
(select field5 + 'A'
from table1
where field5 <> 0);

insert into table2
(select field5 + 'B'
from table1
where field5 <> 0);

insert into table2
(select field5 + 'C'
from table1
where field5 <> 0);

Obviously you have to put in all the fields that you want into the select clause.

If this is something you need to do as you insert records, you can either code it in VB/VBA or use a macro that runs three inserts after any insert to table1.

It doesn't look like a very good design to me, what happens if table1 field5 is changed? Why not use a new field for the ABC part and just store the field5 value as a foreign key in the second table. [sig][/sig]