Problem with Null Queries

[Nick]

Yes, that's right, I have created a NULL query!

OK, to explain. I am developing a form of Financial Management database for
my organisation. The database manages information for projects, purchase
orders (or contracts) against projects and project budget variations (as well
as other things). Everything seems to be fine at the moment - however, I have
a problem with NULL fields.

There are two tables that are related - the Project Information Table and
the Project Variation Table. This is a One to Many Relationship. Essentially,
personnel enter the initial project information into the PROJECT INFORMATION
TABLE and, as the projects continue, they enter any project budget variations
to the PROJECT VARIATION TABLE.

The PROJECT INFORMATION TABLE includes the intial budget. That is all. This
is kept to ensure that datamining and future estimations can be conducted. As
such, I can not use an Update Query to change the Budget Amount in the
PROJECT INFORMATION TABLE. The project's are numbered under [Project Number]
in this table.

So, the PROJECT VARIATION TABLE was created. This is the [Project Number] (a
drop down list of all the project numbers in the PROJECT INFORMATION TABLE)
and the variation amount - [Variation] - as well as other management
information.

A Sum Query - VARIATION TOTAL QUERY - is used to sum the total of variations
for all projects, by [Project Number]. The sum is obviously called
[SumofVariation]

Another query - TOTAL QUERY - then calculates the current total of all
projects. This lists the [Project Number] from the PROJECT INFORMATION TABLE,
the [Initial Budget] from the PROJECT INFORMATION TABLE and the
[SumofVariation] from the VARIATION TOTAL QUERY. It then adds [Initial
Budget] + [SumofVariation] to create [Current Budget].

PROBLEM: If you have no variations for a project, then the VARIATION TOTAL
QUERY returns nothing. Therefore, [SumofVariation] is NULL and then [Current
Budget] is NULL. When you view the TOTAL QUERY in datashet view you see
nothing, because there is nothing in the VARIATION TOTAL QUERY.

So - how do I make the TOTAL QUERY spit out the initial budget where
VARIATION TOTAL QUERY is NULL (ie, no variations have occured).

I have tried to use an iif statement in the following:

iif ( IsNull([SumofVariation]),0,[SumofVariation]) *tables obmitted*

No joy with that, it still spat out NULL.

Then tried:

Nz([SumofVariation],0)

Still no good.

The problem seems to be the fact that the VARIATIONS TOTAL QUERY is NULL.


Any help would be great - Cheers!

 

[Nick]

Et Al,

Thanks for the replies. I have tried multiple iterations of the Nz() concept
with no joy as I knew that adding to NULL is NULL for Access (though this is
not a mathematics Axiom - though I do love Access, I wish it could be just a
little more sensible at times!).

With respect to the two relationships. There is a One to Many Relationship
between the Project Information Table and the PVR Table. There is also a
Relationship between the Project Information Table and the VARIATION TOTAL
TABLE. This relationship is an "Inner Join" relationship (default as I have
not changed it).

Could the relationship type be the problem, as suggested? I am not brilliant
at setting Query Relationships so it would not suprise me.

I have attached below the SQL coding for the two queries. The first is the
VARIATION TOTAL QUERY that just calculates the sum of the variations to date,
by Project Number (known as [PAS Number], where [PAS PVRs] is the variation
table):

SELECT DISTINCTROW [PAS PVRs].[PAS Number], Sum([PAS PVRs].[PAS AUD Budget
Variation]) AS [Sum Of PAS AUD Budget Variation]
FROM [PAS PVRs]
GROUP BY [PAS PVRs].[PAS Number];

The second is the TOTAL QUERY that collects information from the Project
Table ([PAS INFORMATION]) and the VARIATION TOTAL QUERY to find current
budget by project number (once again [PAS Number]):

SELECT [PAS Information].[PAS Number], [PVR Totals].[Sum Of PAS AUD Budget
Variation], [PAS Information].[PAS AUD Initial Allocated Total], [PAS
Information].[PGK-AUD Conversion Rate], [Sum Of PAS AUD Budget
Variation]+[PAS AUD Initial Allocated Total] AS PASAUDTot,
Round(([PASAUDTot]*[PGK-AUD Conversion Rate])) AS PASPGKTot
FROM [PAS Information] INNER JOIN [PVR Totals] ON [PAS Information].[PAS
Number] = [PVR Totals].[PAS Number];

just to note, the PASAUDTot and PASPGKTot are calculated to find the current
budget of the project in Australian Dollars and PNG Kina. The Conversion
Factor is listed in the [PAS INFORMATION TABLE].


Cheers

--

Nick B

if you submit the sql code i'll fix it for you

Yes, that's right, I have created a NULL query!

OK, to explain. I am developing a form of Financial Management database for
my organisation. The database manages information for projects, purchase
orders (or contracts) against projects and project budget variations (as well
as other things). Everything seems to be fine at the moment - however, I have
a problem with NULL fields.

There are two tables that are related - the Project Information Table and
the Project Variation Table. This is a One to Many Relationship. Essentially,
personnel enter the initial project information into the PROJECT INFORMATION
TABLE and, as the projects continue, they enter any project budget variations
to the PROJECT VARIATION TABLE.

