Link to home
Start Free TrialLog in
Avatar of Sandra Smith
Sandra SmithFlag for United States of America

asked on

Turn two queries into one with a subquery ACCESS 2010

I have two queries, but I want to turn them into one with a subquery, but not sure how to do it.  Below are the two queries.  I am trying to get the PersonID to display in a combobox that are NOT in tblCaseSatisfactionSurvey

SELECT DISTINCT tblCase.CaseID, tblCase.PersonID, [FirstName] & " " & [LastName] AS [Full Name], tblPerson.DOB
FROM (tblCase INNER JOIN tblPerson ON tblCase.[PersonID] = tblPerson.[PersonID]) INNER JOIN qumPersonId ON tblPerson.PersonID = qumPersonId.PersonID
ORDER BY tblCase.PersonID;


SELECT tblPerson.PersonID
FROM tblPerson LEFT JOIN tblCaseSatisfactionSurvey ON tblPerson.[PersonID] = tblCaseSatisfactionSurvey.[PersonID]
WHERE (((tblCaseSatisfactionSurvey.PersonID) Is Null));
Avatar of PortletPaul
PortletPaul
Flag of Australia image

You are leaving a great deal to our intuition (i.e. we have to guess). It would be better to illustrate what you want using a sample of data from each existing query and then the expected result once combined.

Having said that, this is my first guess:
SELECT DISTINCT
      tblCase.CaseID
    , tblCase.PersonID
    , p.[FirstName] & " " & p.[LastName] AS [Full Name]
    , p.DOB
    , p.[InSurvey]
FROM (tblCase
      INNER JOIN (
                SELECT
                      tblPerson.PersonID, tblPerson.FirstName, tblPerson.LastName, tblPerson.DOB
                      , IIF(tblCaseSatisfactionSurvey.[PersonID] IS NULL, 'No', 'Yes') AS [InSurvey]
                FROM tblPerson
                      LEFT JOIN tblCaseSatisfactionSurvey ON tblPerson.[PersonID] = tblCaseSatisfactionSurvey.[PersonID]
                ) p ON tblCase.[PersonID] = p.[PersonID])
ORDER BY tblCase.PersonID;

Open in new window

SOLUTION
Avatar of Mike Eghtebas
Mike Eghtebas
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Sandra Smith

ASKER

Both pieces of information helped, thank you.  It does what I want without the need for more than one query.