[AccessD] A2003: Left Join problem

  It's a conflict to return all on one side but limit the other side with a
WHERE -- can't do it I don't think. 

Susan H. 

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; 

SELECT Species.SpecID, Sum(CBCData.countID) AS SumOfcountID
FROM Species LEFT JOIN CBCData ON Species.SpecID = CBCData.specID
GROUP BY Species.SpecID;
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