Should I use a Checkbox in Table ?

[spazzer]

Hi,
I have a table with contact details of over five hundred drivers, they all can drive between 1 and 3 different types of vehicle, some can drive all three and some only one, how is the best way to show what vehicles these drivers drive, should I create a SEPARATE TABLE for VehicleID , My Contacts page has Autonumber as Primary Key with FirstName,LastName,PayNumber,Depot and ContactNumber in it,can anyone advise the best way to show this ????

Many Thanks,
Nick C

 

[stopher]

You should create a separate table that shows which vehicle types each driver can drive (normalised).

Consider if you wanted to create a report when the user is prompted for a vehicle type and the report lists all the drivers for that vehicle. This is easy when the data is normalised. But if you had three tick boxes you would need to code or create a report to cover each tick box scenario.

Also, what happens if you introduce a fourth vehicle type. If you have tick boxes, then you have to redesign all your reports and forms to accomodate the new requirement. Whereas with a normalised design you simply add another record.

hth
Chris

 

[spazzer]

Ok thanks for reply ,could you tell me the fields etc i need to create to achieve this and where to create relationship please ,I have Autonumber ,FirstName,LastName,PayNumber,MobilePhone,DepotID ,these are in my contacts table ???

Thanks,
Nick C

 

[DCrake]

First you need to add a FK in your drivers table

fldVehicleFK: Long Integer


Then create a Vehicle Table

TblVehicles
fldVehiclePK: Autonumber
fldVehicleType: Narrative

Then create a relationship between the fldVehiclePK in the tblVehicles and the fldVehicleFK in the drivers table using a one to many relationship.

David

 

[spazzer]

Thanks Again,
How do I then show which driver drives which vehicles ,whether all three or just two !!

Thanks,
Nick C

 

[DCrake]

You also need a third table that holds this information

TblDriverVehicles
fldPK: Auto number
fldDriverFK: Long Integer
fldVehicleFK : long integer

Example

record 1
fldKP:1
fldDriverFK: 1
fldVehilceFK:1

record 2
fldKP:2
fldDriverFK: 1
fldVehilceFK:2

record 3
fldKP:1
fldDriverFK: 1
fldVehilceFK:3

In the above driver 1 can drive vehicle type 1,2 & 3

On your form that displays driver information you would have a combo/list box that is linked to this table via a query to display the vehicle types that they have been assigned.

David

 

[spazzer]

Thanks Thats Great,
Just one other thing, if I was to add a further table with Routes would I do the same thing and create a separate table for with Routes in it and then a further table again with Drivers Routes and do as you have said above ?

Thanks for help,
Nick C

 

[DCrake]

Essentially Yes, the concept is still the same.