Bruce M

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

131313 via AccessMonster.com

Bruce, this is a new post from the question you responded to yesterday.


Firstname, lastName, associate ID, start step, program start date,program,
etc., is all in tbl associates
tbl data entry is the table that holds all associate ID,course completions,
dates .
tbl certificates holds all certificate names, certificate codes, and step
qry Certificates Earned takes the above 3 tables and determines if all
training codes for each certificate (or step) is complete, if so it shows the
associate Id, first name, last name, certificates earned. All informaytion I
need is there. All tables are linked by associate ID or Training code

The program is for all maintenance associates that have the title TDP
(program they are in), it's the same amount of steps, certificates, etc.
total of 8 certificates, steps. all of which are the same for everyone.
The problem I am having is how to set the whole thing up.
How to show that if the associate has placed out of the first 1-4 steps then
show "Tested Out", and how to start the step date at the step following the
last step that was tested out, how to multiply the date for each additional
step. How to maybe have the field certain colors for "Tested Out", Current
step the associate is on, and completed steps. This is where I am having
difficulty.
I will be happy to provide you with more info if needed, I am just backed in
a hole on this one!
 
B

BruceM

Responses inline.

131313 via AccessMonster.com said:
Bruce, this is a new post from the question you responded to yesterday.


Firstname, lastName, associate ID, start step, program start date,program,
etc., is all in tbl associates

As I explained in an earlier posting, a table should contain information
about a single entity such as Employee. Training information may be related
to the employee, but it is not an attribute of the employee. StartStep,
ProgramStart, Program, and other such fields belong in another table (or two
or three).
tbl data entry is the table that holds all associate ID,course
completions,
dates .
tbl certificates holds all certificate names, certificate codes, and step
qry Certificates Earned takes the above 3 tables and determines if all
training codes for each certificate (or step) is complete, if so it shows
the
associate Id, first name, last name, certificates earned. All informaytion
I
need is there. All tables are linked by associate ID or Training code
I don't know where TrainingCode is located or what it does, nor do I see the
relationship between the information in tblCertificate and the other
table(s). It may be there, but it is not clear from your description.

These are some questions from my earlier post:
" Does every associate participate in the exact
same program? I realize that some people can start at a higher step than
others, but is it the case that everybody who takes Step 1 takes the same
Step1, and that everybody who takes Step 8 takes the same Step 8? Is there
only one program for every single person in the company? Does each person
take only that one program, no matter how long they work there? Is it also
the case that there will always be the exact same 8 steps, no more and no
less, and that they will never change?

