Hi Chris,
If you'll post back with a more complete description of your application and
your motivation in copying data from one part of your database to another
part of your database.
It's a fairly hard and fast rule in Relational Databases that you not do
that. Of course, it's your application and you can do what you want, but
people will generally (not always) limit their assistance to what they
perceive to be good practice. By having data in the database multiple times
it will surely get out of synch in time. Usually you can recalculate the
information when ever you need to display it. If so, don't store it
redundantly.
If all you need in the future is the list of Products then create another
field in the table, say Flag. Give Flag a description of "Flag for
_________ Report. Your later report will be based on a query that includes
only those records where the flag is True. On your form chkFlag is a
checkbox.
On the other hand, if you need a complete printout of the product and it's
ingredients, you'll have to do the above and design your tables
appropriately - which you should do in any case. There is a natural
relationship between Products and Ingredients in the usual sense in that
Products have one or more Ingredients. You might also want to know every
Product in which this Ingredient is used. There is a many-yo-many
relationship between those two entities. In order to deal with a
many-to-many relationship you need a Junction Table. In this case your
tables might be tblProduct, tblIngredient and tblProductIngredient
Some suggested field types and names for your table designs -
tblProduct: ProductID, Autonumber; ProductName, text-100;
ProductDescription, text-250, ProductNotes
tblIngredient: IngredientID, autonumber; IngredientName, text-100;
IngredientDescription, text-250; IngredientNotes
tblProductIngredient: ProductIngredientID, Autonumber; ProductID, Long
Integer ForeignKey; IngredientID, Long Integer ForeignKey; ProdIngNotes,
text-250 (you may need to change the field type to Memo if you might need
lengthy notes). This note is about this instance of this Ingredient in this
Product - i.e. you may need to re-work a standard Ingredient to make it work
here ...
Post back with questions.
HTH
