long long query

[khong]

Hello Again

I need help (yet again) with queries. I have a long long query and I cannot figure out how to make it.

I need to find records who [(have answered "1" to Q6 AND HAVE NOT left Q6A or Q6B or Q6C or Q6D or Q6E blank) OR (have answered "0" to Q6 AND HAVE left the others blank) or (have answered "1" to Q6B AND HAVE NOT left Q6D blank)]

Is it possible to make that into one query. Can anyone help me make this query?

Another question, in Microsoft Access 2000, how many "OR" ROWS can one have?

Thanking every one in here for all their previous help.

Gratefully

Khong

 

[JimHorton]

Khong,
That query is doable, you just have to be careful of how you parenthisize (if that's even a word!) and how you use 'and' and 'or'. I don't know of any logical limit for the number of or's & ands, but I seem to remember a 64K limit to the query, which I don't believe refered to the sql text, but the compiled execution plan, which could be much bigger than the sql text.

I've found though, that it's sometimes easier to manage stuff like that via DAO, ie a recordset. It's visually easier to use a Select Case statement or If's & Else's, along with maybe another Yes/No field where you can mark as 'Selected', so, ie, if you have a huge list of these criteria, once the record 'passes' a criteria test, it's marked, you move to the next record, and so on. Then when done, open a simple query based on the 'Selected' field.

This is so much more easy to add/change/remove crieteria, and easier to visually see exactly what is going on then to see a huge page of sql text with a thousand parenthesis, brackets, and's & or's, etc.
--Jim

 

[tlbroadbent]

Here is one solution. This works in Access 2000. I haven't tested it in other versions. It's not even too long. The key is the way I've used the IN and Not In statements.

SELECT Questions.*
FROM Questions
WHERE (((Questions.Q6)="1&quot
AND (("&quot Not In (Nz([Q6A],"&quot,Nz([Q6B],"&quot,Nz([Q6C],"&quot,Nz([Q6D],"&quot,Nz([Q6E],"&quot)))
OR (((Questions.Q6)="0&quot
AND (("&quot In (Nz([Q6A],"&quot,Nz([Q6B],"&quot,Nz([Q6C],"&quot,Nz([Q6D],"&quot,Nz([Q6E],"&quot)))
OR (((Questions.Q6B)="1&quot
AND ((nz([Q6D],&quot;&quot)<>&quot;&quot);
Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.