Multiple Criteria

[DazJWood]

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. It doesn't really matter about the vb side of it because I can alter the construction easy enough. it is more a question of how I get the criteria right.

I'll show you the SQL below first:

SELECT DISTINCTROW tblCandidate.CandidateID, tblCandidate.FirstName & ' ' & [Surname] AS Name, tblCandidate.Telephone, tblCandidate.Mobile, tblCandidateAvailability.Status, tblCandidate.CandidateID AS [Count]
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')) OR (((tblCandidateAvailability.Status)='Available') AND ((tblBookingDetail.ScheduleDetailsID)=105) AND ((tblCandidateJobCode.JobCode)='DO') AND ((tblCandidateArea.PostCodeID)='DO')) OR (((tblCandidateAvailability.Status)='Available') AND ((tblBookingDetail.ScheduleDetailsID)=106) AND ((tblCandidateJobCode.JobCode)='DO') AND ((tblCandidateArea.PostCodeID)='HP'));

The table structuring is fine it is the SQL after the WHERE statement that I am having trouble with.

This query works fine to give me all Candidates that comply with these criteria. The only trouble is I want to only results where the Candidate(s) ALL three set of criteria. I have tried replacing the OR with AND and using extra brackets to enclose the whole WHERE statement to imply I need all criteria met but this reports back no results despite me having test data where a candidate meets all criteria.

I am sure this is not as hard as I am making it but I can't figure it out at all.

I also ideally wanted to show only one record (ie one candidate that meets all instead of three records showing the candidates). This is not so important as I can work this out easily enough, by probably hiding fields I don't want to see and showing only unique values, etc...

Can anyone help me with this?

Thanks in advance,

Daz

 

[FoFa]

WHERE (((tblCandidateAvailability.Status)='Available') AND ((tblBookingDetail.ScheduleDetailsID)=103) AND
 ((tblCandidateJobCode.JobCode)='DO') AND ((tblCandidateArea.PostCodeID)='HP')) OR 
(((tblCandidateAvailability.Status)='Available') AND ((tblBookingDetail.ScheduleDetailsID)=105) AND 
((tblCandidateJobCode.JobCode)='DO') AND ((tblCandidateArea.PostCodeID)='DO')) OR (((tblCandidateAvailability.Status)='Available') AND 
((tblBookingDetail.ScheduleDetailsID)=106) AND ((tblCandidateJobCode.JobCode)='DO') AND ((tblCandidateArea.PostCodeID)='HP'));

If you consider the 3 things you want met, it appears it is not possible.
The first part is asking for Available, 103, DO, HP and the third is asking for the SAME fields to be Available, 106, DO, HP AND the second one is asking the same fields to be Available, 105, DO, DO.
So the question becomes, how can the same field contain different values, in other words how can have Status = Available, DetailsID = 103 and 106, Jobcode = DO and Postcode = HP? DetailsID can't contain 2 values in one field. That is why AND's don't return anything. Nothing can match all that criteria (in one row anyway). Maybe if you explain differently?

 

[DazJWood]

Thanks, yeh I realise what you are saying and that is the quandry I guess.

i'll try explaining it differently.

I am thinking it may require a number of queries joined, but anyways let me try again.

I'll try explaining it in steps describing each query that can maybe be done in one:

I want a query that gives me the results based on this criteria:
tblCandidateAvailability.Status='Available') AND tblBookingDetail.ScheduleDetailsID=103 AND tblCandidateJobCode.JobCode='DO' AND tblCandidateArea.PostCodeID='HP'

I also want a query that gives me the results based on this criteria:
tblCandidateAvailability.Status='Available' AND tblBookingDetail.ScheduleDetailsID=105 AND tblCandidateJobCode.JobCode='DO' AND tblCandidateArea.PostCodeID='DO'

I also want a query that gives me the results based on this query:
tblCandidateAvailability.Status='Available' AND tblBookingDetail.ScheduleDetailsID=106 AND tblCandidateJobCode.JobCode='DO' AND tblCandidateArea.PostCodeID='HP'

Now say for instance that the results for each of these queries gives me a common CandidateID (or Cadidate Name). Say for instance Joe Smith appears on all three query results. I want a query that will calculate that Joe Smith meets all these criteria and he is displayed as the result, which means anyone else who appears as a result on the individual queries is not shown. In other words, Joe Smith matches ALL three criteria.

