[AccessD] A2003: Left Join problem

Liz Doering ldoering at symphonyinfo.com
Mon Apr 25 12:29:34 CDT 2005


Steve,

If you have no donations for a particular category, the
DollarsDonated.ReceiptDate date is null.  So you are disallowing categories
with no data in that field.

Liz

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Steve Erbach
Sent: Monday, April 25, 2005 12:07 PM
To: Access Developers discussion and problem solving
Subject: [AccessD] A2003: Left Join problem

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.

What am I missing?


Regards,

Steve Erbach
Scientific Marketing
Neenah, WI
www.swerbach.com
Security Page: www.swerbach.com/security
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com





More information about the AccessD mailing list