Add a record to a table for every record in another table.

[Directlinq]

Hi I really hope someone can help me.
I have 2 tables and I need to create a record in table 2 for every record in table 1. The new records in table 2 will incorporate 2 fields from the records in table 1 and 3 fields that will be text not from any table.
What is the fastest most reliable way of doing this?
This needs to be done in vba.

Thank you all.

 

[plog]

This needs to be done in vba.

Why? And explain the whole process/purpose more.

Is this a one time thing, or will this process be need to ran more than once? If more than once, what will trigger it?

Other than the unexplained VBA requirement, this sounds like it can be done easily with a simple INSERT query (http://www.w3schools.com/sql/sql_insert.asp).

 

[Directlinq]

Hi, thanks for the quick reply. I want to run this on a button press, and it will be run once a month. But the amount of records in table 1 will be different every month and have different values. Also the column names between the 2 tables are different. I see how i can add the fields from one table to another using the select query but cant seem to figure out how to add my extra custome text fields that are not in table 1. Maybe my insert knowledge isn't up to scratch as I've been trying all day with no luck. Any tips would be greatly appreciated.
Thanks

 

[plog]

Here's some SQL that addresses both your issues:

INSERT INTO Table2 (AccountNumber, AccountNotes )
SELECT Table1.AcctNum, "Notes Here" AS AccountNotes
FROM Table1;

It is putting the value from the [AcctNum] field in Table1 into the AccountNumber field in Table2. At the same time it's putting in a value that's not in Table1 into the AccountNotes field of Table2.