The PROJECT INFORMATION TABLE includes the intial budget. That is all. This
is kept to ensure that datamining and future estimations can be conducted. As
such, I can not use an Update Query to change the Budget Amount in the
PROJECT INFORMATION TABLE. The project's are numbered under [Project Number]
in this table.

So, the PROJECT VARIATION TABLE was created. This is the [Project Number] (a
drop down list of all the project numbers in the PROJECT INFORMATION TABLE)
and the variation amount - [Variation] - as well as other management
information.

A Sum Query - VARIATION TOTAL QUERY - is used to sum the total of variations
for all projects, by [Project Number]. The sum is obviously called
[SumofVariation]

Another query - TOTAL QUERY - then calculates the current total of all
projects. This lists the [Project Number] from the PROJECT INFORMATION TABLE,
the [Initial Budget] from the PROJECT INFORMATION TABLE and the
[SumofVariation] from the VARIATION TOTAL QUERY. It then adds [Initial
Budget] + [SumofVariation] to create [Current Budget].

PROBLEM: If you have no variations for a project, then the VARIATION TOTAL
QUERY returns nothing. Therefore, [SumofVariation] is NULL and then [Current
Budget] is NULL. When you view the TOTAL QUERY in datashet view you see
nothing, because there is nothing in the VARIATION TOTAL QUERY.

So - how do I make the TOTAL QUERY spit out the initial budget where
VARIATION TOTAL QUERY is NULL (ie, no variations have occured).

I have tried to use an iif statement in the following:

iif ( IsNull([SumofVariation]),0,[SumofVariation]) *tables obmitted*

No joy with that, it still spat out NULL.

Then tried:

Nz([SumofVariation],0)

Still no good.

The problem seems to be the fact that the VARIATIONS TOTAL QUERY is NULL.

Any help would be great - Cheers!

 

[John Spencer]

Change your first query to read as follows:

SELECT [PAS INFORMATION].[PAS Number]
, Nz(Sum([PAS PVRs].[PAS AUD Budget Variation]),0) AS [Sum Of PAS AUD
Budget Variation]
FROM [PAS INFORMATION] LEFT JOIN [PAS PVRs]
ON [PAS INFORMATION].[PAS Number] = [PAS PVRs].[PAS Number]
GROUP BY [PAS PVRs].[PAS Number];

That will return one record for every PAS Number in PAS Information table.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[Nick]

John,

Thank you. Funnily enough, the comment by Dale_Fye also made me think! I
rechecked my Dummy's Guide to Access (I am a numpty after all!) and learnt
about Query Joins properly.

In essence, I literally worked out that the thing needed to be a Left Join
just as I recieved your post!

My query is not as eligent, but now it works!

Thank you all for your assistance with this.
--

Nick B

Change your first query to read as follows:

SELECT [PAS INFORMATION].[PAS Number]
, Nz(Sum([PAS PVRs].[PAS AUD Budget Variation]),0) AS [Sum Of PAS AUD
Budget Variation]
FROM [PAS INFORMATION] LEFT JOIN [PAS PVRs]
ON [PAS INFORMATION].[PAS Number] = [PAS PVRs].[PAS Number]
GROUP BY [PAS PVRs].[PAS Number];

That will return one record for every PAS Number in PAS Information table.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Et Al,

Thanks for the replies. I have tried multiple iterations of the Nz() concept
with no joy as I knew that adding to NULL is NULL for Access (though this is
not a mathematics Axiom - though I do love Access, I wish it could be just a
little more sensible at times!).

With respect to the two relationships. There is a One to Many Relationship
between the Project Information Table and the PVR Table. There is also a
Relationship between the Project Information Table and the VARIATION TOTAL
TABLE. This relationship is an "Inner Join" relationship (default as I have
not changed it).

Could the relationship type be the problem, as suggested? I am not brilliant
at setting Query Relationships so it would not suprise me.

I have attached below the SQL coding for the two queries. The first is the
VARIATION TOTAL QUERY that just calculates the sum of the variations to date,
by Project Number (known as [PAS Number], where [PAS PVRs] is the variation
table):

SELECT DISTINCTROW [PAS PVRs].[PAS Number], Sum([PAS PVRs].[PAS AUD Budget
Variation]) AS [Sum Of PAS AUD Budget Variation]
FROM [PAS PVRs]
GROUP BY [PAS PVRs].[PAS Number];

The second is the TOTAL QUERY that collects information from the Project
Table ([PAS INFORMATION]) and the VARIATION TOTAL QUERY to find current
budget by project number (once again [PAS Number]):

SELECT [PAS Information].[PAS Number], [PVR Totals].[Sum Of PAS AUD Budget
Variation], [PAS Information].[PAS AUD Initial Allocated Total], [PAS
Information].[PGK-AUD Conversion Rate], [Sum Of PAS AUD Budget
Variation]+[PAS AUD Initial Allocated Total] AS PASAUDTot,
Round(([PASAUDTot]*[PGK-AUD Conversion Rate])) AS PASPGKTot
FROM [PAS Information] INNER JOIN [PVR Totals] ON [PAS Information].[PAS
Number] = [PVR Totals].[PAS Number];

just to note, the PASAUDTot and PASPGKTot are calculated to find the current
budget of the project in Australian Dollars and PNG Kina. The Conversion
Factor is listed in the [PAS INFORMATION TABLE].


Cheers