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

 

[ocpaul20]

I dont know if this will help, but what I would probably do is to mess around in a MYSQL query browser, trying different things out and see what works. You may find that it is about the kind of indexes you have set up on the table(s) and when that is sorted out, you will be able to add as many as you like. As you say, there should be a way to do it.