Record listed multiple times

[JR Hester]

Access XP running in Windows XP

Just noticed that my Query lists 3 separate records twice each time it is
run. Thus the report based on this query is incorrect.

The query is pulling data from 4 tables. Could this be the problem? The data
has NOT been entered multiple times, verified by transaction number.

I want to insert a "unique" instruction for the transaction field in the
query, but I can quite find a right way to accomplish this. Any suggestions
greatly appreciated!!

Thanks in advance

 

[John W. Vinson]

Access XP running in Windows XP

Just noticed that my Query lists 3 separate records twice each time it is
run. Thus the report based on this query is incorrect.

The query is pulling data from 4 tables. Could this be the problem? The data
has NOT been entered multiple times, verified by transaction number.

Yes. I suspect that the record in the parent table matches two records in one
of the child tables.

I want to insert a "unique" instruction for the transaction field in the
query, but I can quite find a right way to accomplish this. Any suggestions
greatly appreciated!!

Please open the query in SQL view and post the SQL text here. If we can see
the query, maybe we can help fix it.

John W. Vinson [MVP]

 

[JR Hester]

Here is the code for this query:
SELECT tblParticipantTrx.ClassID, tblParticipantTrx.EmpID,
tblParticipants.EmpLname, tblParticipantTrx.TrxClassDate,
tblParticipantTrx.TrxRegistered, tblParticipantTrx.TrxCompleted,
tblSessions.StrtDate, tblSessions.SessionID, tblParticipantTrx.TrxID,
tblClasses.ClassName, tblParticipants.EmpDept
FROM (tblParticipants INNER JOIN tblParticipantTrx ON (tblParticipants.EmpID
= tblParticipantTrx.EmpID) AND (tblParticipants.EmpID =
tblParticipantTrx.EmpID)) INNER JOIN (tblClasses INNER JOIN tblSessions ON
(tblClasses.ClassID = tblSessions.ClassID) AND (tblClasses.ClassID =
tblSessions.ClassID) AND (tblClasses.ClassID = tblSessions.ClassID)) ON
tblParticipantTrx.SessionID = tblSessions.SessionID
WHERE (((tblSessions.StrtDate) Between [start] And [end]))
ORDER BY tblSessions.StrtDate;

I do not profess to understand SQL code, but my first thoughts as I read
through this is that somehow the FROM portion has become corrupted with too
many INNER JOIN statements. Thanks for your time and suggestions

 

[John W. Vinson]

Here is the code for this query:
SELECT tblParticipantTrx.ClassID, tblParticipantTrx.EmpID,
tblParticipants.EmpLname, tblParticipantTrx.TrxClassDate,
tblParticipantTrx.TrxRegistered, tblParticipantTrx.TrxCompleted,
tblSessions.StrtDate, tblSessions.SessionID, tblParticipantTrx.TrxID,
tblClasses.ClassName, tblParticipants.EmpDept
FROM (tblParticipants INNER JOIN tblParticipantTrx ON (tblParticipants.EmpID
= tblParticipantTrx.EmpID) AND (tblParticipants.EmpID =
tblParticipantTrx.EmpID)) INNER JOIN (tblClasses INNER JOIN tblSessions ON
(tblClasses.ClassID = tblSessions.ClassID) AND (tblClasses.ClassID =
tblSessions.ClassID) AND (tblClasses.ClassID = tblSessions.ClassID)) ON
tblParticipantTrx.SessionID = tblSessions.SessionID
WHERE (((tblSessions.StrtDate) Between [start] And [end]))
ORDER BY tblSessions.StrtDate;

I do not profess to understand SQL code, but my first thoughts as I read
through this is that somehow the FROM portion has become corrupted with too
many INNER JOIN statements. Thanks for your time and suggestions

I really don't think there's anything corrupt here at all. This query will
retrieve two records from tblParticipants if that participant has enrolled in
two courses (i.e. there are two records for that EmpID in tblParticipantTRX),
or participated in two Sessions. That's just how queries work!

Take a look at the "duplicate" records. Do they have identical values of
EmpLName but different values of TrxCompleted or StrtDate? If so, Access is
doing precisely what you're asking it to do.

