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!!!

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!!!
.

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!!!

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!!!
.

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