UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Ase Tech Certification Database, Any Version    
 
   
MadPiet
post Jan 7 2018, 04:37 PM
Post#1



Posts: 2,421
Joined: 27-February 09



Just curious if anyone knows how to do this...

I'm trying to get my head around how to build a database to keep track of mechanic certifications and courses toward certification. Tracking which exams a mechanic has taken is easy. The tricky one is when I don't have a "plan" for a mechanic.

A certification is earned after the technician passes all of the exams that are mandatory for the cert. [Certification--(1,M)--Exam]

CODE
CREATE TABLE Exam (
               ExamID INT IDENTITY,
               CertificationID INT NOT NULL,
               ExamTitle VARCHAR(30) NOT NULL,
               RequiredforMaster BIT
CONSTRAINT fkCertification FOREIGN KEY CertificationID REFERENCES Certification(CertificationID)
);


Exam scores is pretty easy ...

CODE
CREATE TABLE Score (
               TechnicianID INT,
               ExamID INT,
               ExamDate DATE,
               Pass BIT );


Then there is a "LearningPlan" (It's a short-term plan of what the technician will learn/exams he will take in the next year) table, which I think is like this:

CODE
CREATE TABLE LearningPlan (
               PlanID INT IDENTITY,
               TechnicianID INT,
               PlanYear SMALLINT,
               ExamID INT );


The part I'm trying to figure out is what to do with mechanics who have certifications already and have no learning plan... I'm trying to get my head around how to find missing exams for a certification. If a tech/mechanic has at least one exam under his belt, I can go from Score --> Exam --> Certification and then find all exams that are in that cert. What do I do if the Mechanic has no certs or exams? (Then use the Learning Plan?) Do I have to use a UNION query to get EITHER the Learning Plan stuff or the Scores to identify what's missing?

Apologies if this doesn't make sense. Feel free to ask questions!

Thanks!
Pieter
Go to the top of the page
 
zaxbat
post Mar 3 2018, 09:15 AM
Post#2



Posts: 932
Joined: 26-January 06
From: .....the wiregrass (either you know or you don't)


MechanicTbl-------------
---CertPlanTbl |---
------CertTbl(qry only shows cert(s) in plan) ------CertTbl(qry only shows cert(s) NOT in a cert plan)


My idea would be to have a main form with a series of subforms
per mechanice there is a subform to show their certplan(s)
per certform there is a subform to show their certification(s) under selected certplan THEN another subform to show their certifications(s) not associated with any certplan

should be easy using master child linkage during form design and using dynamic filtering at run time with VBA





--------------------
Kindest regards, and Cheers!
ZAX

A picture is worth a thousand words and a zipped DB is worth a thousand pictures.
Oh, and....please don't disappear into the Twilight Zone.... Holler back with your results!
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    19th June 2018 - 03:51 AM