Duplicating and splitting a record in a new table
I am trying to come up with a script to make this work:
I have one table called "Contributions" that I would like, upon record creation, to automatically duplicate and split into one or more new records in a related "Designations" table. The majority of contributions records would only need a single "copy" created in the designations table, but I also need to be able split them in the cases when there are more than one designation for a single contribution -- based on an entry in one of the contributions fields.
For example, if a given Contributions record only needs to be designated for one item, a number field in the contributions record would be filled in "1" and there would be one new record created and opened in Designations. If the number field were "3", however, I would need three new records created in the Designations table -- and in all of these cases, with a few fields in the new record/s automatically filled in with information from the original Contributions record (if that's possible).
Does this even make sense, and is there a logical way to do this? Thanks for any help!!!!