470,599 Members | 1,465 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,599 developers. It's quick & easy.

MS Access query

Hey everyone,

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.

Thanks so much in advance,

Mar 14 '07 #1
1 1423
14,534 Expert Mod 8TB
Hi Dave

Post the metadata for all the tables concerned as it will make it easier to see what's happening.


Here is an example of how to post table MetaData :
Table Name=tblBookings
Expand|Select|Wrap|Line Numbers
  1. Field; Type; IndexInfo
  2. Field; Type; IndexInfo
  3. StudentID; Autonumber; PK
  4. Family; String; FK
  5. Name; String
  6. University; String; FK
  7. Mark; Numeric
  8. LastAttendance; Date/Time
Mar 17 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

8 posts views Thread by Frnak McKenney | last post: by
1 post views Thread by Joris Kempen | last post: by
14 posts views Thread by Sean C. | last post: by
52 posts views Thread by Neil | last post: by
3 posts views Thread by mnjkahn via AccessMonster.com | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.