Subquery help

  • Thread starter Thread starter K
  • Start date Start date
K

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
 
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
 
Thanks so much John! This worked liked a charm.

John Spencer said:
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
Click to expand...
Click to expand...
 
Back
Top