Now as for what you *want* it to do... that might be a different issue! What
result would you like to see if an employee is in two sessions?

John W. Vinson [MVP]

 

[JR Hester]

Thanks for reviewing the code. The problem I am trying to resolve is that
several records seem to be repeated, the sessionID, employeeID are exactly
the same.

Wait, you just triggered a thought. Yes, the EmpDept field somehow is
different for each member of these three problem pairs. That SHOULD not be
possible. I have to find out how that is happening.

Thanks so much for slowing me down enough to catch that subtle difference. I
was only looking at what I THOUGHT it should be comparing, namely the EmpID
and teh SessionID.

I started tracking teh EmpDEpt at time of training rather than currently
assigned dept as a more accurate accounting. Is it possible to eliminate
duplicates based on the EmployeeID+SessionID join only? If yes, how might I
accomplish this, in the same or a similar query?

Thanks again

Here is the code for this query:
SELECT tblParticipantTrx.ClassID, tblParticipantTrx.EmpID,
tblParticipants.EmpLname, tblParticipantTrx.TrxClassDate,
tblParticipantTrx.TrxRegistered, tblParticipantTrx.TrxCompleted,
tblSessions.StrtDate, tblSessions.SessionID, tblParticipantTrx.TrxID,
tblClasses.ClassName, tblParticipants.EmpDept
FROM (tblParticipants INNER JOIN tblParticipantTrx ON (tblParticipants.EmpID
= tblParticipantTrx.EmpID) AND (tblParticipants.EmpID =
tblParticipantTrx.EmpID)) INNER JOIN (tblClasses INNER JOIN tblSessions ON
(tblClasses.ClassID = tblSessions.ClassID) AND (tblClasses.ClassID =
tblSessions.ClassID) AND (tblClasses.ClassID = tblSessions.ClassID)) ON
tblParticipantTrx.SessionID = tblSessions.SessionID
WHERE (((tblSessions.StrtDate) Between [start] And [end]))
ORDER BY tblSessions.StrtDate;

I do not profess to understand SQL code, but my first thoughts as I read
through this is that somehow the FROM portion has become corrupted with too
many INNER JOIN statements. Thanks for your time and suggestions

I really don't think there's anything corrupt here at all. This query will
retrieve two records from tblParticipants if that participant has enrolled in
two courses (i.e. there are two records for that EmpID in tblParticipantTRX),
or participated in two Sessions. That's just how queries work!

Take a look at the "duplicate" records. Do they have identical values of
EmpLName but different values of TrxCompleted or StrtDate? If so, Access is
doing precisely what you're asking it to do.

Now as for what you *want* it to do... that might be a different issue! What
result would you like to see if an employee is in two sessions?

John W. Vinson [MVP]

 

[JR Hester]

PArdon the assumtion in my previous response. Further investigation reveals
that query is cuerrently pulling the EmpDept field from teh Employee table.
There CAN be( and surrently is) only one entry per employee in this employee
table.

The entries in question in my query are Kasey Brooker-sessionID 900, Trina
Juan-session ID 910, and Isabel GArcia-session ID 912. I don't understand how
the Brooker entries extracted "Kasey" as EmpFname on one line and "Brooker"
as EmpFname on the second line. The same goes for the different "dept" fields
for Juan and Garcia.
** I attempted to check the relationships between teh table in the QUERY,
but actually opened the database table relationships screen. Very confusing
because the "CLASS" table is listed with relationships) multiple times as
CLass, Class1, CLass2, Class3, CLass4, and CLass5. Is this a normal
occurrence? or has something unusaul happened here that may be causing this
duplicate reporting issue? I can't think if any legitiamte reason to have all
these occurrences of the CLASS table in the relationships window.**

