If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Batching Append Queries
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 |
#2
|
|||
|
|||
Batching Append Queries
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. "Steve" wrote: 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 |
#3
|
|||
|
|||
Batching Append Queries
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? -- Daryl S "Steve" wrote: 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. "Steve" wrote: 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 |
#4
|
|||
|
|||
Batching Append Queries
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" wrote: 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? -- Daryl S "Steve" wrote: 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. "Steve" wrote: 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 |
#5
|
|||
|
|||
Batching Append Queries
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 "Steve" wrote: 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" wrote: 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? -- Daryl S |
#6
|
|||
|
|||
Batching Append Queries
Sweet! Thank you!
"Daryl S" wrote: 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 "Steve" wrote: 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" wrote: 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? -- Daryl S |
Thread Tools | |
Display Modes | |
|
|