duplicate and triplet

  • Thread starter Thread starter tracktraining
  • Start date Start date
T

tracktraining

Hi All,

I created a report function in Access to return all records assigned to an
employee with XYZ email. For some employees the report returns the correct
records. For other employees I am getting duplicate and triplets of the same
records in the report. And it is consistent based on employees. If Amy's
report is correct, it will always be correct. If Bob's report is wrong, it
will always be correct (returning duplicate or triplet records). See example
below. Please help.

Correct (happens about 75% of the time):
006-0007 A
008-0004 B
009-0011 A
012-4015 C

Wrong (happens about 15% of the time):
006-0007 A
006-0007 A
006-0007 A
008-0004 B
008-0004 B
008-0004 B
009-0011 A
009-0011 A
009-0011 A
012-4015 C
012-4015 C
012-4015 C

Wrong:
006-0007 A
006-0007 A
008-0004 B
008-0004 B
009-0011 A
009-0011 A
012-4015 C
012-4015 C

Please help if possible. Thanks in advance!
Tracktraining
 
Who are Amy and Bob? Are they the people running the report or employees in
the report?
Are you using two tables to develop the data for the report?
Post the SQL of the query use to feed the report.
 
Sorry, Amy and Bob are the employees, not the people running the report.

yes, I am using two tables.

the code is below (query):

SELECT EmpDocStatus.FirstName, EmpDocStatus.LastName, EmpDocStatus.DocID,
EmpDocStatus.Revision, EmpDocStatus.DateAssigned, EmpDocStatus.DateCompleted,
EmpInfo.JobFunc, EmpInfo.JobTitle
FROM EmpDocStatus, EmpInfo
WHERE [Enter Employee Username] = ( EmpDocStatus.EmpEmail) AND
EmpInfo.JobTitle = (SELECT EmpInfo.JobTitle FROM EmpInfo WHERE
EmpInfo.EmpEmail = EmpDocStatus.EmpEmail)
ORDER BY EmpDocStatus.DocID, EmpDocStatus.DateAssigned;
 
sorry again... i posted the wrong code. below is the correct one:

Report Query
SELECT LatestRev.FirstName, LatestRev.LastName, LatestRev.DocID,
LatestRev.Revision, LatestRev.DateAssigned, LatestRev.DateCompleted
FROM LatestRev
WHERE (([Enter Document Number])=LatestRev.DocID)
ORDER BY LatestRev.DateAssigned, LatestRev.LastName, LatestRev.FirstName;


LatestRev query
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;
 
sorry again.. i don't know what is wrong with me right now.. but the right
code i posted is the correct code: here it is again.... I hope I didn't
confused and pissed off anyone enough that I won't recieve any help. Pleaase
help.

the code is below (query):

SELECT EmpDocStatus.FirstName, EmpDocStatus.LastName, EmpDocStatus.DocID,
EmpDocStatus.Revision, EmpDocStatus.DateAssigned, EmpDocStatus.DateCompleted,
EmpInfo.JobFunc, EmpInfo.JobTitle
FROM EmpDocStatus, EmpInfo
WHERE [Enter Employee Username] = ( EmpDocStatus.EmpEmail) AND
EmpInfo.JobTitle = (SELECT EmpInfo.JobTitle FROM EmpInfo WHERE
EmpInfo.EmpEmail = EmpDocStatus.EmpEmail)
ORDER BY EmpDocStatus.DocID, EmpDocStatus.DateAssigned;


--
Learning


tracktraining said:
Sorry, Amy and Bob are the employees, not the people running the report.

yes, I am using two tables.

the code is below (query):

SELECT EmpDocStatus.FirstName, EmpDocStatus.LastName, EmpDocStatus.DocID,
EmpDocStatus.Revision, EmpDocStatus.DateAssigned, EmpDocStatus.DateCompleted,
EmpInfo.JobFunc, EmpInfo.JobTitle
FROM EmpDocStatus, EmpInfo
WHERE [Enter Employee Username] = ( EmpDocStatus.EmpEmail) AND
EmpInfo.JobTitle = (SELECT EmpInfo.JobTitle FROM EmpInfo WHERE
EmpInfo.EmpEmail = EmpDocStatus.EmpEmail)
ORDER BY EmpDocStatus.DocID, EmpDocStatus.DateAssigned;

--
Learning


