Please Fix This Query

[Guest]

I have a query that essentially pulls 7 different types of numbers from 2
different tables. It also calculates the difference, if any, between the 2
numbers in any of the 7 types. For example, it takes the deposit portfolio $
from Table 1 and subtracts the deposit portfolio $ from Table 2 to see if
they are identical. If they're not, then there is a difference between the
two.

The two tables are (1) Managers and (2) Assistant Managers for a bank with
11 different branches. The only problem is, when a particular bank has an
Assistant Manager but no Manager (or vice versa), the $ for that branch do
not appear in the results. How can I force the query to display results for
each and every branch, even if that branch does not have both a Manager and
an Assistant Manager? I've tried using Nz without success, and I've pasted
the SQL below.

Thanks!

SELECT Branches.Branch_Number,
Format([Assistant_Retail_Manager_Activities].[Month],"mmmm") AS [Month],
Nz([Retail_Manager],"None") AS RM, Nz([Assistant_Retail_Manager],"None") AS
ARM, Retail_Manager_Activities.Deposits_Outstanding AS [RM Dep Port],
Assistant_Retail_Manager_Activities.Deposits_Outstanding AS [ARM Dep Port],
CCur(Nz([RM Dep Port]-[ARM Dep Port],0)) AS [Dep Port Comp],
Retail_Manager_Activities.New_Deposits AS [RM New Dep],
Assistant_Retail_Manager_Activities.New_Deposits AS [ARM New Dep],
CCur(Nz([RM New Dep]-[ARM New Dep],0)) AS [New Dep Comp],
Retail_Manager_Activities.Security_Referrals AS [RM Sec],
Assistant_Retail_Manager_Activities.Security_Referrals AS [ARM Sec],
CCur(Nz([RM Sec]-[ARM Sec],0)) AS [Sec Comp],
Retail_Manager_Activities.Trust_Referrals AS [RM Trust],
Assistant_Retail_Manager_Activities.Trust_Referrals AS [ARM Trust],
CCur(Nz([RM Trust]-[ARM Trust],0)) AS [Trust Comp],
Retail_Manager_Activities.Mortgage_Closed AS [RM Mort],
Assistant_Retail_Manager_Activities.Mortgage_Closed AS [ARM Mort],
CCur(Nz([RM Mort]-[ARM Mort],0)) AS [Mort Comp],
Retail_Manager_Activities.Loan_Portfolio_Outstanding AS [RM Loan Port],
Assistant_Retail_Manager_Activities.Loan_Portfolio_Outstanding AS [ARM Loan
Port], CCur(Nz([Rm Loan Port]-[ARM Loan Port],0)) AS [Loans Port Comp],
Retail_Manager_Activities.New_Loans_Funded AS [RM New Loans],
Assistant_Retail_Manager_Activities.New_Loans_Funded AS [ARM New Loans],
CCur(Nz([RM New Loans]-[ARM New Loans],0)) AS [New Loans Comp]
FROM Branches INNER JOIN (Assistant_Retail_Manager_Activities INNER JOIN
Retail_Manager_Activities ON (Assistant_Retail_Manager_Activities.Month =
Retail_Manager_Activities.Month) AND
(Assistant_Retail_Manager_Activities.[Branch#] =
Retail_Manager_Activities.[Branch#])) ON (Branches.Branch_Number =
Assistant_Retail_Manager_Activities.[Branch#]) AND (Branches.Branch_Number =
Retail_Manager_Activities.[Branch#])
WHERE
(((Format([Assistant_Retail_Manager_Activities].[Month],"mmmm"))=Format(DateAdd("m",-1,Now()),"mmmm")));

 

[[MVP] S.Clark]

The trick is to use an Outer Join. So, you would have Branch as the Parent,
with Outer Joins to both the Manager data and the AsstManager data. (This
may need to be two different queries, but you can link them back together
later by the BranchID)

And, BTW, if you don't have a Branch table, then this would be the perfect
opportunity to make one.

--
Steve Clark, Access MVP
FMS, Inc.
Call us for all of your Access Development Needs!
1-888-220-6234
(e-mail address removed)
www.fmsinc.com/consulting

I have a query that essentially pulls 7 different types of numbers from 2
different tables. It also calculates the difference, if any, between the 2
numbers in any of the 7 types. For example, it takes the deposit portfolio
$
from Table 1 and subtracts the deposit portfolio $ from Table 2 to see if
they are identical. If they're not, then there is a difference between the
two.

The two tables are (1) Managers and (2) Assistant Managers for a bank with
11 different branches. The only problem is, when a particular bank has an
Assistant Manager but no Manager (or vice versa), the $ for that branch do
not appear in the results. How can I force the query to display results
for
each and every branch, even if that branch does not have both a Manager
and
an Assistant Manager? I've tried using Nz without success, and I've pasted
the SQL below.

Thanks!

SELECT Branches.Branch_Number,
Format([Assistant_Retail_Manager_Activities].[Month],"mmmm") AS [Month],
Nz([Retail_Manager],"None") AS RM, Nz([Assistant_Retail_Manager],"None")
AS
ARM, Retail_Manager_Activities.Deposits_Outstanding AS [RM Dep Port],
Assistant_Retail_Manager_Activities.Deposits_Outstanding AS [ARM Dep
Port],
CCur(Nz([RM Dep Port]-[ARM Dep Port],0)) AS [Dep Port Comp],
Retail_Manager_Activities.New_Deposits AS [RM New Dep],
Assistant_Retail_Manager_Activities.New_Deposits AS [ARM New Dep],
CCur(Nz([RM New Dep]-[ARM New Dep],0)) AS [New Dep Comp],
Retail_Manager_Activities.Security_Referrals AS [RM Sec],
Assistant_Retail_Manager_Activities.Security_Referrals AS [ARM Sec],
CCur(Nz([RM Sec]-[ARM Sec],0)) AS [Sec Comp],
Retail_Manager_Activities.Trust_Referrals AS [RM Trust],
Assistant_Retail_Manager_Activities.Trust_Referrals AS [ARM Trust],
CCur(Nz([RM Trust]-[ARM Trust],0)) AS [Trust Comp],
Retail_Manager_Activities.Mortgage_Closed AS [RM Mort],
Assistant_Retail_Manager_Activities.Mortgage_Closed AS [ARM Mort],
CCur(Nz([RM Mort]-[ARM Mort],0)) AS [Mort Comp],
Retail_Manager_Activities.Loan_Portfolio_Outstanding AS [RM Loan Port],
Assistant_Retail_Manager_Activities.Loan_Portfolio_Outstanding AS [ARM
Loan
Port], CCur(Nz([Rm Loan Port]-[ARM Loan Port],0)) AS [Loans Port Comp],
Retail_Manager_Activities.New_Loans_Funded AS [RM New Loans],
Assistant_Retail_Manager_Activities.New_Loans_Funded AS [ARM New Loans],
CCur(Nz([RM New Loans]-[ARM New Loans],0)) AS [New Loans Comp]
FROM Branches INNER JOIN (Assistant_Retail_Manager_Activities INNER JOIN
Retail_Manager_Activities ON (Assistant_Retail_Manager_Activities.Month =
Retail_Manager_Activities.Month) AND
(Assistant_Retail_Manager_Activities.[Branch#] =
Retail_Manager_Activities.[Branch#])) ON (Branches.Branch_Number =
Assistant_Retail_Manager_Activities.[Branch#]) AND (Branches.Branch_Number
=
Retail_Manager_Activities.[Branch#])
WHERE
(((Format([Assistant_Retail_Manager_Activities].[Month],"mmmm"))=Format(DateAdd("m",-1,Now()),"mmmm")));