Question 1 newb and Access Relationships

[xirokx]

Hi there,

I decided to learn MS Access so having read many tutorials and guides I wanted to try to create my first database.

"I am confused about how best to set up relationships in Access"

As I am in the transport industry I thought I would try and create a database to help me automate some manual tasks.

I have 20 drivers, their driver documents i.e. M.O.T., insurance, license and other details expire at different times during the month. Currently I go through each drivers paperwork each week to alert them when their new paperwork is due.

So I though I would create a database that would:-

- in a form enable me to view each drivers documents and other details

- run a weekly report that would alert me as to which drivers documents are to expire the following week

I however am having difficulty creating the initial relationship in this file:-

- example file:- http://www.filefactory.com/file/ag93961/n/Driver_Details_Test_xls

Apologies for uploading the file using filefactory I didnt know what else to use, any suggestions would be appreciated?

If anyone could provide any clarity I would be really grateful.

Thanks in advance..

 

[NigelShaw]

Hi

this would be easy enough using one to many relationships i.e. One driver has many documents, one driver has many jobs etc.

Every table has an id so you make sure the related id is in your related table.

For example

drivers table would have driverID as primary key
documents table would have docsid as primary and driversID as foreign. The tables would relate via driversID.

On this basis, you could also relate more to documents table.

Example

documents would contain MOT, insurance etc and these are renewed annually.

I'll knock up an example when I get home from shopping


Nigel

 

[xirokx]

that would be terrific - thank you very much...

I was thinking of having 4 -5 tables as follows:-

- driver table i.e. name , address , dob etc

- Insurance table i.e. insurance company name, start date, expiry date

- car table i.e. license no, start date, expiry date,

- vehicle table i.e. vehicle start date, vehicle end date, registration mark, colour, model, license number

- MOT table i.e. mot start date, mot end date,

To make sure I understand what you mean are you saying:-

- driver details table would consist of auto generated driver ID which is a primary key
- car table would consist of a foreign key (which I believe is the same as a primary key) which would enable me to create a one to many relationship between 1 driver and many cars?

- so on and so forth for each other table?

With regards to the relationships I would have thought as 1 driver has only 1 car and all the documents relate to that 1 car and 1 driver that would be a one to one relationship? please clarify...

Also as I have your attention could you kindly clarify if it would be easy to make all dates that are due to expire in 1 week when running off a report be in RED font?

thanks for your help, really appreciate your support.

Am excited about creating my first of many databases..

thanks nigel..

cheers

 

[Atomic Shrimp]

Is it possible to generalise about the documents/licences/certificates to the extent that you could store them all in a single 'documents' table?

i.e. DocumentID, DocType, StartDate, ExpiryDate...

Although each driver may only have one car and each car only one driver, cars and drivers presumably change from time to time? (i.e. drivers get new cars, maybe people leave and their vehicles get passed to someone else?)
-In which case, it's (potentially) a many to many relationship - you may want to have a table of vehicles, a table of drivers, then a junction table that just describes which vehicle was assigned to which driver between which dates

- and you could just add records to this table as time goes on - and historical information about which driver had which car would be preserved - which might be important in the case of claims or complaints etc that only come to light after the vehicle has left the ownership of the driver.

 

[gemma-the-husky]

i wouldnt have a separate car table and vehicle table - a vehicle is a vehicle - and both will in general be treated the same way - just have a flag of some sort to indicate a car or other sort of vehicle, so that where there are differences they can be dealt with

you definitely dont need a separate mot table - the mot expiry dates just belongs in the vehicle table.


i probably wouldnt have a separate insurance table either - assuming each vehicle is separately insured, then the expiry date and other policy details for this also belongs in the vehicle table. This is a bit of a grey area, since often there will be a common policy covering a group of vehicles - in which case you may need a different structure.

this principle also applies to any other permits, such as london exclusion zone, toll road season tickets etc, lifting gear checks etx - if you deal with them by vehicle, the details belong in the vehicle table

 

[Atomic Shrimp]

i probably wouldnt have a separate insurance table either - assuming each vehicle is separately insured, then the expiry date and other policy details for this also belongs in the vehicle table. This is a bit of a grey area, since often there will be a common policy covering a group of vehicles - in which case you may need a different structure.

this principle also applies to any other permits, such as london exclusion zone, toll road season tickets etc, lifting gear checks etx - if you deal with them by vehicle, the details belong in the vehicle table

I'd keep them separate if there is any chance there might be multiple, overlapping versions of the same permit type (or even just to keep track of permits renewed in advance of expiry of the old one).
Also, if there is an indefinite number of permits that apply to each vehicle, then storing them as items in a child table might be better than keeping them as fields in the vehicle table.

 

[xirokx]

hey guys,

thanks for your responses...

can I request you treat me as a total newb at this stage and therefore kindly spell out which tables I will need based on my above physical example attached. Also please clarify which fields each table should have and the which field would be the primary and foreign keys for each table....

I know I am asking alot here, I really want to get my setup right so that the rest of the system will work well....

I was hoping once the database is created I setup a report which once selected enables you to view which drivers particulars i.e. insurance, mot, license is due for expiry soon and also which documents have already expired...Is this difficult to do?

Also I think keeping historical records whether the driver stays or leaves is vital to the business.

Finally I agree, in the event a driver changes his/her car then a new record is added for that driver to maintain the data.

I really appreciate your help guys,

Thanks alot

 

[Atomic Shrimp]

No problem - I'd do it like this:
(Wait for Gemma's input on this before cracking on with table design, as I'm not claiming the following to be the best way to do it)


tbldrivers
DriverID (autonumber, Primary Key)
Firstname
Surname
Address
DOB
Join Date
Leave Date
etc. - any other fields specifically talking about the person of the driver,

tblVehicles
VehicleID (Autonumber PK)
Registration number
Vehicle type
Date acquired
Date released ?
Status (owned, leased, etc) - make this a lookup from a table of vehicle ownership status descriptions, perhaps
etc. - any other fields specifically relating (materially) to the vehicle

tblDocuments
DocumentID (Autonumber PK)
Document type (MOT Certificate, insurance, etc) - make this a lookup from a table of document types later, perhaps
ValidFrom
ExpiryDate
Issuer - make this a lookup from a table of issuers later, perhaps
Document Details


tblVehicle_Drivers (this table will describe which vehicle is assigned to which vehicle)
DriverID (foreign key to DriverID in tbldrivers)
VehicleID (foreign key to VehicleID in tblvehicles)
AssignedDate (the date the driver acquired the vehicle)
ReleasedDate (the date the driver released the vehicle)
txtComments (might be useful to note vehicles assigned to drivers on short term temporary loan, etc)

tblVehicle_Documents (to attach any number of documents, current, future and expired, to a vehicle)
VehicleID (foreign key to VehicleID in tblvehicles)
DocumentID (foreign key to DocumentID in tblDocuments)

tblDriver_Documents (to attach any number of documents, current, future and expired, to a Driver)
VehicleID (foreign key to VehicleID in tblVehicles)
DriverID (foreign key to DriverID in tblDrivers)

That way, you can just:
Add drivers as they join, updating their Leave Date when they leave

Add vehicles as they are acquired, updating their release date when they are disposed of

Assign vehicles to drivers for a time-specific period (and include date criteria against this in a query to get current records only)

Add documents pertaining to either drivers, or vehicles

Attach the documents to drivers, or to vehicles, and query the current ones by setting criteria against their valid and expiry dates.