Adding up 3 columns

[roxanat]

Dear All,

I have another question, even more stupid than the previous. This is a basic query, but my brain does not work :banghead:

I have an append query. I want to get a column that is the sum of 3 other columns:

Oil_T_C_(bpd) Gas_T_C_(MMcfd) LNG_T_C_(Tonnes_per_Annum)

1000 2000 3000

I want to get a column called Total_Throughput

How do I do that?

Can you please give me the exact syntax that I have to put into the query design form?

so: Total_Throughput: than what?


Thank you so much!

 

[pbaldy]

Total_Throughput: [Oil_T_C_(bpd)] + [Gas_T_C_(MMcfd)] + [LNG_T_C_(Tonnes_per_Annum)]

 

[plog]

I have an append query. I want to get a column that is the sum of 3 other columns:

That's not how things are suppose to work in a database. You don't store redundant data. A calculated value is just that. Instead, when you need the Total value you don't retrieve it from a field, you calculate it, either in a query or on the Form/Report that needs it.

What happens when one of the underlying values change? You have to remember to run that UPDATE query to update the total field as well. If you just calculate that value, your calculation pulls in the new underlying value and generates the total correctly without a user having to remember to run that UPDATE query.

 

[roxanat]

That's not how things are suppose to work in a database. You don't store redundant data. A calculated value is just that. Instead, when you need the Total value you don't retrieve it from a field, you calculate it, either in a query or on the Form/Report that needs it.

What happens when one of the underlying values change? You have to remember to run that UPDATE query to update the total field as well. If you just calculate that value, your calculation pulls in the new underlying value and generates the total correctly without a user having to remember to run that UPDATE query.

Thank you so much both for helping.

The result of the macro goes to the analysts of our company, and they need in the result just the total, not the 3 separate columns. So i will probably hide the 3 and create the fourth column as permanent.

I will try it when i go back to work( monday) and hopefully it will work.thank you very much to both , again and have a lovely evening.

 

[roxanat]

I tried that and the resulting column is empty!

Maybe I should specify the source table for each of those 3 elements in my expression?

 

[pr2-eugin]

Could you show the Query you are using?

 

[roxanat]

I am trying to add a word document with the image but it wont let me:banghead:

Hahah.
How else can I show you a screenshot?

 

[pr2-eugin]

You can ZIP your files and upload it.

 

[roxanat]

View attachment New Microsoft Word Document.zip

This is a screenshot.Thank you so much.

 

[pr2-eugin]

Good ! Do not store the value of Total in the table. You need not need to. When you need them you can calculate them in a Query. Again do not store the information.

Calculated fields belong in queries, not tables.

 

[roxanat]

This query is part of a macro containing 8 queries.The end result of the macro is a table for the analysts of the company. They need in the table a column with the Total.So the Totals should be in this query,shouldnt it?

But why do I get blanks in the column?Where do I go wrong?

 

[pbaldy]

Might any of the 3 fields be Null? If so, you may need the Nz() function around each. Are the 3 fields numeric, or text? What does the query look like when run?

 

[roxanat]

The 3 fields are numeric,yes, but many of the records are blank.So some have values, but most dont. Are the blank ones considered nulls? Do I need Nz function if some of the records have values? How exactly would the syntax look,if you dont mind?

The way it looks when it is run is with the fourth column not showing any value, even when the corresponding 3 other rows have values.

I apologise I keep asking questions, I just dont know why such a simple thing does not work.

 

[pbaldy]

Nz(Field1, 0) + Nz(Field2, 0) +...

 

[roxanat]

It worked! Finally!

Total_Throughput: Nz([Oil_Throughput_Capacity_(bpd)],0)+Nz([Gas_Throughput_Capacity_(MMcfd)],0)+Nz([LNG_Throughput_Capacity_(Tonnes_per_Annum)],0)

Thank you so much!

There will be rows from other append queries that will be attached to Total Throughput,but they will be blanks.

Is there any way I can replace the 0 with just blanks in the above expression?

Thank you again so much!

 

[pbaldy]

You can try replacing 0 with "", but not sure how that will affect the math. The "" would make the field text rather than numeric.

 

[roxanat]

I tried ""," ", nothing works,it gives me Error. I gave up on trying,although I find it very strange that access does not give you a way to replace a 0 with a blank in an expression...

Thank you so much.

The more pressing issue is with forms/subforms ,I have posted a question in the Forms subsection of the Forum.I dont dare asking you to have a look at that,you must be busy and taking time to help us in here is very kind of you.

 

[Galaxiom]

I tried ""," ", nothing works,it gives me Error. I gave up on trying,although I find it very strange that access does not give you a way to replace a 0 with a blank in an expression....

Of course Access can replace zero with a Null.

IIF({expression} = 0, Null, {expression})

 

[roxanat]

Of course Access can replace zero with a Null.

IIF({expression} = 0, Null, {expression})

Do you mind writing the entire expression considering that at the moment it is :

Total_Throughput:Nz([Oil_Throughput_Capacity_(bpd)],0)+Nz([Gas_Throughput_Capacity_(MMcfd)],0)+Nz([LNG_Throughput_Capacity_(Tonnes_per_Annum)],0).

Thank you!

 

[Galaxiom]

Personally I would use a custom function to make the query tidier. Otherwise the entire expression must be repeated.

Total_Throughput:Iif(Nz([Oil_Throughput_Capacity_(bpd)],0)+Nz([Gas_Throughput_Capacity_(MMcfd)],0)+Nz([LNG_Throughput_Capacity_(Tonnes_per_Annum)],0), Null,  Nz([Oil_Throughput_Capacity_(bpd)],0)+Nz([Gas_Throughput_Capacity_(MMcfd)],0)+Nz([LNG_Throughput_Capacity_(Tonnes_per_Annum)],0))