Going round in circles

[liddlem]

Each month, I need to collect information related to equipment in vehicles at a particular location. Other than getting a written report from each location, I do not know what vehicles are there until I get the report.

I need to keep a record of past data. (easy enough) . . . but
In order for me to ensure that broken/missing equipment is replaced quickly, I need to be able to see what was recorded last month, before I update this months record.

I was thinking of applying something along the following lines.

1. TblVehicle
- Registration

2. TblClinic
- ClinicName
- Address
- eMail

3. TblClinicVehicleCheck
- Registration
- ClinicName
- Yr
- Mth
- FaxSubmitted (Yes/No to confirm that the report was received)
- Item1 - (Yes/No to indicate that the item needs attention)
- Item2 - ditto
- Item - ditto
Records which vehicle was at which clinic in which year/month, and what the issues were.

At the start of each month, run a "month end" process which selects * from 3.TblClinicVehicleCheck where Yr=current_year, Mth=Current_Month, FaxSubmitted = True ....
And then append these records back into the same table (3.TblClinicVehicleCheck) with the current year\month

So now, when I get the fax from the clinic, I simply have to modify the record for this month.


Some issues
1. The corporate car pool manages the logistics of the vehicles and he chaps at the corporate car pool do not communicate vehicle change info with me despite my numerous requests.

Because we are talking about clinics, I need to ensure that the vehicles are adequately stocked - the only way to manage this properly is to link the stock to a vehicle.

Consequently, vehicles
a. move between locations without my knowledge.
b. they are temporarily suspended from actions while they go in for a service.
c. they are simply retired without my knowledge.

2. There is a high staff turnover at the Clinics because they are manned by volunteers and medical locums. (loci?) So, as you might have guessed, I don't get the required reports from all clinics, every month. So there is a risk of "losing" data between months.

Has anyone come across a similar conundrum?
do you have suggestions as to how I could make this work effectively?

Thanks for any input.

 

[jdraw]

Have you seen this

http://www.databaseanswers.org/data...maintenance_with_attributes_print_version.htm

It may give you some ideas.

 

[liddlem]

Thanks JDRAW - I did take a gander at a number of the data modeling diagrams. And yes - they have been useful. But, (i think) my problem is not with the structure of my rather. Rather, it is with the internal process of the organization. Unfortunately a culture that I am not able to change. (ie: Lack of communication between 3 parties.)

Perhaps a little more explanation/history might clarify:
The reports that we were getting were basically useless. When I checked the existing data, I discovered that the existing way of capturing the information is the cause of the problem.
To now, the user has simply navigated to the current record and typed in a vehicle registration (no list) and then in a free-text field, (256 char) typed in all the things that need attention.
This is okay if the data that is submitted (by fax) is correct, but often, the registration number is not. And also, the comment may say "spare type missing" in month 3, but in month 4 there is no comment, and then in month 5, the "spare tyre missing" shows up again. So in month 4, we assume that the tyre has been replaced.

So assuming that i have cleaned up the existing data (previous months record)
- Rego now linked to a lookup table (TblVehicle)
- Important items now listed in separate fields. (So that we can purchase x number of widgets as reflected in a report)

My issue is how to proceed from here.
In order to try and ensure the integrity of the data, I need to put in some checks & balances.
1. Somehow copy last months record onto this months record so that when the user wishes to update, they have a sense of what might still be outstanding. (bear in mind that we want to keep last months record in tact)

Some issues that present.
1. If a vehicle is changed/replaced, I dont know about till I get the monthly fax.
2. I dont always get a fax from every clinic each month. (Hence the flag to indicate whether the report was received or not)
3. If a vehicle is moved from one clinic to another, I dont know about it until clinic "A" report does not show it AND clinic B report does show it.
4. If a vehicle is retired, I dont know about it at all. I simply assume that its out of commission if it doesnt appear on a report for a few months. (Still working on system to track how ALL the equipment is transferred at change over.)
5. I have suggested that we make the DB available at each Clinic, but there are legal issues that require we keep a hand-written (and signed) copy of the paperwork. So getting it faxed to a central location is the most accurate method.

So
A. Do i copy last months record if the report was not submitted? (If NOT, then how do I retrieve/ up the default data in 2 months time)
B. How do I handle vehicle data when a NEW vehicle shows up. Bearing in mind that there are often discrepancies with the hand written data. Sometimes, the locum just assumes that he/she has written the correct vehicle rego. Other times, a vehicle has been switched without our (or even the locum's) knowledge. (ie, between locums coming and going)

One of the things that I plan to do is send a list to the clinic, showing what we THINK is there. The problem with that, is the locum gets lazy and does not check the rego - they could simply sign on the dotted line. (Sure, there could be an issue if something untoward happens, but not if nothing happens for 1 change-over or two.)

Thanks again.

 

[jdraw]

For what it's worth, you might want to make a list of the steps/processes/transactions involved.

eg: create work order/complete work order
order parts/receive parts
transfer vehicle for locA to locB etc.

Some of these will be critical to knowing status; others perhaps less so.

Identify what MUST be correct/done/recorded and put in the appropriate control.

I think this would go a long way to having "up to date data", but it is certainly loose at best. Software is not ever going to resolve people not communicating, but some discipline for the sake of the business has to be put in place. (Again easier said than done).

Is this a profitable business? Are there certain records that must be kept (and accurate to some level) for regulatory or financial purposes.

Just my 2 cents....

 

[gemma-the-husky]

are you the only database user.

you ought to record the dates that you get notified of the movement records, both for your own protection, and for "ammunition" should it be needed.

maybe this would be political, but also consider notifying your bosses that you aren't getting this information on time.

then the dbs design needs to be able to store and manage this information

 

[liddlem]

You are correct about being PC correct and taking "CYA" precautions. I have simply told management that what they want, cannot be done. They have to take what they get. Hopefully they will now drive a process of change. (but I'm not holding my breath)

I strongly suspected that there was no solution, but thought that I'd put it out there in case someone had a potential solution.

I have have decided that I am not going to try and be "pro-active" by trying to forecast which vehicles should be where, each month. Instead, I will add a new record only for the data as/when I get it.

Thanks again for your input