|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
May 9th, 2008, 05:08 PM
posted to microsoft.public.access.queries
|
|||
|
|||
Subquery help
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 
|
|
#2
May 9th, 2008, 06:04 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Subquery help
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 wrote: 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
|
|
#3
May 9th, 2008, 08:26 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Subquery help
Thanks so much John! This worked liked a charm.
"John Spencer" wrote: 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 wrote: 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
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
