How to make an Exception query

[Peter Hibbs]

Hi All

I have a query which returns all the company orders that are currently
outstanding, i.e. where OrderID is not null.

I have simplified the query a bit for this post -

SELECT tblCompany.Company, tblOrders.OrderDate, tblOrders.OrderID
FROM tblCompany INNER JOIN tblOrders ON tblCompany.ID = tblOrders.ID
GROUP BY tblCompany.Company, tblOrders.OrderDate, tblOrders.OrderID
HAVING (((tblOrders.OrderID) Is Not Null));

What I want to do is add a new table to the database (call it
tblExceptions with a field called CompanyName) which holds a list of
companies that would then be excluded from the list returned by the
query. The comparison would be between tblCompany.Company and
tblExceptions.CompanyName. Also I want to have a partial match using
the Like command so if the CompanyName field is set to British it
would exclude any companies that start with British, i.e. British Gas,
British Airways, etc.

Anyone know what mods I need to make to my query to accomplish this.

TIA.

Peter Hibbs.

 

[Guest]

Peter:

Firstly you don't need to use a GROUP BY clause. As there presumably can
only be one possible row per company per order grouping is superfluous (even
if not, SELECT DISTINCT would eliminate any duplication). Also testing for
NOT NULL OderIDs is also unnecessary as that can only be the case where a
match exists in tblOrders. As an INNER JOIN only returns rows where a match
exists only rows with NOT NULL OrderIDs will be returned without testing for
NOT NULL. To exclude companies from the exceptions list you can use the NOT
EXISTS predicate against a subquery. To restrict the result set use a LIKE
operation when correlating the subquery with the outer query:

SELECT tblCompany.Company, tblOrders.OrderDate, tblOrders.OrderID
FROM tblCompany INNER JOIN tblOrders ON tblCompany.ID = tblOrders.ID
WHERE NOT EXISTS
(SELECT *
FROM tblExceptions
WHERE tblCompany.Company LIKE
tblExceptions.CompanyName & "*");

Another thing you might like to note for future reference is that a HAVING
clause operates after grouping, so is used for things like 'all customers
*having* made orders totalling more than £100,000'. For a simple restriction
before grouping use a WHERE clause, e.g. ' total sum of all orders made by
each company *where* company location is UK'.

Ken Sheridan
Stafford, England

 

[Peter Hibbs]

Hi Ken

That is just what I wanted, thanks very much.

The reason for the unusual structure of the query was that, as I said
in my post, it was a cut down version of my original query which was
actually a Totals query and had another table attached. I think I can
now build it back, stage by stage, to my original requirement.

Peter.