Updating a field in a column in a datasheet

[tonygg]

Thanks. I posted one of the calculations in an earlier post - Function OrderLevelDiscountsPercentCalc(...). Please see above



Am I right to understand you are saying I can embed this code into a query? That is great. How do I do that so it passes the values into the function and puts the results into to the results.

Thanks again.

Tony

[June7]

I am saying the VBA code is probably not needed at all. Do the calculations with expressions in query or textbox ControlSource.

I am not sure what that function is calculating. Are you trying to summarize order details? Summary calculations should be done with aggregate (GROUP BY) Totals queries or with report design.

[tonygg]

Ok. I guess I am not sure how practical it would - sorry if I am wrong. I would be really very happy if it was. I will give an example in English and would really appreciate it if you can sense check it is feasibility:

An example is to apply a proportion of any order level discounts that should be attributed to a product.

In order to do this I need to find all the orders that have this product in them. For each order found add up the total lines ordered (regardless of product) then divide it by the number units bought to see the proportion due to this product. These need to be summed up across all the orders and then the result is the value to display.

A second example find the highest price paid for any product (which can be bought in 4 different ways).

To do this you need to again find all orders for the product and then compare each order to find the one with the highest selling price for each buying choice (1 of 4). Not all products have all buying choices and so only once you have gone through all the buying choices for all the orders can you then decide which is in fact the highest unit price paid.

I guess each of these require access to multiple other tables and need looping through multiple rows in those tables.

Do these sound practical in expressions?

How does one do this if it is impractical to have it in expressions?

Many thanks

Tony

[June7]

I expect queries can do the calculations you describe. Or report using Grouping & Sorting with aggregate calcs. Some aggregate functions are Sum, Avg, Max, Min.

[tonygg]

Great. I know how to the simple if a + B = c expressions but I don't really understand how to create the nested looping through different tables and retain intermediate calculations. Would it be possible for you to show me an example so I can see the structure? Many thanks Tony

[June7]

There is no 'nested looping', there is a query (or multiple queries) or there is report design.

[tonygg]

Ok So if I understand correctly it is not appropriate to do what I need through queries. I guess after all this I am back to square one. You indicated report design may be the solution. Can I achieve my original goal if I change the datasheet into a report? If so could you be so kind as to show me the outline logic to be able to update the columns (per row) of a report and insert rows into the report. Thank you Tony

[June7]

Report design has Grouping and Sorting feature. Aggregate calcs can be done in report and group header/footer sections. Textbox in detail or group section can reference the aggregate calc textboxes to do percentage calcs.