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]

 

[30Hobbes]

Walt,
Yes, the best way to accomplish this functionality is to use VBA.
Create a procedure that opens a recordset of Table 1 and another of Table 2.
Start looping through each record in Table 1 until you reach the end of file(EOF). While processing each record check Field5 to determine the number of records to be created in Table 2. If the number is 0 move to the next record. If the number is greater than 0 start a 'For' loop to add the number of required records to Table 2 and concantenate the appropriate letter to the end of Field1 using a case statement.
I hope this helps, if you need more give me a yell.
Andre [sig][/sig]