Hi all,

I have a set of data which loosely resembles the following:

Table 1	-	Table 2
Team 1	-	Payments
Team 2	-	Payments
Team 3	-	Payments

I wish to create a query that will return the sum of all payments for each
team.  Where I get stuck is when a team has made no payments at all, in this
case the team is totally excluded.  I need all teams to be included whether
they have recieved payments or not.  Any ideas?

Thanks in advance
Ben



NOTICE AND DISCLAIMER:
This email (including attachments) is confidential.  If you have received
this email in error please notify the sender immediately and delete this
email from your system without copying or disseminating it or placing any
reliance upon its contents.  We cannot accept liability for any breaches of
confidence arising through use of email.  Any opinions expressed in this
email (including attachments) are those of the author and do not necessarily
reflect our opinions.  We will not accept responsibility for any commitments
made by our employees outside the scope of our business.  We do not warrant
the accuracy or completeness of such information.

you need to use an outer join.  Lookup the keyword LEFT JOIN...

-----Original Message-----
From: Ben Mildren [mailto:Ben.Mildren@t...]
Sent: Thursday, December 12, 2002 1:32 PM
To: Access
Subject: [access] Query Quandry!


Hi all,

I have a set of data which loosely resembles the following:

Table 1	-	Table 2
Team 1	-	Payments
Team 2	-	Payments
Team 3	-	Payments

I wish to create a query that will return the sum of all payments for each
team.  Where I get stuck is when a team has made no payments at all, in this
case the team is totally excluded.  I need all teams to be included whether
they have recieved payments or not.  Any ideas?

Thanks in advance
Ben



NOTICE AND DISCLAIMER:
This email (including attachments) is confidential.  If you have received
this email in error please notify the sender immediately and delete this
email from your system without copying or disseminating it or placing any
reliance upon its contents.  We cannot accept liability for any breaches of
confidence arising through use of email.  Any opinions expressed in this
email (including attachments) are those of the author and do not necessarily
reflect our opinions.  We will not accept responsibility for any commitments
made by our employees outside the scope of our business.  We do not warrant
the accuracy or completeness of such information.

Will thid do it?

SELECT
     tblTeams.Team,
    Sum(tblPayments.PaymentAmount) AS SumOfPaymentAmount
FROM
    tblTeams
LEFT JOIN
    tblPayments ON tblTeams.TeamID = tblPayments.TeamID
GROUP BY
     tblTeams.Team;

The left outer join pulls records from the teams table whether they
have payment records in the payments table or not.


>From: Ben Mildren <Ben.Mildren@t...>
>Reply-To: "Access" <access@p...>
>To: "Access" <access@p...>
>Subject: [access] Query Quandry!
>Date: Thu, 12 Dec 2002 19:32:01 -0000
>
>Hi all,
>
>I have a set of data which loosely resembles the following:
>
>Table 1	-	Table 2
>Team 1	-	Payments
>Team 2	-	Payments
>Team 3	-	Payments
>
>I wish to create a query that will return the sum of all payments for each
>team.  Where I get stuck is when a team has made no payments at all, in 
>this
>case the team is totally excluded.  I need all teams to be included whether
>they have recieved payments or not.  Any ideas?
>
>Thanks in advance
>Ben
>
>
>
>NOTICE AND DISCLAIMER:
>This email (including attachments) is confidential.  If you have received
>this email in error please notify the sender immediately and delete this
>email from your system without copying or disseminating it or placing any
>reliance upon its contents.  We cannot accept liability for any breaches of
>confidence arising through use of email.  Any opinions expressed in this
>email (including attachments) are those of the author and do not 
>necessarily
>reflect our opinions.  We will not accept responsibility for any 
>commitments
>made by our employees outside the scope of our business.  We do not warrant
>the accuracy or completeness of such information.
>
>


_________________________________________________________________
The new MSN 8: advanced junk mail protection and 2 months FREE* 
http://join.msn.com/?page=features/junkmail