Variance of data, and total of each variance.

[cjharris]

I would like to calculate the total number of records either identified as >10 or >15. This is the coding I have thus far for >10 Expr3:IIf([VARIANCE DATE] Between 10 And 14,">=10")


>15 Expr4: IIf([VARIANCE DATE]>=15,">=15")

My expressions Identify accurately what columns are greater than 10, and the other expression identifies which columns are greater than 15. But I cannot get the sum function to work. I need for the query to count how many records are greater than 10 days and how many are greater than 15 days.

Thanks and Regards
Charles

 

[GingerR]

ok in a blank query, put in one calculated control:

Grouping: IIf([VARIANCE DATE] Between 10 And 14,">=10",IIf([VARIANCE DATE]>=15,">=15",Null))

bring down "VARIANCE DATE" into the query grid.

From the menu pick View+Totals
change the GroupBy under VARIANCE DATE to COUNT.

that'll do it.

 

[cjharris]

Thank You Ginger for the prompt reply, and the Grouping:coding works fine now I dont have to have two seperate columns to produced the desired results. But I am not clear on when you say bring down "VARIANCE DATE" into the grid and, from the menu pick View+Totals change the GroupBy under VARIANCE DATE to COUNT. I am 90% complete with this task this is the complete 10% please Help.

Thanks and regards,
Charles Harris

 

[cjharris]

Ginger,
It is also telling me that "You can't calculate totals on the asterick" Because the asterick represents all the fields in a table, you can't calculate totals on it. Remove the asterick from the query design grid. Add the fields you want to use to the design grid, and then select the total you want to calculate for specific fields. This is the alert that I get when trying to use the Totals feature. I need the asterick because query 3 refers to coding that uses astericks in my query 1 and query 2.

Is there any other way to get the sums.
I even tried the Sum(Expr3) but that says make reference to query 1. Please help I have to have the total of the two.

 

[GingerR]

ok you're totally confusing me. Naming things "expr1", "expr2", etc is confusing. In the future you'll probably want to give things more descriptive names. You can do that by writing over "expr1" and "expr2".

Anyhow, I'd done exactly what I wrote to you and it worked perfectly for me.

In a query that is based on whatever it is (table or query) where the numbers are that you want to sum up:

You should end up with ONLY two columns in the query.
First do what I said with the calculated field above.
Then double-click on the field VARIANCE DATE that is listed in your table/query, so that it comes down into the query grid.
Then from the menu, choose VIEW, then select TOTALS.
Under the column where VARIANCE DATE is, select the drop-down box that is in the row called TOTAL, and change GROUP BY to COUNT.

You cannot put any other fields into the query grid or it will not sum correctly. Just get this part working and if you still need help after that, let us know.