How to do Comparison Query

[Guest]

I work for a bank with 11 branches. I have two tables: (1) Retail Manager
Activities, and (2) Assistant Retail Manager Activities. Each table contains
multiple dollar amounts, such as Loan Portfolio, Deposit Portfolio, Past Due,
New Loans, etc. Each table has one entry for each manager per month in 2005.
So, John Doe has 6 entries in table 1 thus far this year, and his asst. mgr.
Tom Smith also has 6 entires in table 2 thus far this year.

I need to build a query that will display the columns for each manager side
by side, because I have to check the numbers each month to ensure they are
the same. In other words, these are the column headings I want in the results:

Branch
Month
Retail Manager Loan Portfolio
Asst. Retail Manager Loan Portfolio
Retail Manager Deposit Portfolio
Asst. Retail Manager Deposit Portfolio
Etc.

I have two other tables that list simply the manager's name and branch (one
table for retail, one for assistant retail). I have built a query that
cross-references the Retail Managers with the Assistant Retail Managers:

SELECT Retail_Manager.Branch, Retail_Manager.Retail_Manager,
Assistant_Retail_Manager.Assistant_Retail_Manager
FROM Retail_Manager INNER JOIN Assistant_Retail_Manager ON
Retail_Manager.Branch = Assistant_Retail_Manager.Branch;

Using this "cross-reference" query, what is the best way to approach
building the query I want? I have played around with it some on my own, but
all I can get is a query with like 30 entries per branch. There should only
be 6 entries per branch.

Your help is most appreciated.

 

[Guest]

Try...

SELECT Retail_Manager.Branch, Retail_Manager.Retail_Manager,
Assistant_Retail_Manager.Assistant_Retail_Manager
FROM Retail_Manager INNER JOIN Assistant_Retail_Manager ON
Retail_Manager.Branch = Assistant_Retail_Manager.Branch
AND Retail_Manager.Month = Assistant_Retail_Manager.Month;

 

[Guest]

Thanks for your reply, but there's just one problem. None of the tables I'm
using in the cross-reference query have a month field in them. Only the two
tables I'm trying to join with the cross-reference query have the month field
in them. So, I can't use your code because it won't solve my problem. But
thanks for trying!

 

[Guest]

The reason you are getting multiple crossmatches is because you are not
including the month in the linking.
To solve your problem you need to include these in the "from" linking even
if you dont include them in the "select"