[AccessD] A2003: Left Join problem

Steve,

...or you can add criteria to your date field to include NULLS in addition 
to the dates provided... DollarsDonated.ReceiptDate=null.


Mark

>From: "Gustav Brock" <Gustav at cactus.dk>
>Reply-To: Access Developers discussion and problem 
>solving<accessd at databaseadvisors.com>
>To: <accessd at databaseadvisors.com>
>Subject: Re: [AccessD] A2003: Left Join problem
>Date: Mon, 25 Apr 2005 19:13:28 +0200
>
>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.
>
>--
>AccessD mailing list
>AccessD at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/accessd
>Website: http://www.databaseadvisors.com