Calculations with Null fields

[jodilees]

Help me please, it is probably a simple answer

Hi everyone

I have a main table that contains all of our Work in Progress with a primary key of Job number.

I am running a query on this table with certain criteria such as Project manager etc to generate a sum of the contract.

There are other tables controlled by subforms in my main form that contain information such as invoicing and variation values to contracts.

What I am trying to achieve is my query will provide me with a contract value for each job number and add the value of any variations less any invoicing that has been completed.

Yes it is working and in theory it is probably very very simple, however if I don't have any invoicing or variations for a job it is not returning a value.

How do I tell it that if these fields have null value then it is actually a 0 value so it will calculate them.

Or am I way off track and this is not the problem.

I have looked through the forum for information but can't find anything that i can really comprehend to help me answer this.

Kind regards

Jodi

 

[Rich]

Look up the Nz function

 

[jodilees]

Nz Function More detail required

Look up the Nz function

Thanks, i looked up the Nz Function and have now got that working in my forms. It is fantastic. However in my query I have imported 3 tables as follows

WIP, Invoicing Details, Variation Details.

In this query I want to show the total of the value from the WIP Value field, which is easy using the sum option in the query and then show the value from the invoicing details Value field and the same for the variation Details.

I then want to take the WIP Value field, Deduct the invoice value field and add the Variation details field. The problem I am having is that I have stupidly named the fields Value in all three tables. I don't want to go back and change anything as all else is working like a dream.

How do I put the Nz function into a query and how do I get it to refer to a previous column in the table and check there is a result and if not return a 0 value so that I can calculate my totals.

Any hints or help would be greatly appreciated.

Kind Regards

Jodi

 

[neileg]

Not a good idea to duplicate the Value names as you realise. However, the full name of these fields includes the table name, thus:
[WIP].[Value] + [Invoicing Details].[Value] - [Variation Details].[Value]
should do the trick

 

[jodilees]

Not a good idea to duplicate the Value names as you realise. However, the full name of these fields includes the table name, thus:
[WIP].[Value] + [Invoicing Details].[Value] - [Variation Details].[Value]
should do the trick

Thanks for the feedback. I have done something similar to this, but am unsure and not confident with queries. Very very rusty. In my Forms I have been doing similar to what you have above, but I have been using the ! between the names and it has not been working. Should I be using a . or does it not make any difference.

also I am assuming this can go in a new column in the query as an expression? And I don't think what you have put above deals with the null value. That is the biggest problem I am having. If there has been no invoicing for a job in the wip Table it will not give me any totals.

Thanks once again for your help.

Kind Regards

Jodi

 

[neileg]

Should I be using a . or does it not make any difference.

Dunno There's an FAQ on this site all about . and ! but I usually just try one and see if it works

also I am assuming this can go in a new column in the query as an expression?

Yep

I don't think what you have put above deals with the null value.

All I was illustrating was the referencing. You'll have to use Nz() as well, like Nz(

.[Field], 0)

 

[Graham T]

jodilees

How do I tell it that if these fields have null value then it is actually a 0 value so it will calculate them

You may want to look at the following:

Defining Null Values

-OR-

What does NULL mean?

HTH

Graham

 

[jodilees]

Ok that is all working but what now

This is the expression I have used in my query. It is working in the fact that it is returning a 0 value if there are no entries.

Invoiced: Sum(Nz([Invoicing].[Value],0))
Variation: Sum(Nz([Variation Records].[Variation Amount],0))

My next question, a relatively simple one for most I am sure, is how do I stop it from summing twice. To elaborate. A job has 3 invoices against it for 1000 each and 3 variations for 1000. Instead of returning 3000 as a sum for each it is returning 9000. The strange bit here is if it only has either invoicing or variations not both recorded against it I am not encountering the same problem, it is returning a value of 3000. I am sure it has something to do with the duplicate lines created in my query and it is summing it twice, how do I stop this from happening?

Kind regards

Jodi

 

[Pat Hartman]

In a query that joins two tables in a 1-many relationship, you cannot sum data from the 1-side since the 1-side data "appears" to be duplicated since it occurs once for each instance of the many-side data. If you want to sum data from the 1-side table, you need to do it in a query that does not inflate the figure by including many-side data.

The best approach to use is to do the summing in the report. Make a main report for the 1-side data and a subreport for the many-side data.

 

[jodilees]

Not sure if I understand

In a query that joins two tables in a 1-many relationship, you cannot sum data from the 1-side since the 1-side data "appears" to be duplicated since it occurs once for each instance of the many-side data. If you want to sum data from the 1-side table, you need to do it in a query that does not inflate the figure by including many-side data.

The best approach to use is to do the summing in the report. Make a main report for the 1-side data and a subreport for the many-side data.

Hi Pat

I am not sure what you mean in the above. I recreated my queries, one to show invoicing totals and one to show variation totals. I then created another query and pulled the information into that query and it is doing everything I want it to do except if there are no invoices or variations I want it to show a 0 value and still calculate on these fields. At present if there are variations and invoicing it is calculating fine, but if there is nothing there then it is running into trouble.

Sorry if I am going over the same ground but if you have any solutions to my problems that I can make sense of I would really appreciate it. Sorry I am not the most advanced access user in the world when it comes to this side of it all.

Kind Regards

Jodi

 

[Pat Hartman]

I was answering your most recent question -

is how do I stop it from summing twice

.

In answer to your current question -

if there are no invoices or variations I want it to show a 0

- use a Left Join rather than an inner join. Switch to SQL view and change inner to Left OR in QBE view, double click on the join line and choose the appropriate option.