assigning all employee with first name start with D rev N document

[tracktraining]

Hi Everyone,

So I created this program on Access to keep track of training records - who
was/assigned to a training document (based on part number and rev.).

Now it is LIVE for my coworkers to use.

One problem that was recently found:
- All employees with first name start with "D" (i.e. Don, Dawn, Dan), every
training documents assigned to them somehow (magic) also have the same
document with rev. N.

For example:
Name Doc Number Rev.
Don 005-0001-000 B
Don 005-0001-000 N < --- should not be there
Daniel 080-0005-100 R (employee started with this doc. & Rev)
Daniel 080-0005-100 N <---- should not be there

So, it seems like the program is automatically assigning every employee with
first name start with D a rev N document for all document that employee was
assigned to.

Any idea what the problem is?

Thanks,
Tracktraining

 

[KARL DEWEY]

same document with rev. N.

The problem is that we do not have the 'magic' to see into your database and
analyze it.

Do you think you can tell us what the process is (you created it) that
assigns documents to people? Maybe post the SQL or code?

 

[tracktraining]

I have a form called Update Training Records. In this form, the admin would
pick the employee (primary key is the employee's username) from a drop down
box. Then assign the employee a document (can be picked from a drop down box)
and the current revision of that document is automatically inputted in the
revision column (via some other mean).

No employee can be assigned to the same doc number + rev.

I use the query below in the Update Training Records form to display the
latest rev of that document that each employee was assigned to (meaning ---
if an employee was assigned to 002-0001 A and 002-0001 B, then it would only
show 002-0001 B):

SELECT A.EmpEmail, A.FirstName, A.LastName, A.DocID, A.Revision,
A.DateAssigned, A.DateCompleted, A.JobFunc, A.TrainReq, A.Comments
FROM EmpDocStatus AS A
WHERE (((A.EmpEmail) In (Select DISTINCT D.EmpEmail From EmpDocStatus As D))
AND ((A.Revision)=(Select Max(B.Revision) From EmpDocStatus As B Where
B.DocID = A.DocID And B.EmpEmail = A.EmpEmail And Len(B.Revision) = (Select
Max(Len(C.Revision)) From EmpDocStatus As C Where C.DocID = A.DocID And
C.EmpEmail = A.EmpEmail))) AND ((A.JobFunc)<>"TERMINATED"))
ORDER BY A.LastName, A.FirstName, A.DocID, A.JobFunc, A.DateCompleted,
A.DateAssigned;

I have another form which is a Search form, which shows all the doc the
employee has ever been assigned/trained on (i.e. it will show both 002-0001 A
and 002-0001 B.). That query is as follows:

SELECT EmpDocStatus.FirstName, EmpDocStatus.LastName, EmpInfo.JobTitle,
EmpDocStatus.JobFunc, EmpDocStatus.EmpEmail, EmpDocStatus.DocID,
EmpDocStatus.Revision, EmpDocStatus.DateAssigned, EmpDocStatus.DateCompleted,
EmpDocStatus.TrainReq, EmpDocStatus.Comments
FROM EmpInfo, DocInfo, EmpJob, EmpDocStatus
WHERE EmpJob.EmpEmail = EmpDocStatus.EmpEmail AND EmpJob.JobFunc =
EmpDocStatus.JobFunc AND DocInfo.DocID = [EmpDocStatus].[DocID] AND
EmpInfo.EmpEmail = [EmpJob].[EmpEmail]

UNION SELECT EmpDocStatus.FirstName, EmpDocStatus.LastName,
EmpInfo.JobTitle, EmpDocStatus.JobFunc, EmpDocStatus.EmpEmail,
EmpDocStatus.DocID, EmpDocStatus.Revision, EmpDocStatus.DateAssigned,
EmpDocStatus.DateCompleted, EmpDocStatus.TrainReq, EmpDocStatus.Comments
FROM EmpInfo, DocInfo, EmpDocStatus
WHERE EmpDocStatus.JobFunc = 'ADDITIONAL' AND DocInfo.DocID =
[EmpDocStatus].[DocID] AND EmpInfo.EmpEmail = [EmpDocStatus].[EmpEmail]
ORDER BY EmpDocStatus.LastName, EmpDocStatus.FirstName,
EmpDocStatus.JobFunc, EmpDocStatus.DocID, EmpDocStatus.DateAssigned;



Now during my testing phrase everything worked as expected. BUT as of
yesterday, this problem was found --- and this problem is ONLY for employee
who's name start with a "D".

Problem: For every training record the employee was assigned/trained on for
any rev. there is a correspondining record with doc + rev N.

Please help if you can. Thanks!

 

[KARL DEWEY]

and the current revision of that document is automatically inputted in the
revision column (via some other mean).
You never did say what the 'other mean' was. What is that process?

Also you have "Len(B.Revision) = (Select Max(Len(C.Revision))" but I do not
understand why the lenght of the revision matter.

I have a form called Update Training Records. In this form, the admin would
pick the employee (primary key is the employee's username) from a drop down
box. Then assign the employee a document (can be picked from a drop down box)
and the current revision of that document is automatically inputted in the
revision column (via some other mean).

No employee can be assigned to the same doc number + rev.

I use the query below in the Update Training Records form to display the
latest rev of that document that each employee was assigned to (meaning ---
if an employee was assigned to 002-0001 A and 002-0001 B, then it would only
show 002-0001 B):

SELECT A.EmpEmail, A.FirstName, A.LastName, A.DocID, A.Revision,
A.DateAssigned, A.DateCompleted, A.JobFunc, A.TrainReq, A.Comments
FROM EmpDocStatus AS A
WHERE (((A.EmpEmail) In (Select DISTINCT D.EmpEmail From EmpDocStatus As D))
AND ((A.Revision)=(Select Max(B.Revision) From EmpDocStatus As B Where
B.DocID = A.DocID And B.EmpEmail = A.EmpEmail And Len(B.Revision) = (Select
Max(Len(C.Revision)) From EmpDocStatus As C Where C.DocID = A.DocID And
C.EmpEmail = A.EmpEmail))) AND ((A.JobFunc)<>"TERMINATED"))
ORDER BY A.LastName, A.FirstName, A.DocID, A.JobFunc, A.DateCompleted,
A.DateAssigned;

I have another form which is a Search form, which shows all the doc the
employee has ever been assigned/trained on (i.e. it will show both 002-0001 A
and 002-0001 B.). That query is as follows:

SELECT EmpDocStatus.FirstName, EmpDocStatus.LastName, EmpInfo.JobTitle,
EmpDocStatus.JobFunc, EmpDocStatus.EmpEmail, EmpDocStatus.DocID,
EmpDocStatus.Revision, EmpDocStatus.DateAssigned, EmpDocStatus.DateCompleted,
EmpDocStatus.TrainReq, EmpDocStatus.Comments
FROM EmpInfo, DocInfo, EmpJob, EmpDocStatus
WHERE EmpJob.EmpEmail = EmpDocStatus.EmpEmail AND EmpJob.JobFunc =
EmpDocStatus.JobFunc AND DocInfo.DocID = [EmpDocStatus].[DocID] AND
EmpInfo.EmpEmail = [EmpJob].[EmpEmail]

UNION SELECT EmpDocStatus.FirstName, EmpDocStatus.LastName,
EmpInfo.JobTitle, EmpDocStatus.JobFunc, EmpDocStatus.EmpEmail,
EmpDocStatus.DocID, EmpDocStatus.Revision, EmpDocStatus.DateAssigned,
EmpDocStatus.DateCompleted, EmpDocStatus.TrainReq, EmpDocStatus.Comments
FROM EmpInfo, DocInfo, EmpDocStatus
WHERE EmpDocStatus.JobFunc = 'ADDITIONAL' AND DocInfo.DocID =
[EmpDocStatus].[DocID] AND EmpInfo.EmpEmail = [EmpDocStatus].[EmpEmail]
ORDER BY EmpDocStatus.LastName, EmpDocStatus.FirstName,
EmpDocStatus.JobFunc, EmpDocStatus.DocID, EmpDocStatus.DateAssigned;



Now during my testing phrase everything worked as expected. BUT as of
yesterday, this problem was found --- and this problem is ONLY for employee
who's name start with a "D".

Problem: For every training record the employee was assigned/trained on for
any rev. there is a correspondining record with doc + rev N.

Please help if you can. Thanks!






--
Learning

same document with rev. N.

The problem is that we do not have the 'magic' to see into your database and
analyze it.

Do you think you can tell us what the process is (you created it) that
assigns documents to people? Maybe post the SQL or code?

 

[tracktraining]

I am getting the current revision via ODBC.

the reason for the lenght is because after the revision hit Z, the next
revision is AB. Without that line, it would think that AB is after A which is
not the case. So revision goes as follow: A, B, C....., Z, AB, AC, ......


--
Learning

and the current revision of that document is automatically inputted in the
revision column (via some other mean).
You never did say what the 'other mean' was. What is that process?

Also you have "Len(B.Revision) = (Select Max(Len(C.Revision))" but I do not
understand why the lenght of the revision matter.

I have a form called Update Training Records. In this form, the admin would
pick the employee (primary key is the employee's username) from a drop down
box. Then assign the employee a document (can be picked from a drop down box)
and the current revision of that document is automatically inputted in the
revision column (via some other mean).

No employee can be assigned to the same doc number + rev.

I use the query below in the Update Training Records form to display the
latest rev of that document that each employee was assigned to (meaning ---
if an employee was assigned to 002-0001 A and 002-0001 B, then it would only
show 002-0001 B):

SELECT A.EmpEmail, A.FirstName, A.LastName, A.DocID, A.Revision,
A.DateAssigned, A.DateCompleted, A.JobFunc, A.TrainReq, A.Comments
FROM EmpDocStatus AS A
WHERE (((A.EmpEmail) In (Select DISTINCT D.EmpEmail From EmpDocStatus As D))
AND ((A.Revision)=(Select Max(B.Revision) From EmpDocStatus As B Where
B.DocID = A.DocID And B.EmpEmail = A.EmpEmail And Len(B.Revision) = (Select
Max(Len(C.Revision)) From EmpDocStatus As C Where C.DocID = A.DocID And
C.EmpEmail = A.EmpEmail))) AND ((A.JobFunc)<>"TERMINATED"))
ORDER BY A.LastName, A.FirstName, A.DocID, A.JobFunc, A.DateCompleted,
A.DateAssigned;

I have another form which is a Search form, which shows all the doc the
employee has ever been assigned/trained on (i.e. it will show both 002-0001 A
and 002-0001 B.). That query is as follows:

SELECT EmpDocStatus.FirstName, EmpDocStatus.LastName, EmpInfo.JobTitle,
EmpDocStatus.JobFunc, EmpDocStatus.EmpEmail, EmpDocStatus.DocID,
EmpDocStatus.Revision, EmpDocStatus.DateAssigned, EmpDocStatus.DateCompleted,
EmpDocStatus.TrainReq, EmpDocStatus.Comments
FROM EmpInfo, DocInfo, EmpJob, EmpDocStatus
WHERE EmpJob.EmpEmail = EmpDocStatus.EmpEmail AND EmpJob.JobFunc =
EmpDocStatus.JobFunc AND DocInfo.DocID = [EmpDocStatus].[DocID] AND
EmpInfo.EmpEmail = [EmpJob].[EmpEmail]

UNION SELECT EmpDocStatus.FirstName, EmpDocStatus.LastName,
EmpInfo.JobTitle, EmpDocStatus.JobFunc, EmpDocStatus.EmpEmail,
EmpDocStatus.DocID, EmpDocStatus.Revision, EmpDocStatus.DateAssigned,
EmpDocStatus.DateCompleted, EmpDocStatus.TrainReq, EmpDocStatus.Comments
FROM EmpInfo, DocInfo, EmpDocStatus
WHERE EmpDocStatus.JobFunc = 'ADDITIONAL' AND DocInfo.DocID =
[EmpDocStatus].[DocID] AND EmpInfo.EmpEmail = [EmpDocStatus].[EmpEmail]
ORDER BY EmpDocStatus.LastName, EmpDocStatus.FirstName,
EmpDocStatus.JobFunc, EmpDocStatus.DocID, EmpDocStatus.DateAssigned;



Now during my testing phrase everything worked as expected. BUT as of
yesterday, this problem was found --- and this problem is ONLY for employee
who's name start with a "D".

Problem: For every training record the employee was assigned/trained on for
any rev. there is a correspondining record with doc + rev N.

Please help if you can. Thanks!






--
Learning

every training documents assigned to them somehow (magic) also have the
same document with rev. N.

The problem is that we do not have the 'magic' to see into your database and
analyze it.

Do you think you can tell us what the process is (you created it) that
assigns documents to people? Maybe post the SQL or code?

:

Hi Everyone,

So I created this program on Access to keep track of training records - who
was/assigned to a training document (based on part number and rev.).

Now it is LIVE for my coworkers to use.

One problem that was recently found:
- All employees with first name start with "D" (i.e. Don, Dawn, Dan), every
training documents assigned to them somehow (magic) also have the same
document with rev. N.

For example:
Name Doc Number Rev.
Don 005-0001-000 B
Don 005-0001-000 N < --- should not be there
Daniel 080-0005-100 R (employee started with this doc. & Rev)
Daniel 080-0005-100 N <---- should not be there

So, it seems like the program is automatically assigning every employee with
first name start with D a rev N document for all document that employee was
assigned to.

Any idea what the problem is?

Thanks,
Tracktraining

 

[KARL DEWEY]

What is the process that assigns revisions to the employees? Do you have an
append query?

I am getting the current revision via ODBC.

the reason for the lenght is because after the revision hit Z, the next
revision is AB. Without that line, it would think that AB is after A which is
not the case. So revision goes as follow: A, B, C....., Z, AB, AC, ......


--
Learning

Then assign the employee a document (can be picked from a drop down box)

and the current revision of that document is automatically inputted in the
revision column (via some other mean).
You never did say what the 'other mean' was. What is that process?

Also you have "Len(B.Revision) = (Select Max(Len(C.Revision))" but I do not
understand why the lenght of the revision matter.

I have a form called Update Training Records. In this form, the admin would
pick the employee (primary key is the employee's username) from a drop down
box. Then assign the employee a document (can be picked from a drop down box)
and the current revision of that document is automatically inputted in the
revision column (via some other mean).

No employee can be assigned to the same doc number + rev.

I use the query below in the Update Training Records form to display the
latest rev of that document that each employee was assigned to (meaning ---
if an employee was assigned to 002-0001 A and 002-0001 B, then it would only
show 002-0001 B):

SELECT A.EmpEmail, A.FirstName, A.LastName, A.DocID, A.Revision,
A.DateAssigned, A.DateCompleted, A.JobFunc, A.TrainReq, A.Comments
FROM EmpDocStatus AS A
WHERE (((A.EmpEmail) In (Select DISTINCT D.EmpEmail From EmpDocStatus As D))
AND ((A.Revision)=(Select Max(B.Revision) From EmpDocStatus As B Where
B.DocID = A.DocID And B.EmpEmail = A.EmpEmail And Len(B.Revision) = (Select
Max(Len(C.Revision)) From EmpDocStatus As C Where C.DocID = A.DocID And
C.EmpEmail = A.EmpEmail))) AND ((A.JobFunc)<>"TERMINATED"))
ORDER BY A.LastName, A.FirstName, A.DocID, A.JobFunc, A.DateCompleted,
A.DateAssigned;

I have another form which is a Search form, which shows all the doc the
employee has ever been assigned/trained on (i.e. it will show both 002-0001 A
and 002-0001 B.). That query is as follows:

SELECT EmpDocStatus.FirstName, EmpDocStatus.LastName, EmpInfo.JobTitle,
EmpDocStatus.JobFunc, EmpDocStatus.EmpEmail, EmpDocStatus.DocID,
EmpDocStatus.Revision, EmpDocStatus.DateAssigned, EmpDocStatus.DateCompleted,
EmpDocStatus.TrainReq, EmpDocStatus.Comments
FROM EmpInfo, DocInfo, EmpJob, EmpDocStatus
WHERE EmpJob.EmpEmail = EmpDocStatus.EmpEmail AND EmpJob.JobFunc =
EmpDocStatus.JobFunc AND DocInfo.DocID = [EmpDocStatus].[DocID] AND
EmpInfo.EmpEmail = [EmpJob].[EmpEmail]

UNION SELECT EmpDocStatus.FirstName, EmpDocStatus.LastName,
EmpInfo.JobTitle, EmpDocStatus.JobFunc, EmpDocStatus.EmpEmail,
EmpDocStatus.DocID, EmpDocStatus.Revision, EmpDocStatus.DateAssigned,
EmpDocStatus.DateCompleted, EmpDocStatus.TrainReq, EmpDocStatus.Comments
FROM EmpInfo, DocInfo, EmpDocStatus
WHERE EmpDocStatus.JobFunc = 'ADDITIONAL' AND DocInfo.DocID =
[EmpDocStatus].[DocID] AND EmpInfo.EmpEmail = [EmpDocStatus].[EmpEmail]
ORDER BY EmpDocStatus.LastName, EmpDocStatus.FirstName,
EmpDocStatus.JobFunc, EmpDocStatus.DocID, EmpDocStatus.DateAssigned;



Now during my testing phrase everything worked as expected. BUT as of
yesterday, this problem was found --- and this problem is ONLY for employee
who's name start with a "D".

Problem: For every training record the employee was assigned/trained on for
any rev. there is a correspondining record with doc + rev N.

Please help if you can. Thanks!






--
Learning


:

every training documents assigned to them somehow (magic) also have the
same document with rev. N.

The problem is that we do not have the 'magic' to see into your database and
analyze it.

Do you think you can tell us what the process is (you created it) that
assigns documents to people? Maybe post the SQL or code?

:

Hi Everyone,

So I created this program on Access to keep track of training records - who
was/assigned to a training document (based on part number and rev.).

Now it is LIVE for my coworkers to use.

One problem that was recently found:
- All employees with first name start with "D" (i.e. Don, Dawn, Dan), every
training documents assigned to them somehow (magic) also have the same
document with rev. N.

For example:
Name Doc Number Rev.
Don 005-0001-000 B
Don 005-0001-000 N < --- should not be there
Daniel 080-0005-100 R (employee started with this doc. & Rev)
Daniel 080-0005-100 N <---- should not be there

So, it seems like the program is automatically assigning every employee with
first name start with D a rev N document for all document that employee was
assigned to.

Any idea what the problem is?

Thanks,
Tracktraining

 

[tracktraining]

no, the admins (person in charge of data entry) enters the training records
into the database.

so, when the employee(s) finished training on a document (i.e. 001-0001-001
B), then the employee(s) will tell the admins about it and the admins will
enter the data into the database. I added a check in the database so that the
admins are unable to enter an old revision or "future" revision, only the
current doc+rev is accepted.. see code below....

Set db = CurrentDb

Set rs = db.OpenRecordset(strAssignDup)
Set rst = db.OpenRecordset(strDocRev)

If Not rs.EOF Then
'Error message if empemail has doc+rev already
MsgBox "Part number and revision has already" & vbLf & _
"been assigned to employee."
cancel = True
Me.DocID.SetFocus
ElseIf rst.EOF Then
'Error message if doc+rev doesn't exist
MsgBox "Invalid part number and/or revision."
cancel = True
Me.DocID.SetFocus
Else
'No error message thus allow for data to be recorded
MsgBox "Training record updated."
End If


with this i still don't understand why it is only affecting employee's with
first name starting with the letter "D" and not other employees..... I can't
have my db assigining employees doc. that the admins didn't enter. And I
already asked the admins about this, and none of them enter the doc+ rev N
for those employees.

Here is the problem again: For some reason, all my employees with first name
starting with the letter "D" are getting revision N assigned to them
automatically (i.e. without a person entering in the data) for all the
training documents. See example below.

Name Doc Number Rev. Date completed
Dean 001-0001-002 A 08/05/08
Dean 001-0001-002 N
Dean 006-0001-004 S 02/08/09
Dean 006-0001-004 N

The rev. N records above should not have been there.
--
Learning

What is the process that assigns revisions to the employees? Do you have an
append query?

I am getting the current revision via ODBC.

the reason for the lenght is because after the revision hit Z, the next
revision is AB. Without that line, it would think that AB is after A which is
not the case. So revision goes as follow: A, B, C....., Z, AB, AC, ......


--
Learning

Then assign the employee a document (can be picked from a drop down box)
and the current revision of that document is automatically inputted in the
revision column (via some other mean).
You never did say what the 'other mean' was. What is that process?

Also you have "Len(B.Revision) = (Select Max(Len(C.Revision))" but I do not
understand why the lenght of the revision matter.

:

I have a form called Update Training Records. In this form, the admin would
pick the employee (primary key is the employee's username) from a drop down
box. Then assign the employee a document (can be picked from a drop down box)
and the current revision of that document is automatically inputted in the
revision column (via some other mean).

No employee can be assigned to the same doc number + rev.

I use the query below in the Update Training Records form to display the
latest rev of that document that each employee was assigned to (meaning ---
if an employee was assigned to 002-0001 A and 002-0001 B, then it would only
show 002-0001 B):

SELECT A.EmpEmail, A.FirstName, A.LastName, A.DocID, A.Revision,
A.DateAssigned, A.DateCompleted, A.JobFunc, A.TrainReq, A.Comments
FROM EmpDocStatus AS A
WHERE (((A.EmpEmail) In (Select DISTINCT D.EmpEmail From EmpDocStatus As D))
AND ((A.Revision)=(Select Max(B.Revision) From EmpDocStatus As B Where
B.DocID = A.DocID And B.EmpEmail = A.EmpEmail And Len(B.Revision) = (Select
Max(Len(C.Revision)) From EmpDocStatus As C Where C.DocID = A.DocID And
C.EmpEmail = A.EmpEmail))) AND ((A.JobFunc)<>"TERMINATED"))
ORDER BY A.LastName, A.FirstName, A.DocID, A.JobFunc, A.DateCompleted,
A.DateAssigned;

I have another form which is a Search form, which shows all the doc the
employee has ever been assigned/trained on (i.e. it will show both 002-0001 A
and 002-0001 B.). That query is as follows:

SELECT EmpDocStatus.FirstName, EmpDocStatus.LastName, EmpInfo.JobTitle,
EmpDocStatus.JobFunc, EmpDocStatus.EmpEmail, EmpDocStatus.DocID,
EmpDocStatus.Revision, EmpDocStatus.DateAssigned, EmpDocStatus.DateCompleted,
EmpDocStatus.TrainReq, EmpDocStatus.Comments
FROM EmpInfo, DocInfo, EmpJob, EmpDocStatus
WHERE EmpJob.EmpEmail = EmpDocStatus.EmpEmail AND EmpJob.JobFunc =
EmpDocStatus.JobFunc AND DocInfo.DocID = [EmpDocStatus].[DocID] AND
EmpInfo.EmpEmail = [EmpJob].[EmpEmail]

UNION SELECT EmpDocStatus.FirstName, EmpDocStatus.LastName,
EmpInfo.JobTitle, EmpDocStatus.JobFunc, EmpDocStatus.EmpEmail,
EmpDocStatus.DocID, EmpDocStatus.Revision, EmpDocStatus.DateAssigned,
EmpDocStatus.DateCompleted, EmpDocStatus.TrainReq, EmpDocStatus.Comments
FROM EmpInfo, DocInfo, EmpDocStatus
WHERE EmpDocStatus.JobFunc = 'ADDITIONAL' AND DocInfo.DocID =
[EmpDocStatus].[DocID] AND EmpInfo.EmpEmail = [EmpDocStatus].[EmpEmail]
ORDER BY EmpDocStatus.LastName, EmpDocStatus.FirstName,
EmpDocStatus.JobFunc, EmpDocStatus.DocID, EmpDocStatus.DateAssigned;



Now during my testing phrase everything worked as expected. BUT as of
yesterday, this problem was found --- and this problem is ONLY for employee
who's name start with a "D".

Problem: For every training record the employee was assigned/trained on for
any rev. there is a correspondining record with doc + rev N.

Please help if you can. Thanks!






--
Learning


:

every training documents assigned to them somehow (magic) also have the
same document with rev. N.

The problem is that we do not have the 'magic' to see into your database and
analyze it.

Do you think you can tell us what the process is (you created it) that
assigns documents to people? Maybe post the SQL or code?

:

Hi Everyone,

So I created this program on Access to keep track of training records - who
was/assigned to a training document (based on part number and rev.).

Now it is LIVE for my coworkers to use.

One problem that was recently found:
- All employees with first name start with "D" (i.e. Don, Dawn, Dan), every
training documents assigned to them somehow (magic) also have the same
document with rev. N.

For example:
Name Doc Number Rev.
Don 005-0001-000 B
Don 005-0001-000 N < --- should not be there
Daniel 080-0005-100 R (employee started with this doc. & Rev)
Daniel 080-0005-100 N <---- should not be there

So, it seems like the program is automatically assigning every employee with
first name start with D a rev N document for all document that employee was
assigned to.

Any idea what the problem is?

Thanks,
Tracktraining

 

[KARL DEWEY]

I can not help you with code.

I would suspect the admin error. Remove the errored data and you try an
entry.

no, the admins (person in charge of data entry) enters the training records
into the database.

so, when the employee(s) finished training on a document (i.e. 001-0001-001
B), then the employee(s) will tell the admins about it and the admins will
enter the data into the database. I added a check in the database so that the
admins are unable to enter an old revision or "future" revision, only the
current doc+rev is accepted.. see code below....

Set db = CurrentDb

Set rs = db.OpenRecordset(strAssignDup)
Set rst = db.OpenRecordset(strDocRev)

If Not rs.EOF Then
'Error message if empemail has doc+rev already
MsgBox "Part number and revision has already" & vbLf & _
"been assigned to employee."
cancel = True
Me.DocID.SetFocus
ElseIf rst.EOF Then
'Error message if doc+rev doesn't exist
MsgBox "Invalid part number and/or revision."
cancel = True
Me.DocID.SetFocus
Else
'No error message thus allow for data to be recorded
MsgBox "Training record updated."
End If


with this i still don't understand why it is only affecting employee's with
first name starting with the letter "D" and not other employees..... I can't
have my db assigining employees doc. that the admins didn't enter. And I
already asked the admins about this, and none of them enter the doc+ rev N
for those employees.

Here is the problem again: For some reason, all my employees with first name
starting with the letter "D" are getting revision N assigned to them
automatically (i.e. without a person entering in the data) for all the
training documents. See example below.

Name Doc Number Rev. Date completed
Dean 001-0001-002 A 08/05/08
Dean 001-0001-002 N
Dean 006-0001-004 S 02/08/09
Dean 006-0001-004 N

The rev. N records above should not have been there.
--
Learning

What is the process that assigns revisions to the employees? Do you have an
append query?

I am getting the current revision via ODBC.

the reason for the lenght is because after the revision hit Z, the next
revision is AB. Without that line, it would think that AB is after A which is
not the case. So revision goes as follow: A, B, C....., Z, AB, AC, ......


--
Learning


:

Then assign the employee a document (can be picked from a drop down box)
and the current revision of that document is automatically inputted in the
revision column (via some other mean).
You never did say what the 'other mean' was. What is that process?

Also you have "Len(B.Revision) = (Select Max(Len(C.Revision))" but I do not
understand why the lenght of the revision matter.

:

I have a form called Update Training Records. In this form, the admin would
pick the employee (primary key is the employee's username) from a drop down
box. Then assign the employee a document (can be picked from a drop down box)
and the current revision of that document is automatically inputted in the
revision column (via some other mean).

No employee can be assigned to the same doc number + rev.

I use the query below in the Update Training Records form to display the
latest rev of that document that each employee was assigned to (meaning ---
if an employee was assigned to 002-0001 A and 002-0001 B, then it would only
show 002-0001 B):

SELECT A.EmpEmail, A.FirstName, A.LastName, A.DocID, A.Revision,
A.DateAssigned, A.DateCompleted, A.JobFunc, A.TrainReq, A.Comments
FROM EmpDocStatus AS A
WHERE (((A.EmpEmail) In (Select DISTINCT D.EmpEmail From EmpDocStatus As D))
AND ((A.Revision)=(Select Max(B.Revision) From EmpDocStatus As B Where
B.DocID = A.DocID And B.EmpEmail = A.EmpEmail And Len(B.Revision) = (Select
Max(Len(C.Revision)) From EmpDocStatus As C Where C.DocID = A.DocID And
C.EmpEmail = A.EmpEmail))) AND ((A.JobFunc)<>"TERMINATED"))
ORDER BY A.LastName, A.FirstName, A.DocID, A.JobFunc, A.DateCompleted,
A.DateAssigned;

I have another form which is a Search form, which shows all the doc the
employee has ever been assigned/trained on (i.e. it will show both 002-0001 A
and 002-0001 B.). That query is as follows:

SELECT EmpDocStatus.FirstName, EmpDocStatus.LastName, EmpInfo.JobTitle,
EmpDocStatus.JobFunc, EmpDocStatus.EmpEmail, EmpDocStatus.DocID,
EmpDocStatus.Revision, EmpDocStatus.DateAssigned, EmpDocStatus.DateCompleted,
EmpDocStatus.TrainReq, EmpDocStatus.Comments
FROM EmpInfo, DocInfo, EmpJob, EmpDocStatus
WHERE EmpJob.EmpEmail = EmpDocStatus.EmpEmail AND EmpJob.JobFunc =
EmpDocStatus.JobFunc AND DocInfo.DocID = [EmpDocStatus].[DocID] AND
EmpInfo.EmpEmail = [EmpJob].[EmpEmail]

UNION SELECT EmpDocStatus.FirstName, EmpDocStatus.LastName,
EmpInfo.JobTitle, EmpDocStatus.JobFunc, EmpDocStatus.EmpEmail,
EmpDocStatus.DocID, EmpDocStatus.Revision, EmpDocStatus.DateAssigned,
EmpDocStatus.DateCompleted, EmpDocStatus.TrainReq, EmpDocStatus.Comments
FROM EmpInfo, DocInfo, EmpDocStatus
WHERE EmpDocStatus.JobFunc = 'ADDITIONAL' AND DocInfo.DocID =
[EmpDocStatus].[DocID] AND EmpInfo.EmpEmail = [EmpDocStatus].[EmpEmail]
ORDER BY EmpDocStatus.LastName, EmpDocStatus.FirstName,
EmpDocStatus.JobFunc, EmpDocStatus.DocID, EmpDocStatus.DateAssigned;



Now during my testing phrase everything worked as expected. BUT as of
yesterday, this problem was found --- and this problem is ONLY for employee
who's name start with a "D".

Problem: For every training record the employee was assigned/trained on for
any rev. there is a correspondining record with doc + rev N.

Please help if you can. Thanks!






--
Learning


:

every training documents assigned to them somehow (magic) also have the
same document with rev. N.

The problem is that we do not have the 'magic' to see into your database and
analyze it.

Do you think you can tell us what the process is (you created it) that
assigns documents to people? Maybe post the SQL or code?

:

Hi Everyone,

So I created this program on Access to keep track of training records - who
was/assigned to a training document (based on part number and rev.).

Now it is LIVE for my coworkers to use.

One problem that was recently found:
- All employees with first name start with "D" (i.e. Don, Dawn, Dan), every
training documents assigned to them somehow (magic) also have the same
document with rev. N.

For example:
Name Doc Number Rev.
Don 005-0001-000 B
Don 005-0001-000 N < --- should not be there
Daniel 080-0005-100 R (employee started with this doc. & Rev)
Daniel 080-0005-100 N <---- should not be there

So, it seems like the program is automatically assigning every employee with
first name start with D a rev N document for all document that employee was
assigned to.

Any idea what the problem is?

Thanks,
Tracktraining