multiple criteria

[tcgaines]

hi. my name is tim. im having some problems with a query that I am writing. it should be rather simple and involves only one table.

the table is called tblCustomer. The query involves 2 fields: cResort and cWeek. Neither of these fields are unique. Together they make up the primary key.

cResort is populated with a textfield: PF, PV, and GS.
cWeek is populated with an integer: 1-52 (the number of weeks in a year)

This is what the query is supposed to do:
SELECT tblCustomer.*
FROM tblCustomer
WHERE cResort DOES NOT EQUAL "PF" AND WEEK is LESS THAN 10

here's where the problem lies, this filters out ALL "PF" and ALL cWeek < 10.

I want PF's with a corresponding cWeek from 1-10. I dont want to see PFs with corresponding cWeek 11-52.

Example:
Show me cResorts: PF with corresponding cWeek:1-10.
Do NOT show me cResorts: PF with corresponding cWeek:11-52.

Is this clear? Thank you for the help.

 

[tcgaines]

perhaps i need a nest query?

 

[tcgaines]

SELECT tblCustomer.cResort, tblCustomer.cUnit, tblCustomer.cWeek, tblCustomer.cLName, tblCustomer.cFName, tblCustomer.cAddress, tblCustomer.cCity, tblCustomer.cState, tblCustomer.cZipCode, tblCustomer.cAreaCode, tblCustomer.cPhoneNum, tblCustomer.cYear
FROM tblCustomer
WHERE (tblCustomer.cResort <> "PF" AND tblCustomer.cWeek<11);

This removes ALL Records where cResort=PF and ALL Records where cWeek>11.

I only want to remove the PFs that are 1-10, NOT PF 11-52.

 

[john471]

Hi Tim,

Looks like you're having a nice little conversation with yourself here.....

ONE way to do this would be to create two queries.

First:
Select the records you want to exclude

qryExclude:-
------------
SELECT tblCustomer.cResort, tblCustomer.cWeek
FROM tblCustomer
WHERE (tblCustomer.cResort = "PF" AND tblCustomer.cWeek<11);

Secondly:
Write an unmatched query to return the complement of the excluded set. The un-matched query wizard should get you 80% of the way there. I suspect you'll have fun working out the last 20% yourself. If you need help, post back.

Looking at your above select statement, I strongly suspect you also have some design issues, but I will limit this post to the topic of your question.

 

[john471]

Why is it that when replying with "post quick reply", the post does not show up an an advanced search of all posts by one's username ?
This post is just so I can find the thread again.

 

[Pat Hartman]

The problem revolves around how humans speak and how computers work. When you have a compound condition that includes AND with OR or NOT, you most likely will need to use parentheses to accomplish your intent. I find it easier to write the positive statement and then exclude that set.

You SAID - WHERE cResort DOES NOT EQUAL "PF" AND WEEK is LESS THAN 10
But you MENT - WHERE the combination of cResort EQUALs "PF" AND WEEK is LESS THAN 10 is not true.

One query will do it.

WHERE Not (cResort = "PF" AND WEEK < 10);

 

[tcgaines]

Perfect Pat Hartman. Your suggestion works nicely. I also find great value in your comment about writing the positive first.

Thank You.