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
|
|||
|
|||
Where to start
I am trying to figure out how to run a report that shows certificates earned
by associates that have completed training. Problem is that there are different types of training with different training codes that need to be complete for each certificate. I have multiple tables: associates, training data, certificates, and courses. The training data is a weekly dump of training results that associates have taken. What is the best route to go to link training needed to each certificate. I want to run a report that lets me know associates have completed training and have earned certain certificates. Example: based on training data, the report will show the associate name and what certificate they have earned, maybe date it was earned as well. How can I link 5 different courses to point to a certain certificate? I don't know where to start on this one. Most of the database is complete and I have all my tables, forms and all that working. I am lost on this part! |
#2
|
|||
|
|||
Where to start
We aren't there. We can't see your data.
Since a report in Access is based on the underlying data, you'll have to provide a more complete description of the data you have to work with. Regards Jeff Boyce Microsoft Office/Access MVP "131313" u32128@uwe wrote in message news:6e80a23d51e51@uwe... I am trying to figure out how to run a report that shows certificates earned by associates that have completed training. Problem is that there are different types of training with different training codes that need to be complete for each certificate. I have multiple tables: associates, training data, certificates, and courses. The training data is a weekly dump of training results that associates have taken. What is the best route to go to link training needed to each certificate. I want to run a report that lets me know associates have completed training and have earned certain certificates. Example: based on training data, the report will show the associate name and what certificate they have earned, maybe date it was earned as well. How can I link 5 different courses to point to a certain certificate? I don't know where to start on this one. Most of the database is complete and I have all my tables, forms and all that working. I am lost on this part! |
#3
|
|||
|
|||
Where to start
I have a table with the associates information, a table with training courses,
around 45 different ones. Each of these have it's own code like LAB1324 along with it's description. I also have training data in a table with the associates number, name, and training course code taken (could be multiple courses depending on what the associate took in that given week. Then I have a table listing all of the certificate names (Advanced Electrical Training). There are numerous other tables that deal with the associates information (dept.,etc.) The main problem is that I want the database to look for the completed courses an associate has taken. If the associate has all requirements for the certificate, it will show on the report. Report example: Associate number, Associate Name, Certificate Names (maybe shown with a checkbox if required courses are complete) The problem I am having is figuring out how to show that these certain 10 courses equal a certain certificate. Would this be like a one-to-many? For some reason I don't think it's going to be that easy? I have a fair amount of Access knowledge, but this has got me confused. If you need more info, let me know. Jeff Boyce wrote: We aren't there. We can't see your data. Since a report in Access is based on the underlying data, you'll have to provide a more complete description of the data you have to work with. Regards Jeff Boyce Microsoft Office/Access MVP I am trying to figure out how to run a report that shows certificates earned [quoted text clipped - 18 lines] all my tables, forms and all that working. I am lost on this part! -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...esign/200703/1 |
#4
|
|||
|
|||
Where to start
See comments in-line below:
"131313 via AccessMonster.com" u32128@uwe wrote in message news:6e82cc0c6652a@uwe... I have a table with the associates information, a table with training courses, around 45 different ones. Each of these have it's own code like LAB1324 along with it's description. I also have training data in a table with the associates number, name, and training course code taken (could be multiple courses depending on what the associate took in that given week. If you have an Associates table, presumably with your associates' names, why are you repeating their names in the Training table? Are you saying the Training table contains one row for every Course taken by an Associate? ?what "given week"?g Then I have a table listing all of the certificate names (Advanced Electrical Training). There are numerous other tables that deal with the associates information (dept.,etc.) You have Certificates. But where do you connect Courses to Certificates? Are you saying that you can "complete" Certification in an area by taking, say 5 specific Courses? Do any of your Courses apply toward more than one Certification? What is the relationship between Certifications and Courses (1-many, many-1, many-many)? The main problem is that I want the database to look for the completed courses an associate has taken. If the associate has all requirements for the certificate, it will show on the report. I take it you want it to show on the report, but you don't yet have a report doing this... Report example: Associate number, Associate Name, Certificate Names (maybe shown with a checkbox if required courses are complete) This sounds like you want a report that shows each Associate and ALL Certifications. The previous description seemed to imply that you only wanted a single Associate and a single Certification. The problem I am having is figuring out how to show that these certain 10 courses equal a certain certificate. This will be determined by what you tell us the relationship is between Certificates and Courses. Would this be like a one-to-many? For some reason I don't think it's going to be that easy? I have a fair amount of Access knowledge, but this has got me confused. If you need more info, let me know. Regards Jeff Boyce Microsoft Office/Access MVP |
#5
|
|||
|
|||
Where to start
Jeff,
Yes one table is just associate information (tbl associate) another table has all certificates (certificate code, certifiicate name) another table is data entry (associate number,date of training,training code). The data entry table is the weekly dump of training data of associate that completed training each week. I want a report that when I run it, it will creat a list of associates that have earned a certificate. The problem I am having is how to link multiple training codes to one certificate code. Each group of training codes are unique to a specific certificate, none are repeated. I would like the report to list all certificates at the top, all associates in a column and have check boxes for each certificate earned. Sorry if this is confusing! Jeff Boyce wrote: See comments in-line below: I have a table with the associates information, a table with training courses, [quoted text clipped - 3 lines] associates number, name, and training course code taken (could be multiple courses depending on what the associate took in that given week. If you have an Associates table, presumably with your associates' names, why are you repeating their names in the Training table? Are you saying the Training table contains one row for every Course taken by an Associate? ?what "given week"?g Then I have a table listing all of the certificate names (Advanced Electrical Training). There are numerous other tables that deal with the associates information (dept.,etc.) You have Certificates. But where do you connect Courses to Certificates? Are you saying that you can "complete" Certification in an area by taking, say 5 specific Courses? Do any of your Courses apply toward more than one Certification? What is the relationship between Certifications and Courses (1-many, many-1, many-many)? The main problem is that I want the database to look for the completed courses an associate has taken. If the associate has all requirements for the certificate, it will show on the report. I take it you want it to show on the report, but you don't yet have a report doing this... Report example: Associate number, Associate Name, Certificate Names (maybe shown with a checkbox if required courses are complete) This sounds like you want a report that shows each Associate and ALL Certifications. The previous description seemed to imply that you only wanted a single Associate and a single Certification. The problem I am having is figuring out how to show that these certain 10 courses equal a certain certificate. This will be determined by what you tell us the relationship is between Certificates and Courses. Would this be like a one-to-many? For some reason I don't think it's going to be that easy? I have a fair amount of Access knowledge, but this has got me confused. If you need more info, let me know. Regards Jeff Boyce Microsoft Office/Access MVP -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...esign/200703/1 |
#6
|
|||
|
|||
Where to start
You need another table to provide the link between training code and
certificate. This could be as simple as a two-column table. Of course you have to provide the table and its data before you can write the report. -Dorian "131313 via AccessMonster.com" wrote: Jeff, Yes one table is just associate information (tbl associate) another table has all certificates (certificate code, certifiicate name) another table is data entry (associate number,date of training,training code). The data entry table is the weekly dump of training data of associate that completed training each week. I want a report that when I run it, it will creat a list of associates that have earned a certificate. The problem I am having is how to link multiple training codes to one certificate code. Each group of training codes are unique to a specific certificate, none are repeated. I would like the report to list all certificates at the top, all associates in a column and have check boxes for each certificate earned. Sorry if this is confusing! Jeff Boyce wrote: See comments in-line below: I have a table with the associates information, a table with training courses, [quoted text clipped - 3 lines] associates number, name, and training course code taken (could be multiple courses depending on what the associate took in that given week. If you have an Associates table, presumably with your associates' names, why are you repeating their names in the Training table? Are you saying the Training table contains one row for every Course taken by an Associate? ?what "given week"?g Then I have a table listing all of the certificate names (Advanced Electrical Training). There are numerous other tables that deal with the associates information (dept.,etc.) You have Certificates. But where do you connect Courses to Certificates? Are you saying that you can "complete" Certification in an area by taking, say 5 specific Courses? Do any of your Courses apply toward more than one Certification? What is the relationship between Certifications and Courses (1-many, many-1, many-many)? The main problem is that I want the database to look for the completed courses an associate has taken. If the associate has all requirements for the certificate, it will show on the report. I take it you want it to show on the report, but you don't yet have a report doing this... Report example: Associate number, Associate Name, Certificate Names (maybe shown with a checkbox if required courses are complete) This sounds like you want a report that shows each Associate and ALL Certifications. The previous description seemed to imply that you only wanted a single Associate and a single Certification. The problem I am having is figuring out how to show that these certain 10 courses equal a certain certificate. This will be determined by what you tell us the relationship is between Certificates and Courses. Would this be like a one-to-many? For some reason I don't think it's going to be that easy? I have a fair amount of Access knowledge, but this has got me confused. If you need more info, let me know. Regards Jeff Boyce Microsoft Office/Access MVP -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...esign/200703/1 |
#7
|
|||
|
|||
Where to start
Please re-read my response. I suggested adding a table (as does Dorian)
that holds the valid Certificate-Course combinations. The structure of this table will depend on your answer to my earlier question -- how are Certificates and Courses related? Regards Jeff Boyce Microsoft Office/Access MVP "131313 via AccessMonster.com" u32128@uwe wrote in message news:6e8e910c7b870@uwe... Jeff, Yes one table is just associate information (tbl associate) another table has all certificates (certificate code, certifiicate name) another table is data entry (associate number,date of training,training code). The data entry table is the weekly dump of training data of associate that completed training each week. I want a report that when I run it, it will creat a list of associates that have earned a certificate. The problem I am having is how to link multiple training codes to one certificate code. Each group of training codes are unique to a specific certificate, none are repeated. I would like the report to list all certificates at the top, all associates in a column and have check boxes for each certificate earned. Sorry if this is confusing! Jeff Boyce wrote: See comments in-line below: I have a table with the associates information, a table with training courses, [quoted text clipped - 3 lines] associates number, name, and training course code taken (could be multiple courses depending on what the associate took in that given week. If you have an Associates table, presumably with your associates' names, why are you repeating their names in the Training table? Are you saying the Training table contains one row for every Course taken by an Associate? ?what "given week"?g Then I have a table listing all of the certificate names (Advanced Electrical Training). There are numerous other tables that deal with the associates information (dept.,etc.) You have Certificates. But where do you connect Courses to Certificates? Are you saying that you can "complete" Certification in an area by taking, say 5 specific Courses? Do any of your Courses apply toward more than one Certification? What is the relationship between Certifications and Courses (1-many, many-1, many-many)? The main problem is that I want the database to look for the completed courses an associate has taken. If the associate has all requirements for the certificate, it will show on the report. I take it you want it to show on the report, but you don't yet have a report doing this... Report example: Associate number, Associate Name, Certificate Names (maybe shown with a checkbox if required courses are complete) This sounds like you want a report that shows each Associate and ALL Certifications. The previous description seemed to imply that you only wanted a single Associate and a single Certification. The problem I am having is figuring out how to show that these certain 10 courses equal a certain certificate. This will be determined by what you tell us the relationship is between Certificates and Courses. Would this be like a one-to-many? For some reason I don't think it's going to be that easy? I have a fair amount of Access knowledge, but this has got me confused. If you need more info, let me know. Regards Jeff Boyce Microsoft Office/Access MVP -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...esign/200703/1 |
#8
|
|||
|
|||
Where to start
Ok,
Here is what the new table looks like: LAB Code LAB Name Certificate Code Certificate Name FMS0001 FMS Week One TDP0500 Snesor & Actuator Fault TS FMS0002 FMS Week Two TDP0600 DeviceNet & SCADA TS FMS0003 FMS Week Three TDP0700 Process Control FMS0004 FMS Week Four TDP0800 Integrated Systems Fault TS TEC1500 Mechanical Lab One TDP0110 Fundamentals TEC1501 Mechanical Lab Two TDP0200 Advanced Electrical & Mechanical TEC1502 Mechanical Lab Three TDP0200 Advanced Electrical & Mechanical TEC1503 Mechanical Lab Four TDP0200 Advanced Electrical & Mechanical TEC2110 Electrical Lab One TDP0100 Fundamentals TEC2111 Electrical Lab Two TDP0100 Fundamentals Now I all the information in this table. As you can see, the course codes that are needed for the certificate have the same certificate code, is this correct? Thanks Jeff Boyce wrote: Please re-read my response. I suggested adding a table (as does Dorian) that holds the valid Certificate-Course combinations. The structure of this table will depend on your answer to my earlier question -- how are Certificates and Courses related? Regards Jeff Boyce Microsoft Office/Access MVP Jeff, [quoted text clipped - 91 lines] Jeff Boyce Microsoft Office/Access MVP -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...esign/200703/1 |
#9
|
|||
|
|||
Where to start
If I'm interpreting your example data correctly, you have two Certificates
that require multiple Courses. If I recall, you wanted a way to see all the Courses required for a particular Certification. This table seems to be able to do that. Regards Jeff Boyce Microsoft Office/Access MVP "131313 via AccessMonster.com" u32128@uwe wrote in message news:6e986649229d3@uwe... Ok, Here is what the new table looks like: LAB Code LAB Name Certificate Code Certificate Name FMS0001 FMS Week One TDP0500 Snesor & Actuator Fault TS FMS0002 FMS Week Two TDP0600 DeviceNet & SCADA TS FMS0003 FMS Week Three TDP0700 Process Control FMS0004 FMS Week Four TDP0800 Integrated Systems Fault TS TEC1500 Mechanical Lab One TDP0110 Fundamentals TEC1501 Mechanical Lab Two TDP0200 Advanced Electrical & Mechanical TEC1502 Mechanical Lab Three TDP0200 Advanced Electrical & Mechanical TEC1503 Mechanical Lab Four TDP0200 Advanced Electrical & Mechanical TEC2110 Electrical Lab One TDP0100 Fundamentals TEC2111 Electrical Lab Two TDP0100 Fundamentals Now I all the information in this table. As you can see, the course codes that are needed for the certificate have the same certificate code, is this correct? Thanks Jeff Boyce wrote: Please re-read my response. I suggested adding a table (as does Dorian) that holds the valid Certificate-Course combinations. The structure of this table will depend on your answer to my earlier question -- how are Certificates and Courses related? Regards Jeff Boyce Microsoft Office/Access MVP Jeff, [quoted text clipped - 91 lines] Jeff Boyce Microsoft Office/Access MVP -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...esign/200703/1 |
#10
|
|||
|
|||
Where to start
Now that I set up the table this way, how can I compare it to the data entry
table which is the table that is entered weekly with associate training codes. There are numerous associate training records in the data base. That table includes the lab code for each of the labs they have taken. 131313 wrote: Ok, Here is what the new table looks like: LAB Code LAB Name Certificate Code Certificate Name FMS0001 FMS Week One TDP0500 Snesor & Actuator Fault TS FMS0002 FMS Week Two TDP0600 DeviceNet & SCADA TS FMS0003 FMS Week Three TDP0700 Process Control FMS0004 FMS Week Four TDP0800 Integrated Systems Fault TS TEC1500 Mechanical Lab One TDP0110 Fundamentals TEC1501 Mechanical Lab Two TDP0200 Advanced Electrical & Mechanical TEC1502 Mechanical Lab Three TDP0200 Advanced Electrical & Mechanical TEC1503 Mechanical Lab Four TDP0200 Advanced Electrical & Mechanical TEC2110 Electrical Lab One TDP0100 Fundamentals TEC2111 Electrical Lab Two TDP0100 Fundamentals Now I all the information in this table. As you can see, the course codes that are needed for the certificate have the same certificate code, is this correct? Thanks Please re-read my response. I suggested adding a table (as does Dorian) that holds the valid Certificate-Course combinations. [quoted text clipped - 12 lines] Jeff Boyce Microsoft Office/Access MVP -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...esign/200703/1 |
|
Thread Tools | |
Display Modes | |
|
|