If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Issue with junction table
Hi guys.
I am building a database for my university and need to keep track of students and tutors who are using the carparks. My tables are as follows: Tutors: Tutor ID (PK - autonumber) Name License Students: Stud ID (PK) Name License Registered Cars (Junction table): Register ID (PK) Date/Time CarPark ID Registration Number User ID Car Parks: Car Park ID (PK) Location Cars: Registration Number (PK) Nationality Make MOdel Colour Relationships: (all one to many, tho it is a many to many relationship, registered cars being the junction table) Tutor ID (tutors) connected to User ID (Registered Cars) Stud ID (students) connected User ID (registered cars) Car Park ID (carparks) connected to Car park ID (registered cars) REgistration number (cars) connected to Registration number (registered cars) When i try to enter info into the registered cars table, particularly in the field user id, i get the message: 'cannot add or change a record because a related record is required in table tutors'. I have a combo box for the field user id. My questions: 1) i want the user id combo box to show both the stud id and the tutors id when selected. 2) how do i stop the 'cannot add or change record' message from appearing and let me update the field? Cheers folks!!! |
#2
|
|||
|
|||
Issue with junction table
Is ther any reason why you have separate tables for
students and tutors? If you merge the two - and perhaps include a field (IdType) to indicate which is which - your problem should dissapear. -----Original Message----- Hi guys. I am building a database for my university and need to keep track of students and tutors who are using the carparks. My tables are as follows: Tutors: Tutor ID (PK - autonumber) Name License Students: Stud ID (PK) Name License Registered Cars (Junction table): Register ID (PK) Date/Time CarPark ID Registration Number User ID Car Parks: Car Park ID (PK) Location Cars: Registration Number (PK) Nationality Make MOdel Colour Relationships: (all one to many, tho it is a many to many relationship, registered cars being the junction table) Tutor ID (tutors) connected to User ID (Registered Cars) Stud ID (students) connected User ID (registered cars) Car Park ID (carparks) connected to Car park ID (registered cars) REgistration number (cars) connected to Registration number (registered cars) When i try to enter info into the registered cars table, particularly in the field user id, i get the message: 'cannot add or change a record because a related record is required in table tutors'. I have a combo box for the field user id. My questions: 1) i want the user id combo box to show both the stud id and the tutors id when selected. 2) how do i stop the 'cannot add or change record' message from appearing and let me update the field? Cheers folks!!! . |
#3
|
|||
|
|||
Issue with junction table
Hi Luke,
Here's a table setup similar to what Chris suggested,but it has an added table for defining user types. tblCarPark cpID Location tblUserType UserTypeID Description (Studetnt, Tutor, Professor, Visitor, etc.) tblUser UserID UserTypeID Name (other fields, eg. Address,etc.) Licence tblCars CarID Make Model (other fields) tblRegistration cpID UserID CarID StartDate EndDate I included start and end date fields in the registration table just in case you wanted to track if a user's registration expired. hth rpw ----- Luke P wrote: ----- Hi guys. I am building a database for my university and need to keep track of students and tutors who are using the carparks. My tables are as follows: Tutors: Tutor ID (PK - autonumber) Name License Students: Stud ID (PK) Name License Registered Cars (Junction table): Register ID (PK) Date/Time CarPark ID Registration Number User ID Car Parks: Car Park ID (PK) Location Cars: Registration Number (PK) Nationality Make MOdel Colour Relationships: (all one to many, tho it is a many to many relationship, registered cars being the junction table) Tutor ID (tutors) connected to User ID (Registered Cars) Stud ID (students) connected User ID (registered cars) Car Park ID (carparks) connected to Car park ID (registered cars) REgistration number (cars) connected to Registration number (registered cars) When i try to enter info into the registered cars table, particularly in the field user id, i get the message: 'cannot add or change a record because a related record is required in table tutors'. I have a combo box for the field user id. My questions: 1) i want the user id combo box to show both the stud id and the tutors id when selected. 2) how do i stop the 'cannot add or change record' message from appearing and let me update the field? Cheers folks!!! |
#4
|
|||
|
|||
Issue with junction table
Responding to the last suggestion (I have not read the whole thread):
That won't work for people who are students and tutors. For example, Fred Smith, who is a student in course 'A', and also a tutor in course 'B'. HTH, TC "ChrisJ" wrote in message ... Is ther any reason why you have separate tables for students and tutors? If you merge the two - and perhaps include a field (IdType) to indicate which is which - your problem should dissapear. -----Original Message----- Hi guys. I am building a database for my university and need to keep track of students and tutors who are using the carparks. My tables are as follows: Tutors: Tutor ID (PK - autonumber) Name License Students: Stud ID (PK) Name License Registered Cars (Junction table): Register ID (PK) Date/Time CarPark ID Registration Number User ID Car Parks: Car Park ID (PK) Location Cars: Registration Number (PK) Nationality Make MOdel Colour Relationships: (all one to many, tho it is a many to many relationship, registered cars being the junction table) Tutor ID (tutors) connected to User ID (Registered Cars) Stud ID (students) connected User ID (registered cars) Car Park ID (carparks) connected to Car park ID (registered cars) REgistration number (cars) connected to Registration number (registered cars) When i try to enter info into the registered cars table, particularly in the field user id, i get the message: 'cannot add or change a record because a related record is required in table tutors'. I have a combo box for the field user id. My questions: 1) i want the user id combo box to show both the stud id and the tutors id when selected. 2) how do i stop the 'cannot add or change record' message from appearing and let me update the field? Cheers folks!!! . |
#5
|
|||
|
|||
Issue with junction table
Hey guys, thanks ever so much for all your contributions, its working!!! This forum is a god send, that is if god was bill gates! haha. cheers again
|
Thread Tools | |
Display Modes | |
|
|