Two joins in a report

[apk19]

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.

[CJ_London]

implication is you don't have any sponsor records with a matching ID in tblTrustAccount

try changing the second join to a left join - right click on join line and select 'include all records from tblTrustAccounts...'

[AccessToGo]

Hi, It sounds like a UNION would do the trick...
In new query SQL view
SELECT [LastName] & " " & [GivenName], [ID], [amount] FROM [Applicant]
UNION ALL
SELECT [TradingName], [ID], [amount] FROM [Sponsor]
ORDER BY 1

Plus whatever WHERE conditions on each should limit results

Hope I've understood your question.

Regards
Chris

[apk19]

implication is you don't have any sponsor records with a matching ID in tblTrustAccount

try changing the second join to a left join - right click on join line and select 'include all records from tblTrustAccounts...'

LEFT JOIN seems to have solved the issue of the disappearing results, however, the Sponsor's number's don't show. Currently have:

SELECT tblTrustAccount.ClientID, Applicants.GivenNames, Applicants.LastName, tblTrustAccount.SponsorID, Sponsor.TradingName, Sum(tblTrustAccount.Amount) AS SumOfAmount
FROM (tblTrustAccount INNER JOIN Applicants ON tblTrustAccount.ClientID = Applicants.ClientID) LEFT JOIN Sponsor ON tblTrustAccount.SponsorID = Sponsor.SponsorID
GROUP BY tblTrustAccount.ClientID, Applicants.GivenNames, Applicants.LastName, tblTrustAccount.SponsorID, Sponsor.TradingName
HAVING (((Sum(tblTrustAccount.Amount))<>0));

[CJ_London]

if by sponsor number you mean sponserid, it wont appear if it doesn't exist - or you've hidden the column. If this is still an issue, post some data from your tables and show the relationships

[apk19]

Solved it. My report design was flawed. Instead of attempting to combine two joins, it was far easier to combine two reports.

Report 1:
SELECT tblTrustAccount.ClientID, Applicants.GivenNames, Applicants.LastName, Sum(tblTrustAccount.Amount) AS SumOfAmount
FROM tblTrustAccount INNER JOIN Applicants ON tblTrustAccount.ClientID = Applicants.ClientID
GROUP BY tblTrustAccount.ClientID, Applicants.GivenNames, Applicants.LastName
HAVING (((Sum(tblTrustAccount.Amount))<>0));


Report 2:
SELECT tblTrustAccount.SponsorID, Sponsor.TradingName, Sum(tblTrustAccount.Amount) AS SumOfAmount
FROM tblTrustAccount INNER JOIN Sponsor ON tblTrustAccount.SponsorID = Sponsor.SponsorID
GROUP BY tblTrustAccount.SponsorID, Sponsor.TradingName
HAVING (((Sum(tblTrustAccount.Amount))<>0));

Simply added both reports into another report. Had a total for both as well.

Silly me!