Join Question?

I have two tables that I'm pulling information from, one for the current
year, one for last year. I am attempting to pull budget information for
certain projects from both tables and add them together to get a cumulative
funding picture for each project. I can do this successfully as long as a
particular project existed in both tables, however, new projects for the
current year (that didn't exist last year) show blank budget values.

I've tried all three kinds of joins and only #3 even shows the new projects,
but again it only shows blank budget values.

Anyone know how I can get the new projects' budgets to show?

You surely have a table with all the projects, isn't it?

Bring that table in addition to the two others. Make a join keeping all
records from the said table to each of the two other tables (option 2). Pull
the data from any of the other two tables, except for the project name, take
it from the table supplying all the projects.


Vanderghast, Access MVP


"Dez" wrote in message
...
I have two tables that I'm pulling information from, one for the current
year, one for last year. I am attempting to pull budget information for
certain projects from both tables and add them together to get a
cumulative
funding picture for each project. I can do this successfully as long as a
particular project existed in both tables, however, new projects for the
current year (that didn't exist last year) show blank budget values.

I've tried all three kinds of joins and only #3 even shows the new
projects,
but again it only shows blank budget values.

Anyone know how I can get the new projects' budgets to show?

The reason you are not getting a value for those projects present in only one
of the tables is that when you use a RIGHT OUTER JOIN (which is what option 3
in the dialogue in query design view represents) the returned budget column
position for those rows which don't exist in the table on the left side of
the join is Null. In arithmetical expressions Null propagates, so anything +
Null = Null, so adding the budget from one year to the Null budget for the
previous year won't give you the value of the new year's budget, but Null.
This is because Null is not a value, like zero, but the absence of a value,
an unknown; it could be anything so the result of adding it to a value could
also be anything, i.e. Null.

This sort of situation is catered for by the Nz function which returns a
value in place of a Null, so in your case the expression to add the two
year's budgets would be:

Nz([LastYearTable].[Budget],0)+[ThisYearTable].[Budget]

If your expression includes more than one column from each table apply the Nz
function to each of the columns from last year's table.

However, the fact that you have to do this is due to having separate tables
for each year in the first place, which is not good design as it amounts to
encoding data as table names. A fundamental principle of the database
relational model (the Information Principle) is that data is stored as values
at column positions in rows in tables and in no other way.

The correct model is to have a single table with a column from which the year
can be identified. This can be a true date/time date type, in which the year
can be returned with YEAR([ProjectDate], or in the absence of specific dates
it could be an integer number ProjectYear column in which the year value per
project is stored. Its then simply a matter of summing the budgets for the
years in question, grouping the query by project.

Ken Sheridan
Stafford, England

Dez wrote:
I have two tables that I'm pulling information from, one for the current
year, one for last year. I am attempting to pull budget information for
certain projects from both tables and add them together to get a cumulative
funding picture for each project. I can do this successfully as long as a
particular project existed in both tables, however, new projects for the
current year (that didn't exist last year) show blank budget values.

I've tried all three kinds of joins and only #3 even shows the new projects,
but again it only shows blank budget values.

Anyone know how I can get the new projects' budgets to show?

--
Message posted via http://www.accessmonster.com

The Nz function got it. Thanks!!

"KenSheridan via AccessMonster.com" wrote:

The reason you are not getting a value for those projects present in only one
of the tables is that when you use a RIGHT OUTER JOIN (which is what option 3
in the dialogue in query design view represents) the returned budget column
position for those rows which don't exist in the table on the left side of
the join is Null. In arithmetical expressions Null propagates, so anything +
Null = Null, so adding the budget from one year to the Null budget for the
previous year won't give you the value of the new year's budget, but Null.
This is because Null is not a value, like zero, but the absence of a value,
an unknown; it could be anything so the result of adding it to a value could
also be anything, i.e. Null.

This sort of situation is catered for by the Nz function which returns a
value in place of a Null, so in your case the expression to add the two
year's budgets would be:

Nz([LastYearTable].[Budget],0)+[ThisYearTable].[Budget]

If your expression includes more than one column from each table apply the Nz
function to each of the columns from last year's table.

However, the fact that you have to do this is due to having separate tables
for each year in the first place, which is not good design as it amounts to
encoding data as table names. A fundamental principle of the database
relational model (the Information Principle) is that data is stored as values
at column positions in rows in tables and in no other way.

The correct model is to have a single table with a column from which the year
can be identified. This can be a true date/time date type, in which the year
can be returned with YEAR([ProjectDate], or in the absence of specific dates
it could be an integer number ProjectYear column in which the year value per
project is stored. Its then simply a matter of summing the budgets for the
years in question, grouping the query by project.

Ken Sheridan
Stafford, England

Dez wrote:
I have two tables that I'm pulling information from, one for the current
year, one for last year. I am attempting to pull budget information for
certain projects from both tables and add them together to get a cumulative
funding picture for each project. I can do this successfully as long as a
particular project existed in both tables, however, new projects for the
current year (that didn't exist last year) show blank budget values.

I've tried all three kinds of joins and only #3 even shows the new projects,
but again it only shows blank budget values.

Anyone know how I can get the new projects' budgets to show?

--
Message posted via http://www.accessmonster.com

.