Is MS Access the right programme to use?

[Sgt Bilkp]

Bit of a dangerous question in this forum, but i plod on...

I am looking at trying to put together some form of database for my employment but i have the gut feeling that it may be too large for MS Access to deal with. If i outline the requirements, can the seasoned pro's who frequent this forum offer some of their valuable knowledge for a strict novice?

Background: Delivery company with multiple drivers (80), vehicles (20) and "routes" (10) for deliveries.

I need a database that will:

1. Against driver name, plot the shift pattern they are working (Earlies, Earlies, Lates, Nights, Rest day, Rest day repeated). The shifts sometimes vary and need to be changed. There needs to be the option of adding further shifts types (eg a day shift or a training course if we go that way)

2. Display the shift pattern for the whole company, listing names and dates on either axis. Selectable between date ranges or one date.

3. Be able to pick from the available drivers on duty that day, a driver and assign a van and a route - known as a "running" sheet

4. Either export or save the daily "running" sheet for archive

 

[KenHigg]

The number of records shouldn't be an issue but the bigger question is how is it to be deployed - How many users? Are they all in one office? etc...

 

[Sgt Bilkp]

The number of records shouldn't be an issue but the bigger question is how is it to be deployed - How many users? Are they all in one office? etc...

Number of users would be one, at any one time.

Any advice how this kind of data needs to be split up?

 

[KenHigg]

Hum... Looks like you need a drivers table, a vehicles table and a routes table. Then I'm guessing you'll need a table where each record represents an occurance of a person actually working a 'shift'. Probably several more I would guess...

 

[Sgt Bilkp]

Righty,

The basic tables (drivers, routes etc poses no problem). I am a bit stuck with the shift pattern table (date/person).

Any thoughts on the exporting/saving of the daily running?

 

[Banana]

What format do you want to export the reports? Excel Spreadsheet, Word documents or just a simple CSV text file? Access can do all of those. If you need a specific format, you'll need to check on that or use Access's Reports to give you that format, if you don't need to open it in another application.

Since it's one-user, I don't think you need to worry about splitting the database right now. BUT, it's a good idea to make sure you design your database so it can be easily splitted later in time when it goes multi-user. You could start off by reading about what splitting a database involves (usually just moving tables into their own copy and keeping forms, reports and code), and how it will affect your queries (your queries will continue to work, but performance may be different if the backend is now somewhere else, especially if it's going to be outside of the office's network).

HTH.

 

[KenHigg]

Yeah... That one is going to be tricky. I'm thinking it first needs to be considered from the perepective of how you currently keep up with it without a system. Does the driver sign in a log book of some type and turn it in or does a central person record the information?

 

[Sgt Bilkp]

What format do you want to export the reports? Excel Spreadsheet, Word documents or just a simple CSV text file? Access can do all of those. If you need a specific format, you'll need to check on that or use Access's Reports to give you that format, if you don't need to open it in another application.

Since it's one-user, I don't think you need to worry about splitting the database right now. BUT, it's a good idea to make sure you design your database so it can be easily splitted later in time when it goes multi-user. You could start off by reading about what splitting a database involves (usually just moving tables into their own copy and keeping forms, reports and code), and how it will affect your queries (your queries will continue to work, but performance may be different if the backend is now somewhere else, especially if it's going to be outside of the office's network).

HTH.

Exporting would be done into Excel, preferably not CSV (sometimes needs to be emailed).

Luckily all user (if multi-user) would be on a shared drive system (between offices)

 

[Sgt Bilkp]

Yeah... That one is going to be tricky. I'm thinking it first needs to be considered from the perepective of how you currently keep up with it without a system. Does the driver sign in a log book of some type and turn it in or does a central person record the information?

All drivers have to sign a useage record (for speeding tickets/accidents etc) but rather than check all 20-odd books to see who was driving it would be nice to do a check by driver of the vehicles they have driven and the dates.

Does it complicate it if i muddy the water by adding the fact that there are two drivers per vehicle for each shift? (drivers regs).

The actual driver for whatever hours is recorded in the usage record. The database should give me an idea which book to look at if i get a complaint.

 

[KenHigg]

I'm going to bail on this one and let Banana have it - The business process hasn't been defined well enough for me to make the some of the assumptions being made. i.e. What the heck are you exporting?

 

[Banana]

Sgt,

I trust you've already read up on normalization.

Two things:

1) If you can't do it on paper, you can't do it on Access. Therefore, you need to make sure you've worked out a plan before assuming that Access will automagically do it for you because Access is like a idiot savant with absolutely no common sense- you have to tell it at every step what you want to do it.

2) You need to make clear what relationship do you have.

For example, if you listed all vehicles, and all drivers are assigned to a vehicle, that's a one-many relationship. But if your drivers may drive different trucks over the course, then we now have a many-many relationship. OTOH, if you have two drivers assigned to just one vehicle at all time, then you have one-many relationship in backward. So, you'll need to enumerate how each of those table will relate with each other.

Do that then come back with your rudimentary design then we'll give more advices once we know a bit more about your model.

 