KARL DEWEY said:
Who are Amy and Bob? Are they the people running the report or employees in
the report?
Are you using two tables to develop the data for the report?
Post the SQL of the query use to feed the report.
Click to expand...
Click to expand...
 
the first code i posted is the correct code.... in addition, i tested the
code in the .mdb environment and it works perfectly... the problem is
happening in the .mde environment... not sure what is going on.

also is there a limit number of record Access can hold?

thanks!
--
Learning


tracktraining said:
sorry again.. i don't know what is wrong with me right now.. but the right
code i posted is the correct code: here it is again.... I hope I didn't
confused and pissed off anyone enough that I won't recieve any help. Pleaase
help.

the code is below (query):

SELECT EmpDocStatus.FirstName, EmpDocStatus.LastName, EmpDocStatus.DocID,
EmpDocStatus.Revision, EmpDocStatus.DateAssigned, EmpDocStatus.DateCompleted,
EmpInfo.JobFunc, EmpInfo.JobTitle
FROM EmpDocStatus, EmpInfo
WHERE [Enter Employee Username] = ( EmpDocStatus.EmpEmail) AND
EmpInfo.JobTitle = (SELECT EmpInfo.JobTitle FROM EmpInfo WHERE
EmpInfo.EmpEmail = EmpDocStatus.EmpEmail)
ORDER BY EmpDocStatus.DocID, EmpDocStatus.DateAssigned;


--
Learning


tracktraining said:
Sorry, Amy and Bob are the employees, not the people running the report.

yes, I am using two tables.

the code is below (query):

SELECT EmpDocStatus.FirstName, EmpDocStatus.LastName, EmpDocStatus.DocID,
EmpDocStatus.Revision, EmpDocStatus.DateAssigned, EmpDocStatus.DateCompleted,
EmpInfo.JobFunc, EmpInfo.JobTitle
FROM EmpDocStatus, EmpInfo
WHERE [Enter Employee Username] = ( EmpDocStatus.EmpEmail) AND
EmpInfo.JobTitle = (SELECT EmpInfo.JobTitle FROM EmpInfo WHERE
EmpInfo.EmpEmail = EmpDocStatus.EmpEmail)
ORDER BY EmpDocStatus.DocID, EmpDocStatus.DateAssigned;

--
Learning


KARL DEWEY said:
Who are Amy and Bob? Are they the people running the report or employees in
the report?
Are you using two tables to develop the data for the report?
Post the SQL of the query use to feed the report.
--
KARL DEWEY
Build a little - Test a little


:

Hi All,

I created a report function in Access to return all records assigned to an
employee with XYZ email. For some employees the report returns the correct
records. For other employees I am getting duplicate and triplets of the same
records in the report. And it is consistent based on employees. If Amy's
report is correct, it will always be correct. If Bob's report is wrong, it
will always be correct (returning duplicate or triplet records). See example
below. Please help.

Correct (happens about 75% of the time):
006-0007 A
008-0004 B
009-0011 A
012-4015 C

Wrong (happens about 15% of the time):
006-0007 A
006-0007 A
006-0007 A
008-0004 B
008-0004 B
008-0004 B
009-0011 A
009-0011 A
009-0011 A
012-4015 C
012-4015 C
012-4015 C

Wrong:
006-0007 A
006-0007 A
008-0004 B
008-0004 B
009-0011 A
009-0011 A
012-4015 C
012-4015 C

Please help if possible. Thanks in advance!
Tracktraining
Click to expand...
Click to expand...
Click to expand...
 
You have two un-joined tables. Your results will be the number of records in
one multiplied by the other. If Amy has three records in one table and one
in the other the results will be three records. If Bob has 4 records in one
and 2 in the other the results will have 8 records. This is know as
Cartesian effect.
Looking at your query the common field between the two tables is JobTitle,
not employee name or ID.

--
KARL DEWEY
Build a little - Test a little


tracktraining said:
sorry again.. i don't know what is wrong with me right now.. but the right
code i posted is the correct code: here it is again.... I hope I didn't
confused and pissed off anyone enough that I won't recieve any help. Pleaase
help.

the code is below (query):

SELECT EmpDocStatus.FirstName, EmpDocStatus.LastName, EmpDocStatus.DocID,
EmpDocStatus.Revision, EmpDocStatus.DateAssigned, EmpDocStatus.DateCompleted,
EmpInfo.JobFunc, EmpInfo.JobTitle
FROM EmpDocStatus, EmpInfo
WHERE [Enter Employee Username] = ( EmpDocStatus.EmpEmail) AND
EmpInfo.JobTitle = (SELECT EmpInfo.JobTitle FROM EmpInfo WHERE
EmpInfo.EmpEmail = EmpDocStatus.EmpEmail)
ORDER BY EmpDocStatus.DocID, EmpDocStatus.DateAssigned;


