criteria question

  • Thread starter Thread starter me
  • Start date Start date
M

me

Query uses 4 databases that are interelated.

Need to select a "company" number in "company table" and NOT select from
various "terminals" from the 'terminals table'.

When I run query with one number in company field (1301) among 6 other "not"
criterial in terminals field, I get the correct "not terminals" listing but
have a whole slew of 1301 & others. Is there a way to further isolate the
query to make it work?

Any help appreciated.

Dave
 
Query uses 4 databases that are interelated.

Need to select a "company" number in "company table" and NOT select from
various "terminals" from the 'terminals table'.

When I run query with one number in company field (1301) among 6 other "not"
criterial in terminals field, I get the correct "not terminals" listing but
have a whole slew of 1301 & others. Is there a way to further isolate the
query to make it work?

Any help appreciated.
Click to expand...

You're assuming that we know the structure of your table, and that
your verbal description conveys the actual structure of your query.
Neither assumption is correct!

Could you please post the SQL view of the query, and some description
of what "the terminals field" means? What results do you expect, vs.
what are you getting?
 
Sure:

SELECT Company.NU_COMPANY_ID, Terminals.VC_LOCATION, Driver.VC_FIRST_NAME,
Driver.VC_LAST_NAME, Driver.NU_SSN, Driver.VC_LICENSE_NUMBER
FROM Company INNER JOIN (Terminals INNER JOIN (Regions INNER JOIN Driver ON
Regions.NU_REGION_ID = Driver.NU_REGION_ID) ON Terminals.NU_TERMINAL_ID =
Driver.NU_TERMINAL_ID) ON Company.NU_COMPANY_ID = Regions.NU_COMPANY_ID
WHERE (((Company.NU_COMPANY_ID)="1301") AND ((Terminals.VC_LOCATION)<>"102
Albany GA")) OR (((Terminals.VC_LOCATION)<>"105 - Lebanon, TN")) OR
(((Terminals.VC_LOCATION)<>"121-Dallas")) OR
(((Terminals.VC_LOCATION)<>"130- Allentown, PA")) OR
(((Terminals.VC_LOCATION)<>"2100 Covington")) OR
(((Terminals.VC_LOCATION)<>"2200 Youngwood, PA")) OR
(((Terminals.VC_LOCATION)<>"2300 DeForest")) OR
(((Terminals.VC_LOCATION)<>"510 - Indianapolis, IN"))
ORDER BY Terminals.VC_LOCATION;

A company can have many terminals. You can have many companies though. I
don't want other companies listed among my "1301".
 
A company can have many terminals. You can have many companies though. I
don't want other companies listed among my "1301".
Click to expand...

The OR logic isn't going to give you what you want:
SELECT Company.NU_COMPANY_ID, Terminals.VC_LOCATION,
Driver.VC_FIRST_NAME,
Driver.VC_LAST_NAME, Driver.NU_SSN, Driver.VC_LICENSE_NUMBER
FROM Company INNER JOIN (Terminals INNER JOIN (Regions INNER JOIN
Driver ON
Regions.NU_REGION_ID = Driver.NU_REGION_ID) ON
Terminals.NU_TERMINAL_ID =
Driver.NU_TERMINAL_ID) ON Company.NU_COMPANY_ID =
Regions.NU_COMPANY_ID
WHERE (((Company.NU_COMPANY_ID)="1301") AND
((Terminals.VC_LOCATION)<>"102
Albany GA")) OR (((Terminals.VC_LOCATION)<>"105 - Lebanon, TN")) OR
(((Terminals.VC_LOCATION)<>"121-Dallas")) OR
(((Terminals.VC_LOCATION)<>"130- Allentown, PA")) OR
(((Terminals.VC_LOCATION)<>"2100 Covington")) OR
(((Terminals.VC_LOCATION)<>"2200 Youngwood, PA")) OR
(((Terminals.VC_LOCATION)<>"2300 DeForest")) OR
(((Terminals.VC_LOCATION)<>"510 - Indianapolis, IN"))
ORDER BY Terminals.VC_LOCATION;

If the LOCATION field is "102 Albany GA" then the first OR clause will
be FALSE - BUT if it's "102 Albany GA" then it is NOT equal to
"121-Dallas", so the third one will be TRUE, and the record will be
retrieved. OR means "retrieve the record if any one of these
conditions is true"!

Either change all the OR's to AND's, or use the NOT IN syntax:

SELECT Company.NU_COMPANY_ID, Terminals.VC_LOCATION,
Driver.VC_FIRST_NAME,
Driver.VC_LAST_NAME, Driver.NU_SSN, Driver.VC_LICENSE_NUMBER
FROM Company INNER JOIN (Terminals INNER JOIN (Regions INNER JOIN
Driver ON
Regions.NU_REGION_ID = Driver.NU_REGION_ID) ON
Terminals.NU_TERMINAL_ID =
Driver.NU_TERMINAL_ID) ON Company.NU_COMPANY_ID =
Regions.NU_COMPANY_ID
WHERE Company.NU_COMPANY_ID="1301" AND Terminals.VC_LOCATION NOT
IN("102 Albany GA", "105 - Lebanon, TN", "121-Dallas", "130-
Allentown, PA", "2100 Covington", "2200 Youngwood, PA", "2300
DeForest", "510 - Indianapolis, IN")
ORDER BY Terminals.VC_LOCATION;
 
Back
Top