Right Outer Join in Access 2000 1

[WBH1138]

Hi

this should be easy but I must be missing something.

Here's my query...

SELECT TRIM(Ac.[Accounts category text]),
SUM(T.[Net value]),
SUM(T.[Vat amount]),
SUM(T.[Amount in currency])
FROM Transactions AS T RIGHT OUTER JOIN [Accounts category] AS Ac ON T.[Accounts category ID] = Ac.[Accounts category ID]
WHERE T.[Invoice date] BETWEEN #06/01/2007 00:00:00# AND #06/30/2007 23:59:59#
AND T.[Client department ID] = 1180434562
AND T.[Currency abbreviation] <> 'IGN'
AND T.[Procedure ID] = 2
GROUP BY Ac.[Accounts category text]
ORDER BY Ac.[Accounts category text]

I want the query to list all of the Account Categories, regardless of whether there have been any Transactions relating to them.
But I only get Accounts Categories with Transactions???

Any ideas?

 

[cmmrfrds]

Assuming the T transaction table has a primary key, say t_id then change your where clause to ask for the records where the category does not have a transaction record. Otherwise, it is effectively an equal join because the where clause as is includes restrictions on the transaction table. For instance, nulls transaction records cannot have a procedure ID = 2.

WHERE (T.[Invoice date] BETWEEN #06/01/2007 00:00:00# AND #06/30/2007 23:59:59#
AND T.[Client department ID] = 1180434562
AND T.[Currency abbreviation] <> 'IGN'
AND T.[Procedure ID] = 2) or t_id is null

 

[WBH1138]

I did wonder if the fact I was asking for Transaction records to meet certain criteria had an effect.

Many thanks!