I get the feeling this might have to be done by constructing each query separately and then doing another query to compile these results. I think!

Any suggestions.

I hope that's a bit clearer.

Thanks in advance,

Daz

 

[neileg]

You still need to do some thinking. What are you going to do if Joe Smith and Dave brown both meet all of the criteria? What if nobody meets all of the criteria, but someone meets some of them? It's no good just having a single record returned if there are equally scoring candidates.

What you could do is create the three queries and then union them together. Then you can count the number of entries for each candidate to provide a ranking.

 

[FoFa]

OK, give this criteria a go and tell us how it worked out.

WHERE (tblCandidateAvailability.Status='Available' AND tblBookingDetail.ScheduleDetailsID in ("103","106") AND tblCandidateJobCode.JobCode='DO' AND tblCandidateArea.PostCodeID='HP')
OR (tblCandidateAvailability.Status='Available' AND tblBookingDetail.ScheduleDetailsID=105 AND tblCandidateJobCode.JobCode='DO' AND tblCandidateArea.PostCodeID='DO')

 

[DazJWood]

neileg,

I am not after a 'single' record returned. I never said anything about a single record. I am after any records where all three are matched as described. So if Joe Smith and Dave Brown meet all three then I want to see them both in the query results. If nobody meets the criteria then I want nothing returned obviously. If some people meet some of the criteria then I don't want to see them returned as this is not the requirement.

FoFa,

Thanks for your response. I can see though that this will not work. The OR statement for one will mean that it will return results where the criteria is true on the left side of the OR statement or the right side, when it needs to meet both.
Also by splitting out the criteria in the way you have done it means that the whole condition of say, tblCandidateAvailability.Status='Available' AND tblBookingDetail.ScheduleDetailsID=105 AND tblCandidateJobCode.JobCode='DO' AND tblCandidateArea.PostCodeID='DO' will now not be met as you have combined some elements into one. This would mean that possibly more records would be found that would go against the original requirement.

I am thinking that there is only one solution and that is to construct a union query on the three separate queries. I just imagined that there must have been another way.

If you have any other suggestions please let me know.

Thanks all,

Daz

 

[DazJWood]

OK so I now have the results working 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):

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'));

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

 

[DazJWood]

Anyone any ideas?

 

[neileg]

neileg,

I am not after a 'single' record returned. I never said anything about a single record. I am after any records where all three are matched as described. So if Joe Smith and Dave Brown meet all three then I want to see them both in the query results. If nobody meets the criteria then I want nothing returned obviously. If some people meet some of the criteria then I don't want to see them returned as this is not the requirement.

OK, I understand now.

FoFa,

Thanks for your response. I can see though that this will not work. The OR statement for one will mean that it will return results where the criteria is true on the left side of the OR statement or the right side, when it needs to meet both.
Also by splitting out the criteria in the way you have done it means that the whole condition of say, tblCandidateAvailability.Status='Available' AND tblBookingDetail.ScheduleDetailsID=105 AND tblCandidateJobCode.JobCode='DO' AND tblCandidateArea.PostCodeID='DO' will now not be met as you have combined some elements into one. This would mean that possibly more records would be found that would go against the original requirement.

I am thinking that there is only one solution and that is to construct a union query on the three separate queries. I just imagined that there must have been another way.

If you have any other suggestions please let me know.

Thanks all,

Daz

I see where FoFa hasn't given you what you want. I would go down the union route, as you suggest. You seem to think this is somehow not a preferred technique, but is think it's the only way you get the three specific sets of conditions matched. You may have to do a totals query based on the union to select those candidates with three results.

 

[DazJWood]

Thanks very much for your response Neil,

Am getting more confused now though.

In my last post I have the query now that gives me the correct results. The only problem is that I can't construct the final SQL query in vb because I am not sure how to reference the previous three queries.

Are you saying that I should try something else instead? And if so how do I do it? I am not sure what your post is giving me above what I last described?
Can you clarify?

Thanks,

Daz

 

[neileg]

Why do you want to build this in VBA? Why not build the querydef in Access and call this from your code. Saved querydefs are more efficient because they are complied, unlike SQL built in code.

 

[DazJWood]

Erm I am lost now.

