Question I have a problem, and an idea, but not enough knowledge....

[fat controller]

I have a bit of a problem, but also have a potential solution banging around in my head - however, I don't quite have the knowledge/confidence to back this up with the practical bit...

Please bear with me as I try and explain with as much detail as I can

THE PROBLEM:
I have a fleet of vehicles, each with a unique ID (FleetID); on any given day, this fleet of vehicles will either be allocated a duty number, be spare/unused or be undergoing service or repair work. Currently, the duty number for each vehicle is attached to it, and in theory if it is not in use it should have an attachment that says spare/service/MOT etc.

The availability of the vehicles is controlled by one department - they either allocate it to a duty number or they pass it to another department who allocate it.

What each duty does throughout the day is controlled by the second department, and if there are any that fail during the day they liaise with the first department to arrange repair/swap etc.

However, this whole system falls over (far too often at the moment if truth be told) for a number of reasons - firstly, if a vehicle has been repaired and then parked up with no duty badge, only the person that parked it up knows it is ready to go; similarly, if there is a number of call-outs to vehicles on the road we can find ourselves in a situation where we have a number of vehicles sat with no duty tags on them and nobody available to ask...... essentially, the department that controls the movements of the vehicles all day are left unable to do anything until someone from the other department returns.

THE SOLUTION:

I currently have a database that is used successfully by both departments, and this database has a table (tblVehicles) that lists all the vehicles that the site has, with the primary key being the vehicles unique FleetID; there is also a query that shows only the current fleet (vehicles sometimes move from site to site, but can also come back, so rather than remove them they are simply given a 'date out' and a 'out to' on the vehicle's record)

My thinking is that it surely cannot be beyond the realms of possibility to use this table to do the following:

• Each day have one record that shows the duty number or status of that vehicle for that day.
• A form which lists all the vehicles that will not close until there is a status next to each FleetID - DCount? Once all vehicles have been given a status, the form will then close.
• The ability to change the status of each vehicle as the day goes on (not sure whether this would be best to have the status simply change and overwrite the original status, or to show the changes (possibly up to four in a day I would think)
• The ability to run a report that shows the current status of all vehicles, regardless of whether they have been allocated a status or not
• If a vehicle is not useable, the ability to add a short line of text stating why (eg engine overheating) and the progress (eg awaiting parts)

I am not sure whether I should integrate this with the current database that both departments can access easily, or if I should create a separate database linking only to the vehicle table (so have two front and two back ends with one table shared/linked?) -- my current thinking is put it all in the current one simply because the current database is in use all the time by the department that controls everything out in the field.

And I am not sure how best to tackle this at the moment in terms of the tables, forms and queries - - so, a plea for your help once again ladies and gentlemen. All suggestions and advice gratefully received

 

[fat controller]

I've done some more thinking about this, and as my brain sees it, I have to make a record for each vehicle per day.

The maximum fleet size at any of our sites will be approx 180; given that each vehicle may have up to four duty numbers allocated to it each day, AND I have to make allowance for service/repair information, that would only serve to make the associated table utterly massive (and in reality far too big to even design!)

So, I am thinking of a table that has the following fields:

Date
FleetID
FitnessStatus (Fit=unticked, Unfit=ticked; thinking tickbox/radio button)
UnfitSince(Date)
FirstDuty
SecondDuty
ThirdDuty
FourthDuty
UnfitReason
UnfitStatus (eg Awaiting Parts)
UnfitNotes
ExpectedFitDate
ActualFitDate
VehicleType
PVRSD
PVRDD
PVRTOT

I am thinking the way to make this work is to have the number of records counted by date versus the number of records in the Live_Vehicles query - if they match, the form will close, if not it won't - is this achievable?

My reason for going for a tickbox/radio button for Fitness Status is that I would like any that are Unfit to be automatically 'carried over' to the following day (so they remain on the unfit list) (any records that have the specified field ticked to be copied to the following day)?

If at all possible, I would also like to be able to show how long a vehicle has been unfit for the current spell (hence the UnfitDate, ExpectedFitDate, and ActualFitDate)

Lastly, based on the VehicleType field, I would like to be able to show that we have a total of 'X-number' of vehicles of that type in the fleet, and 'Y-number' are unfit using that data to then show how many short or over we are against peak-vehicle-requirement (PVR fields)

Am I heading in the right direction?

 

[gemma-the-husky]

all you really need is a table for the vehicles that aren't available on a given day, not all vehicles

If a vehicle is off for a repair - say from 1st October to 10th October, then in theory you should be able to have the table store an off road date range - so you get one entry for this offroad, rather than 10 daily entries.

On the other hand using a date range to determine whether a vehicle is available on a given day is a bit harder than just checking a single date.

Whichever you find easier to manage, I suppose.

All you need is a simple table for offroad vehicles

a) vehicleID, date
b) vehicleID, datefrom, dateto

