Where to start

[131313]

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!

 

[Jeff Boyce]

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 via AccessMonster.com]

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.

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!

 

[Jeff Boyce]

See comments in-line below:

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?

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

 

[131313 via AccessMonster.com]

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!

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?

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

 

[mscertified]

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

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!

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?

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

 

[Jeff Boyce]

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,

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!

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?

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

 

[131313 via AccessMonster.com]

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.

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]

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

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.

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

 

[131313 via AccessMonster.com]

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.

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]

I'm not there ... I can't see the two tables.

And you'll need to describe what YOU mean by "compare it to the data entry
table"...

Regards

Jeff Boyce
Microsoft Office/Access MVP

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.

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