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    
post Jan 7 2018, 04:37 PM

Posts: 2,602
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]

               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 ...

               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:

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!

Go to the top of the page
post Mar 3 2018, 09:15 AM

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

---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

Go to the top of the page

Custom Search

RSSSearch   Top   Lo-Fi    14th December 2018 - 12:15 AM