maybe include other stuff like reason for offroad, notes and so on.

if you do not need the history - then you do not even need this - you could just have an available/not available flag in the vehicle record, that can be set/cleared as necessary.

 

[gemma-the-husky]

by the way - you shouldn't really have the first trip, second trip entries in your table

you need these sort of tables

vehicle table (vehicleID, make, regno etc)
vehicle drops (vehicelID, date, dropno, destination)

in point of fact, since (vehicleID, date) will form a repeating group, what you OUGHT to have for full normalisation is this


vehicle table (vehicleID, make, regno etc)
vehicle run (Runid, VehicleID, date, driver, odostart, odofinish , timeout, timein. etc)
vehicle drops (DropID, RunID, dropno, destination, planneddroptime, actualdroptime, dropinfo, etc)
vehicle offroad (vehicleID, date)

PK is red, FK is blue. The underlined fields should constitute unique keys.

Hope that makes sense. The routine that creates vehicle runs and drops should report an error if you try to select a vehicle that is offroad.

Doing it this way makes it far easier to count total runs/drops, and evaluate stats. you also do not limit yourself to 4 drops. You can have as many as you want.

if a vehicle can make more than 1 run a day, then the run table with just vehicle and date isn't enough - maybe you need vehicle, date AND also RunNumber or TimeOut, say - as you need a unique index on the run in addition to the ID key.

The other information you quoted should logically fit into one of these tables without repeating it (or maybe you find you need other new tables), which is part of the art of normalising the data


Hope this helps

 

[fat controller]

Sort of makes sense, thank you

PK is Primary Key I assume? FK is??

The current database already has a table of vehicles (FleetID, RegNo, VehicleType etc), and for most of the other uses there is a query which shows only 'live' vehicles, which would be an ideal starting point.

VehicleRun - all that is needed here is the DutyID (or multiples thereof) for the day. If I could record the time that it changed from one DutyID to another, it may be useful, but is not a deal-breaker

VehicleDrops - Not needed at all; if we have the DutyID's for each vehicle for the day, the mileage covered and drivers using it are automatically recovered via a GPS-based system.

VehicleOffRoad - Ideal to have this separated out - indeed, it might well help limit the access of one department, as I would rather they had access to this for recording repair/service info, but not be able to touch the rest of the vehicle allocation

So, if I have this right

VehicleTable - as is currently with vehicle details

VehicleRunTable - RunID (presumably this could be our DutyID, VehicleID, Date, TimeChanged (to next DutyID?)

VehicleOffRoad - DateUnfit, VehicleID, VehicleType, ReasonUnfit, Status, StatusReason, DateExpectedFit, DateAcutallyFit

Now, the bit I am really unsure about is the relationship that I will need between these tables to make it all work? Or is it more a case of getting VBA to have a look and see if it is on either the OffRoad or RunID table, and if isn't then assume that it is spare?

Thanks for this - I am learning here too, which is always good

 

[gemma-the-husky]

FK is foreign key.

assume the PK in each table is an autonumber, then the FK is the autonumber of the record in the linked table.

so the PK of vehicle is VehicelID
vehicleID then becomes the FK in the run table.

with regard to drops, I thought that was the dutyid. if that is the run number, then yes, simply

vehicle table (vehicleID)
run table (RunID, vehicleID, date, dutyid)

relations
vehicle table 1 ----- m Run Table
(I vehicle has many runs)

vehicle table 1 ---- m OffRoad Table
(1 vehicle can have many instances of being off road)


when you select a vehicle for a run, you can either
a) do a dlookup on the offroad table to determine whether it is offroad or no, on the day in question OR
b) have the query preselect only the available vehicles.

again - you can assign the dutyid by doing a dmax on the vehicleID + date - or you can set them manually based on time out.

indeed you may not need a dutyid you could just have

run table (RunID, vehicleID, date, timeout)

just depends what works for you.

 

[fat controller]

Champion, thank you

Now, does the attached look about right? I am unsure as to the 'Join Type', which I think is partly because the query Live_Vehicles doesn't have a primary key?

EDIT - now, if I am right I will need a form where users can select a vehicle to make it unfit (so, put it onto the Unfit table), and another form where they can allocate vehicles to their specific running number for the day.

In both cases, how do I get round removing one from being unfit or allocated and return it to 'spare'?

I assume that if it is allocated to a running number and goes unfit that it will have to be de-allocated first and then put on the unfit table?

 

[gemma-the-husky]

don't move vehicles from one table to another. just have a yes/no field for fit/unfit. I don't even think you need those relationships though

all you need is to link the vehicles table to the routes/runs table, by joining the vehicle id. That sets the RI. ie Each Route must have a genuine vehicle.

