I think i am being kind of stupid but i can't quite work out how to do it and wondered if anyone might be able to help!? I'm using MS Access 2002 on Windows XP FYI.
I have a customer database which lists customers and related bookings (containing courses which they book their staff onto). So customer A might book 4 staff onto a NVQ 2 Business course, 3 onto an NVQ 2 Safety course and 5 onto a NVQ 3 Business course for example.
Obviously the main relationship is a one to many relationship between customers and bookings. I have separate tables listing qualifications (eg NVQ 2, NVQ 3 etc) and another listing related courses (eg NVQ 2 Business course, NVQ 3 Business course, NVQ 2 Safety course etc). These are a one to many relationship as well, so one qualification can have many courses. I have done it like this to allow you to easily add new qualifications/courses into the system.
When you select a booking for a particular customer i currently have a combo box linked to qualification and then another combo box linked to courses (which is queried by the value of the qualification combo box) hence restricting the possible course selection based on the qualification. The results of these are then put into the table.
The problem with this is i want to run a report listing all of the courses and all of the total number of learners on each, which i don't think is possible with this setup because the original qualification and course tables that list all of them don't have any direct relationship with the customer bookings table?
As a result i thought it would be more sensible to use CourseID (from the course table) within the bookings table which would then allow a one to many relationship between courses and customers and only display the qualification/course on the form for the users information, but not actually store it in the table as you have the courseID anyway.
Problem is i can't work out how to do it as ultimately what i need is once i select the second combo box the related CourseID to be inputted into another field on the form, i'm not sure how though!
I would really appreciate any help anyone could give me on this as it's really starting to confuse me and i can't work out where to go from here! If i haven't made anything clear enough or haven't given enough detail then let me know.
