Insert with Multiple SubQueries - Possible?

J

John Dinning

Hello All,

I want to insert a single record in a table, using values selected from 2
other
tables, but cannot find the right syntax.
Is this possible in Access?

This is as close as I have come:

INSERT INTO CourseStudent
(SC_CourseID,SC_StuID)
SELECT
(SELECT Course_ID FROM Course WHERE Course_Code = :Course_Code),
(SELECT Stu_ID FROM Student WHERE Stu_Number = :Stu_Number)

Access gives me the error 'Reserved error (-3025); there is no message for
this error'.
I have not been able to find any example of a similar query, but I am sure
it must be possible.
Any help would be very welcome.

John.
 
J

Jeff Boyce

John

I'm not familiar with the "dialect" of SQL you are using. I can't tell what
purpose the colons (":") have preceding what I am assuming is a reference to
values found ...???
 
J

John Dinning

Sorry Jeff,
The colons indicate a parameter - the value is supplied before running the
query. e.g.:

INSERT INTO CourseStudent
(SC_CourseID,SC_StuID)
SELECT
(SELECT Course_ID FROM Course WHERE Course_Code = 'MATH'),
(SELECT Stu_ID FROM Student WHERE Stu_Number = '12345')

The two SELECT SubQueries will return a single value each, and the INSERT
statement will insert only one record in the CourseStudent Table.
I originally had a VALUES clause but was advised that SubQueries cannot be
used in a VALUES clause and I should use SELECT instead, but it gives the
same error.

I can quite happily do the 2 selects first then do the insert but would like
to do it in one if possible.

Regards,
John.
 
J

Jeff Boyce

The use of single quotes around your two WHERE clause values implies that
the underlying fields are of type "text". ?True?

Are you saying that the individual (sub)queries already work correctly?

Where are you doing this (in a query, as code in a form, ...)? (sorry if I
missed that earlier...)
 
J

John Dinning

Jeff

Yes, Course_Code and Stu_Number are text fields.
Course_ID and Stu_ID are Long Integer (AutoNumber).
The ID fields in the CourseStudent table are Long Integer.
Yes, the individual (sub)queries already work correctly.
Currently I am just doing this in a query within Access 2003.

I have found any information indicating whether SubQueries can be used in
Insert statements in Access at all. Do you know if they can?

Regards,
John.
 
J

Jeff Boyce

John

I tend to use "chained" queries where others with more SQL experience create
subqueries. My questions may have distracted others from checking this
thread, so I'd suggest re-posting, asking directly about the use of
subqueries in an INSERT query.

Best of luck

Jeff Boyce
<Access MPV>
 
Top