Duplicate records in joined tables

[msmithtlh]

I have 2 tables, one serves as an extension of the other (in certain cases), joined on record number (autonumber).



My users have situations where they need to enter many records where the data in most fields is identical, and have asked for a way to do this efficiently.

I have tried using the "duplicate" button, but it only duplicates the record in one of the tables. I need something that will create the data in both tables.

I had an idea of writing the source data to a temporary local table with the fields that we want to copy. Then creating a loop in a macro for 1 to x times to append a record to table "A" and then append a record to table "B"

Questions:
1) I don't know how to do the code for the loop
2) I'm not sure when it ran the append queries, if the new records would end up with the shared record number - which is essential for data integrity.

Thanks in advance for your help.

[orange]

Data should not be duplicated in several tables. The whole idea of relational database is to identify subjects/tables, and relations and to identify attributes and values according to some rules (Normal forms).

Please see this tutorial for a procedure to go from requirements, to identifying subjects, normalization and database design.
http://www.rogersaccesslibrary.com/T...lationship.zip

If you want more focused assistance, then please describe the database in plain English telling us WHAT the database is about; how the business will use this database, what are the things involved, how do the things relate to one another?

Trust me --getting your design built according to some basic rules will make database much more satisfying.

[msmithtlh]

Let me try again to explain. I am not duplicating data in several tables.

There is a "primary record" for each case, called "worklog" with an id field which is an autonumber.

Worklog table fields are:
ID (primary key)
Category
DateReceived
TaskName
LoggedBy
ItemCount
AssignedTo
AssignedDate
DateComplete
RequestMainType
RequestSubType
LoggedOutBy

Records in the primary table, which are one of the categories "Record Requests" have many additional fields. Rather than adding all these fields to the primary table, and having them blank for most records, I created a second table (Extended Records Data for those fields. It inherits the record number from the primary table, and is joined to the primary table (worklog) on the primary key.

Extended Records Data Table fields are:
Record ID (primary key - same as ID in Worklog table)
AccountName
BP
FEIN
SSN
CertificateNumber
AcknowledgeBy
AcknowledgedDate
PagesPrinted
PagesSent
Mode
DateSent
CIT
Sales
RT
Misc
Intangible
SignatureOrigin
Authorized
AuthorizedBy
AuthorizedDate
DateDue
Comment
ReviewedBy
ReviewedDate
InReview
RequestLink
SentLink
AddtlBackupLink
DBA_Name
AcknowledgeMethod
Power_of_Attorney
DR1
Other
PhotoID
Return_And_Payment
Signed_Agency_Request
SSCorp

Now, the situation is this. Normally for an incoming request of category "Record Requests" (the category where there is data in the second table), there is one Worklog record and one Extended Records Data record. However, they also get requests which come in "groups." In these cases, each request in the group needs a Worklog record and an Extended Records Data record, but with the exception of AssignedTo, AssignedDate, Name, DBA_Name, FEIN, SSN, BP, and CertificateNumber, the data is the same. So this is NOT truly duplicate data. But when there can be 50 or more records that are so similar in nature, I want to give them a way of creating the data without having to enter all the data that IS the same over and over again (which is what they currently have to do). Then they will edit those records to fill in the unique data in each.

Hopefully this is a better explanation of the data and the situation.

Again, thank you in advance for your help.