Insert Into subtable for each record in main table

[brharrii]

I have a sub table that acts as a revision history for the items in my main table. I've just finished uploading all the records into the main table and now I want to insert one record into the subtable for each record in the main table to start the history with the record creation.

The sub table looks like this:

tblRevisions
- RevID (auto Number / primary Key)
- RevDate (date of revision)
- RevName (Who made the revision)
- RevDesc (What revision was made)
- RevAuthorized (Who authorized the revision)
- RevLabel (Foreign key to the primary table)

The info I want to insert is:
RevID - Auto Number
RevDate - 11/27/2013
RevName - 3
RevDesc - "Added Label to database"
RevAuthorized - 1
RevLabel - (One for each record ID in tblLabels)

I suspect that I want to start with an insert into statement, something along the lines of:

Insert into tblRevisions (Revdate, RevName, RevDesc, RevAuthorized, RevLabel)
Values ("11/27/2013",3,"Added label to database", 1, ?)

I'm not sure how to indicate that there should be one label for each Record in tblLabels or that revLabel value should match the ID from tblLabels though. can anyone help nudge me in the right direction?

Thanks!

 

[jdraw]

Just curious about your naming of fields???

- RevName (Who made the revision) 3

- RevAuthorized (Who authorized the revision) 1

These are the PersonId or similar, right?

 

[brharrii]

That's correct

 

[Geotch]

Why not do an append query?

 

[brharrii]

Would append work if I'm creating new records? What is the syntax you would suggest?

thanks,

 

[Geotch]

I mostly use the design view when building queries instead of SQL. I'll try to upload a sample.

 

[Geotch]

Here's a sample database to get you started. Let me know if you have any questions.

If the form frmLabels doesn't open automatically, start there to see how the database is working.