[Sgt Bilkp]

Sorry about that. I totally forgot about the multiple drivers when typing the first post. I do have the design, with relationships and controls but i am not sure how to post them on here in the best way so you can understand.

How about this?
http://i196.photobucket.com/albums/aa31/highwayslab/DBmap.jpg

 

[TIbbs]

I might just but in , but in shift you have early, day, night and so on, in my understanding this would be part of attributes patterns, they would not be attributes themselves.
So a member of staff would cover many shifts what's the relationship on the other direction? is there optionality?
Does a route consist of many drivers? are there two men at any time in the same vehicle or only on the route?
You migth want to show the optionality and the relationship more detailed.
You might want to look back again at the table shift as it seems the fields that you have as attributes should really be values of an attribute.

 

[Sgt Bilkp]

I might just but in , but in shift you have early, day, night and so on, in my understanding this would be part of attributes patterns, they would not be attributes themselves.
So a member of staff would cover many shifts what's the relationship on the other direction? is there optionality?
Does a route consist of many drivers? are there two men at any time in the same vehicle or only on the route?
You migth want to show the optionality and the relationship more detailed.
You might want to look back again at the table shift as it seems the fields that you have as attributes should really be values of an attribute.

The shifts do follow a pattern, but the pattern can be changed at 48 hours notice and other options need to be selectable (eg: Someone moving from an early shift to a late shift, or perhaps someone working a late shift now training or sick). Staff can only do one option in relation to shifts (eg: someone listed as an early, cannot be training as well)

The route consists of one vehicle driving that route. Each vehicle has two drivers. There are multiple routes.

 

[TIbbs]

You will need to map out the business rather than the outputs that you require. The outputs will come later for consideration.
A business requirements documents is what you would need to produce or requirements gathering exercise.
Right down everything that is of interest to the business in the real world excluding any computer based outputs and inputs.
For what you explained so far you would have:
-staff
-vehicle
-route
-shift
Other may come up of the relationships between them.
You need to create some entity relationship diagrams they are very simple to use and understand, it will show the relationship between each entity(real world thing of interest to the business), it includes the users of the business that interact with what you are trying to map out.
Your diagram looks like class diagrams, so I assume you can use ERD?

You can read on this here: http://www.devarticles.com/c/a/Development-Cycles/Entity-Relationship-Modeling/

You need to think now of the relationship between each entity.
E.g. Look at staff and vehicle and ask the following questions:
-Is an employee always assigned to 1 vehicle?
-Can an employee exist without a vehicle?
Ask the questions the other way around.
So far you have:

A vehicle has 2 drivers, so a vehicle is driven by 1 to many employees. to many assuming that other drivers may go on to drive that vehicle at later dates.
Looking at the other relationship from the staff side you would have:
A staff member is assigned to 1 vehicle (but can they at a later date be assigned to other vehicles? If that is the case it would be a 1 to many relationship from the employee side.
To resolve that you would create a third entity, I would use route as the third entity so you would end up with:

Staff is assigned to 0 to many routes, a route is undertaken by a driver.
A vehicle is placed on a route, a route consists of a vehicle.
See diagram below:
View attachment Doc1.doc

I changed to a 0 to many relationship as vehicle entity can still exist even if route doesn't, the same applies to staff. Also it will allow more flexibility to your design.
Hope this helps, is that more or less what you where looking for?
If your route has attributes then create an entity running route and have a 0 to many relationship going from route, vehicle and staff into the running route entity.

Sorry but without having my previous questions answered I am working on some assumptions about the business, that you would know better.

 

[TIbbs]

I would also be tempted to have the entity shift (table shift) as follow:

Shift

ID
​

Shift_Type
Date
Hours
StaffID (Foreign key)

Pattern you would have your early, late and so on, the date assigned to that shift and staffId as foreign key.
But looking at it as it stands a staff member is assigned to a shift and a shift belongs to a member of staff.
Is that correct accordind to the business?
If you keep this one then you could have another table that holds the shift patterns, times, etc:

Shift_Pattern
Shift_Type
Start_Time
End_Time

Shift Type being the primary key, you would assign the values early, late, mids, etc, and time would hold the period that range from. having shift patterns consisting of 0 or many shifts.

 

[TIbbs]

1. Against driver name, plot the shift pattern they are working (Earlies, Earlies, Lates, Nights, Rest day, Rest day repeated). The shifts sometimes vary and need to be changed. There needs to be the option of adding further shifts types (eg a day shift or a training course if we go that way)

Assign your shifts to your drivers using the table Shift, where StaffID is a foreign key.
You can add further shift types in the Shift_Pattern table.

2. Display the shift pattern for the whole company, listing names and dates on either axis. Selectable between date ranges or one date.

Use a parameter query to retrieve information between table Staff and table Shift.

3. Be able to pick from the available drivers on duty that day, a driver and assign a van and a route - known as a "running" sheet

Use shift table, add extra field that shows if staff is available or not depending on the shift.

4. Either export or save the daily "running" sheet for archive

Use an Append Query to append your data from the running route table or Route table (depending on the design you follow)