Selecting two ranges on two fields in same query

[adamsjw2]

Greetings,
This is a follow-up to an earlier post about a similar issue, but now
the query is a little more complicated.

I have a mdb of clients. Each client has a diagnosis on Axis1-1 and
Axis 1-2. I want to select only those clients whose diagnosis falls
within two differents ranges of values, on either Axis1-1 or Axis 1-2.
When I run a query on Axis1-1 alone, my total records are 358
when I run a query on Axis 1-2 alone, my total reacords are 237.

When I try to run one query, then my totals is 403.
Here's the SQL statement for this last query.

SELECT ClientDataSet.DiagAxis1one, ClientDataSet.DiagAxis1two,
ClientDataSet.SSN

FROM ClientDataSet

WHERE (((ClientDataSet.DiagAxis1one) Between "291.00" And "292.90"))
OR (((ClientDataSet.DiagAxis1one) Between "303.00" And "305.93"))

OR

(((ClientDataSet.DiagAxis1two) Between "303.00" And "305.93")) OR
(((ClientDataSet.DiagAxis1two) Between "291.00" And "292.90"));

Any suggestions greatly appreciated,
Jim

 

[John Spencer (MVP)]

Is it possible that you have records where the value for DiagAxis1One and
DiagAxis1Two both fall into the range you are querying. If so, then even though
you match both criteria, you will only get the row returned one time.

For instance, a record where the values were
DiagAxis1One = 291.00
DiagAxis1Two = 303.00
would return one record in each of your separate queries and also ONLY one
record in the combined query.

 

[Jim]

Is it possible that you have records where the value for DiagAxis1One and
DiagAxis1Two both fall into the range you are querying. If so, then even though
you match both criteria, you will only get the row returned one time.

For instance, a record where the values were
DiagAxis1One = 291.00
DiagAxis1Two = 303.00
would return one record in each of your separate queries and also ONLY one
record in the combined query.

John,
You're guess is correct and that explains the discrepancies.
As a side note, are there any good books/resources dedicated to Access
and queries in particular? I have some SQL stuff and I use Crystal
Reports, but I must admit, Access 2002's query tool is very powerful,
if not somewhat "non-intuitive."
Thanks so much for your taking the time or a quick and courteous
response.
Jim

 

[John Spencer (MVP)]

Lots of books --- but I won't give advice on which is "good" or "bad". I have
some favorites, but whether they are good or bad depends on YOUR level of
expertise and needs.

SQL for Mere Mortals (not Access specific)
Access XXXX Developer's Handbook (Advanced)
Rebecca Riordan's (Don't recall the title right now) book on Access development

If you can, go to your local bookstore and browse their books on the subject.
Look through them, look for solutions to your current problems, and pick out the
one that seems to answer your questions at a level you can understand.