Append new records to table based on two other tables

[Naram-Sin via AccessMonster.com]

I am trying to add new records to a table using data from two other tables.
The purpose is to connect contract requirements to a Purchase Contract that
is associated with a certain supplier. It seems like it should be fairly
simple, but I haven't been able to get it to work as of yet. The set up is
like this:

Table1 Table2 Table 3
Clause# PC# PC#
Text Clause# Rec1
Mandatory Rec2
Government Rec3
Commercial


I was hoping to create a query that would create a list of clauses by adding
records in Table2 using the PC# in Table3 and the Clause# from Table1 that
had been checked as meeting a certain criteria such as yes for “Commercialâ€.
The query would create a list using the commercial clauses that would be
added to Table2 like this:

Table2
PC# Clause#
1232 XXZ (existing record)
1233 XXY (existing record)
1233 XXX (existing record)
1234 A01 (new record)
1234 A02 (new record)
1234 A03 (new record)
1234 A04 (new record)
1234 A05 (new record)

I would like the query to use the PC# from the current record in a form for
Table3 so it could be run from a command button.

 

[Ken Sheridan]

Try this:

INSERT INTO Table2
SELECT [PC#], [Clause#]
FROM [Table3], [Table1]
WHERE Commercial = TRUE
AND [PC#] = Forms![YourForm]![PC#];

BTW having separate columns for Mandatory, Commercial and Government is not
good design. Its what's known as 'encoding data as column headings'. A
fundamental principle of the database relational model, 'the information
principle' is that data is stored as explicit values at column positions in
rows in tables, and in no other way. These values should really be in a
column in a separate related table.

Ken Sheridan
Stafford, England