Design of relations

[b747_440]

Dear Newsgroup,
I'm did not much Access programming or design until now.
I'm trying to figure out a good database design for the following
problem. Any advice would be highly appreciated since I simply can't
figure out. Maybe I'm overseeing something really simple.
I would like to design an employee database. Each employee is a driver.
Each driver has a license to drive cars, heavy trucks or both. Each
group of vehicles (trucks, cars) contains several vehicles itself.
I would like to set for each driver if he may drive trucks, cars or
both. When viewing the database in Access, I'd like to see an employee
with the typical nodes below his dataset. When opening the node, I'd
like to see the categories of vehicels he may drive. Then there should
be another node with all individual vehicels he may drive.
At the moment, I have a solution which "almost" works.
1. Table "Employee", contains "ID" (primary), "Name"
2. Table "Types", contains "TypeID" (primary), "Name_of_Type"
3. Table "Employee-Type", contains "ID", "TypeID", "Enumeration"
(primary)
4. Table "Vehicles", contains "VehicleID" (primary), "TypeID",
"Registration"
Relations:
1. Table "Employee"(ID)---1:n--->"Employee-Type"(ID)
2. Table "Employee-Type"(TypeID)---n:1--->Table "Types"(TypeID)
3. Table "Types"(TypeID)---1:n--->Table "Vehicles"(TypeID)
With this design, I never get all the desired nodes... - although it
seems to work in one direction. I can't do reverse queries. I suppose
it's because of the n:1-relation which I get in (2.).
Any ideas, how to accomplish this task. I must be terrible simple, but
I have already spent too many nerves.
Thanks a lot,
Bart

 

[KARL DEWEY]

Try this --
1. Table "Employee", contains "ID" (primary), "Name"
2. Table "Types", contains "TypeID" (primary), "Name_of_Type"
3. Table "Vehicles", contains "VehicleID" (primary), "TypeID", "Registration"
4. Table "Employee-Vehicle", contains "ID", "VehicleID", “ValidDateâ€,
“Remarksâ€

1. Table "Employee"(ID)---1:n--->"Employee-Vehicle"( VehicleID)
2. Table "Types"(TypeID)---1:n--->Table "Vehicles"(TypeID)

Table "Employee-Vehicle" is a junction table.

 

[b747_440]

Hello Karl,
thanks a lot!
Unfortunately, I don't get a connection between the
"Employee/Employee-Vehicle"-Block and the "Types/Vehicles"-Block with
each block containing two tables. Do you have maybe any suggestions
left?
Is there anything special about the junction table? Maybe something
like a composite key?
Thank you,
Bart