The answers you provide have a bearing on what I can suggest. You have said
you have everything you need, but it does not appear to be effectively
organized. ProgramStart (presumably a date) is in one table (where it
doesn't belong), but I don't know where ProgramEnd is located, or if there
is one. I assume that the Dates you mention in tblDataEntry are all of the
dates except for ProgramStart (CertificateStart, CertificateEnd, etc.). If
the connection between tblDataEntry and tblCertificates is TrainingID, you
will need to explain how that works.
The program is for all maintenance associates that have the title TDP
(program they are in), it's the same amount of steps, certificates, etc.
total of 8 certificates, steps. all of which are the same for everyone.
The problem I am having is how to set the whole thing up.
How to show that if the associate has placed out of the first 1-4 steps
then
show "Tested Out", and how to start the step date at the step following
the
last step that was tested out, how to multiply the date for each
additional
step. How to maybe have the field certain colors for "Tested Out", Current
step the associate is on, and completed steps. This is where I am having
difficulty.

This is where you would use a CertificateEmployee table, with each record
associated with a single employee, and with information about a single
certificate. Each employee may have many certificates, and each type of
certificate may be associated with many employees, so a junction table is
needed to resolve the many-to-many relationship. It may be possible to
somehow cajole the information out of the tables you have, but I doubt it
could be effective or efficient. If it is possible, it will require a lot
more detail than you have provided. If you were open to the idea of using a
properly normalized structure it would much easier to suggest a course.
 
1

131313 via AccessMonster.com

Bruce, here is what I have.

tbl Associates tbl data entry
tbl certificates tbl labs
last name Associate ID
certificate code training code
first name Date of Training
certificate lab name
associate ID Job Type
step certificate code
date(todays) Training Code
certificate name
Plant pass/fail
step
Dept hours
Coordinator ID minutes
Coordinator Name
Associate Contact #
Job Type
From Pool
Outside Hire
Start Step
Program Start Date
Seniority Date

As for the program, it is the same (step 1 is step 1 for all) and so on. This
program is only for associates that have the job type "TDP" (tbl associates,
Job Type). The program end date for each associate depends on where they
start the program associate 1 can start at step 4, associate 2 can start at
step 1, which determines the amount of time being that every step requires 6
months of training. program start date is the date they became maintenance
which is used as the training start date, tbl associate "Program Start Date" .
That date hase 6 months added to it for every step, total of 8 steps in the
program. Training code is the code given to each lab "TEC1500", "TEC2110".
There are numerous training codes. There are eight certificates, each
certificate is also a step in the program.
Each certificate requires various labs (Fundamentals certificate/step 1
requires TEC1500, TEC4110, TEC2110, TEC2111) this is all sorted out in the
certificates earned qry.

I'm sorry this is so confusing, you have been the only one willing to help me
out on this. Please let me know if you need more info.
Responses inline.
Bruce, this is a new post from the question you responded to yesterday.

Firstname, lastName, associate ID, start step, program start date,program,
etc., is all in tbl associates

As I explained in an earlier posting, a table should contain information
about a single entity such as Employee. Training information may be related
to the employee, but it is not an attribute of the employee. StartStep,
ProgramStart, Program, and other such fields belong in another table (or two
or three).
tbl data entry is the table that holds all associate ID,course
completions,
[quoted text clipped - 6 lines]
I
need is there. All tables are linked by associate ID or Training code

I don't know where TrainingCode is located or what it does, nor do I see the
relationship between the information in tblCertificate and the other
table(s). It may be there, but it is not clear from your description.

These are some questions from my earlier post:
" Does every associate participate in the exact
same program? I realize that some people can start at a higher step than
others, but is it the case that everybody who takes Step 1 takes the same
Step1, and that everybody who takes Step 8 takes the same Step 8? Is there
only one program for every single person in the company? Does each person
take only that one program, no matter how long they work there? Is it also
the case that there will always be the exact same 8 steps, no more and no
less, and that they will never change?

The answers you provide have a bearing on what I can suggest. You have said
you have everything you need, but it does not appear to be effectively
organized. ProgramStart (presumably a date) is in one table (where it
doesn't belong), but I don't know where ProgramEnd is located, or if there
is one. I assume that the Dates you mention in tblDataEntry are all of the
dates except for ProgramStart (CertificateStart, CertificateEnd, etc.). If
the connection between tblDataEntry and tblCertificates is TrainingID, you
will need to explain how that works.
The program is for all maintenance associates that have the title TDP
(program they are in), it's the same amount of steps, certificates, etc.
[quoted text clipped - 9 lines]
step the associate is on, and completed steps. This is where I am having
difficulty.

This is where you would use a CertificateEmployee table, with each record
associated with a single employee, and with information about a single
certificate. Each employee may have many certificates, and each type of
certificate may be associated with many employees, so a junction table is
needed to resolve the many-to-many relationship. It may be possible to
somehow cajole the information out of the tables you have, but I doubt it
could be effective or efficient. If it is possible, it will require a lot
more detail than you have provided. If you were open to the idea of using a
properly normalized structure it would much easier to suggest a course.
I will be happy to provide you with more info if needed, I am just backed
in
a hole on this one!
 
B

BruceM

I can't figure out what goes with what. Perhaps this is because of
wrapping. Please use something like this:

tblAssociates
AssociateID (primary key)
FirstName
other relevant fields

tblDataEntry
DataEntryID (primary key)
AssociateID (foreign key)
relevant fields

tblCertificate
?

I can see from this that tblAssociates and tblDataEntry are related
one-to-many, and I can tell which field go with which tables. Without
something of the sort there is no way I can figure out what is going on.

131313 via AccessMonster.com said:
Bruce, here is what I have.

tbl Associates tbl data entry
tbl certificates tbl labs
last name Associate ID
certificate code training code
first name Date of Training
certificate lab name
associate ID Job Type
step certificate code
date(todays) Training Code
certificate name
Plant pass/fail
step
Dept hours
Coordinator ID minutes
Coordinator Name
Associate Contact #
Job Type
From Pool
Outside Hire
Start Step
Program Start Date
Seniority Date

As for the program, it is the same (step 1 is step 1 for all) and so on.
This
program is only for associates that have the job type "TDP" (tbl
associates,
Job Type). The program end date for each associate depends on where they
start the program associate 1 can start at step 4, associate 2 can start
at
step 1, which determines the amount of time being that every step requires
6
months of training. program start date is the date they became maintenance
which is used as the training start date, tbl associate "Program Start
Date" .
That date hase 6 months added to it for every step, total of 8 steps in
the
program. Training code is the code given to each lab "TEC1500", "TEC2110".
There are numerous training codes. There are eight certificates, each
certificate is also a step in the program.
Each certificate requires various labs (Fundamentals certificate/step 1
requires TEC1500, TEC4110, TEC2110, TEC2111) this is all sorted out in the
certificates earned qry.

I'm sorry this is so confusing, you have been the only one willing to help
me
out on this. Please let me know if you need more info.
Responses inline.
Bruce, this is a new post from the question you responded to yesterday.

Firstname, lastName, associate ID, start step, program start
date,program,
etc., is all in tbl associates

As I explained in an earlier posting, a table should contain information
about a single entity such as Employee. Training information may be
related
to the employee, but it is not an attribute of the employee. StartStep,
ProgramStart, Program, and other such fields belong in another table (or
two
or three).
tbl data entry is the table that holds all associate ID,course
completions,
[quoted text clipped - 6 lines]
I
need is there. All tables are linked by associate ID or Training code

I don't know where TrainingCode is located or what it does, nor do I see
the
relationship between the information in tblCertificate and the other
table(s). It may be there, but it is not clear from your description.

These are some questions from my earlier post:
" Does every associate participate in the exact
same program? I realize that some people can start at a higher step than
others, but is it the case that everybody who takes Step 1 takes the same
Step1, and that everybody who takes Step 8 takes the same Step 8? Is
there
only one program for every single person in the company? Does each person
take only that one program, no matter how long they work there? Is it
also
the case that there will always be the exact same 8 steps, no more and no
less, and that they will never change?

The answers you provide have a bearing on what I can suggest. You have
said
you have everything you need, but it does not appear to be effectively
organized. ProgramStart (presumably a date) is in one table (where it
doesn't belong), but I don't know where ProgramEnd is located, or if there
is one. I assume that the Dates you mention in tblDataEntry are all of
the
dates except for ProgramStart (CertificateStart, CertificateEnd, etc.).
If
the connection between tblDataEntry and tblCertificates is TrainingID, you
will need to explain how that works.
The program is for all maintenance associates that have the title TDP
(program they are in), it's the same amount of steps, certificates, etc.
[quoted text clipped - 9 lines]
step the associate is on, and completed steps. This is where I am having
difficulty.

This is where you would use a CertificateEmployee table, with each record
associated with a single employee, and with information about a single
certificate. Each employee may have many certificates, and each type of
certificate may be associated with many employees, so a junction table is
needed to resolve the many-to-many relationship. It may be possible to
somehow cajole the information out of the tables you have, but I doubt it
could be effective or efficient. If it is possible, it will require a lot
more detail than you have provided. If you were open to the idea of using
a
properly normalized structure it would much easier to suggest a course.
I will be happy to provide you with more info if needed, I am just
backed
in
a hole on this one!
 
1

131313 via AccessMonster.com

Bruce,
hope this helps!

tbl associate
associate id (primary key)
first name
last name
start step
program start date

tbl certificates
certificate code (primary key)
certificate
step

tbl data entry
associate id (primary key)
date of training
job type
training code (primary key)

tbl labs
training code (primary key)
lab name
certificate code
certificate name
step
I can't figure out what goes with what. Perhaps this is because of
wrapping. Please use something like this:

tblAssociates
AssociateID (primary key)
FirstName
other relevant fields

tblDataEntry
DataEntryID (primary key)
AssociateID (foreign key)
relevant fields

tblCertificate
?

I can see from this that tblAssociates and tblDataEntry are related
one-to-many, and I can tell which field go with which tables. Without
something of the sort there is no way I can figure out what is going on.
Bruce, here is what I have.
[quoted text clipped - 118 lines]
 
B

BruceM

It's much easier to follow. The other thing that would help would be
answers to the questions I have posted several times, most recently in the
posting before my previous one. More comments inline.

131313 via AccessMonster.com said:
Bruce,
hope this helps!

tbl associate
associate id (primary key)
first name
last name
start step
program start date

StartStep and ProgramStartDate are not attributes of an employee, as I have
mentioned. You will create a lot of complications if you store them here.
This table should contain information about all employees, by the way. You
should not have separate tables for different departments, etc.
tbl certificates
certificate code (primary key)
certificate
step

Is each type of certificate earned by several employees? If so, there is a
many-to-many relationship between employees and certificates, as described
in my previous posting.
tbl data entry
associate id (primary key)
date of training
job type
training code (primary key)

AssociateID should be a foreign key. It should be related to AssociateID in
tblAssociate. A table can have a multi-field PK, but it can't have two
separate PKs. Remember that a foreign key is not established in table
design view as is a PK, but rather it becomes a FK when it is related to a
PK field.
tbl labs
training code (primary key)
lab name
certificate code
certificate name
step

Remember that I cannot see your project, and am not familiar with your work
procedures. How do labs fit into the picture? Is TrainingCode intended to
relate to TrainingCode in tblDataEntry? If so, they can't both be PKs.

I can't figure out what goes with what. Perhaps this is because of
wrapping. Please use something like this:

tblAssociates
AssociateID (primary key)
FirstName
other relevant fields

tblDataEntry
DataEntryID (primary key)
AssociateID (foreign key)
relevant fields

tblCertificate
?

I can see from this that tblAssociates and tblDataEntry are related
one-to-many, and I can tell which field go with which tables. Without
something of the sort there is no way I can figure out what is going on.
Bruce, here is what I have.
[quoted text clipped - 118 lines]
in
a hole on this one!
 
1

131313 via AccessMonster.com

Bruce,

taking your advice I have moved the startstep and program start date to it's
own table, but I still need to add the associate id, correct?

All dept's are in the same table tbl assocaite, no seperate tables.

Every type of certificate is earned by several associates, how would I set up
the many to many?

Can you explain the foreign key and how to set that up?

Training Code is to relate to training code in tbl data entry.

I understand this must be hard for you see what's going on, I can explain
more to you, or send you a copy of the DB if you would like?

Thanks again
It's much easier to follow. The other thing that would help would be
answers to the questions I have posted several times, most recently in the
posting before my previous one. More comments inline.
Bruce,
hope this helps!
[quoted text clipped - 5 lines]
start step
program start date

StartStep and ProgramStartDate are not attributes of an employee, as I have
mentioned. You will create a lot of complications if you store them here.
This table should contain information about all employees, by the way. You
should not have separate tables for different departments, etc.
tbl certificates
certificate code (primary key)
certificate
step

Is each type of certificate earned by several employees? If so, there is a
many-to-many relationship between employees and certificates, as described
in my previous posting.
tbl data entry
associate id (primary key)
date of training
job type
training code (primary key)

AssociateID should be a foreign key. It should be related to AssociateID in
tblAssociate. A table can have a multi-field PK, but it can't have two
separate PKs. Remember that a foreign key is not established in table
design view as is a PK, but rather it becomes a FK when it is related to a
PK field.
tbl labs
training code (primary key)
lab name
certificate code
certificate name
step

Remember that I cannot see your project, and am not familiar with your work
procedures. How do labs fit into the picture? Is TrainingCode intended to
relate to TrainingCode in tblDataEntry? If so, they can't both be PKs.
I can't figure out what goes with what. Perhaps this is because of
wrapping. Please use something like this: [quoted text clipped - 21 lines]
in
a hole on this one!
 
B

BruceM

Just to let you know, I'm out for the rest of the day, so I won't be able to
add much now, except to say that this information fills in a number of gaps.

131313 via AccessMonster.com said:
Bruce,

taking your advice I have moved the startstep and program start date to
it's
own table, but I still need to add the associate id, correct?

All dept's are in the same table tbl assocaite, no seperate tables.

Every type of certificate is earned by several associates, how would I set
up
the many to many?

Can you explain the foreign key and how to set that up?

Training Code is to relate to training code in tbl data entry.

I understand this must be hard for you see what's going on, I can explain
more to you, or send you a copy of the DB if you would like?

Thanks again
It's much easier to follow. The other thing that would help would be
answers to the questions I have posted several times, most recently in the
posting before my previous one. More comments inline.
Bruce,
hope this helps!
[quoted text clipped - 5 lines]
start step
program start date

StartStep and ProgramStartDate are not attributes of an employee, as I
have
mentioned. You will create a lot of complications if you store them here.
This table should contain information about all employees, by the way.
You
should not have separate tables for different departments, etc.
tbl certificates
certificate code (primary key)
certificate
step

Is each type of certificate earned by several employees? If so, there is
a
many-to-many relationship between employees and certificates, as described
in my previous posting.
tbl data entry
associate id (primary key)
date of training
job type
training code (primary key)

AssociateID should be a foreign key. It should be related to AssociateID
in
tblAssociate. A table can have a multi-field PK, but it can't have two
separate PKs. Remember that a foreign key is not established in table
design view as is a PK, but rather it becomes a FK when it is related to a
PK field.
tbl labs
training code (primary key)
lab name
certificate code
certificate name
step

Remember that I cannot see your project, and am not familiar with your
work
procedures. How do labs fit into the picture? Is TrainingCode intended
to
relate to TrainingCode in tblDataEntry? If so, they can't both be PKs.
I can't figure out what goes with what. Perhaps this is because of
wrapping. Please use something like this:
[quoted text clipped - 21 lines]
in
a hole on this one!
 
B

BruceM

131313 via AccessMonster.com said:
Bruce,

taking your advice I have moved the startstep and program start date to
it's
own table, but I still need to add the associate id, correct?
I was trying to say that Startstep and ProgramStart are attributes of
something (program? certificate? training?). Whatever it is, those fields
belong in that table.
All dept's are in the same table tbl assocaite, no seperate tables.

Do you mean that each associate is in one department, and that there is a
Department in an associate's record? That will work, provided there is only
one department at a time, and you don't want to save the history if an
associate goes to a different department.
Every type of certificate is earned by several associates, how would I set
up
the many to many?

tblAssociate
AssociateID (PK)
FirstName
LastName
Dept
etc.

tblCertificate
CertID (PK)
CertDescription
Step

tblAssocCert (junction table)
AssocCertID (autonumber PK)
AssociateID (foreign key, or FK)
CertID (FK)
TrainingStartDate
TrainingEndDate
Skipped (Yes/No)

Click View > Relationships. Add all three tables. Drag AssociateID from
tblAssociate to tblAssocCert. Click Enforce Referential Integrity. Do the
same with CertID.
Make a form based on tblAssociate, and a (continuous) subform based on
tblAssocCert. On the subform, add a combo box bound to CertID. Use tblCert
as the basis for the combo box row source. Use just the CertID field from
tblCert as the row source for now. If this approach accomplishes what you
need we can add the other fields from tblCert if you want to see them. Also
on the subform, add the TrainingStartDate, etc. fields that are specifically
related to an associate's certificate training (there may be more than I
have suggested). You can click the Skipped check box if the employee has
tested out of a step.

I still don't know if there is just the one training program for all
employees. My approach assumes just the one program for everybody, since
you haven't provided information to the contrary.
Can you explain the foreign key and how to set that up?

I hope the explanation above shed some light on that.
Training Code is to relate to training code in tbl data entry.

It would be helpful if you used more meaningful names than tblDataEntry when
posting a question. Also, when replying to a question, it would help if you
include the question, or some sort of context. I don't have time to go back
and sort out how TrainingCode fit into your explanation, but I don't see the
point of the relationship.
I understand this must be hard for you see what's going on, I can explain
more to you, or send you a copy of the DB if you would like?


Thanks again
It's much easier to follow. The other thing that would help would be
answers to the questions I have posted several times, most recently in the
posting before my previous one. More comments inline.
Bruce,
hope this helps!
[quoted text clipped - 5 lines]
start step
program start date

StartStep and ProgramStartDate are not attributes of an employee, as I
have
mentioned. You will create a lot of complications if you store them here.
This table should contain information about all employees, by the way.
You
should not have separate tables for different departments, etc.
tbl certificates
certificate code (primary key)
certificate
step

Is each type of certificate earned by several employees? If so, there is
a
many-to-many relationship between employees and certificates, as described
in my previous posting.
tbl data entry
associate id (primary key)
date of training
job type
training code (primary key)

AssociateID should be a foreign key. It should be related to AssociateID
in
tblAssociate. A table can have a multi-field PK, but it can't have two
separate PKs. Remember that a foreign key is not established in table
design view as is a PK, but rather it becomes a FK when it is related to a
PK field.
tbl labs
training code (primary key)
lab name
certificate code
certificate name
step

Remember that I cannot see your project, and am not familiar with your
work
procedures. How do labs fit into the picture? Is TrainingCode intended
to
relate to TrainingCode in tblDataEntry? If so, they can't both be PKs.
I can't figure out what goes with what. Perhaps this is because of
wrapping. Please use something like this:
[quoted text clipped - 21 lines]
in
a hole on this one!
 
1

131313 via AccessMonster.com

Bruce,
and sort out how TrainingCode fit into your explanation, but I don't see the
point of the relationship.
The training code is what gets entered into the database on a weekly basis,
"tbl Data Entry" this is how I track each associates completed labs. All
labs have training codes, all certificates need groups of completed labs to
be complete. That is how the training codes are involved in the DB.
Do you mean that each associate is in one department, and that there is a
Department in an associate's record? That will work, provided there is only
one department at a time, and you don't want to save the history if an
associate goes to a different department.

No, within the database there are numerous types of associates entered,
production, maintenance, engineer, supplier, etc. The associates table list
the contact info, and dept info for general purposes only. Associates in the
maintenance program have a job type of "TDP", they can be from all over the
three plants, departments, but they have the program job type of "TDP", this
is what we will go by, all associates with job type "TDP".

Hope this clears some issues up. Sorry about my posting abilities, kinda new
to this. You have been a big help so far, don't give up on me now. Thanks
greg
Bruce,

taking your advice I have moved the startstep and program start date to
it's
own table, but I still need to add the associate id, correct?
I was trying to say that Startstep and ProgramStart are attributes of
something (program? certificate? training?). Whatever it is, those fields
belong in that table.
All dept's are in the same table tbl assocaite, no seperate tables.

Do you mean that each associate is in one department, and that there is a
Department in an associate's record? That will work, provided there is only
one department at a time, and you don't want to save the history if an
associate goes to a different department.
Every type of certificate is earned by several associates, how would I set
up
the many to many?

tblAssociate
AssociateID (PK)
FirstName
LastName
Dept
etc.

tblCertificate
CertID (PK)
CertDescription
Step

tblAssocCert (junction table)
AssocCertID (autonumber PK)
AssociateID (foreign key, or FK)
CertID (FK)
TrainingStartDate
TrainingEndDate
Skipped (Yes/No)

Click View > Relationships. Add all three tables. Drag AssociateID from
tblAssociate to tblAssocCert. Click Enforce Referential Integrity. Do the
same with CertID.
Make a form based on tblAssociate, and a (continuous) subform based on
tblAssocCert. On the subform, add a combo box bound to CertID. Use tblCert
as the basis for the combo box row source. Use just the CertID field from
tblCert as the row source for now. If this approach accomplishes what you
need we can add the other fields from tblCert if you want to see them. Also
on the subform, add the TrainingStartDate, etc. fields that are specifically
related to an associate's certificate training (there may be more than I
have suggested). You can click the Skipped check box if the employee has
tested out of a step.

Can you explain the foreign key and how to set that up?

I hope the explanation above shed some light on that.
Training Code is to relate to training code in tbl data entry.

It would be helpful if you used more meaningful names than tblDataEntry when
posting a question. Also, when replying to a question, it would help if you
include the question, or some sort of context. I don't have time to go back
and sort out how TrainingCode fit into your explanation, but I don't see the
point of the relationship.
I understand this must be hard for you see what's going on, I can explain
more to you, or send you a copy of the DB if you would like?
Thanks again
[quoted text clipped - 56 lines]
 
B

BruceM

See inline.

131313 via AccessMonster.com said:
Bruce,
The training code is what gets entered into the database on a weekly
basis,
"tbl Data Entry" this is how I track each associates completed labs. All
labs have training codes, all certificates need groups of completed labs
to
be complete. That is how the training codes are involved in the DB.

Explain the real-world relationship between Program, Certificate, and Lab.
From what I can tell, a program is completed when a certain number of
certificates is earned, and earning a certificate depends upon completion of
several labs.
One program consists of several certificates, and each certificate consists
of several labs. Is that correct?
No, within the database there are numerous types of associates entered,
production, maintenance, engineer, supplier, etc. The associates table
list
the contact info, and dept info for general purposes only. Associates in
the
maintenance program have a job type of "TDP", they can be from all over
the
three plants, departments, but they have the program job type of "TDP",
this
is what we will go by, all associates with job type "TDP".

OK, so JobType is relevant to programs and certificates, but Department is
not. Is that correct? What do you mean "in the maintenance program"? I
thought a program consists of earning certificates. Once all of the
certificates are earned, what becomes of the program?
Hope this clears some issues up. Sorry about my posting abilities, kinda
new
to this. You have been a big help so far, don't give up on me now. Thanks
greg
Bruce,

taking your advice I have moved the startstep and program start date to
it's
own table, but I still need to add the associate id, correct?
I was trying to say that Startstep and ProgramStart are attributes of
something (program? certificate? training?). Whatever it is, those fields
belong in that table.
All dept's are in the same table tbl assocaite, no seperate tables.

Do you mean that each associate is in one department, and that there is a
Department in an associate's record? That will work, provided there is
only
one department at a time, and you don't want to save the history if an
associate goes to a different department.
Every type of certificate is earned by several associates, how would I
set
up
the many to many?

tblAssociate
AssociateID (PK)
FirstName
LastName
Dept
etc.

tblCertificate
CertID (PK)
CertDescription
Step

tblAssocCert (junction table)
AssocCertID (autonumber PK)
AssociateID (foreign key, or FK)
CertID (FK)
TrainingStartDate
TrainingEndDate
Skipped (Yes/No)

Click View > Relationships. Add all three tables. Drag AssociateID from
tblAssociate to tblAssocCert. Click Enforce Referential Integrity. Do
the
same with CertID.
Make a form based on tblAssociate, and a (continuous) subform based on
tblAssocCert. On the subform, add a combo box bound to CertID. Use
tblCert
as the basis for the combo box row source. Use just the CertID field from
tblCert as the row source for now. If this approach accomplishes what you
need we can add the other fields from tblCert if you want to see them.
Also
on the subform, add the TrainingStartDate, etc. fields that are
specifically
related to an associate's certificate training (there may be more than I
have suggested). You can click the Skipped check box if the employee has
tested out of a step.