--
Learning


tracktraining said:
Sorry, Amy and Bob are the employees, not the people running the report.

yes, I am using two tables.

the code is below (query):

SELECT EmpDocStatus.FirstName, EmpDocStatus.LastName, EmpDocStatus.DocID,
EmpDocStatus.Revision, EmpDocStatus.DateAssigned, EmpDocStatus.DateCompleted,
EmpInfo.JobFunc, EmpInfo.JobTitle
FROM EmpDocStatus, EmpInfo
WHERE [Enter Employee Username] = ( EmpDocStatus.EmpEmail) AND
EmpInfo.JobTitle = (SELECT EmpInfo.JobTitle FROM EmpInfo WHERE
EmpInfo.EmpEmail = EmpDocStatus.EmpEmail)
ORDER BY EmpDocStatus.DocID, EmpDocStatus.DateAssigned;

--
Learning


KARL DEWEY said:
Who are Amy and Bob? Are they the people running the report or employees in
the report?
Are you using two tables to develop the data for the report?
Post the SQL of the query use to feed the report.
--
KARL DEWEY
Build a little - Test a little


:

Hi All,

I created a report function in Access to return all records assigned to an
employee with XYZ email. For some employees the report returns the correct
records. For other employees I am getting duplicate and triplets of the same
records in the report. And it is consistent based on employees. If Amy's
report is correct, it will always be correct. If Bob's report is wrong, it
will always be correct (returning duplicate or triplet records). See example
below. Please help.

Correct (happens about 75% of the time):
006-0007 A
008-0004 B
009-0011 A
012-4015 C

Wrong (happens about 15% of the time):
006-0007 A
006-0007 A
006-0007 A
008-0004 B
008-0004 B
008-0004 B
009-0011 A
009-0011 A
009-0011 A
012-4015 C
012-4015 C
012-4015 C

Wrong:
006-0007 A
006-0007 A
008-0004 B
008-0004 B
009-0011 A
009-0011 A
012-4015 C
012-4015 C

Please help if possible. Thanks in advance!
Tracktraining
Click to expand...
Click to expand...
Click to expand...
 
Millions. Database size at 2Gb tops it out. Have you done a backup and
then Compact and Repair?
--
KARL DEWEY
Build a little - Test a little


tracktraining said:
the first code i posted is the correct code.... in addition, i tested the
code in the .mdb environment and it works perfectly... the problem is
happening in the .mde environment... not sure what is going on.

also is there a limit number of record Access can hold?

thanks!
--
Learning


tracktraining said:
sorry again.. i don't know what is wrong with me right now.. but the right
code i posted is the correct code: here it is again.... I hope I didn't
confused and pissed off anyone enough that I won't recieve any help. Pleaase
help.

the code is below (query):

SELECT EmpDocStatus.FirstName, EmpDocStatus.LastName, EmpDocStatus.DocID,
EmpDocStatus.Revision, EmpDocStatus.DateAssigned, EmpDocStatus.DateCompleted,
EmpInfo.JobFunc, EmpInfo.JobTitle
FROM EmpDocStatus, EmpInfo
WHERE [Enter Employee Username] = ( EmpDocStatus.EmpEmail) AND
EmpInfo.JobTitle = (SELECT EmpInfo.JobTitle FROM EmpInfo WHERE
EmpInfo.EmpEmail = EmpDocStatus.EmpEmail)
ORDER BY EmpDocStatus.DocID, EmpDocStatus.DateAssigned;


--
Learning


tracktraining said:
Sorry, Amy and Bob are the employees, not the people running the report.

yes, I am using two tables.

the code is below (query):

SELECT EmpDocStatus.FirstName, EmpDocStatus.LastName, EmpDocStatus.DocID,
EmpDocStatus.Revision, EmpDocStatus.DateAssigned, EmpDocStatus.DateCompleted,
EmpInfo.JobFunc, EmpInfo.JobTitle
FROM EmpDocStatus, EmpInfo
WHERE [Enter Employee Username] = ( EmpDocStatus.EmpEmail) AND
EmpInfo.JobTitle = (SELECT EmpInfo.JobTitle FROM EmpInfo WHERE
EmpInfo.EmpEmail = EmpDocStatus.EmpEmail)
ORDER BY EmpDocStatus.DocID, EmpDocStatus.DateAssigned;

