SQL Query Problem

[slaterino]

Hi,
I am trying to do what I thought would be a simple SQL query but am having some problems and can't find the answer to this on the web.

Put simply I want to add three values into a table using an append query. One of those values I want to retrieve from another table and which is dependent on a value on a form.

Okay, this is the start of my query:

INSERT INTO tbl_course_attendees_new ( URN, current, session_ID )

This first bit is fine. I then want to add a value from the form as well as set 'current' which is a yes/no field to 'no.' So I have:

SELECT Forms!frm_search!URN AS URN, -1 AS current,

My problem is with the next bit which is that I want to find every value from a table that has the same course_ID that is selected on the form in a drop-down box. How can I add this to the query? The SQL statement for this alone would be:

SELECT session_ID FROM sessions WHERE course_ID = Forms!frm_search!CourseName

How can I integrate that into the rest of the SQL query?

Thanks
Russ

 

[jal]

SELECT session_ID FROM sessions WHERE course_ID = Forms!frm_search!CourseName

Can you put another textbox on the form called SessionID whose source is set to the above? (Make it invisible if you want). Then you could add the sessionID value directly into your query.

 

[jal]

In other words you would do something like this:

INSERT INTO tbl_course_attendees_new ( URN, current, session_ID )
VALUES (Forms!frm_search!URN , -1 , Forms!frm_search!SessionID)