Hi all
Been stuck joining a couple of tables and showing related fields in a report. I have two groups: Applicants and Sponsors. Records in each can deposit money into one account recorded as tblTrustAccount. When I run a report, only the Applicant's funds show, and I cannot join a "Trading Name" field from the Sponsor's table. I only want to show the report when there is any balance other than zero. For Applicants I want to show their names, ID and amount. For Sponsors, their Trading Names, ID, and amount.
SQL is currently:
SELECT tblTrustAccount.ClientID, Applicants.GivenNames, Applicants.LastName, tblTrustAccount.SponsorID, Sponsor.TradingName, Sum(tblTrustAccount.Amount) AS SumOfAmount
FROM Applicants INNER JOIN tblTrustAccount ON Applicants.ClientID = tblTrustAccount.ClientID
GROUP BY tblTrustAccount.ClientID, Applicants.GivenNames, Applicants.LastName, tblTrustAccount.SponsorID, Sponsor.TradingName
HAVING (((Sum(tblTrustAccount.Amount))<>0));
Obviously, the above is incomplete. I believe I need a separate join for Sponsors, but do not know how to do this. When I change the FROM clause to tblTrustAccount to add a second join, I get zero results:
SELECT tblTrustAccount.ClientID, Applicants.GivenNames, Applicants.LastName, tblTrustAccount.SponsorID, Sponsor.TradingName, Sum(tblTrustAccount.Amount) AS SumOfAmount
FROM (tblTrustAccount INNER JOIN Applicants ON Applicants.ClientID = tblTrustAccount.ClientID) INNER JOIN Sponsor ON Sponsor.SponsorID = tblTrustAccount.SponsorID
GROUP BY tblTrustAccount.ClientID, Applicants.GivenNames, Applicants.LastName, tblTrustAccount.SponsorID, Sponsor.TradingName
HAVING (((Sum(tblTrustAccount.Amount))<>0));
Assistance greatly appreciated.