Need help with Queries

[131313]

Hello,
Can someone offer help on a database problem I'm having.
The situation: Training database with associates table, completed training
table, certificates earned table.
What I am wanting to do is use the database to run a report that shows which
associates have earned certificates. The problem is that each certificate
requires a number of courses to be complete in order to get the certificate.
Each course has it's own training code which are all listed in a course table.
The course completion scores for each associate are dumped into the database
every week. How can I link them so that when running the report it shows if
an associate has earned a certificate or not. There are a number of
certificates as well, which included a number of courses to be completed. Can
this be done?

 

[Michel Walsh]

Sure, but try doing it one step at a time. If, to get a certificate, someone
has to have succeed many courses/exams, then, assume we have two tables.


Requirements
Certificate, Exam ' fields
A1 Ex1
A1 Ex2
A2 Ex1
A3 Ex1
A3 Ex3


as example, means that to get certificate A1, you need to have exam Ex1 and
Ex2. I am ASSUMING there is no duplicated (Certificate, Exam), ie, do not
repeat (A1, Ex1)



Skills
EmployeeID, Exam
0001 Ex1
0001 Ex2
0002 Ex1
0002 Ex2
0002 Ex3
0003 Ex1



as example, means that employeeID has succeeded the exams Ex1, Ex2 and Ex3.
I am ASSUMING no duplicated (EmployeeID, Exam).


To get all the certificate, for all employee, you can try:


SELECT s.EmployeeID, r.Certificate

FROM Requirements AS r INNER JOIN Skills AS s
ON r.exam = s.exam

GROUP BY s.EmployeeID, r.Certificate

HAVING COUNT(*) = (SELECT COUNT(*)
FROM Requirements As w
WHERE w.Certificate=r.Certificate)




Hoping it may help,
Vanderghast, Access MVP

 

[131313 via AccessMonster.com]

Thanks Michel,
I think that will work!
greg

Sure, but try doing it one step at a time. If, to get a certificate, someone
has to have succeed many courses/exams, then, assume we have two tables.

Requirements
Certificate, Exam ' fields
A1 Ex1
A1 Ex2
A2 Ex1
A3 Ex1
A3 Ex3

as example, means that to get certificate A1, you need to have exam Ex1 and
Ex2. I am ASSUMING there is no duplicated (Certificate, Exam), ie, do not
repeat (A1, Ex1)

Skills
EmployeeID, Exam
0001 Ex1
0001 Ex2
0002 Ex1
0002 Ex2
0002 Ex3
0003 Ex1

as example, means that employeeID has succeeded the exams Ex1, Ex2 and Ex3.
I am ASSUMING no duplicated (EmployeeID, Exam).

To get all the certificate, for all employee, you can try:

SELECT s.EmployeeID, r.Certificate

FROM Requirements AS r INNER JOIN Skills AS s
ON r.exam = s.exam

GROUP BY s.EmployeeID, r.Certificate

HAVING COUNT(*) = (SELECT COUNT(*)
FROM Requirements As w
WHERE w.Certificate=r.Certificate)

Hoping it may help,
Vanderghast, Access MVP

Hello,
Can someone offer help on a database problem I'm having.

[quoted text clipped - 15 lines]

Can
this be done?

 

[131313 via AccessMonster.com]

Michel,

OK this didn't work for me. I think I don't have something right. Here is a
the names of the tables I am using, and what's in them.

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?

Hope this is better information for you. I can't seem to figure it out.

thanks greg

 

[Michel Walsh]

It seems there is missing the table giving the required training codes to
get a given certificate code, the "Requirements" table, something like:



Requirements
CertificateCode TrainingCode ' fields
Security FirstAid
Security Reanimation
Maintenance Electricity101
Maintenance FixedEngines
Maintenance Mechanic101 ' data sample
....


so that, for Security certificate, you need First Aid and Reanimation
Trainings.



The table skills, in my example, seems to be your table [tbl data entry].


Hoping it may help,
Vanderghast, Access MVP