Can you explain the foreign key and how to set that up?

I hope the explanation above shed some light on that.
Training Code is to relate to training code in tbl data entry.

It would be helpful if you used more meaningful names than tblDataEntry
when
posting a question. Also, when replying to a question, it would help if
you
include the question, or some sort of context. I don't have time to go
back
and sort out how TrainingCode fit into your explanation, but I don't see
the
point of the relationship.
I understand this must be hard for you see what's going on, I can
explain
more to you, or send you a copy of the DB if you would like?
Thanks again
[quoted text clipped - 56 lines]
in
a hole on this one!
 
1

131313 via AccessMonster.com

Bruce,
Explain the real-world relationship between Program, Certificate, and Lab.
From what I can tell, a program is completed when a certain number of
certificates is earned, and earning a certificate depends upon completion of
several labs.
One program consists of several certificates, and each certificate consists
of several labs. Is that correct?

This is correct.

OK, so JobType is relevant to programs and certificates, but Department is
not. Is that correct? What do you mean "in the maintenance program"? I
thought a program consists of earning certificates. Once all of the
certificates are earned, what becomes of the program?

The TDP (Technician Development Program) is designed for all new maintenance
associates in the company, either from outside hire or our maintenance pool
(production associates that have completed the fundamentals certificate on
their own time. The program has 8 steps, same for everyone. One week of lab
training plus 6 months of on the job training per step. After 8th step is
complete, then the associate has completed the program. We offer all kinds of
training for everyone, the TDP is only for the maintenance associates. There
are always new associates entering the program.

thanks
See inline.
[quoted text clipped - 6 lines]
to
be complete. That is how the training codes are involved in the DB.

Explain the real-world relationship between Program, Certificate, and Lab.
From what I can tell, a program is completed when a certain number of
certificates is earned, and earning a certificate depends upon completion of
several labs.
One program consists of several certificates, and each certificate consists
of several labs. Is that correct?
[quoted text clipped - 12 lines]
this
is what we will go by, all associates with job type "TDP".

OK, so JobType is relevant to programs and certificates, but Department is
not. Is that correct? What do you mean "in the maintenance program"? I
thought a program consists of earning certificates. Once all of the
certificates are earned, what becomes of the program?
Hope this clears some issues up. Sorry about my posting abilities, kinda
new
[quoted text clipped - 85 lines]
 
B

BruceM

131313 via AccessMonster.com said:
Bruce,
Explain the real-world relationship between Program, Certificate, and Lab.

This is correct.

OK, so JobType is relevant to programs and certificates, but Department is

The TDP (Technician Development Program) is designed for all new
maintenance
associates in the company, either from outside hire or our maintenance
pool
(production associates that have completed the fundamentals certificate on
their own time. The program has 8 steps, same for everyone. One week of
lab
training plus 6 months of on the job training per step. After 8th step is
complete, then the associate has completed the program. We offer all kinds
of
training for everyone, the TDP is only for the maintenance associates.
There
are always new associates entering the program.

Can the TDP be thought of as a sort of degree program, where the
certificates are rewarded upon the successful completion of a semester
consisting of OJT and lab?
Are there programs other than TDP? If so, do any of the TDP associates
participate in those programs? This goes back to a question I have asked
several times: *Do associates ever take part in more than one degree
program?*
Does an associate's TDP designation ever change? Is tblAssociates for all
employees, or just a few in certain departments?
thanks
See inline.
Bruce,
and sort out how TrainingCode fit into your explanation, but I don't see
[quoted text clipped - 6 lines]
to
be complete. That is how the training codes are involved in the DB.

Explain the real-world relationship between Program, Certificate, and Lab.
From what I can tell, a program is completed when a certain number of
certificates is earned, and earning a certificate depends upon completion
of
several labs.
One program consists of several certificates, and each certificate
consists
of several labs. Is that correct?
Do you mean that each associate is in one department, and that there is
a
Department in an associate's record? That will work, provided there is
[quoted text clipped - 12 lines]
this
is what we will go by, all associates with job type "TDP".

OK, so JobType is relevant to programs and certificates, but Department is
not. Is that correct? What do you mean "in the maintenance program"? I
thought a program consists of earning certificates. Once all of the
certificates are earned, what becomes of the program?
Hope this clears some issues up. Sorry about my posting abilities, kinda
new
[quoted text clipped - 85 lines]
in
a hole on this one!
 
1

131313 via AccessMonster.com

Bruce,
Sorry it took so long getting back to you.
Can the TDP be thought of as a sort of degree program, where the
certificates are rewarded upon the successful completion of a semester
consisting of OJT and lab?

In a way, yes. The maitenence associates are new, they spend the first 2 or
so years in the program until complete. Every step they earn is a pay
increase.

Are there programs other than TDP? If so, do any of the TDP associates
participate in those programs? This goes back to a question I have asked
several times: *Do associates ever take part in more than one degree
program?*

As of right now, there is optional training that the associate can take, or
the dept. he/she is in requires. This does not affect the program in any way.
The TDP program is the same for every maintenance associate, every associate
must complete all training on the TDP program outline.

Does an associate's TDP designation ever change? Is tblAssociates for all
employees, or just a few in certain departments?

No
tbl associate contains every associate that has taken training in the lab per
Ki. April to March the following year. All records are backed up and then the
DB is used for the next Ki. We are currently working on a new version to
stream-line the process and automate as much as possible.

Thanks
Bruce,
Explain the real-world relationship between Program, Certificate, and Lab.
[quoted text clipped - 26 lines]
There
are always new associates entering the program.

Can the TDP be thought of as a sort of degree program, where the
certificates are rewarded upon the successful completion of a semester
consisting of OJT and lab?
Are there programs other than TDP? If so, do any of the TDP associates
participate in those programs? This goes back to a question I have asked
several times: *Do associates ever take part in more than one degree
program?*
Does an associate's TDP designation ever change? Is tblAssociates for all
employees, or just a few in certain departments?
[quoted text clipped - 32 lines]
 
B

BruceM

131313 via AccessMonster.com said:
Bruce,
Sorry it took so long getting back to you.
Can the TDP be thought of as a sort of degree program, where the

In a way, yes. The maitenence associates are new, they spend the first 2
or
so years in the program until complete. Every step they earn is a pay
increase.

You have said before that each step takes about six months, and that there
are eight steps (as I recall) in the TDP. What is this, then, about new
associates spending two or so years completing the program. You can't mean
the TDP, which would take at least four years.
Are there programs other than TDP? If so, do any of the TDP associates

As of right now, there is optional training that the associate can take,
or
the dept. he/she is in requires. This does not affect the program in any
way.
The TDP program is the same for every maintenance associate, every
associate
must complete all training on the TDP program outline.

I will take that as "No".
Does an associate's TDP designation ever change? Is tblAssociates for all

No
tbl associate contains every associate that has taken training in the lab
per
Ki. April to March the following year. All records are backed up and then
the
DB is used for the next Ki. We are currently working on a new version to
stream-line the process and automate as much as possible.

Is every employee in the company in tblAssociates? If not, do you intend to
build a new table for every database that involves associates?

It sounds as if you are planning to make a new tblAssociates every year.
This is a spreadsheet way of thinking. There's nothing wrong with
spreadsheets, but they should be used with their own program. tblAssociates
should be a listing of all associates. The best thing would be to have a
single Employee table, and to use queries to limit the records to, say, all
Maintenance personnel. That query can be further filtered to show only
records for associates who have had training in the last year. Specific
training information is stored, in my suggested model, in tblAssocCert. In
effect, you would be saying something like "Select all associates in the
Maintenance program who have a record in tblAssocCert with a
TrainingStartDate in the past twelve months".

My first posting on April 5 contained a suggested table structure, and a way
to implement it through the use of forms. Try the structure I suggested,
using just a few test records. See how to put together the related tables,
and begin to gain an understanding of how a relational database works. Even
if you are convinced that it is not going to work in your situation, it will
let you see how the pieces fit together.

Another thing (the first thing, really) that you need to do is map out the
real-world situation: Each employee may participate in several programs,
and each program involves several employees. Each program consists of
several steps. And so forth. Only you know the situation.

Bruce,
Explain the real-world relationship between Program, Certificate, and
Lab.
[quoted text clipped - 26 lines]
There
are always new associates entering the program.

Can the TDP be thought of as a sort of degree program, where the
certificates are rewarded upon the successful completion of a semester
consisting of OJT and lab?
Are there programs other than TDP? If so, do any of the TDP associates
participate in those programs? This goes back to a question I have asked
several times: *Do associates ever take part in more than one degree
program?*
Does an associate's TDP designation ever change? Is tblAssociates for all
employees, or just a few in certain departments?
[quoted text clipped - 32 lines]
in
a hole on this one!
 
1

131313 via AccessMonster.com

Bruce,
To answer your questions.
You have said before that each step takes about six months, and that there
are eight steps (as I recall) in the TDP. What is this, then, about new
associates spending two or so years completing the program. You can't mean
the TDP, which would take at least four years.

Your correct, I typed in 2 when it is around 4, depending on where they step.

Is every employee in the company in tblAssociates? If not, do you intend to
build a new table for every database that involves associates?
No, only maintenance associates, those who want to be in maintenance, and
associates who made need targeted training for a project.

It sounds as if you are planning to make a new tblAssociates every year.

We try to keep records of every Ki, this is why we start each Ki with a fresh
database, carrying over associates that are still in training.

tblAssociates
should be a listing of all associates. The best thing would be to have a
single Employee table, and to use queries to limit the records to, say, all
Maintenance personnel. That query can be further filtered to show only
records for associates who have had training in the last year. Specific
training information is stored, in my suggested model, in tblAssocCert. In
effect, you would be saying something like "Select all associates in the
Maintenance program who have a record in tblAssocCert with a
TrainingStartDate in the past twelve months".

tbl associates is a table with every single associate that has dcome into the
lab for training. There are around 30,000 employees here, we only see around
2,000. If that. tbl associate has everyone from production to maintenance
that comes in the lab for training, I have already created queries that sort
ot all that are in the TDP

I think I have everything mapped out to what I need (don't think it is coming
out write when explained).
Another thing (the first thing, really) that you need to do is map out the
real-world situation: Each employee may participate in several programs,
and each program involves several employees. Each program consists of
several steps. And so forth. Only you know the situation.
We track all training in the lab, there is only one program "TDP", this is
the same training as everyone else can take if they wanted, but is required
by the maintenance associates to take. They have to go through the program.
production associates have to complete the first certificate to get in the
maintenance pool, if they are selected into maintenance, then they go through
the TDP Program. Production associates can take any training we offer on
their own time. Engineers can take any training as well.

I understand how it needs to work and what I want it to do, I just don't know
how to set it up.
thanks
Bruce,
Sorry it took so long getting back to you.
[quoted text clipped - 6 lines]
so years in the program until complete. Every step they earn is a pay
increase.

You have said before that each step takes about six months, and that there
are eight steps (as I recall) in the TDP. What is this, then, about new
associates spending two or so years completing the program. You can't mean
the TDP, which would take at least four years.
Are there programs other than TDP? If so, do any of the TDP associates
[quoted text clipped - 8 lines]
associate
must complete all training on the TDP program outline.

I will take that as "No".
Does an associate's TDP designation ever change? Is tblAssociates for all
[quoted text clipped - 6 lines]
DB is used for the next Ki. We are currently working on a new version to
stream-line the process and automate as much as possible.

Is every employee in the company in tblAssociates? If not, do you intend to
build a new table for every database that involves associates?

It sounds as if you are planning to make a new tblAssociates every year.
This is a spreadsheet way of thinking. There's nothing wrong with
spreadsheets, but they should be used with their own program. tblAssociates
should be a listing of all associates. The best thing would be to have a
single Employee table, and to use queries to limit the records to, say, all
Maintenance personnel. That query can be further filtered to show only
records for associates who have had training in the last year. Specific
training information is stored, in my suggested model, in tblAssocCert. In
effect, you would be saying something like "Select all associates in the
Maintenance program who have a record in tblAssocCert with a
TrainingStartDate in the past twelve months".

My first posting on April 5 contained a suggested table structure, and a way
to implement it through the use of forms. Try the structure I suggested,
using just a few test records. See how to put together the related tables,
and begin to gain an understanding of how a relational database works. Even
if you are convinced that it is not going to work in your situation, it will
let you see how the pieces fit together.

Another thing (the first thing, really) that you need to do is map out the
real-world situation: Each employee may participate in several programs,
and each program involves several employees. Each program consists of
several steps. And so forth. Only you know the situation.
Bruce,
Explain the real-world relationship between Program, Certificate, and [quoted text clipped - 18 lines]
in
a hole on this one!
 
B

BruceM

131313 via AccessMonster.com said:
Bruce,
To answer your questions.
You have said before that each step takes about six months, and that there

Your correct, I typed in 2 when it is around 4, depending on where they
step.

Is every employee in the company in tblAssociates? If not, do you intend
to
No, only maintenance associates, those who want to be in maintenance, and
associates who made need targeted training for a project.

It sounds as if you are planning to make a new tblAssociates every year.

We try to keep records of every Ki, this is why we start each Ki with a
fresh
database, carrying over associates that are still in training.

Wrong. You do not make a new database. This is what I have been trying to
explain. By the way, if you defined "Ki" I don't recall what it means, but
it doesn't affect my point.
tblAssociates

tbl associates is a table with every single associate that has dcome into
the
lab for training. There are around 30,000 employees here, we only see
around
2,000. If that. tbl associate has everyone from production to maintenance
that comes in the lab for training, I have already created queries that
sort
ot all that are in the TDP

So you enter each associate's name when they come in? Is there no Employee
table available?
I think I have everything mapped out to what I need (don't think it is
coming
out write when explained).

I still don't know what you need. The reason I suggested you map it out is
so that you can understand the relationships between the various entities
(associates, programs, certificates, steps, etc.). These relationships
determine the structure of the database. I cannot build the database
through newsgroup messages. I can only try to point you toward the
principles involved in setting it up. The suggested table structure will
let you see how to set up the type of relationships you need. You need to
first get it to work on a simplified, small scale, then adapt that to your
actual situation.
Another thing (the first thing, really) that you need to do is map out the
We track all training in the lab, there is only one program "TDP", this is
the same training as everyone else can take if they wanted, but is
required
by the maintenance associates to take. They have to go through the
program.
production associates have to complete the first certificate to get in the
maintenance pool, if they are selected into maintenance, then they go
through
the TDP Program. Production associates can take any training we offer on
their own time. Engineers can take any training as well.

OK, each employee may take part in several programs, and each program (such
as TDP) involves many employees. That's what I'm getting out of this. See
my earlier suggestion about the table structure, except that there will be a
Program table instead of a Certificate table, and tblAssocCert (the junction
table) will be replaced by a similarly-structured tblAssocProgram. If you
get this set up you can incorporate certificates later. You need to get the
basic structure in place.
I understand how it needs to work and what I want it to do, I just don't
know
how to set it up.

I have explained a way to set it up, in the absence of information about
which I have inquired several times. Have you attempted the set-up I
suggested?
thanks
Bruce,
Sorry it took so long getting back to you.
[quoted text clipped - 6 lines]
so years in the program until complete. Every step they earn is a pay
increase.

You have said before that each step takes about six months, and that there
are eight steps (as I recall) in the TDP. What is this, then, about new
associates spending two or so years completing the program. You can't
mean
the TDP, which would take at least four years.
Are there programs other than TDP? If so, do any of the TDP associates
participate in those programs? This goes back to a question I have
asked
[quoted text clipped - 8 lines]
associate
must complete all training on the TDP program outline.

I will take that as "No".
Does an associate's TDP designation ever change? Is tblAssociates for
all
employees, or just a few in certain departments?
[quoted text clipped - 6 lines]
DB is used for the next Ki. We are currently working on a new version to
stream-line the process and automate as much as possible.

Is every employee in the company in tblAssociates? If not, do you intend
to
build a new table for every database that involves associates?

It sounds as if you are planning to make a new tblAssociates every year.
This is a spreadsheet way of thinking. There's nothing wrong with
spreadsheets, but they should be used with their own program.
tblAssociates
should be a listing of all associates. The best thing would be to have a
single Employee table, and to use queries to limit the records to, say,
all
Maintenance personnel. That query can be further filtered to show only
records for associates who have had training in the last year. Specific
training information is stored, in my suggested model, in tblAssocCert.
In
effect, you would be saying something like "Select all associates in the
Maintenance program who have a record in tblAssocCert with a
TrainingStartDate in the past twelve months".

My first posting on April 5 contained a suggested table structure, and a
way
to implement it through the use of forms. Try the structure I suggested,
using just a few test records. See how to put together the related
tables,
and begin to gain an understanding of how a relational database works.
Even
if you are convinced that it is not going to work in your situation, it
will
let you see how the pieces fit together.

Another thing (the first thing, really) that you need to do is map out the
real-world situation: Each employee may participate in several programs,
and each program involves several employees. Each program consists of
several steps. And so forth. Only you know the situation.
Bruce,
Explain the real-world relationship between Program, Certificate, and
[quoted text clipped - 18 lines]
in
a hole on this one!
 
1

131313 via AccessMonster.com

Bruce,

It's confusing, I know. I don't know how I can explain it. The database works
fine, everything that is being used, has been used for some time. I am re-
creating the DB to better automate our tracking.
I think I might have got off course in my explaination.
I still don't know what you need. The reason I suggested you map it out is
so that you can understand the relationships between the various entities
(associates, programs, certificates, steps, etc.).

I need help figuring out how to have the start step start at the first step
that was not stepped out, add 6 months to each step from that start step
until the last step (step 8). How to change the colors of the field if
possible? Tested Out "Blue", Current Step "Yellow", Step Complete "Green".
How to get it to see if the certificate for that step is complete. These are
the issues that I am having trouble with.

Sorry for any confusion. You gave me a better way of breaking down my
database and I can see how it all should work. I just can't figure out the
above problems. Thanks Again
Bruce,
To answer your questions.
[quoted text clipped - 18 lines]
fresh
database, carrying over associates that are still in training.

Wrong. You do not make a new database. This is what I have been trying to
explain. By the way, if you defined "Ki" I don't recall what it means, but
it doesn't affect my point.
tblAssociates
[quoted text clipped - 16 lines]
sort
ot all that are in the TDP

So you enter each associate's name when they come in? Is there no Employee
table available?
I think I have everything mapped out to what I need (don't think it is
coming
out write when explained).

I still don't know what you need. The reason I suggested you map it out is
so that you can understand the relationships between the various entities
(associates, programs, certificates, steps, etc.). These relationships
determine the structure of the database. I cannot build the database
through newsgroup messages. I can only try to point you toward the
principles involved in setting it up. The suggested table structure will
let you see how to set up the type of relationships you need. You need to
first get it to work on a simplified, small scale, then adapt that to your
actual situation.
Another thing (the first thing, really) that you need to do is map out the
[quoted text clipped - 11 lines]
the TDP Program. Production associates can take any training we offer on
their own time. Engineers can take any training as well.

OK, each employee may take part in several programs, and each program (such
as TDP) involves many employees. That's what I'm getting out of this. See
my earlier suggestion about the table structure, except that there will be a
Program table instead of a Certificate table, and tblAssocCert (the junction
table) will be replaced by a similarly-structured tblAssocProgram. If you
get this set up you can incorporate certificates later. You need to get the
basic structure in place.
I understand how it needs to work and what I want it to do, I just don't
know
how to set it up.

I have explained a way to set it up, in the absence of information about
which I have inquired several times. Have you attempted the set-up I
suggested?
[quoted text clipped - 66 lines]
 
B

BruceM

131313 via AccessMonster.com said:
Bruce,

It's confusing, I know. I don't know how I can explain it. The database
works
fine, everything that is being used, has been used for some time. I am re-
creating the DB to better automate our tracking.
I think I might have got off course in my explaination.


I need help figuring out how to have the start step start at the first
step
that was not stepped out, add 6 months to each step from that start step
until the last step (step 8). How to change the colors of the field if
possible? Tested Out "Blue", Current Step "Yellow", Step Complete "Green".
How to get it to see if the certificate for that step is complete. These
are
the issues that I am having trouble with.

To change the color of the *text box or other control* (not the field, which
is in the table), use conditional formatting. Help has more information
about that. Take a look, and post back if you still have questions.
Each step for each person should be a separate record in a Steps table. If
there are three people with eight steps each, there will be 24 records in
the Steps table. With a separate Steps table there could be a "SteppedOut"
(Yes/No) field, which could be marked if the person stepped out of that
step. Or maybe you could mark the StartDate and EndDate fields with the
same date, and use conditional formatting to turn the text boxes blue if
StartDate and EndDate are the same.
You could use the same general principles if you are using spreadsheet
design and putting all of the steps into a single record. It is not
advisable to do it that way, but it sounds as if you do not wish to change
the design of what you now have, so you can only improvise to the extent the
structure will allow.
To select one example, if you wish to select all associates who are
currently in the sixth step, you could query for records in which the sixth
step has a StartDate but no EndDate. If Steps are in a related table you
would do something that accomplishes the same thing by looking for all
Associate records in which the highest Step record is for Step 6, and in
which Step 6 shows a StartDate but no EndDate.
When you say "How to get it to see if the certificate for that step is
complete", do you mean a way to see visually (through color coding, for
instance) if the step is complete? If so, conditional formatting may do
much of what you seek.
Sorry for any confusion. You gave me a better way of breaking down my
database and I can see how it all should work. I just can't figure out the
above problems. Thanks Again
Bruce,
To answer your questions.
[quoted text clipped - 18 lines]
fresh
database, carrying over associates that are still in training.

Wrong. You do not make a new database. This is what I have been trying
to
explain. By the way, if you defined "Ki" I don't recall what it means,
but
it doesn't affect my point.
tblAssociates
should be a listing of all associates. The best thing would be to have
a
[quoted text clipped - 16 lines]
sort
ot all that are in the TDP

So you enter each associate's name when they come in? Is there no
Employee
table available?
I think I have everything mapped out to what I need (don't think it is
coming
out write when explained).

I still don't know what you need. The reason I suggested you map it out
is
so that you can understand the relationships between the various entities
(associates, programs, certificates, steps, etc.). These relationships
determine the structure of the database. I cannot build the database
through newsgroup messages. I can only try to point you toward the
principles involved in setting it up. The suggested table structure will
let you see how to set up the type of relationships you need. You need to
first get it to work on a simplified, small scale, then adapt that to your
actual situation.
Another thing (the first thing, really) that you need to do is map out
the
real-world situation: Each employee may participate in several
programs,
[quoted text clipped - 11 lines]
the TDP Program. Production associates can take any training we offer on
their own time. Engineers can take any training as well.

OK, each employee may take part in several programs, and each program
(such
as TDP) involves many employees. That's what I'm getting out of this.
See
my earlier suggestion about the table structure, except that there will be
a
Program table instead of a Certificate table, and tblAssocCert (the
junction
table) will be replaced by a similarly-structured tblAssocProgram. If you
get this set up you can incorporate certificates later. You need to get
the
basic structure in place.
I understand how it needs to work and what I want it to do, I just don't
know
how to set it up.

I have explained a way to set it up, in the absence of information about
which I have inquired several times. Have you attempted the set-up I
suggested?
[quoted text clipped - 66 lines]
in
a hole on this one!
 
1

131313 via AccessMonster.com

Bruce,
Thanks, I will let you kno how it turns out.
[quoted text clipped - 17 lines]
are
the issues that I am having trouble with.

To change the color of the *text box or other control* (not the field, which
is in the table), use conditional formatting. Help has more information
about that. Take a look, and post back if you still have questions.
Each step for each person should be a separate record in a Steps table. If
there are three people with eight steps each, there will be 24 records in
the Steps table. With a separate Steps table there could be a "SteppedOut"
(Yes/No) field, which could be marked if the person stepped out of that
step. Or maybe you could mark the StartDate and EndDate fields with the
same date, and use conditional formatting to turn the text boxes blue if
StartDate and EndDate are the same.
You could use the same general principles if you are using spreadsheet
design and putting all of the steps into a single record. It is not
advisable to do it that way, but it sounds as if you do not wish to change
the design of what you now have, so you can only improvise to the extent the
structure will allow.
To select one example, if you wish to select all associates who are
currently in the sixth step, you could query for records in which the sixth
step has a StartDate but no EndDate. If Steps are in a related table you
would do something that accomplishes the same thing by looking for all
Associate records in which the highest Step record is for Step 6, and in
which Step 6 shows a StartDate but no EndDate.
When you say "How to get it to see if the certificate for that step is
complete", do you mean a way to see visually (through color coding, for
instance) if the step is complete? If so, conditional formatting may do
much of what you seek.
Sorry for any confusion. You gave me a better way of breaking down my
database and I can see how it all should work. I just can't figure out the
[quoted text clipped - 72 lines]
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Publisher is filling blank lines with data 2
Please Help...........Anyone? 5
Please Help! I am Lost 0
A Nigtmare! 0
HELP! 3
Where to start 10
Need help with Queries 4
Database Help! 14

Top