the issue is just making sure that when you select a vehicle for a route, the vehicle selected is ACTUALLY in service on that day - which is a different issue.

 

[fat controller]

Would the fit/unfit field need to go into the vehicle table then? Presumably that would allow me to create a query for the allocation side that only allowed vehicles that were fit to be allocated (ie, where the field is not null)? Is there a way (query?) that allows the form to have all the fields from the Unfit table and the yes/no field for the vehicle table?

Just a further sanity check - when allocating vehicles off the 'fit' list onto the Runs table, the best method would be to do a DLookup onto the Runs table first just to ensure that a vehicle is not about to be double allocated?

Should I also be adding another yes/no field to the vehicle table - Allocated yes/no - to allow a query to show which are not allocated (ie, the spares list?), and then use the same principle for the Allocation form where it displays all fields in the Run Table, but only the Allocated yes/no field from the vehicle table?



Checking that it is actually in service is not an issue - there is a physical check and they are tracked by GPS all the time.

 

[fat controller]

Just sat thinking about it - the yes/no field for the vehicle being allocated wouldn't work, as each vehicle would remain 'allocated' into the following day and would have to be manually unallocated to start the next day..........

Hmmmmm......

Is there a way to show all records from a query (one field would be sufficient, in my case Bonnet Number), and then DEDUCT all the records that are in another table to show what is left over?

 

[gemma-the-husky]

well somehow you need to change something to indicate when a vehicle is available or not. if you know in advance, then the previous idea of a separate table for offroad events probably works better.

with regard to the last bit, that's an UNMATCHED query. The wizard will show you how, but it's easy to do manually.

have one query that selects all the vehicles you can use. another to select the vehicles that are already allocated.

the ones still available will show as the result of an unmatched query.

basically it's the set of items in SET A that are not in SET B.

 

[fat controller]

I think that would have to be done on time basis then, so something like:

Route Run TimeOut TimeIn BonnetNo
266 19 04:55 15.35 CD113
(^combo that populates all other fields with the exception of BonnetNo)

With the ability to manually deallocate (button that sets TimeIn=Now?) to make the vehicle available to go onto something else?

Would a further DLookup of this table work to see if the vehicle is allocated based on the current time? (ie, if it checks the allocated table at 1515 it will see CD113 as being currently in use, whereas at 1615 it will then be available?

Now, I do have advance schedule information based on day type (Saturday, Sunday, Monday to Thursday, Friday, School Holiday etc - presumably the sensible thing would be to have a table for each day type?) that can be made available in a spreadsheet format(Route, Run, TimeIn, TimeOut) which could presumably be uploaded to a table? Although these are reasonably static, they do change for various reasons, and whilst I personally am quite comfortable to copy and paste from Excel into a table to overwrite the current schedule, I wouldn't want users at other locations to be able to access a table (for all the obvious reasons) - - - is there a way to handle import/overwrite of data from a spreadsheet that could be done by a user running the database in the Access runtime app?

PS - is it wrong that I am getting a wee kick out of working through this, and learning on the hoof so to speak

 

[gemma-the-husky]

I don't quite follow what you are trying to do with the time out. If a vehicle goes out on a run at 3.15am, say, it might not get back until 11am.

If you try and set up a run at 6.am, you need a way of knowing that it is still not returned from the first run. The timeout doesn't necessarily help, but I am not sure what your actual business process is.

 

[fat controller]

I think it is the vagaries of my industry that is the main problem really - very little 'standard' stuff fits our model, which is one of the reasons that the pen and paper approach has persisted as long as it has.

I can have a vehicle leave at 0400hrs and not return until 0100hrs the following morning; indeed, I have nearly 200 vehicles with similar parameters at each site, (10 of them), every day.

I also have to cater for a team of users who are not comfortable in the least with using computers - so to stick them in front of a datasheet or continuous form is going to scare them utterly witless. Beside all this, I am keen to keep things as simple as possible, not least as everything is tracked and recorded throughout each day, so mileage operated and what was where and when is already taken care of.

I have pretty much resigned myself that I am going to have to change tack on this to get it work, starting with splitting the allocation across two tables, then build around that. I cannot guarantee that it will be the most elegant solution, but it should work.....

 

[gemma-the-husky]

I suppose it really comes back to some "route planning" process. You need some sort of method to determine how many runs a vehicle should be able to do in a day, and set these details up in the database. this planning process is not really an access task, I think - although it could be.

you really need satnav type stuff to plan routes and determine when vehicles should be back. there are package that do this sort of stuff, but native access won't without a lot of work.

I know what is involved. I do a fair bit of logistics stuff, and most of the people I work with have route optimisation packages to deal with the route planning process.

I thought you were just bothered about managing the available fleet for a given day, say. Not planning what they might be doing for the day.