[AccessD] A2003: Left Join problem

You can try this:

SELECT
  DonationCategories.CategoryDescr,
  temp1.SumOfDonations
FROM DonationCategories
LEFT JOIN

(SELECT
  DollarsDonated.CategoryKey,
  Sum(DollarsDonated.DonationAmount) as SumOfDonations
FROM DollarsDonated
WHERE (((DollarsDonated.ReceiptDate) Between #1/1/2005# And #3/31/2005#))
GROUP BY DollarsDonated.CategoryKey) AS temp1

ON DonationCategories.CategoryKey = temp1.CategoryKey



Jeffrey F. Demulling
Project Manager
U.S. Bank Corporate Trust Services
60 Livingston Avenue
EP-MN-WS3C
St. Paul, MN  55107-2292
Ph: 651-495-3925
Fax: 651-495-8103
email: jeffrey.demulling at usbank.com


                                                                           
             "Steve Erbach"                                                
             <erbachs at gmail.co                                             
             m>                                                         To 
             Sent by:                  "Access Developers discussion and   
             accessd-bounces at d         problem solving"                    
             atabaseadvisors.c         <accessd at databaseadvisors.com>      
             om                                                         cc 
                                                                           
                                                                   Subject 
             04/25/2005 12:06          [AccessD] A2003: Left Join problem  
             PM                                                            
                                                                           
                                                                           
             Please respond to                                             
                  "Access                                                  
                Developers                                                 
              discussion and                                               
             problem solving"                                              
             <accessd at database                                             
               advisors.com>                                               
                                                                           
                                                                           




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




------------------------------------------------------------------------------
Electronic Privacy Notice. This e-mail, and any attachments, contains information that is, or may be, covered by electronic communications privacy laws, and is also confidential and proprietary in nature. If you are not the intended recipient, please be advised that you are legally prohibited from retaining, using, copying, distributing, or otherwise disclosing this information in any manner. Instead, please reply to the sender that you have received this communication in error, and then immediately delete it. Thank you in advance for your cooperation.
==============================================================================