Subquery help

[K]

I think I can do this in one query but I am not sure the verbage. I have the
following scenario:

Lname FName DOS Provider1 Provider2
Smith John 20081212 Jones Williams
Smith John 20081212 Wilson Simpson

What I want to do is find all the records where provider1 = "Jones" and
Provider2 = "Williams" and then with that criteria find all the records that
where lname, fname and DOS are the same. Is this possible in one query?

TIA!

Kim

 

[John Spencer]

Perhaps the following will work for you.

SELECT A.LName, A.FName, A.DOS
FROM [TheTable] as A INNER JOIN [TheTable] As B
ON A.LName = B.LName
AND A.FName = B.FName
AND A.DOS = B.DOS
WHERE B.Provider1 = "Jones" and B.Provider2="Williams"

If the query need to be updatable then you will probably need to use a
correlated subquery to identify the records.

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

 

[K]

Thanks so much John! This worked liked a charm.

Perhaps the following will work for you.

SELECT A.LName, A.FName, A.DOS
FROM [TheTable] as A INNER JOIN [TheTable] As B
ON A.LName = B.LName
AND A.FName = B.FName
AND A.DOS = B.DOS
WHERE B.Provider1 = "Jones" and B.Provider2="Williams"

If the query need to be updatable then you will probably need to use a
correlated subquery to identify the records.

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

I think I can do this in one query but I am not sure the verbage. I have the
following scenario:

Lname FName DOS Provider1 Provider2
Smith John 20081212 Jones Williams
Smith John 20081212 Wilson Simpson

What I want to do is find all the records where provider1 = "Jones" and
Provider2 = "Williams" and then with that criteria find all the records that
where lname, fname and DOS are the same. Is this possible in one query?

TIA!

Kim