CODEWITHSUNDEEP
ms-accessvba
Brett
Brett

Reputation: 117

How can I find records in a table / recordset based on the result of a query on the same table?

I'm trying to return records for an alert system based on two conditions. The first condition is there is a booking in the system for tomorrow's date [Date()+1] with a Type value of B. If that JobNumber also has a Type value (in another record) of A AND the Result field's value is "Not Approved" we need to return an alert.

Example table:

JobNumber   Type    Company Date            Result
58129       B       3       22/03/2013      
58129       A       3       20/03/2013      Not Approved
58129       C       3

So far I have been able to create a SQL query in VBA to return the results of the first condition and have looped through the results to return the relevant JobNumbers. How do I insert these JobNumbers as criteria for the second SQL query or is it possible to combine all criteria into one SQL statement?

My SQL so far:

strSQL1 = "SELECT I.JobNumber, I.Type, I.Company, I.Date, I.Result " & _
          "FROM tblInspection I " & _
          "WHERE (((I.Type)='B') AND ((I.Date)=Date()+1));"

strSQL2 = "SELECT I.JobNumber, I.Type, I.Company, I.Date, I.Result " & _
          "FROM tblInspection I " & _
          "WHERE (((I.Type)='A') AND ((I.Result)<>'approved'));"

Any help would be much appreciated.

Upvotes: 1

Views: 349

Answers (1)

Fionnuala
Fionnuala

Reputation: 91376

You can get a field from the same or another table. This will give an error if more than one row is returned, but whether or not more than one row will be returned depends on your data. If it is likely, you will need to add another criterion, such as date.

SELECT I.JobNumber, I.Type, I.Company, I.Date, I.Result,
    (SELECT Result 
     FROM tblInspection q 
     WHERE q.JobNumber=i.JobNumber 
     AND Result="Not Approved" 
     AND Type="A")
As ResultA
FROM tblInspection I
WHERE I.Type='B' AND I.Date=Date()+1

Upvotes: 0

Related Questions