How to resolve ambiguous outer joins in a find unmatched query? 1

[TomYC]

I have two tables, PositionDetail, and BudgetNew. Each has a field called Job Code, and each has a field identifying a department, called [Rpt Dept] in PositionDetail, and [Department] in BudgetNew. I want to create a query to find, for each [Rpt Dept] in PositionNew, those records in PositionDetail containing a [Job Code] that does not appear in BudgetNew for the identical [Department]. I thought this would be simple, and I created a query like the following:

SELECT PositionDetail.[Rpt Dept], PositionDetail.[Job Code], BudgetNew.Department
FROM PositionDetail LEFT JOIN BudgetNew ON PositionDetail.[Job Code] = BudgetNew.[Job Code]
WHERE (((BudgetNew.[Job Code]) Is Null));

However when I want the inner join on departments:

BudgetNew INNER JOIN PositionDetail ON BudgetNew.Department = PositionDetail.[Rpt Dept]

and add this to the query I get the ambiguous outer join error message. Is the solution to this some kind of intermediate query? I have tried and not succeeded so far!
T.Y.

 

[r937]

is this what you want? i couldn't understand why you wanted to add another join...

SELECT PositionDetail.[Rpt Dept]
     , PositionDetail.[Job Code]
     , BudgetNew.Department
  FROM PositionDetail 
LEFT OUTER
  JOIN BudgetNew 
    ON ( 
       BudgetNew.[Job Code] = PositionDetail.[Job Code]
   AND BudgetNew.Department = PositionDetail.[Rpt Dept]
       )
 WHERE BudgetNew.[Job Code] IS NULL

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[TomYC]

I'm working with your query, thanks! I'm trying to get fallouts: job codes in one department that don't appear in the other list of departments and job codes. Then I want to combine them somehow!

 

[r937]

i guess then i didn't understand your problem

could you perhaps show a few sample rows of data from each table, enough to illustrate the problem?

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[TomYC]

OK; and I will give a more thorough account of my tables and desired outcomes.
I have one table, BudgetNew, with the following fields, for which I will give two examples each:
[Dept ID] [Department] [Job Code] [Job Description] [Total Regular FTE] [Total Overtime FTE] [Total Holiday FTE] [Total FTE]
Outcome Management 10600135 100127 Medical Secretary 1 0 0 1
Human Resources 10600070 100126 HR Representative 2 0 0 2
Now I have another Table, PositionDetail, that contains many fields, but which shares the first four above, albeit with different names.
[Rpt Dept] [Rpt Dept Desc] [Jobcode] [Jobcode Title] etc.
10600135 Outcome Management 100127 Medical Secretary etc.
10600135 Outcome Management 101396 Asst Dir

My example records from the second table, PositionDetail, contain one record that occurs in BudgetNew, the first table, and one that does not, i.e., the department information exists, but not the job (code & title) information.

So, my real goal is first, to construct a query that will identify the 'fallout' -- the records in the PositionDetail table that share department information (as for those that do not, that is a question for another day!) but contain job code & job title information that does not occur in the first table, BudgetNew. Then, I want to create, either a query or perhaps a temporary table, if that is advised (the information in PositionDetail is updated weekly, while the BudgetNew information is static), that will contain all the records in BudgetNew, and all of the shared-department/unmatched-job information, such that any report or query that currently has BudgetNew as its datasource will, when attached to the proposed new query or table, contain the additional information. As a context, my report shows, for each deparment, the budgeted totals (measured in 'FTEs') for each job type, and right now the report misses those job types that are not in the budget info, but are in fact in the HR info, i.e. are real, in real time. I need my report for the Outcome Management department to include that 'new' job type, Asst Dir, even though it is not in the BudgetNew table, and has no figures for [Total FTE], etc. Wordy, yes, but I hope that is clear! I have been struggling mightily with this!
T.Y.

 

[PHV]

You wanted something like this ?

SELECT [Dept ID], Department, [Job Code], [Job Description]
FROM BudgetNew
UNION SELECT [Rpt Dept], [Rpt Dept Desc], Jobcode, [Jobcode Title]
FROM PositionDetail P LEFT JOIN BudgetNew N ON P.[Rpt Dept]=N.[Dept ID] AND P.Jobcode=N.[Job Code]
WHERE N.[Dept ID] IS NULL

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[TomYC]

I'll try it now. What are N & P? And how do I get those other (FTE-related) fields into my query results, since they are nulls in the PostionDetail table?
Thanks!

 

[TomYC]

I interpreted your effort to work for me as:

SELECT [Dept ID], Department, BudgetNew.[Job Code], [Job Description]
FROM BudgetNew
UNION SELECT [Rpt Dept], [Rpt Dept Desc], PositionDetail.[Job code], [Jobcode Title]
FROM PositionDetail LEFT JOIN BudgetNew ON PositionDetail.[Rpt Dept]=BudgetNew.[Department]
WHERE BudgetNew.[Job Code] IS NULL

This still does not give me the fallout/unmatched records, or Job Code 101396 in the example above.
I'll keep trying; I appreciate the economy of your code!

 

[PHV]

With the correct joining columns:

SELECT Department, [Dept ID], [Job Code], [Job Description]
FROM BudgetNew
UNION SELECT [Rpt Dept], [Rpt Dept Desc], P.Jobcode, [Jobcode Title]
FROM PositionDetail P LEFT JOIN BudgetNew N ON P.[Rpt Dept]=N.Department AND P.Jobcode=N.[Job Code]
WHERE N.[Job Code] IS NULL

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[TomYC]

Thank you! I'm almost there. This code appears to work, as far as combining the two sets of information is concerned. Now I only have find a way to include those several other fields in the original table BudgetNew, i.e., the "FTE" items. The query below I named qryBudgetNewPositionDetailJOINED (for now) :

SELECT Department, [Dept ID], [Job Code], [Job Description]
FROM BudgetNew
UNION SELECT [Rpt Dept], [Rpt Dept Desc], PositionDetail.[Job code], [Jobcode Title]
FROM PositionDetail PositionDetail LEFT JOIN BudgetNew ON PositionDetail.[Rpt Dept]=BudgetNew.Department AND PositionDetail.[Job code]=BudgetNew.[Job Code]
WHERE BudgetNew.[Job Code] IS NULL

and when I connect it up with BudgetNew, joined at Department, I have this:
SELECT BudgetNew.Department, BudgetNew.[Dept ID], qryBudgetNewPositionDetailJOINED.[Job Code], qryBudgetNewPositionDetailJOINED.[Job Description], BudgetNew.[Total Regular FTE], BudgetNew.[Total Overtime FTE], BudgetNew.[Total Holiday FTE], BudgetNew.[Total FTE]
FROM BudgetNew INNER JOIN qryBudgetNewPositionDetailJOINED ON BudgetNew.Department = qryBudgetNewPositionDetailJOINED.Department;

This gives me precisely 5 times too many records, and I'm sure it is because the BudgetNew table contains those four fields that the joining query does not. Is there a quick aolution to this!
T.Y.

 

[TomYC]

Have a Star, PHV. Between you and dhookum, on the reports section, I got what I needed and learned about unions and nulls (not annullments). T.Y.