duplicating a set of records 1

[swtrader]

Given: tblCust, tblTrans, tblDocs
1 cust has many transactions; 1 trans has many documents
however, a customer's 2nd transaction may be similar to its first transaction. Therefore, an identical list of docs will be used for the 2nd trans. (I have considered many-to-many relationship....it's possible but not necessary or wanted.)

So, what is the easiest way to set up user-duplication of the doc-set? (i.e., user clicks button that duplicates the doc-set and asks for the new transaction number)

I can SELECT the docs from the 1st trans (qryDocs_tmp) and append to a new table (tblDoc_tmp) and then append that table to the original tblDoc. I am using autonumber for the primary key so some manual manipulation of the primary key would be necessary. This seems like a round-about way of getting a simple duplication of a record set.

thanks to all -- you're a smart bunch of folks.

 

[traingamer]

I can SELECT the docs from the 1st trans (qryDocs_tmp) and append to a new table (tblDoc_tmp) and then append that table to the original tblDoc.

Why can't you simply append them to tblDoc with the new key info for the new trans?

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[jsteph]

One way to design the tables would be to have the tblTrans have a key of
CustID*
TransID* --This can be autonumber, even though the PK is the compound key.

The tblDocs key would be
CustID*
TransID*
Docid* --Again--autonumber, but the actual PK is the compound key shown here.

This way the append query for a given cust/trans, would appen 2/3's of the key--Custid and TransID--and let autonumber do the 3rd component of the key automatically.

Bottom line--we're working with Access, we don't need to be all high-falutin' and get all "EF Codd" here--there is nothing wrong with having a unique autonumber as a component of a compound key
--Jim

 

[swtrader]

Good suggestion, jsteph. I will try that. Never thought of doing it that way.