Adding together the results of calculations in previous columns

[MichealShinn]

I'm trying to do a query for our engineers that's getting way too complicated for me. They sent me an Excel spreadsheet with formulas in it, and now I'm trying to reproduce that in Access 2007. The first one figures out BTUs per square foot for electric meters and the second one figures out BTUs per square foot for gas meters. Now, in the third one, I'm trying to add together the numbers from the previous two columns, and all I'm getting is blanks. Here's the first formula:

kBTU/SF e (AD): IIf([Numbers Combined]![Product_Code]="E",[Numbers Combined]![Usage]*3413/1000/[Benchmarking]![FacilitySquareFeet])

and here's the second one:

kBTU/SF/yr Therm nrml(AF): (((IIf([Numbers Combined]![Product_Code]="G",([Numbers Combined]![Usage]*7122)/[Numbers Combined]![Degree Days]))*100)/[Benchmarking]![FacilitySquareFeet])

So, in the third column, I copied and pasted the first formula and then put in a plus sign and then pasted in the second formula:

k BTU/SF/yr E & G: IIf([Numbers Combined]![Product_Code]="E",[Numbers Combined]![Usage]*3413/1000/[Benchmarking]![FacilitySquareFeet])+(((IIf([Numbers Combined]![Product_Code]="G",([Numbers Combined]![Usage]*7122)/[Numbers Combined]![Degree Days]))*100)/[Benchmarking]![FacilitySquareFeet])

and I'm getting nothing, just a column full of blank cells.

Am I going about this in totally the wrong way?

Thanks in advance for your help.

[Robeen]

I think you're on the right track.
You can try this:
Create a new query - using your existing query as the data source.
Create a new cloumn in the new query like this:

TotalBTUs: [kBTU/SF e (AD)] + [kBTU/SF/yr Therm nrml(AF)]

Hope this helps!

P.S. I am wary of using '/' & ')' . . . in field names. I try not to use spaces in my field names either.

[MichealShinn]

Thank you very much.

[MichealShinn]

Robeen,

Thanks again for your answer. Unfortunately, as it turns out, my problem really occurs several steps earlier in this process.

We are a utility company. We have a table in our data warehouse that has the results of meter reads in it. If an electric meter gets read, there's a field called Product Code that has an E in it. If a gas meter gets read, there's a G in the Product Code fields. So, when my meter gets read, there are two records, like this

ID Product Code Usage Revenue

1 E 200 $50
1 G 50 $15 (made-up numbers, of course)

Now, what I need to do, before I even get to the fancy calculations for the engineers, is to get all the numbers for customer number 1 into one record, that looks like this:

ID Electric Usage Electric Revenue Gas Usage Gas Revenue

1 200 $50 50 $15


I tried a union query, but that didn't do it, I still had two separate records. What should I do?

Thanks again for your help.

[MichealShinn]

I think I might have figured it out. If not, I'll be back. Thanks again.

[orange]

You might want to read RainLover's tutorial on naming conventions at
http://www.access-programmers.co.uk/...d.php?t=225837

Seems you are making headway with your approach. Let us know if you solve it.

You might want to look at a function with some parameters.

My advice would be to work from the formula in excel with some known test data and results.
Create a function in Access with parms.
Call the function with a test case,
resolve/reconcile any differences with the Excel sample
then approach more test cases.

Good luck.

[ssanfu]

kBTU/SF e (AD): IIf([Numbers Combined]![Product_Code]="E",[Numbers Combined]![Usage]*3413/1000/[Benchmarking]![FacilitySquareFeet])

and here's the second one:

kBTU/SF/yr Therm nrml(AF): (((IIf([Numbers Combined]![Product_Code]="G",([Numbers Combined]![Usage]*7122)/[Numbers Combined]![Degree Days]))*100)/[Benchmarking]![FacilitySquareFeet])

Something I noticed is that the IIF() is incorrect. The syntax is:
IIf(expr, truepart, falsepart) .......... Note that ALL 3 parts are required.

You are missing the falsepart in both functions.

Here is how I build complicated IIF() statements.

--Start with the function:
kBTU_E: IIf(expr, truepart, falsepart)

--Enter in the expr part (the condition that evaluates to TRUE or FALSE)

kBTU_E: IIf([Numbers Combined]![Product_Code]="E", truepart, falsepart)

--Add the truepart:

kBTU_E: IIf([Numbers Combined]![Product_Code]="E", [Numbers  Combined]![Usage]*3413/1000/[Benchmarking]![FacilitySquareFeet], falsepart)

--Then add the falsepart . In this case, anything other that "E" should be zero. So:

kBTU_E: IIf([Numbers Combined]![Product_Code]="E", [Numbers  Combined]![Usage]*3413/1000/[Benchmarking]![FacilitySquareFeet], 0)

Now the second formula.

--Start with:
kBTU_G: IIf(expr, truepart, falsepart)

--Enter in the expr part (the condition that evaluates to TRUE or FALSE)

kBTU_G: IIf([Numbers Combined]![Product_Code]="G", truepart, falsepart)

--Add the truepart:

kBTU_G: IIf([Numbers Combined]![Product_Code]="G", (([Numbers Combined]![Usage]*7122) / ([Numbers Combined]![Degree Days])*100)/[Benchmarking]![FacilitySquareFeet], falsepart)

--Then add the falsepart . In this case, anything other that "G" should be zero. So:

kBTU_G: IIf([Numbers Combined]![Product_Code]="G", (([Numbers Combined]![Usage]*7122) / ([Numbers Combined]![Degree Days])*100)/[Benchmarking]![FacilitySquareFeet], 0)

(In this second formula, you had a couple of parenthsises in front of the IIF(); those parenthsises were in the wrong place.)

Then you can do like Robeen said:

TotalBTUs: kBTU_E + kBTU_G