Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2002
    Posts
    16

    Unanswered: Construct Multiple Criteria On The Fly In VB

    I am trying to construct a fairly complex query on the fly. I have it working except for the criteria side of the query which I am having trouble with. The table structuring is fine it is the SQL after the WHERE statement that I am having trouble with.

    I want to only see results where the Candidate(s) meet ALL three set of criteria as can be seen in the queries below.

    I have the results I want based on a number of queries, but now I am not sure how to construct the queries in vb on the fly.

    I have three seperate queries generated as follows, (I have named them for easier reference later):

    Note: The queries are all the same except for the criteria after the WHERE statement.

    TEST QUERY 1 = SELECT DISTINCT tblCandidate.CandidateID, tblCandidate.FirstName & ' ' & [Surname] AS Name, tblCandidate.Telephone, tblCandidate.Mobile, tblCandidateAvailability.AvailableDate, tblCandidateAvailability.Status, tblBookingDetail.ScheduleDetailsID
    FROM ((tblCandidate LEFT JOIN tblCandidateArea ON tblCandidate.CandidateID = tblCandidateArea.CandidateID) LEFT JOIN tblCandidateJobCode ON tblCandidate.CandidateID = tblCandidateJobCode.CandidateID) INNER JOIN ((tblBranchDetail RIGHT JOIN (tblCandidateAvailability INNER JOIN tblBooking ON tblCandidateAvailability.AvailableDate = tblBooking.ScheduleDate) ON tblBranchDetail.BranchID = tblBooking.BranchID) INNER JOIN tblBookingDetail ON tblBooking.ScheduleID = tblBookingDetail.ScheduleID) ON tblCandidate.CandidateID = tblCandidateAvailability.CandidateID
    WHERE (((tblCandidateAvailability.Status)='Available') AND ((tblBookingDetail.ScheduleDetailsID)=103) AND ((tblCandidateJobCode.JobCode)='DO') AND ((tblCandidateArea.PostCodeID)='HP'));


    TEST QUERY 2 = SELECT DISTINCT tblCandidate.CandidateID, tblCandidate.FirstName & ' ' & [Surname] AS Name, tblCandidate.Telephone, tblCandidate.Mobile, tblCandidateAvailability.AvailableDate, tblCandidateAvailability.Status, tblBookingDetail.ScheduleDetailsID
    FROM ((tblCandidate LEFT JOIN tblCandidateArea ON tblCandidate.CandidateID = tblCandidateArea.CandidateID) LEFT JOIN tblCandidateJobCode ON tblCandidate.CandidateID = tblCandidateJobCode.CandidateID) INNER JOIN ((tblBranchDetail RIGHT JOIN (tblCandidateAvailability INNER JOIN tblBooking ON tblCandidateAvailability.AvailableDate = tblBooking.ScheduleDate) ON tblBranchDetail.BranchID = tblBooking.BranchID) INNER JOIN tblBookingDetail ON tblBooking.ScheduleID = tblBookingDetail.ScheduleID) ON tblCandidate.CandidateID = tblCandidateAvailability.CandidateID
    WHERE (((tblCandidateAvailability.Status)='Available') AND ((tblBookingDetail.ScheduleDetailsID)=105) AND ((tblCandidateJobCode.JobCode)='DO') AND ((tblCandidateArea.PostCodeID)='HP'));


    TEST QUERY 3 = SELECT DISTINCT tblCandidate.CandidateID, tblCandidate.FirstName & ' ' & [Surname] AS Name, tblCandidate.Telephone, tblCandidate.Mobile, tblCandidateAvailability.AvailableDate, tblCandidateAvailability.Status, tblBookingDetail.ScheduleDetailsID
    FROM ((tblCandidate LEFT JOIN tblCandidateArea ON tblCandidate.CandidateID = tblCandidateArea.CandidateID) LEFT JOIN tblCandidateJobCode ON tblCandidate.CandidateID = tblCandidateJobCode.CandidateID) INNER JOIN ((tblBranchDetail RIGHT JOIN (tblCandidateAvailability INNER JOIN tblBooking ON tblCandidateAvailability.AvailableDate = tblBooking.ScheduleDate) ON tblBranchDetail.BranchID = tblBooking.BranchID) INNER JOIN tblBookingDetail ON tblBooking.ScheduleID = tblBookingDetail.ScheduleID) ON tblCandidate.CandidateID = tblCandidateAvailability.CandidateID
    WHERE (((tblCandidateAvailability.Status)='Available') AND ((tblBookingDetail.ScheduleDetailsID)=106) AND ((tblCandidateJobCode.JobCode)='DO') AND ((tblCandidateArea.PostCodeID)='HP'));

    Note: The queries are all the same except for the criteria after the WHERE statement.
    Note: The Job Code and Post Code fields have the same criteria in this example but will change with real data, just in case you think I could simplify further.

    My code now generates these queries and loads them into an array arrSQLString(i), (which depends on how many selections in a listbox are selected, 3 in this case, hence the three queries, which will be arrSQLString (1), arrSQLString (2) and arrSQLString (3)).

    This obviously gives me the three separate queries generating the criteria separately.

    Now to meet my original requirements of having results which list a Candidate that meet all three criteria exactly then I have now got another query which generates this based on the above queries, as below:

    SELECT [TEST QUERY2].CandidateID, [TEST QUERY2].Name, [TEST QUERY2].Telephone, [TEST QUERY2].Mobile
    FROM ([TEST QUERY1] INNER JOIN [TEST QUERY2] ON [TEST QUERY1].CandidateID = [TEST QUERY2].CandidateID) INNER JOIN [TEST QUERY3] ON ([TEST QUERY2].CandidateID = [TEST QUERY3].CandidateID) AND ([TEST QUERY1].CandidateID = [TEST QUERY3].CandidateID);

    This query above now gives me exactly what I want. The only problem is generating the above query in vb code. Bear in mind that the above query contains references to the names of REAL queries I was generating to test the query. I obviously have to somehow reference my queries in code loaded in my array and construct the above query in code.

    That's where I am now stuck.

    I hope this doesn't sound too confusing. I'm sure I have done the hardest bit in constructing the first queries in code and having the second query that works. I am sure its probably fairly easy to construct the final query. I guess its just knowing the right referencing in SQL.

    Thanks in advance guys,

    Daz

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by DazJWood
    I'm sure I have done the hardest bit in constructing the first queries in code and having the second query that works. I am sure its probably fairly easy to construct the final query.
    You've got that right

    I would alias your queries in the final query as this then means you only have to feed the three query names into the from clause. E.g.
    Code:
    SELECT B.CandidateID, B.Name, B.Telephone, B.Mobile
    FROM [TEST QUERY1] AS A INNER JOIN [TEST QUERY2] AS B ON 
    A.CandidateID = B.CandidateID 
    INNER JOIN [TEST QUERY3] AS C ON 
    B.CandidateID = C.CandidateID
    I've knocked out one of your joins as it was unnecesary, as well as the brackets. So - you can have a fixed string for the SELECT and the actual joins. All you need to do now is feed in the query names.

    How comfy are you navigating arrays?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Actually - you could simply use the Replace function to change the query names ([TEST QUERY3]) to the names in the array. I presume there is a reason you are using dynamic query names and therefore are forced to store them in an array rather than call them Query1, Query2 etc?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Oct 2002
    Posts
    16
    Hiya,

    Thanks for your replies guys.

    This gives me something to go on. I will have a play around today.

    I can't just replace the query names because the code is all based on how many criteria are selected in a listbox. So the first three queries could be five queries or could be just one query, but I can sort this out with some code I think. That's the main reason I am using an array to load the queries into.

    I'll let you know how I get on

    Thanks,

    Daz

  5. #5
    Join Date
    Mar 2006
    Posts
    163
    Daz

    Why do you need the multiple queries?

    Couldn't you just create 1 query based on the selection in the listbox?

  6. #6
    Join Date
    Oct 2002
    Posts
    16
    Norie,

    Thanks, what's the one query I can use then?

  7. #7
    Join Date
    Oct 2002
    Posts
    16
    I have cracked it!!!

    I have figured out now how to do it using nested queries. For the example given I have nested all three queries into the final query. It now gives me the results I want. All that is left to do now is to build this SQL in VBA which with a bit of tinkering and an array should be no problem at all.

    For anyone interested here is the code. Bear in mind it is fairly raw at the moment, but it works

    SELECT QUERY1.CandidateID, QUERY1.Name, QUERY1.Telephone, QUERY1.Mobile
    FROM ([SELECT DISTINCT tblCandidate.CandidateID, tblCandidate.FirstName & ' ' & tblCandidate.Surname AS Name, tblCandidate.Telephone, tblCandidate.Mobile, tblCandidateAvailability.AvailableDate, tblCandidateAvailability.Status, tblBookingDetail.ScheduleDetailsID
    FROM ((tblCandidate LEFT JOIN tblCandidateArea ON tblCandidate.CandidateID = tblCandidateArea.CandidateID) LEFT JOIN tblCandidateJobCode ON tblCandidate.CandidateID = tblCandidateJobCode.CandidateID) INNER JOIN ((tblBranchDetail RIGHT JOIN (tblCandidateAvailability INNER JOIN tblBooking ON tblCandidateAvailability.AvailableDate = tblBooking.ScheduleDate) ON tblBranchDetail.BranchID = tblBooking.BranchID) INNER JOIN tblBookingDetail ON tblBooking.ScheduleID = tblBookingDetail.ScheduleID) ON tblCandidate.CandidateID = tblCandidateAvailability.CandidateID
    WHERE (((tblCandidateAvailability.Status)='Available') AND ((tblBookingDetail.ScheduleDetailsID)=103) AND ((tblCandidateJobCode.JobCode)='DO') AND ((tblCandidateArea.PostCodeID)='HP'))].
    AS QUERY1 INNER JOIN [SELECT DISTINCT tblCandidate.CandidateID, tblCandidate.FirstName & ' ' & tblCandidate.Surname AS Name, tblCandidate.Telephone, tblCandidate.Mobile, tblCandidateAvailability.AvailableDate, tblCandidateAvailability.Status, tblBookingDetail.ScheduleDetailsID
    FROM ((tblCandidate LEFT JOIN tblCandidateArea ON tblCandidate.CandidateID = tblCandidateArea.CandidateID) LEFT JOIN tblCandidateJobCode ON tblCandidate.CandidateID = tblCandidateJobCode.CandidateID) INNER JOIN ((tblBranchDetail RIGHT JOIN (tblCandidateAvailability INNER JOIN tblBooking ON tblCandidateAvailability.AvailableDate = tblBooking.ScheduleDate) ON tblBranchDetail.BranchID = tblBooking.BranchID) INNER JOIN tblBookingDetail ON tblBooking.ScheduleID = tblBookingDetail.ScheduleID) ON tblCandidate.CandidateID = tblCandidateAvailability.CandidateID
    WHERE (((tblCandidateAvailability.Status)='Available') AND ((tblBookingDetail.ScheduleDetailsID)=105) AND ((tblCandidateJobCode.JobCode)='DO') AND ((tblCandidateArea.PostCodeID)='HP'))].
    AS QUERY2 ON QUERY1.CandidateID = QUERY2.CandidateID) INNER JOIN [SELECT DISTINCT tblCandidate.CandidateID, tblCandidate.FirstName & ' ' & tblCandidate.Surname AS Name, tblCandidate.Telephone, tblCandidate.Mobile, tblCandidateAvailability.AvailableDate, tblCandidateAvailability.Status, tblBookingDetail.ScheduleDetailsID
    FROM ((tblCandidate LEFT JOIN tblCandidateArea ON tblCandidate.CandidateID = tblCandidateArea.CandidateID) LEFT JOIN tblCandidateJobCode ON tblCandidate.CandidateID = tblCandidateJobCode.CandidateID) INNER JOIN ((tblBranchDetail RIGHT JOIN (tblCandidateAvailability INNER JOIN tblBooking ON tblCandidateAvailability.AvailableDate = tblBooking.ScheduleDate) ON tblBranchDetail.BranchID = tblBooking.BranchID) INNER JOIN tblBookingDetail ON tblBooking.ScheduleID = tblBookingDetail.ScheduleID) ON tblCandidate.CandidateID = tblCandidateAvailability.CandidateID
    WHERE (((tblCandidateAvailability.Status)='Available') AND ((tblBookingDetail.ScheduleDetailsID)=106) AND ((tblCandidateJobCode.JobCode)='DO') AND ((tblCandidateArea.PostCodeID)='HP'))].
    AS QUERY3 ON QUERY2.CandidateID = [QUERY3].CandidateID;

    Note: The coloured text highlight the three nested queries.

    Thanks all,

    Daz

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •