Duppliate Records Returned

[Rick]

I am trying to build a query pulling data from two tables,
tblMember and tblEPR with a one-to-may relationship. On a
form, I have a drop box that will use this query to pull a
record and add it to the form. Here is my problem:

1. This form is for adding a new performance report
2. Limit records to individuals who currently don't have a
performance report being worked (can't have two
peformance reports being worked at the same time.)
3. No multiple records (many individuals have mutliple
performance reports-employees get one each year so if a
memeber has received 3 performance reports, they show
up in the query three times.)

Here is the SQL:

SELECT tblMember.[Member Last Name], tblMember.[Member
First Name], tblEPR.[Closed out]
FROM tblMember LEFT JOIN tblEPR ON tblMember.MemberID =
tblEPR.MemberID
WHERE (((tblEPR.[Closed out]) Is Null Or (tblEPR.[Closed
out])="Yes"))
ORDER BY tblMember.[Member Last Name];

Any thoughts or do I need to include additional
information? Thanks in advance!!!!!!

 

[Tom Ellison]

Dear Rick:

So, of the multiple performance reports, how would you want to
determine the one to show? Perhaps the most recent one? Likely this
will involve having a "correlated subquery."

An alternative is possible. From the EPR table you are showing only
[Closed out]. But, you have filtered to where this column must be
NULL or "Yes". Do you really need to see which are NULL and which are
"Yes" or is this column really not necessary.

If that's the case there's another way of doing this:

SELECT [Member Last Name], [Member First Name]
FROM tblMember M
WHERE MemberID IN (SELECT MemberID FROM tblEPR
WHERE Nz([Closed out], "Yes") = "Yes")
ORDER BY [Member Last Name];

The above includes this subquery:

SELECT MemberID FROM tblEPR
WHERE Nz([Closed out], "Yes") = "Yes";

which should create a list of MemberIDs meeting your criteria. Then
it uses IN() to limit the Member table to those MemberIDs.

Will you need another approach, the correlated subquery perhaps, so
you can still display the value of [Closed out]. If so, please supply
information on how to specify which ONE of multiple EPR rows is to be
used to provide the [Closed out] value (after all, they COULD be
different, some "Yes" and some NULL). So you must tell the query how
to select the ONE from which to extract this value.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts