Insert with Multiple SubQueries - Possible?

  • Thread starter Thread starter John Dinning
  • Start date Start date
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.
 
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 ...???
 
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.
 
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...)
 
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.
 
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>
 
Insert with Multiple Subqueries

Hi,

I know it's an old post but might help someone else though, I think you need to do something like:

INSERT INTO CourseStudent (SC_CourseID,SC_StuID) SELECT tb1.CourseID, tb2.StuID FROM Course AS tb1, Student As tb2 WHERE tb1.Course_Code = :Course_Code AND tb2.Stu_Number = :Stu_Number

So pseudo:

INSERT INTO inserttb (val1,val2) SELECT tb1.val1, tb2.val2 FROM main1 AS tb1, Main2 As tb2 WHERE tb1.val1 = condition AND tb2.val2 = condition

Also if you have a value that is manually entered then simply do this:

INSERT INTO inserttb (val1,val2,manVal) SELECT tb1.val1, tb2.val2, 'value' FROM main1 AS tb1, Main2 As tb2 WHERE tb1.val1 = condition AND tb2.val2 = condition

Hope it helps someone.

Craig
 
Last edited:
Back
Top