--
Learning


:

Who are Amy and Bob? Are they the people running the report or employees in
the report?
Are you using two tables to develop the data for the report?
Post the SQL of the query use to feed the report.
--
KARL DEWEY
Build a little - Test a little


:

Hi All,

I created a report function in Access to return all records assigned to an
employee with XYZ email. For some employees the report returns the correct
records. For other employees I am getting duplicate and triplets of the same
records in the report. And it is consistent based on employees. If Amy's
report is correct, it will always be correct. If Bob's report is wrong, it
will always be correct (returning duplicate or triplet records). See example
below. Please help.

Correct (happens about 75% of the time):
006-0007 A
008-0004 B
009-0011 A
012-4015 C

Wrong (happens about 15% of the time):
006-0007 A
006-0007 A
006-0007 A
008-0004 B
008-0004 B
008-0004 B
009-0011 A
009-0011 A
009-0011 A
012-4015 C
012-4015 C
012-4015 C

Wrong:
006-0007 A
006-0007 A
008-0004 B
008-0004 B
009-0011 A
009-0011 A
012-4015 C
012-4015 C

Please help if possible. Thanks in advance!
Tracktraining
Click to expand...
Click to expand...
Click to expand...
 
any suggestion on how to solve the problem?

the common fields between the two tables are empemail and jobfunc

thanks in advance.
--
Learning


KARL DEWEY said:
You have two un-joined tables. Your results will be the number of records in
one multiplied by the other. If Amy has three records in one table and one
in the other the results will be three records. If Bob has 4 records in one
and 2 in the other the results will have 8 records. This is know as
Cartesian effect.
Looking at your query the common field between the two tables is JobTitle,
not employee name or ID.

--
KARL DEWEY
Build a little - Test a little


tracktraining said:
sorry again.. i don't know what is wrong with me right now.. but the right
code i posted is the correct code: here it is again.... I hope I didn't
confused and pissed off anyone enough that I won't recieve any help. Pleaase
help.

the code is below (query):

SELECT EmpDocStatus.FirstName, EmpDocStatus.LastName, EmpDocStatus.DocID,
EmpDocStatus.Revision, EmpDocStatus.DateAssigned, EmpDocStatus.DateCompleted,
EmpInfo.JobFunc, EmpInfo.JobTitle
FROM EmpDocStatus, EmpInfo
WHERE [Enter Employee Username] = ( EmpDocStatus.EmpEmail) AND
EmpInfo.JobTitle = (SELECT EmpInfo.JobTitle FROM EmpInfo WHERE
EmpInfo.EmpEmail = EmpDocStatus.EmpEmail)
ORDER BY EmpDocStatus.DocID, EmpDocStatus.DateAssigned;


--
Learning


tracktraining said:
Sorry, Amy and Bob are the employees, not the people running the report.

yes, I am using two tables.

the code is below (query):

SELECT EmpDocStatus.FirstName, EmpDocStatus.LastName, EmpDocStatus.DocID,
EmpDocStatus.Revision, EmpDocStatus.DateAssigned, EmpDocStatus.DateCompleted,
EmpInfo.JobFunc, EmpInfo.JobTitle
FROM EmpDocStatus, EmpInfo
WHERE [Enter Employee Username] = ( EmpDocStatus.EmpEmail) AND
EmpInfo.JobTitle = (SELECT EmpInfo.JobTitle FROM EmpInfo WHERE
EmpInfo.EmpEmail = EmpDocStatus.EmpEmail)
ORDER BY EmpDocStatus.DocID, EmpDocStatus.DateAssigned;

--
Learning


:

Who are Amy and Bob? Are they the people running the report or employees in
the report?
Are you using two tables to develop the data for the report?
Post the SQL of the query use to feed the report.
--
KARL DEWEY
Build a little - Test a little


:

Hi All,

I created a report function in Access to return all records assigned to an
employee with XYZ email. For some employees the report returns the correct
records. For other employees I am getting duplicate and triplets of the same
records in the report. And it is consistent based on employees. If Amy's
report is correct, it will always be correct. If Bob's report is wrong, it
will always be correct (returning duplicate or triplet records). See example
below. Please help.

