When is a subreport necessary? 1

[TomYC]

I have a fully functioning report, already with some subreports.
Now I discover I need to modify the query that is the basis of the main report, as I have been missing some records.
my existing query is the following (simplified somewhat) qryBudgetNew:

SELECT BudgetNew.Department, BudgetNew.[Dept ID], BudgetNew.[Job Code], BudgetNew.[Job Description], BudgetNew.[Total Regular FTE], BudgetNew.[Total Overtime FTE], BudgetNew.[Total Holiday FTE], BudgetNew.[Total FTE]
FROM BudgetNew;

However in order to bring in the missing records (i.e. missing from the BudgetNew table), I have this query, which as you can see is a UNION query pulling together records from BudgetNew table and from another table that does not have certain fields that BudgetNew has (e.g. "FTE, etc."):

qryBudgetNewPositionDetailJOINED is
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;

My problems is that, when I join this query to my original query (joined on Department), I get erroneous and multiple results, presumably due to the fact that the records brought in do not have those other fields.

Is there a way to finesse this, or do I have to rework my report such that a subreport displays data when there is data, and blanks when there is none? More broadly, can I get a query to "accept" nulls, or turn them into something else?
T.Y.

 

[dhookom]

If I understand correctly, you can try:

qryBudgetNewPositionDetailJOINED is
SELECT Department, [Dept ID], [Job Code], [Job Description], 
  [Total Regular FTE], [Total Overtime FTE], [Total Holiday FTE],[Total FTE]
FROM BudgetNew
UNION SELECT [Rpt Dept], [Rpt Dept Desc], PositionDetail.[Job code], [Jobcode Title], 
  Null, Null, Null, Null
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;

Duane
Hook'D on Access
MS Access MVP

 

[TomYC]

It worked! Thanks so much on a Friday afternoon. I wish I had been better able to express what my issue was -- unbalanced tables, etc., etc.
T.Y.