HELP!

  • Thread starter Thread starter 131313 via AccessMonster.com
  • Start date Start date
1

131313 via AccessMonster.com

Ok, so here is the problem I am having. The following are tables I have
created, the data entry table beeing a weekly dump of training data. Problem
is that there are many training requirements that make up a training
certificate. As associates pass training it is entered into the database with
a training code. Once all training for a certificate is complete then the
associate gets that certificate. So here is where the problem begins. How can
I link traing codes(many per each certificate) to the cerificate codes.

tbl Associate
associate id
associate first name
associate last name

tbl certificates
cetificate code
certificate name

tbl data entry
associate number
date of training
training code (this is codes for training that has been completed)

Again, I would like to have the database see that the required training(many
training codes) is complete for a specific certificate. If associate has all
requirements met then report shows that they are in need of certificate.
Would I set this up using queries?
 
You need one more table listing all the training codes required for each
certificate.
CertTrain --
CertCode
TrainCode
 
If you were to have tables as such:

AssociateCourses: associate_id, course_id, start_date, completion_date
CertificateCourses: certificate_code, course_id
AssociateCertificates: associate_id, certificate_code, start_date,
award_date

then this might work.

SELECT a.associate_id,
a.certificate_code
FROM (SELECT associatecourses.associate_id,
associatecourses.course_id,
associatecertificates.certificate_code
FROM associatecourses
INNER JOIN associatecertificates
ON associatecourses.associate_id =
associatecertificates.associate_id
WHERE (((associatecertificates.award_date) IS NULL)
AND ((associatecourses.completion_date) IS NOT NULL))) AS
a
WHERE (((associatecourses.course_id) IN (SELECT course_id
FROM certificatecourses
WHERE certificate_code =
a.certificate_code)))
GROUP BY a.associate_id,a.certificate_code
HAVING (((COUNT(* )) = (SELECT COUNT(* )
FROM certificatecourses
WHERE certificate_code = a.certificate_code)));

I am no expert, but I did not see any other responses.
This is a relational division problem.
 
Back
Top