Parameter query..multiple criteria..AND....multiple fields...Access 97 1

[BLPandAJF]

Hi -<br><br>A simplified version of my challenge is this...<br><br>I want to use a parameter query to select records that match two different Field2/Field 3 criteria.<br><br>Field1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Field2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Field3<br>lot #&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;marker&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;interpretation<br><br>1234&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;apple&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;positive<br>1234&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;banana&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;negative<br>1234&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;pear&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;positive<br>5678&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;apple&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;positive<br>5678&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;banana&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;positive<br>5678&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;pear&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;negative<br>2222&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;apple&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;negative<br>2222&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;banana&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;negative<br>2222&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;pear&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;negative<br><br><br>So, I want to see which lot #'s are apple/positive AND banana/negative (this would be lot #1234 but NOT lot #5678 or 2222).&nbsp;&nbsp;I tried doing a subquery to first find apple/positive but then my final query showed me ONLY banana negative (although for the correct lot #).&nbsp;&nbsp;Also, I don't want to see any other records related to the lot # (no &quot;pear&quot; info).<br><br>I hope this makes sense to someone.&nbsp;&nbsp;<br><br>Thank you.

 

[famousb]

<b>IIf([Field2]=&quot;apple&quot;, &quot;positive&quot Or IIf([Field2]=&quot;banana&quot;, &quot;negative&quot</b><br><br>put this into the criteria of Field3 - this should work. <br><br> <p>Brian Famous<br><a href=mailto:bfamous@ncdoi.net>bfamous@ncdoi.net</a><br><a href= > </a><br>

 

[Zelandakh]

select field1 from table where ((field2=&quot;apple&quot; and field3=&quot;positive&quot or (field2=&quot;banana&quot; and field3=&quot;negative&quot);<br><br>Your problem was probably brackets due to implicit operators. I guess.<br><br>

 

[BLPandAJF]

I tried both suggestions but I am still unable to get all the results I need.<br><br>Zelandakh, your suggestion worked great as an &quot;OR&quot; criteria but when I tried to make it into an &quot;AND&quot; criteria, it didn't work.&nbsp;&nbsp;Any other ideas?

 

[rochelle]

SELECT Table1.Field1, Count(Table1.Field1) AS CountOfField1<br>FROM Table1<br>WHERE (((Table1.Field2)=&quot;apple&quot AND ((Table1.Field3)=&quot;positive&quot) OR (((Table1.Field2)=&quot;banana&quot AND ((Table1.Field3)=&quot;negative&quot)<br>GROUP BY Table1.Field1<br>HAVING (((Count(Table1.Field1))&gt;1));<br><br>If your table name is Table1, this will work.<br>Copy it into the SQL view of a new query and then convert it to design view to see how it works.<br>