Conditional Calculation

[natep26]

I currently have two queries. One calculates emissionsperhour based on a results field and the other calcualtes emissionsperperiod based on the emissionsperhour query and the inspection date. I need to go a little deeper and seperate the emissionsperperiod into two categories. Category 1 will sum the emissions for the year up to 5 (Results). Category 2 will sum the emissions for the year from 5 and over.

My theory is this...in the emissionsperhour query create 2 more expressions which are Cat1lbperhour and Cat2lbperhour. Cat1lbperhour needs to calculate using a maximum of 5. Cat2lbperhour needs to first look for any results >5, subtract 5, then calculate lbperhour based on the difference.

Any ideas as to what this looks like in SQL?

Thanks

 

[ajetrumpet]

I currently have two queries. One calculates emissionsperhour based on a results field and the other calcualtes emissionsperperiod based on the emissionsperhour query and the inspection date. I need to go a little deeper and seperate the emissionsperperiod into two categories. Category 1 will sum the emissions for the year up to 5 (Results). Category 2 will sum the emissions for the year from 5 and over.

the WHERE clause will serve this purpose.

My theory is this...in the emissionsperhour query create 2 more expressions which are Cat1lbperhour and Cat2lbperhour. Cat1lbperhour needs to calculate using a maximum of 5 <---5 years??. Cat2lbperhour needs to first look for any results >5,
subtract 5, then calculate lbperhour based on the difference. <---not following you here

 

[natep26]

My theory is this...in the emissionsperhour query create 2 more expressions which are Cat1lbperhour and Cat2lbperhour. Cat1lbperhour needs to calculate using a maximum of 5 <---5 years??. Cat2lbperhour needs to first look for any results >5, subtract 5, then calculate lbperhour based on the difference. <---not following you here

The maximum of 5 is Results. For example if a result is 15, i need to calculate Cat1 up to 5 and Cat2 with the difference (10).

Can you give me an example of the WHERE clause that would cap the results at 5 for Cat1?

Thanks for your help.

 

[ajetrumpet]

I was mistaken about the WHERE clause. Now that you have provided more information, that doesn't seem like the route you need.

Let me see if I understand this...

You have a table that lists records that contain a field called "results". Each field cell gives some sort of value that refects data from an inspection. Your 1st query GROUPS and SUMS the field values based on other field data.

There's no problem with all of this, but I'm having difficulty relating to this statement...

The maximum of 5 is Results. For example if a result is 15, i need to calculate Cat1 up to 5 and Cat2 with the difference (10).

It wouldn't hurt to post a screenshot or two of either the table, or the SQL statements of your queries.

 

[natep26]

Below are the SQL Statements for the two queries. Ultimately I need to seperate the 2nd query into Cat1Emissions and Cat2Emissions and sum the VOCperperiod for the year. Cat1Emissions are the sum of vocperperiod <=5 and Cat2Emissions are the sum of vocperperiod over 5ppm.


Does this shed any light?

lbperhourcalc:
SELECT qEPSInspHistory.Results, qEPSInspHistory.CTName, qEPSInspHistory.Inspection_Date, ([circulation rate (gal/min)]*60*8.34*([results]/1000000)) AS VOClbhr
FROM qEPSInspHistory INNER JOIN tMain ON qEPSInspHistory.CTName=tMain.CTName
WHERE (((qEPSInspHistory.CTName)=forms!fctinfo!ctnamedisplay) And ((tMain.[Circulation Rate (gal/min)])=forms!fctinfo![circulation rate (gal/min)]));



emissionsperperiod:
SELECT qEPSEmissionslbperhour.CTName, qEPSEmissionslbperhour.Inspection_Date, IIf(IsNull(DMax("inspection_date","qepsemissionslbperhour","[ctname]='" & [ctname] & "' and [inspection_date]<#" & [inspection_date] & "#")),Null,CDate(DMax("inspection_date","qepsemissionslbperhour","[ctname]='" & [ctname] & "' and [inspection_date]<#" & [inspection_date] & "#"))) AS PreviousInsp, Format([Inspection_date]-PreviousInsp,"dd") AS TimeElapsed, (qEPSEmissionslbperhour.voclbhr*24*([TimeElapsed]/2000)) AS VOCperperiod
FROM qEPSEmissionslbperhour;

 

[natep26]

Switch Function

For anyone intersted I figured out the solution to my problem. By using the "Switch" function.

 

[ajetrumpet]

HOW??? What's the statement look like???

 

[natep26]

VOCupsetlbhr: ([circulation rate (gal/min)]*60*8.34)*((Switch([Results]>5,[results]-5,[results]<5,0))/1000000)

This is in the Expression Builder for this field in the query. Basically it looks for results >5, then subtracts 5 from the results and calculates the field based on whatever is left over from the results. The second part [results]<5,0, looks for results less than 5 and replaces their value with 0, since they are not considered upset emissions.

VOCnormlbhr: ([circulation rate (gal/min)]*60*8.34)*((Switch([Results]>5,"5",[results]<5,[results]))/1000000)

This one looks for results greater than 5 and replaces those values with 5, then it looks for results less than 5, and uses the original value for the calculation.

Make Sense??

 

[ajetrumpet]

Yep!! So, you got two fields, and used your Function inside the expression. Nice job!!