Create Query with criteria from multiple fields

[Gunti]

Hi,

I'm trying to create a query based on multiple fields..

I've got 4 fields, a number, name, adress

I want everything with number 000 to show up AND everything with adress X to
show up. Not everything with both.

I've actually never done any SQL so my access 'experience' is entirely done
with Design Mode.

gr & thanks for any help,
Mathijs

 

[Douglas J. Steele]

Sorry, does that mean if it has number 000 and address X you don't want to
see it, or did you mean "not just everything with both"?

If you want just those records with number 000 or with address X but not
those records that have number 000 and address X, you'd use something like:

WHERE (Number = "000" AND Address <> "X")
OR (Number <> "000" AND Address = "X")

If you want those records with number 000 or with Address X and you're
willing to accept those records that have both number 000 and address X,
you'd use

WHERE Number = "000" OR Address = "X"

(The reason I've got 000 in quotes is that the only way to have three
consecutive zeroes would be if it's a text field. If it's actually a numeric
field, all you can do is check for Number = 0, because 0, 00 and 000 are all
the same for numeric fields)

 

[Gunti]

I said not everything with both is because if you want everything which has
AND 000 AND adress X you can just put them behind eachother in a criteria bar.

I think you're on the right track except for the fact i have no idea how to
use your WHERE function.. Can you maybe give a simple explanation how to use
it? Meanwhile ill look for it aswell..

Thanks,
Gunti

 

[KARL DEWEY]

Then use the design view.
The logical 'OR' means eother to be true for results. Just place the
criteria on separate row.
For logical 'AND' which means both must be true for results place both
criteria in the same row.

Still not sure want you want out but here is a guess.
None that has both and (not logical AND) any that has only one of the
criteria.
Try this --
NUMBER ADDRESS
First criteria row -- "0000" <> "XXXX"
Second criteria row - <>"0000" "XXXX"

 

[John W. Vinson]

Hi,

I'm trying to create a query based on multiple fields..

I've got 4 fields, a number, name, adress

I want everything with number 000 to show up AND everything with adress X to
show up. Not everything with both.

This is one of the difficulties with the words AND and OR in Access (or any
other database) logic. They look like the English language conjunctions but
they're actually somewhat different.

You want OR logic in this case: put 000 on the first line of the grid under
number, and x on the *second* line under address.

This will retrieve the record if the number is 0, *OR* if the address is X.

AND and OR are actually "operators" in what's called Boolean Algebra, just as
+ and - are operators in arithmatic. They both take two arguments which must
be true/false expressions, and do the following operation:

True AND True = True
True AND False = False
False AND True = False
False AND False = False

True OR True = True
True OR False = True
False OR True = True
False OR False = False

So you need to just look at the two expressions you want to include in the
criteria. If they must both be TRUE in order to have the record shown use AND
logic (put them on the same line); if you want to see the record if either one
or the other is true, use OR logic (two lines).