I'm not sure I understand what querydefs are.

I am building the query on the fly in vb because the query always have different criteria because it depends on what selections are highlighted in a listbox. This means I can't have any fixed queries and it has to be built up on the fly. I suppose I could have a query and then just append the where statement to each one, but this still does not help me with building the final query on the fly.

I am really just after how I reference the constructed queries now in the final one. I am sure there are numerous more ways to do this and probably more economical ways in code to produce the queries, but I have it working now albeit I can't reproduce the final query on the fly.

Any ideas?

Thanks,

Daz

 

[DazJWood]

Anyone help?

 

[neileg]

A querydef is a query that is stored in the database and appears in the Query pane.

When you say the criteria are different, do you mean that you are testing on different fields or that the values in the criteria are different? I think you are making this more difficult than it really is. There are plenty of examples in the Sample Database forum of how to use a variety of criteria without building the SQL on the fly.

 

[DazJWood]

Thanks,

I can't use a querydef then because these queries cannot appear in the query pane as they will never be fixed queries. Other than appending the WHERE clause each time but this will always have to be built in SQL due to the dynamic nature of the required queries.

i have looked through the sample databases and cannot find anything that comes close to resolving this.

However I have been looking into nesting my queries. I think this could possibly be the answer although the syntax I'm using seem to always produce an error.


To test if nesting works I have been using just one of the queries, say TEST QUERY 1 and building it into the final query by nesting it and referencing it. Obviously if I could get this to work then I would replace the three queries by nesting them.

The example I have been trying to get to work with JUST ONE of the first queries as a test for nesting is:

SELECT SubQuery.CandidateID, SubQuery.Name, SubQuery.Telephone, SubQuery.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 SubQuery;

Note everything in bold is the nested query but I can't get this to run. I get a message saying

SELECT DISTINCT tblCandidate.CandidateID, tblCandidate.FirstName & ' ' & tblCandidate.Surname AS Name, tblCandidate.Telephone, tblCandidate.Mobile, tblCandidateAvailability.AvailableDate, tblCandidateAvailability.Status, tblBookingDetail.ScheduleDetailsID is not a valid name. Make sure that it does not include invalid characters or punctuation and that it is not too long.

If I could get this to work then I can nest my three queries in the original final query and it would work.

Can anyone tell me why I get the above error message? Am I wrongly nesting the query? Or is there a limit to the length of SQL that can be nested?

Thanks in advance guys,

Daz

 

[neileg]

I can't use a querydef then because these queries cannot appear in the query pane as they will never be fixed queries. Other than appending the WHERE clause each time but this will always have to be built in SQL due to the dynamic nature of the required queries.

i have looked through the sample databases and cannot find anything that comes close to resolving this.

You haven't answered my question. What makes you think that the the queries must be built in VBA? Which bit is dynamic?

 

[DazJWood]

The queries have to be built in SQL because the search screen could be searching on 1 set of criteria or 50 (as opposed to the three I have used as an example). I would have to copy and name 50 or more (as a contingency) in the query pane to allow for this. That's completely unfeasible and bad practise. I could equally have one query and append a set of criteria to the WHERE statement but even so I would have to create an array to load these queries into, that would still mean using vba anyways.

If I could figure out why my nested query produces errors I am sure I would have the answer.

 

[neileg]

Not true. You can set up one query so that it collects its parameters from text boxes on a form. You fill in any fields you want to select on and leave the others blank. As I said before, this technique is illustrated in the sample databases section.

Now if you really have 50 fields to search on, this might not be the most useful, but for smaller numbers, I can't see the problem.

 

[DazJWood]

Lol yes I am well aware of how to reference form listboxes or text boxes via a query, but as I keep saying this method will not work.And yes it may well be 50 sets of criteria needed. Lol I wouldn't say so if it wasn't a requirement. So as I keep saying I cannot do this with fixed queries. They DO need to be constructed on the fly in VBA.

I am gonna look along another route of dynamically creating real queries from the SQL on the fly and then deleting them afterwards as this could possibly be the way to reference them in my final query. It just seems weird that there isn't an easier way to do it solely in VBA, either via nesting or via referencing.

If anyone else has any ideas of how to achieve my FINAL query then please let me know.

Thanks,

Daz

 

[DazJWood]

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