TrxClass Trxparticipant EmpLname TrxClassDate TrxRegistered TrxCompleted StrtDate SessionID TrxID ClassName EmpDept
Debra S. Woolbright Yes Yes 8/3/2007 261 896 File Management T&D
Jessica Lopez Yes No 8/3/2007 261 897 File Management Food & Beverage
Cholyide Pathompongpaing Yes No 8/3/2007 261 898 File Management F&B
Virgil P. Begay Yes Yes 8/3/2007 261 899 File Management Cage Ops
Trina Juan No Yes 8/3/2007 261 900 File Management Buffet
Trina Juan No Yes 8/3/2007 261 900 File Management PC
Karen Sadowy No Yes 8/3/2007 261 901 File Management HRTD
Peter G. Biel Yes Yes 8/14/2007 263 905 Excel,… Advanced Cage Ops
Treena L. Parvello Yes Yes 8/14/2007 263 904 Excel,… Advanced Mktg
David C. Bessette Yes No 8/14/2007 263 903 Excel,… Advanced Slot Tech
Trevor Wells Yes No 8/14/2007 263 902 Excel,… Advanced F&B
Trina Juan Yes Yes 8/15/2007 264 909 Outlook, Beginning Buffet
Maxine Buck Yes No 8/15/2007 264 908 Outlook, Beginning Cage Ops
Trina Juan Yes Yes 8/15/2007 264 909 Outlook, Beginning PC
Brooker Kasey Yes Yes 8/15/2007 264 910 Outlook, Beginning Surveillance
Brooker Brooker Yes Yes 8/15/2007 264 910 Outlook, Beginning Surveillance
Fred Yu Yes Yes 8/15/2007 264 906 Outlook, Beginning EE Dining
Monica S. Jaimez Yes Yes 8/15/2007 264 907 Outlook, Beginning Facilities-H
Maria Rojas Yes Yes 8/24/2007 266 911 PowerPoint, beginning Cage Ops
Ramona P. Zazueta Yes Yes 8/24/2007 266 913 PowerPoint, beginning Agave
Isabel Garcia Yes No 8/24/2007 266 912 PowerPoint, beginning Banquets
Isabel Garcia Yes No 8/24/2007 266 912 PowerPoint, beginning F & B
Moses Pacheco Yes Yes 8/29/2007 267 917 Outlook,. . Intermediate PC
Dena R. Parvello Yes Yes 8/29/2007 267 916 Outlook,. . Intermediate HR
Cholyide Pathompongpaing Yes No 8/29/2007 267 914 Outlook,. .
Intermediate F&B
Harold L. Joaquin Yes Yes 8/29/2007 267 915 Outlook,. . Intermediate P-Club

Here is the code for this query:
SELECT tblParticipantTrx.ClassID, tblParticipantTrx.EmpID,
tblParticipants.EmpLname, tblParticipantTrx.TrxClassDate,
tblParticipantTrx.TrxRegistered, tblParticipantTrx.TrxCompleted,
tblSessions.StrtDate, tblSessions.SessionID, tblParticipantTrx.TrxID,
tblClasses.ClassName, tblParticipants.EmpDept
FROM (tblParticipants INNER JOIN tblParticipantTrx ON (tblParticipants.EmpID
= tblParticipantTrx.EmpID) AND (tblParticipants.EmpID =
tblParticipantTrx.EmpID)) INNER JOIN (tblClasses INNER JOIN tblSessions ON
(tblClasses.ClassID = tblSessions.ClassID) AND (tblClasses.ClassID =
tblSessions.ClassID) AND (tblClasses.ClassID = tblSessions.ClassID)) ON
tblParticipantTrx.SessionID = tblSessions.SessionID
WHERE (((tblSessions.StrtDate) Between [start] And [end]))
ORDER BY tblSessions.StrtDate;

I do not profess to understand SQL code, but my first thoughts as I read
through this is that somehow the FROM portion has become corrupted with too
many INNER JOIN statements. Thanks for your time and suggestions

I really don't think there's anything corrupt here at all. This query will
retrieve two records from tblParticipants if that participant has enrolled in
two courses (i.e. there are two records for that EmpID in tblParticipantTRX),
or participated in two Sessions. That's just how queries work!

Take a look at the "duplicate" records. Do they have identical values of
EmpLName but different values of TrxCompleted or StrtDate? If so, Access is
doing precisely what you're asking it to do.

Now as for what you *want* it to do... that might be a different issue! What
result would you like to see if an employee is in two sessions?

John W. Vinson [MVP]