Correct (happens about 75% of the time):
006-0007 A
008-0004 B
009-0011 A
012-4015 C

Wrong (happens about 15% of the time):
006-0007 A
006-0007 A
006-0007 A
008-0004 B
008-0004 B
008-0004 B
009-0011 A
009-0011 A
009-0011 A
012-4015 C
012-4015 C
012-4015 C

Wrong:
006-0007 A
006-0007 A
008-0004 B
008-0004 B
009-0011 A
009-0011 A
012-4015 C
012-4015 C

Please help if possible. Thanks in advance!
Tracktraining
Click to expand...
Click to expand...
Click to expand...
 
Try this as from statement --
FROM EmpDocStatus INNER JOIN EmpInfo ON EmpDocStatus.empemail =
EmpInfo.empemail
--
KARL DEWEY
Build a little - Test a little


tracktraining said:
any suggestion on how to solve the problem?

the common fields between the two tables are empemail and jobfunc

thanks in advance.
--
Learning


KARL DEWEY said:
You have two un-joined tables. Your results will be the number of records in
one multiplied by the other. If Amy has three records in one table and one
in the other the results will be three records. If Bob has 4 records in one
and 2 in the other the results will have 8 records. This is know as
Cartesian effect.
Looking at your query the common field between the two tables is JobTitle,
not employee name or ID.

--
KARL DEWEY
Build a little - Test a little


tracktraining said:
sorry again.. i don't know what is wrong with me right now.. but the right
code i posted is the correct code: here it is again.... I hope I didn't
confused and pissed off anyone enough that I won't recieve any help. Pleaase
help.

the code is below (query):

SELECT EmpDocStatus.FirstName, EmpDocStatus.LastName, EmpDocStatus.DocID,
EmpDocStatus.Revision, EmpDocStatus.DateAssigned, EmpDocStatus.DateCompleted,
EmpInfo.JobFunc, EmpInfo.JobTitle
FROM EmpDocStatus, EmpInfo
WHERE [Enter Employee Username] = ( EmpDocStatus.EmpEmail) AND
EmpInfo.JobTitle = (SELECT EmpInfo.JobTitle FROM EmpInfo WHERE
EmpInfo.EmpEmail = EmpDocStatus.EmpEmail)
ORDER BY EmpDocStatus.DocID, EmpDocStatus.DateAssigned;


--
Learning


:

Sorry, Amy and Bob are the employees, not the people running the report.

yes, I am using two tables.

the code is below (query):

SELECT EmpDocStatus.FirstName, EmpDocStatus.LastName, EmpDocStatus.DocID,
EmpDocStatus.Revision, EmpDocStatus.DateAssigned, EmpDocStatus.DateCompleted,
EmpInfo.JobFunc, EmpInfo.JobTitle
FROM EmpDocStatus, EmpInfo
WHERE [Enter Employee Username] = ( EmpDocStatus.EmpEmail) AND
EmpInfo.JobTitle = (SELECT EmpInfo.JobTitle FROM EmpInfo WHERE
EmpInfo.EmpEmail = EmpDocStatus.EmpEmail)
ORDER BY EmpDocStatus.DocID, EmpDocStatus.DateAssigned;

--
Learning


:

Who are Amy and Bob? Are they the people running the report or employees in
the report?
Are you using two tables to develop the data for the report?
Post the SQL of the query use to feed the report.
--
KARL DEWEY
Build a little - Test a little


:

Hi All,

I created a report function in Access to return all records assigned to an
employee with XYZ email. For some employees the report returns the correct
records. For other employees I am getting duplicate and triplets of the same
records in the report. And it is consistent based on employees. If Amy's
report is correct, it will always be correct. If Bob's report is wrong, it
will always be correct (returning duplicate or triplet records). See example
below. Please help.

Correct (happens about 75% of the time):
006-0007 A
008-0004 B
009-0011 A
012-4015 C

Wrong (happens about 15% of the time):
006-0007 A
006-0007 A
006-0007 A
008-0004 B
008-0004 B
008-0004 B
009-0011 A
009-0011 A
009-0011 A
012-4015 C
012-4015 C
012-4015 C

Wrong:
006-0007 A
006-0007 A
008-0004 B
008-0004 B
009-0011 A
009-0011 A
012-4015 C
012-4015 C

Please help if possible. Thanks in advance!
Tracktraining
Click to expand...
Click to expand...
Click to expand...
 
Back
Top