Summay Query Sorted and filtered on a calculated field

[tonygg]

Hi

I was hoping for a bit of help. I have a table of profits made by product. Each product is in a section. I have a query to sum up profit made by section (excluding some sections by using HAVING). I then calculate at the section level the percent profit and want to order the results by biggest profit first. My query so far is:

SELECT TONYProductProfitabilityTable.[Product Section Name], First(TONYProductProfitabilityTable.[Product Price Type]) AS [FirstOfProduct Price Type], Sum(TONYProductProfitabilityTable.CostofStockLeft) AS SumOfCostofStockLeft, Sum(TONYProductProfitabilityTable.CostValueofUnitsSold) AS SumOfCostValueofUnitsSold, Sum(TONYProductProfitabilityTable.CurrentProductProfit) AS SumOfCurrentProductProfit, IIf(SumOfCostValueofUnitsSold>0,Round((SumOfCurrentProductProfit/SumOfCostValueofUnitsSold)*100,2),0) AS PrecentProfitonSoldUnits, IIf(SumOfCostofStockLeft>0 And SumOfCostValueofUnitsSold>0,Round(SumOfCostofStockLeft/(SumOfCostValueofUnitsSold+SumOfCostofStockLeft)*100,2), IIf(SumOfCostofStockLeft=0 And SumOfCostValueofUnitsSold>0,0,100)) AS PercentStockLeft
FROM TONYProductProfitabilityTable
GROUP BY TONYProductProfitabilityTable.[Product Section Name]
HAVING (((First(TONYProductProfitabilityTable.ProductReportingType))<>'Gift Voucher' And (First(TONYProductProfitabilityTable.ProductReportingType))<>'Fabric Basics' );

I need to sort the results by biggest profit (IIf(SumOfCostValueofUnitsSold>0,Round((SumOfCurre ntProductProfit/SumOfCostValueofUnitsSold)*100,2),0) AS PrecentProfitonSoldUnits) but cant find a away to express it to make sql understand.



Any ideas of how to do this?

Many thanks

Tony

[orange]

If you put the sections to be excluded in a separate table, you can join that table to your original to get the data you need for processing. I do not use First, and neither do many others.
What exactly do you mean by FIRST? There may be options to achieve what you need.

It would be helpful to readers, and you, if you would provide some sample data showing some raw input, some exceptions, and an example of the desired output/final product.

A jpg of your table layout would also help readers.

Good luck with your project.

[tonygg]

Thanks for the feedback.

I don't want to add an extra table just to summarise the data. This seems excessive. Particularly as I want to cut and summarise the data in various ways. This would require a table for each exclusion.

Everything works on the query excluding the ordering of the results. I think it is because I am trying to order using a calculated field based on summarised data. I could achieve the same result by having the query and then having another query that usies the first and applies the sort (nest query).

I am using the sql code to populate a listbox in a form (to display the results). Is it possible to create sql in VBA that does both queries on the fly and would mean I don't need to keep all the extra queies in my DB?

Many thanks

Tony

[orange]

Probably. See these from Allen Browne.
http://allenbrowne.com/subquery-01.html
http://allenbrowne.com/subquery-02.html


After posting I see that this has been marked solved.
I guess you found a solution.