Solved Grouping Query Results Together Based On Calculated Fields

[LGDGlen]

Hi All

I have the following fields in my query calculating various values but in the end i need to know the Percentage of Waste value. This is working ok but i would like to sort based on the WastePercent field grouping anything 5% or less together, anything between 5% and 10% together and then finally anything over 10% together. The SortOrder field shows the correct values for this grouping process, but when i try to sort ascending for that specific field it throws up a dialog asking for WastePercent as a parameter.

totalSellingKG: IIf(([del_prod_case_num_kept]=0) Or ([del_prod_case_num_kept] Is Null),[del_prod_case_num_orig]*[del_prod_count]*[del_prod_weight],[del_prod_case_num_kept]*[del_prod_count]*[del_prod_weight])

totalSoldKg: IIf(([del_prod_sale_case_num]=0) Or ([del_prod_sale_case_num] Is Null),[totalSellingKG],IIf([del_prod_sale_count]*[del_prod_sale_weight]=0,[del_prod_sale_case_num]*[del_prod_count]*[del_prod_weight],[del_prod_sale_case_num]*[del_prod_sale_count]*[del_prod_sale_weight]))

QuantityClaim: del_prod_claim_cust_quant

SellingTotal: [totalSellingKG]*[del_prod_sale_final_price]

SoldTotal: [totalSoldKg]*[del_prod_sale_final_price]

WastePercent: IIf([SellingTotal]=[SoldTotal],Round(([QualityClaim]/[SellingTotal])*100),Round(([QualityClaim]/[SoldTotal])*100))

SortOrder: IIf(Nz([WastePercent],0)<=5,1,IIf(Nz([WastePercent],0)>5 And Nz([WastePercent],0)<=10,2,3))

I believe it has something to do with aliases but not entirely sure. In the above anything that starts "del_" is a field from a table

If it is because of the aliases for the calculated fields i'm not entirely sure what to do about it so would appreciate any pointers as to the best way to group things together in this query as specified

Hope that makes sense, happy to provide any more information as required

kind regards

Glen

 

[arnelgp]

what is wastepercent? percentage of a value (actual value is 0.05).
you can use Switch() function to sort.

wastepercent is Calculated column so i think you cannot use it (only the base calculation).
you need to create another query based on this query, then you can sort it using wasterpercent column.

 

[LGDGlen]

@arnelgp i had a sneaking suspicion that it would end up being a new query based on this one and sort that, but was just curious to see if it was something i was doing wrong

i am not aware of the Switch() function i'll look it up in case it helps me in the future, but i'll try the second query and sort that and see what happens, thank you for your swift response as always

 

[arnelgp]

Switch(Nz([WastePercent],0)<=5, 1, Nz([WastePercent],0)<=10, 2, True, 3)

 

[LGDGlen]

oh so it is switch (case) statement but in a function to get round the nested IIF statements, well that is certainly helpful as i have a number of places i have used the nested IIFs that would be much much easier to understand using that

 

[LGDGlen]

@arnelgp yep a second query and sorting that solved the issue thank you