Can anyone help me with this minor SQL problem?

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

Hey Thomas,
Both schSchID and schSessionID are in the same table. It’s just that I need to add every schSchID for the schSessionID which is selected in a combo box into a new table, so I’m not sure a JOIN query is needed. Unless I’m missing something that is!

Thanks so much for the help r937. I have just been on holiday for a week so had to leave the database. I have been using the code you suggested but have ran into one further snag. First off, here is the code as it is at the moment:

INSERT INTO tbl_course_attendees_new ( URN, [current], course_ID )
SELECT Forms!frm_register!Combo2, -1, schSchID
FROM tbl_activity_schedule
WHERE schSessionID = Forms!frm_register!Combo0;

The problem I am having with this code at the moment is because there is a one to many relationship that’s causing problems (by the way I haven’t created this relationship in the relationships screen). For every schSessionID there could be a number of different schSchID entries. However many there are I want to be inputted into the new table but the database doesn’t seem to like this.

In case this isn’t that clear. If the schSessionID was 26 there could be three different schSchID’s that match this with values of 3267, 3268, 3269. Alternatively there could be just one schSchID of 3267. However many there are I would like a new entry in the tbl_course_attendees_new table with each of these values as well as the URN and current fields as previously specified.

This seems like it should be quite easy to resolve but I don’t really know what to search for and can’t find the answer at the moment. If you know how to sort this out then please drop me a line.

Russ

could ! you ! try ! with ! the ! exclamation ! marks ! please :smiley:

i mean, try the exact query using Forms!frm_register!Combo2 and Forms!frm_register!Combo0;

Okay, I worked it out. It turned out to be very simple in the end. It always is with Access it seems, it’s just a matter of going through a million possible problems until you find the right one.

Anyway, for some reason my newly-created table had decided to have a relationship with one of the other tables. I have no idea how that started but I deleted the relationship and know it seems to work. The relationship was with the schSessionID field which is why it would never let me create more than one entry.

Cheers!
Russ

Yeah, just tried it and it worked fine. Just did a simple SQL like so:

SELECT 12 AS Expr1, -1 AS Expr2, tbl_activity_schedule.schSessionID, tbl_activity_schedule.schSchID
FROM tbl_activity_schedule
WHERE schSessionID = 4

When schSessionID was set as ‘1’ there was only one entry which was correct as there was only one schSchID for that schSessionID. When I set it to ‘4’ there were 5 entries, each with a different schSchID, exactly the way it should be.

okay, please run the SELECT by itself to verify that it does indeed produce multiple schSchID’s for one schSessionID

As I mentioned before entries are only added to course_attendees_new when there is only one schSchID to the schSessionID. When there are multiple schSchID’s for one schSessionID then nothing gets added to the database. No error messages are displayed for this. This is what I mean by the database ‘doesn’t seem to like this’.

Cheers!

i am sorry, “doesn’t seem to like” is not a valid database error message

could you give me something more concrete to go on

@slaterino: I’m afraid that Thomas is a bot and now he must go forever. The answer posted is not his… but from wikiepedia and that’s why it doesn’t make much sense.

as far as the first two values are concerned, they are just constants (even though one of them is coming from a form field), so all you really need is a single SELECT

INSERT 
  INTO tbl_course_attendees_new 
     ( URN
     , current
     , session_ID )
SELECT Forms!frm_search!URN 
     , -1 
     , session_ID 
  FROM sessions 
 WHERE course_ID = Forms!frm_search!CourseName

notice i removed the column aliases – aliases are not required here, because in an INSERT/SELECT the values provided are simply entered into the corresponding column by ordinal position

of course, it doesn’t hurt to put them in (and it’s good self-documentation for very long lists), but for just a few columns i usually don’t bother