Thanks for your reply,
1. TBL_Updates isn't really part of your data, so it shouldn't be in your relationships. I’ve removed the relationship for this table
2. You shouldn't have tables with only 1 real field in them (ID fields are not real fields of data). That means all your 'sys_' tables should go. Instead of linking to a table, just store the value that it references in the initial table. E.g. TBL_Cancelled will store CanclelledReason and not have CancelledRef. I was using sys_ tables if I don’t manage to make enough progress within the timeframe I can quickly mock up a front end in excel using these.
3. TBL_workorder isn't related. It has an AssetID, so it seems it should go to AssetData. Is that correct? I missed it when creating the relationships. Thanks!
4. TBL_OverallStatus can have many records for the same AssetID, so how will you know what the current status is? Would uploaddate sort this?
5. TBL_OutageDetails has an OutageDuration field. Isn't that just OutageStartDate - OutageEndDate? If so, then you don't need to store OutageDuration, you just calculate it. Deleted
6. TBL_Cancelled can have many records for the same AssetID, does that mean the same asset can be cancelled multiple times? Please see below
7. Cancelled seems like an overall status. Can't you just incorporate those 2 tables together? Done
8. TBL_AssestInfo has an AssetStatus field as well. Is that really a status of the asset or a status of the assetinfo? I think you really need to re-think the whole status thing. It will be the status of the asset (Online/offline/decommissioned)
9. TBL_Personnell has a PersonnellSkill field. That means a person can only have 1 skill, is that correct? I think so, The people involved are completely separate. We have Planners/Admin, engineers in the field, and external inspectors)
You really have a lot of issues and its going to get confusing trying to hammer these all out if we try at once. I think you need to start with TBL_AssetData and then work on one branch until you have that branch correct. The Personnell branch seems to be the best place to start since we've discussed it before and you are coming close to getting it. I’m getting confused myself!
I’ve been thinking over the whole process/data and just wanted to clarify the following thoughts etc:
So we have fixed assets(Tbl_assetData) with the fixed information that will rarely change.
I then have TBL_assetinfo, this contains all the date’s for maintenance etc, the asset working status, defect information/notes made.. this is linked to Tbl_assetData through the AssetNumber.
From TBl_assetinfo we know when the next inspection is due.
For an inspection to be done a planner needs to check if an outage is required(Tbl_outagerequired)
If an outage is required then a planner/admin need to book the outage and record the details, we also need to know who made the booking for accountability(Tbl_OutageDetails)
The Planner/Admin also need to schedule and engineer and an external inspector to the complete the work(Tbl_Assignments)
Part of the validation of the booking is checking that both the inspector and engineer are scheduled to work on the same day and if an outage is required then the Engineer and inspector is booked between the outage start and outage end dates.
We need to be able to capture/make comments against all of these bookings.
Any of the variables (Outage,Engineer,Inspector) can be cancelled and rescheduled for another time. We need to capture the new details and record that this has been rebooked. This can happen multiple times. The statuses we would like to report on in meetings are:
How many are currently booked? or not booked?
How many we are waiting on the outage to be confirmed?
How many are awaiting an engineer?
How many are awaiting an inspector?
We would like to see how many bookings complete first time and how much Churn there is and within what period the churn is happening. (is it over 100 days away from the due date, 50 days, 1 day etc)
how many and which assets have missed the due date.
Reasons for the cancellation/re-booking of the works/outage?
Tbl_AssetInfo will be updated from a spreadsheet received from another system twice a week with the latest information. what would be the best way of handling the new data, Delete the table contents and reupload? update the current table, append below and use MaxUploadDate?
upload then remove Duplicates?
Regards
Rob
