Insert multiple records from 2 tables in 3rd table

mickey munoz

13y1.6kSQL

Many thanks for reading my question!

INTENT: to write a query that can create records in [jncListQS] of glossary terms [tblMASTER].IdTerm matched with a ListName [tblQS].IdQS

note: tblMASTER and tblQS are already filled with data; jncListQS is empty.

USER selects (by checkbox) multiple records [tblMASTER].[IdTerm] in datasheet view
and
then USER selects 1 record (by combobox) [tblQS].[IdQS] in an interactive form.

Then, USER presses button >> "INSERT or ADD selected Terms to chosen ListName"

I had planned to retrieve the IdQS using a parameter query but i can't get the syntax right.

I'd like to used queries, nested or otherwise.
Do i need to do a loop, or ? ---------- many thanks in advance

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Akkiraju Ivaturi13y ago
Change the stored procedure script as below:
CREATE

PROCEDURE InsertQuestions

Instead of the script I have attached:

Alter Procedure InsertQuestions
Akkiraju Ivaturi13y ago
We need a SQL Function which splits characters in a string with a delimiter and returns a table as output.

For example, if we are passing a string with values as "1,2,3,4" then the function should be able to split the values and return a table as below:

Items
1
2
3
4

USER selects (by checkbox) multiple records [tblMASTER].[IdTerm] in datasheet view
and
then USER selects 1 record (by combobox) [tblQS].[IdQS] in an interactive form.

From UI, get all the IDTerm values (convert the values to string) appended with comma. Remove the last comma in the string. So, it should be like "1,2,3,4".

Next grab the iDQS value.

Now write a stored procedure that accepts two parameters. One is the string variable for IDTerms and other one is to accept the IDQs value.

Using the function split the IDTerms and then store all the values into jncListQS.

I have placed the function and the stored procedure as attachment.

Let me know if you have any questions.