If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Need help with Queries
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? |
#2
|
|||
|
|||
Need help with Queries
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" u32128@uwe wrote in message news:6e803add3df65@uwe... 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? |
#3
|
|||
|
|||
Need help with Queries
Thanks Michel,
I think that will work! greg Michel Walsh wrote: 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? -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...eries/200703/1 |
#4
|
|||
|
|||
Need help with Queries
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 131313 wrote: 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? -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...eries/200703/1 |
#5
|
|||
|
|||
Need help with Queries
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 "131313 via AccessMonster.com" u32128@uwe wrote in message news:6e8e622ca64e5@uwe... 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 131313 wrote: 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? -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...eries/200703/1 |
Thread Tools | |
Display Modes | |
|
|