[AccessD] A2003: Left Join problem

Gustav Brock Gustav at cactus.dk
Mon Apr 25 12:13:28 CDT 2005


Hi Steve

You need to move the filtering to a subquery. Then left join that and
not the unfiltered table.

/gustav


>>> erbachs at gmail.com 04/25 7:06 pm >>>
Dear Group,

The Access 2003 Help has this to say about LEFT JOINS:

"Left outer joins include all of the records from the first (left) of
two tables, even if there are no matching values for records in the
second (right) table."

I've done Left and Right Joins so many times over the years I can't
count them. Now I'm puzzled by one that should be butt-simple. Here's
the task: List all of the Donation categories and the sum of the
Donation amounts for a specific time period. Make sure that the
Donation category list contains every category whether there were
donations during the period or not.

Here's the SQL:

SELECT
  DonationCategories.CategoryDescr, 
  Sum(DollarsDonated.DonationAmount) as SumOfDonations 
FROM DonationCategories 
LEFT JOIN DollarsDonated 
ON DonationCategories.CategoryKey = DollarsDonated.CategoryKey
WHERE (((DollarsDonated.ReceiptDate) Between #1/1/2005# And
#3/31/2005#)) 
GROUP BY DonationCategories.CategoryDescr; 

There are 13 Donation Categories. The result set should have 13
records. It does not. It only has 11 for the categories where there
were donations in the period, it appears.




More information about the AccessD mailing list