Query Problem

[tom]

SELECT Invoice.[Invoice Number], Invoice.[Your Order #], Payables.[Supplier
Number]
FROM Payables right JOIN Invoice ON Payables.[Number] = Invoice.[Invoice
Number]
WHERE (((Invoice.[Your Order #]) Like "*TEXT*") AND ((Payables.[Supplier
Number])="TEXT1"))
ORDER BY Invoice.[Your Order #];

I want the query above to include all the invoices that
match text, some of these do and some of these dont
have a payable matching them. When the payable matches I want it to print
and when there is no matching
payable, I still want only the invoice to print.

The above query only prints invoices with matching
payables.

 

[Thomas.Koerfer]

The problem is that because of this:

AND ((Payables.[Supplier Number])="TEXT1"))

only those records are selected where the Supplier Number has Text1 in
it. But if there is no matching Payable there is nothing standing in
the supplier number

Hope this helps

Thomas

 

[tom]

The problem is that because of this:

AND ((Payables.[Supplier Number])="TEXT1"))

only those records are selected where the Supplier Number has Text1 in
it. But if there is no matching Payable there is nothing standing in
the supplier number

Hope this helps

Thomas

So how to get what I want?

 

[Ken Sheridan]

You can't restrict a query on both sides of an outer join and at the same
time return rows with no match on the inner side. By attempting to restrict
the query on the Supplier Number column from Payables you in effect turn it
into an inner join. What you are asking here is for the query to return all
invoices where the Order Number matches the pattern *TEXT* irrespective of
whether there is a matching row in Payables, while at the same time asking
it to return only those rows where the Supplier Number in Payables is Text1.
If you think about it these are logically incompatible with each other.

You should be able to get what you want by means of a Union operation,
however. Firstly return those rows where there are matches in both tables
where the Order Number and Supplier Number values match the criteria.
Secondly return those where the Order Number matches the criterion, but the
Payables.Number is Null, i.e. where there is no matching row in Payables:

SELECT Invoice.[Invoice Number], Invoice.[Your Order #],
Payables.[Supplier Number]
FROM Invoice INNER JOIN Payables
ON Invoice.[Invoice Number] = Payables.[Number]
WHERE Invoice.[Your Order #]) LIKE "*TEXT*"
AND Payables.[Supplier Number] = "TEXT1"
UNION ALL
SELECT Invoice.[Invoice Number], Invoice.[Your Order #],
NULL
FROM Invoice LEFT JOIN Payables
ON Invoice.[Invoice Number] = Payables.[Number]
WHERE Invoice.[Your Order #]) LIKE "*TEXT*"
AND Payables.[Number] IS NULL
ORDER BY Invoice.[Your Order #];

I notice that you are printing the results. If that means the query is the
RecordSource of a report then don't include an ORDER BY clause in the query.
Use the report's internal sorting and grouping mechanism instead.

Ken Sheridan
Stafford, England