Union query or Outer Joins do not create desired results

[jimt]

I'm using Access-2000 and creating query/queries to pull together financial
data (budget & actual) to produce the following in a subform as well as a
report.

parm (form input): fiscal_year & fiscal_month

budget actual YTD Budget Balance
Account Month Expend Budget Actual Annual Budget Actual

Tables (just utilized fields):
chart_of_accounts
account_num
title
select_for_report

budget
account_num
fiscal_year
budget_x (x=1-12 fields, 1 for each month)
annual (total forecasted annual budget)

actual
account_num
fiscal_year
actual_x (x=1-12 fields, 1 for each month)

I'm using the chart_of_accounts table as a "master" however even using an
outer join will not work since some of the accounts has not had an actual for
the year. Similarly if an expenditure (actual) has occurred and the budget
for some reason is not entered the record will not be included.

I've tried a union on account_num across the three tables and then a query
over the union to get the $$$ fields; however, #error occurs in the field
where the actual or budget records do not exist (even with outer joins).

I can not create two queries that first outer joins chart_of_accounts with
budget and actual since I have to select only the records for the tax year
enterd on the form (tax year field is in the "right"/"outer" table)

I know I can use a program to populate a separate "combined" table and then
use a simple query over it to get the results; but, is there a way to use
queries instead?

Thanks
Jim T.

 

[KARL DEWEY]

budget_x (x=1-12 fields, 1 for each month)This is wrong to have field per month. Have field for month and another for
Budget or Actual.

Your other problem - use a union and then left join like these --
SELECT [chart_of_accounts].[account_num]
FROM [chart_of_accounts]
UNION SELECT [Budget].[account_num]
FROM [Budget]
UNION SELECT [Actual].[account_num]
FROM [Actual];

SELECT [Union_qry].[account_num], ... other fields
FROM [Union_qry) LEFT JOIN Budget ON [Union_qry].[account_num] =
[Budget].[account_num]
.....;

The union build list of [account_num] so they are listed even if there is no
entry in Budget or Actual table.

 

[jimt]

Karl,

Thanks! The key as you noted was the union of the chart_of_accounts and
select with BOTH the budget and actual (in my efforts I only included one at
a time) produced a list of all accounts regardless of existing budget and/or
account records. Once the union was set, the outer joins allowed the
inclusion of the monthly budget and monthly actual fields.

I did have a second problem in that the resulting query had null in some
fields where budget or actual records did not exist. I included a IIF for
null and set those to zero and the calculations in the subsequent queries
worked fine.

Thanks Again
Jim T.

This is wrong to have field per month. Have field for month and another for
Budget or Actual.

Your other problem - use a union and then left join like these --
SELECT [chart_of_accounts].[account_num]
FROM [chart_of_accounts]
UNION SELECT [Budget].[account_num]
FROM [Budget]
UNION SELECT [Actual].[account_num]
FROM [Actual];

SELECT [Union_qry].[account_num], ... other fields
FROM [Union_qry) LEFT JOIN Budget ON [Union_qry].[account_num] =
[Budget].[account_num]
.....;

The union build list of [account_num] so they are listed even if there is no
entry in Budget or Actual table.

--
Build a little, test a little.

I'm using Access-2000 and creating query/queries to pull together financial
data (budget & actual) to produce the following in a subform as well as a
report.

parm (form input): fiscal_year & fiscal_month

budget actual YTD Budget Balance
Account Month Expend Budget Actual Annual Budget Actual

Tables (just utilized fields):
chart_of_accounts
account_num
title
select_for_report

budget
account_num
fiscal_year
budget_x (x=1-12 fields, 1 for each month)
annual (total forecasted annual budget)

actual
account_num
fiscal_year
actual_x (x=1-12 fields, 1 for each month)

I'm using the chart_of_accounts table as a "master" however even using an
outer join will not work since some of the accounts has not had an actual for
the year. Similarly if an expenditure (actual) has occurred and the budget
for some reason is not entered the record will not be included.

I've tried a union on account_num across the three tables and then a query
over the union to get the $$$ fields; however, #error occurs in the field
where the actual or budget records do not exist (even with outer joins).

I can not create two queries that first outer joins chart_of_accounts with
budget and actual since I have to select only the records for the tax year
enterd on the form (tax year field is in the "right"/"outer" table)

I know I can use a program to populate a separate "combined" table and then
use a simple query over it to get the results; but, is there a way to use
queries instead?

Thanks
Jim T.

 

[KARL DEWEY]

I included a IIF for null and set those to zero
You could use the 'Nz' function -- Nz([Field]. 0)

--
Build a little, test a little.

Karl,

Thanks! The key as you noted was the union of the chart_of_accounts and
select with BOTH the budget and actual (in my efforts I only included one at
a time) produced a list of all accounts regardless of existing budget and/or
account records. Once the union was set, the outer joins allowed the
inclusion of the monthly budget and monthly actual fields.

I did have a second problem in that the resulting query had null in some
fields where budget or actual records did not exist. I included a IIF for
null and set those to zero and the calculations in the subsequent queries
worked fine.

Thanks Again
Jim T.

budget_x (x=1-12 fields, 1 for each month)
actual_x (x=1-12 fields, 1 for each month)

This is wrong to have field per month. Have field for month and another for
Budget or Actual.

Your other problem - use a union and then left join like these --
SELECT [chart_of_accounts].[account_num]
FROM [chart_of_accounts]
UNION SELECT [Budget].[account_num]
FROM [Budget]
UNION SELECT [Actual].[account_num]
FROM [Actual];

SELECT [Union_qry].[account_num], ... other fields
FROM [Union_qry) LEFT JOIN Budget ON [Union_qry].[account_num] =
[Budget].[account_num]
.....;

The union build list of [account_num] so they are listed even if there is no
entry in Budget or Actual table.

--
Build a little, test a little.

I'm using Access-2000 and creating query/queries to pull together financial
data (budget & actual) to produce the following in a subform as well as a
report.

parm (form input): fiscal_year & fiscal_month

budget actual YTD Budget Balance
Account Month Expend Budget Actual Annual Budget Actual

Tables (just utilized fields):
chart_of_accounts
account_num
title
select_for_report

budget
account_num
fiscal_year
budget_x (x=1-12 fields, 1 for each month)
annual (total forecasted annual budget)

actual
account_num
fiscal_year
actual_x (x=1-12 fields, 1 for each month)

I'm using the chart_of_accounts table as a "master" however even using an
outer join will not work since some of the accounts has not had an actual for
the year. Similarly if an expenditure (actual) has occurred and the budget
for some reason is not entered the record will not be included.

I've tried a union on account_num across the three tables and then a query
over the union to get the $$$ fields; however, #error occurs in the field
where the actual or budget records do not exist (even with outer joins).

I can not create two queries that first outer joins chart_of_accounts with
budget and actual since I have to select only the records for the tax year
enterd on the form (tax year field is in the "right"/"outer" table)

I know I can use a program to populate a separate "combined" table and then
use a simple query over it to get the results; but, is there a way to use
queries instead?

Thanks
Jim T.