Customer order reporting formulas

[messagealicia]

I have an issue that seems (logically) relatively easy to correct but I cannot seem to figure out how to fix it. I've created an Access database for order entry for various locations. My forms all work correctly. Each order is charged a handling fee (various amounts). On my order summary report and order detail report, I total each order, my reference for the handling fee field references my handling fee in the table for that order. When I try to calculate my location totals (multiple orders, the sum of my handling fee is getting added for every line item in the order. If I sum and divide by the count that is not accurate either because it totals the handling fees for every line item and divides by the number of line items in all orders for that location - mathematically not the same as sum and divide per order - and aggregate functions are not allowed (such as sum(sum(handlingfee/count))). So, here are my control sources:

Label: Control Source:
DetailSubtotal =Sum([Quantity]*[UnitPrice]) - subtotal on detail report, listed in Order ID Footer
txtHandlingDetailReport =[Handling Fee 2] - handling fee on detail report, listed in Order ID Footer


DetailOrderTotal =[DetailSubtotal]+[txtHandlingDetailReport] - total on detail report (subtotal + handling fee), listed in Order ID Footer
txtLocalSubtotal =Sum([Quantity]*[UnitPrice]) - subtotal per location, listed in Location ID Footer - formula works fine
txtLocHandlingFee =Sum([Handling Fee 2]) - listed in Location ID Footer - *** IS ADDING FEE PER LINE ITEM INSTEAD OF ONE FEE PER ORDER
txtLocalTotal =Sum([Quantity]*[UnitPrice])+Sum([Handling Fee 2]) - listed in Location ID Footer - will work once handling fee formula is corrected

I have also tried: =Sum([Handling Fee 2])/Count([ProductID]), not accurate results.

PLEASE HELP!!!

[June7]

Hard to follow that narratively. Want to provide the db for analysis so I can see the report structure and test calcs? Follow instructions at bottom of my post.

[messagealicia]

My database file is attached. I changed location and product names due to confidentiality. Thank you for your help.

[June7]

The report aggregate calcs will use data at the most refined level. Since the report RecordSource is a join of parent/child records with 1 to many relationship, the Orders data repeats for each of the joined OrderDetails records and the calcs reflect that. So either use subreports or domain aggregate function.

=DSum("[Handling Fee 2]","Orders","CustomerID=" & [CustomerID])

Any filter criteria applied to the report RecordSource (such as date restrictions) will have to be included in the function for the data to synchronize.