Sorry about the delay writing back. This is long>>>>
I started out with tables like this
marine maintenance)
tblfleet-OK-all like attributes:just took out a couple of items
tblemployees-OK-same as above
tblCategories-OK (items like Electrical,Mechanical etc to organize
work/systems
tblSystems-OK(SysID-pk, CatID-fk)
tblStatus-Eliminated-each job type needed different status elements so I
just entered specific jobtype status' in each jobtype table as a lookup field
tblfacilities had some flaws(I had tried to incorporate office equipment
tracking in this table as it is usually only a dozen or so peices per site)
This information changes too often and is not really a facility
identifier(which was the point of the table) So if I had 1 ID number for each
facility my fields would be endless to include all the identifiers I needed
to list for office equipment. That also applied to my supervisors. Some of
my facilities have more than 1 supervisor (shift work) so regarding
facilities now I have:
tblfacilities(facID-pk)
tblfacequip(facID-fk,facequip-pk)
tblfacSupv(facID-fk,facSupv-pk)
tbljobs and tbljobsDetails(veryflawed--totally AbbyNormal)
incorporated in tbljobs I had covered various jobs as well as equipment
tracking.
So 1st I created an equipment table. As part of maintenance you have to
have reference to the specific model of equipment being worked on. Many of
the peices of equipment had attributes that were so different they required
individual fields. So I layed out all the attributes only (ie. bore, stroke,
rotation, volts etc.) of each peice of equipment I wanted to track, found
all with like attributes and tabled them together. (I was helped through
this in a thread called "Is Data Just Data"-MVPs to the rescue again)This
gave me nine new tables.
To group the nine tables I needed to first identify each peice of equipment
by its' system(already in place), then its' manufacturer then by model.
Further I needed to identify/attach each peice of equipment to its' vessel.
I did this in 1 table which just had Fleet ID , serial numbers, service
meters etc
tblVesselEquip(VEID-pk,SMMID-fk)
From
tblJobs+JobsDetails (just relative to equipment)
I now have
tblManufacturers(ManfID-pk SystemsID-fk:listed at top)
tblSystemManufacturersModels(SSMID-pk,CatID-fk,SystemsID-fk)
9 Equipment tables(SSMID-pk)
tblPM-(PMID-pk)Identifies Preventive Maintenance Items
tblPMRefernce-(PMRID-pk)Identifies reference material specific to the model
PMID + PMRID are also fk's in tblSystemManufacturersModels
tblJobs was looking better but still had many attributes not relative to
each job.
I had incorporated certificate and documentation tracking as a job.
So now I made:
tblCertificates(CertID-pk: this just lists types of
certificates-CO2:Training, LifeRaft, FCC,OrdinarySeaman etc)
tblCertificatesRecords(CRID-PK,CertID-fk, VesselID-fk,EmployeeID-fk)
Since Liferafts are certified and serviced by outside contractors I created
a Liferaft table to keep track of specific life raft info. (This was similar
to an equipment table, as it identified serial number capacity etc- but
because it is serviced and validated by outside source I just linked it to
tblCertificatesRecords.
tblLiferafts(CRID-pk)
The rest of the tables are just different jobtypes and/or jobtypedetail
tables.
That was probably way more than you wanted to know.
Also, the
"Microsoft 2003" All-In-One Desk Reference for Dummies helped alot. It has a
section on Access (2003-version I'm using, I'm sure there are other years
too) It was very basic though and the complexity of my situation was only
made clearer in this forum and countless hours hitting the Access training
tutorials.
On the way one day, after a particularly tedious day I thought "The minutia
in this project is hugh". Then I thought "Huge Minutia" ????????
Have fun!!
Mary
