Summing a query/query order

[rantaljo]

Please open the attached zipped database files. I have included the query, links to tables to run the query, and a report I’m working on that uses the query as the data source in one database, and the tables in the *be file (I had to split it to fit the size reqt). The end game is to display the mass of pollutant discharged, and then sum the mass by group (on the report, I have put a label called ‘Test’ in the spot I want to put the summed total).

Two problems/issues:
(1) I don’t know how to get the query to run without having a popup box for ‘value’ before the results are displayed. I assume that I am doing things in the wrong order, or trying to do too much in one query, but I don’t know how to fix it.
(2) I think related to the first question (i.e. not knowing how to break it apart), is how to sum the ‘mass’ that is calculated. I’m trying the following:

Mass: FormatNumber(IIf([FeedFlowUnits]![UOMID]=26,(([Value]*[FeedFlowRate]*3.785)/1000),([Value]*[FeedFlowRate]*3.785)),2)

The calculation itself is working correctly to convert concentration to mass using a ‘Value’ that is calculated in a different column of the query. However, I cannot use the aggregate functions on this column either in the query or on the report because "the expression is typed incorrectly, or it is too complex to be evaluated." (I want to Sum). In an earlier iteration of the query, I was doing the ‘Value’ calculation on the same column, but split it off one of the calcs into another column (called ‘Value’ in an effort to simplify), thinking that this would allow me to use the ‘Sum’ function.

Can you tell what I’m doing wrong?

Thanks for any thoughts/guidance you may have.

[HansV]

You forgot to include the Analyte table in the backend. Since the query refers to this table, I cannot open it. Could you repost the backend with the extra table?

[rantaljo]

Sorry about that. Here's a new be

[HansV]

Sorry to be a nuisance - I didn't look carefully enough. The Pollutant, Test and UOM tables are missing too.

[rantaljo]

Gosh. No nuisance. My fault. I'm sorry. Attached is the newest zipped back-end file.

[HansV]

Thank you. There doesn't seem to be any need to use a Totals query - it returns 195 records whether it is a standard query or a totals query. If it is a standard query, it works without error.

If you do want to group, you'll have to do so on fewer fields - please indicate which ones.

The definition of Value and of Mass can be changed slightly - I wouldn't use FormatNumber since that returns a text value, not a number. For Value, I'd use

Value: IIf([PerfCalcValueQual]="<",0,[PerfCalcValue])

And for Mass, you can simplify the definition by including the definition of Value and by extracting the common part:

Mass: IIf([PerfCalcValueQual]="<",0,Round([PerfCalcValue]*[FeedFlowRate]*3.785/IIf([UOM]![UOMID]=26,1000,1),2))

See the attached version - you'll have to relink the tables to the backend.

EMS Mock-up_Humphrey2.zip

[rantaljo]

Thank you Hans. As usual, you've exceeded my expectations. This mass calculation you've provided allowed me to do all the things I wanted, and to resolve the 'value' popup (I was able to get rid of that column). I wish I could pay back the help.

Thanks again with warmest regards,

Julie

[HansV]

You're welcome! Glad to have been able to help.