Unmatched Query

  • Thread starter Thread starter APH
  • Start date Start date
A

APH

Hi can someone help me please

I have a table tblorganisations, which contains contact information Primary
key fried is RecordId

I also have table tblSales, which contains sales information. I have a field
OrganisationsId and also Product

I have created a standard Unmatched Query which displays all records in
tblOrganisations with no matching records in tblSales.

However, what I want to try and do is to show all records in
tblOrganisations with no matching records in tblSales where tblSales.Product
is not equal to a "xyz"

is that possible

Thanks

Alex
 
One way would be to make 2 queries. In the first query add the tblSales
table and set the criteria for Product to <> "xyz", then base the unmatched
query on this query instead of on the tblSales table.

The result of this will show all records in tblOrganisations that have no
records in tblSales -or- only have records related to the xyz product.
 
Alex

I'm not quite clear on what you are trying to find. Could you re-state it
in English?

Are you saying you want to find Organizations that have sold anything
besides product "xyz"? Or that have sold none of "xyz"? or ...?
 
Hi,


Sure. First make a query

SELECT * FROM Sales WHERE Product <> 'xyz'


then, use THAT saved query as table you are looking into for a match (or no
match).


In a query, the WHERE clause is executed AFTER the JOIN, if you try,
directly:

SELECT Organisations.*
FROM Organisations LEFT JOIN Sales
ON Organisations.OrgID = Sales.OrgID
WHERE Sales.OrgID IS NULL
AND Sales.Product <> 'xyz'


the extra condition would occur too late (the join has already been made),
and furthermore, the unmatching records would return NULL under all the
field supplied by Sales, that includes Sales.Product. Making the query for
the condition, then


SELECT Organisations.*
FROM Organisations LEFT JOIN myQy
ON Organisations.OrgID = myQy.OrgID
WHERE myQy.OrgID IS NULL


would do the job, in the "right order", ie, the WHERE clause of myQy is
executed first, to "build" the data returned by myQy, then, the JOIN in the
calling query will be made, exactly as we wanted it.



Hoping it may help,
Vanderghast, Access MVP
 
Back
Top