Batching Append Queries

[Steve]

I have two tables that are joined through a joining table where the PK of
both are inserted when there is a relationship between the two PKs. My
problem is, sometimes I have to add a new PK to the second table and I need
to set a relationship between this new PK and all PKs in the first table.
This is not always the case, but it does happen. Since there are hundreds of
entries in the first table, I do not want to do these one at at time. I was
thinking I should loop through each PK in the first table and append it with
the new PK in the second table. Is this possible? Any suggestions on how to
make this work? I am using Access 2000.

Thanks
Steve

 

[Steve]

I just realized what I wrote could make it sound like I have multiple PK per
table. I was just referring to a new entry made into the table and I want to
take the PK from this new entry and join it in the joining table with all
entries in the other table.

 

[Daryl S]

Steve -

If you really want a new record in the join table with the new PK from the
second table and every PK from the first table in it, then you can do that
with a query. Set up an Append Query with the first table in it, selecting
the PK from the first table appending to it's appropriate field in the join
table. Add the new PK from the second table as a reference to the form that
contains the new PK (or pass it in as a parameter, depending on how you want
to run this query), and set it to append to the appropriate field in the join
table.

Does that help?

 

[Steve]

I tried your idea but I cannot get the SQL statement to work. How would I
write the SQL statement for this? I've done plenty of append queries, but not
one like this.

 

[Daryl S]

Steve -

It looks something like this (substitute your table, field, form, and
control names):

INSERT INTO JoinTable ( PK1, PK2 )
SELECT FirstTable.PK1, [Forms]![FormName]![PK2Field] AS PK2
FROM FirstTable;

This pulls all PK1s from the first table and inserts them into the join
table with the value of PK2 that is on your form.

 

[Steve]

Sweet! Thank you!

Steve -

It looks something like this (substitute your table, field, form, and
control names):

INSERT INTO JoinTable ( PK1, PK2 )
SELECT FirstTable.PK1, [Forms]![FormName]![PK2Field] AS PK2
FROM FirstTable;

This pulls all PK1s from the first table and inserts them into the join
table with the value of PK2 that is on your form.

--
Daryl S

I tried your idea but I cannot get the SQL statement to work. How would I
write the SQL statement for this? I've done plenty of append queries, but not
one like this.