Creating SQL query with one-to-many field

[slaterino]

Okay, I'm hoping someone might be able to help with this. I've tried trawling the web but it's quite a hard problem to look for and I haven't managed to find a solution yet. I have an append query that adds four fields to a table. However there is one issue because schSessionID and schSchID have a one-to-many relationship. It means that the query works fine when there is only one occasion of schSchID for a schSessionID but when there are multiple nothing gets added.



It is vital that a new entry is created for every single schSchID. How can I change this query so that this happens? It doesn't seem like it should be that complicated but I just can't figure it out!

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

Thanks!
Russ

[weekend00]

I don't understand you tables clearly.
if you meant you have more than one record with same sessionID in you table tbl_activity_schedule, and you only want one of them, then you need more criteria to identify.

[NTC]

Kinda sorta have a few issues; in terms of sql it is your WHERE statement that is calling a specific single value from the form. So it can only work with a single record.

Completely out of the box one has to suggest that moving data from one table to another is a design no no. Since schSessionID and schSchID are identical cross references - you should leave everything as is; and simply use subforms to display related info or join the data in a query for a display.


Of course not every situation conforms to db design guidelines; so if you want to insert fields of multiple records try doing it this way: first create a plain old select query that captures/creates the record set (rows & fields) you want to append - call this query 'preAppend' and save. Then make a new query using query design, put both the target table & preAppend up as record sources - make it into an UpdateQuery and drag down the appropriate fields of the target table into the query design columns. Then manually type in the 'UpdateTo' field [preAppend].[fieldname] using the appropriate field name for each column. In schSessionID/schSchID column don't put it in the 'UpdateTo' because you are not updating this one but instead but it in the criteria field. If this abbreviated explanation isn't clear - you should have/get an Access text book in the version you are using and that will show an example of creating an UpdateQuery. It should work fine.