Multiple Criteria within related tables.

[Guest]

I am having trouble setting the criteria to extract data from a table where
some of the the criteria is dependent on a related table.
Here are the relevant fields in the 2 tables
..
Table1:
lng_PassengerID
str_mode
str_status
lng_townID

Table2:
lng_townID
str_townname
lng_postcode

These tables are joined with lng_townID being the common field.

str_mode is either: plane, boat, train, OR car
str_status is either: waiting, planned, tasked OR cancelled

I want to return all records from table1 EXCLUDING those where
table1.str_status = cancelled OR waiting, AND EXCLUDING those where
table1.str_mode = boat, AND EXCLUDING those where table1.str_mode = car with
related table2.lng_postcode between 3000 and 4000

The first two lots of criterea I can handle but the "AND EXCLUDING those
where table1.str_mode = car with related table2.lng_postcode between 3000 and
4000" has got me beat!! Is it possible to do all of this within one query? I
am thinking that I may need to use some SQL as criteria but I'm not having
much success. Any help or guidance would be greatly appreciated.

 

[Guest]

Try:

SELECT lng_PassengerID, str_status, str_mode, Table1.lng_townID, lng_postcode
FROM Table1 INNER JOIN Table2 ON Table1.lng_townID = Table2.lng_townID
WHERE (((str_status <> "cancelled") AND (str_status <> "waiting") AND
(str_mode <> "boat")) AND NOT ((str_mode = "car") AND
(lng_postcode BETWEEN 3000 AND 4000)));

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.

 

[Guest]

Thankyou!! Works fantastic! Really appreciated.