Report Footer totals

[Guest]

I have a sales summary report based on a multi-table query (with date range
criteria) which includes tblOrders (one off order information such as
OrderID, CustomerCode, ShippedDate, DeliveryCharge etc) and tblOrderDetails
(order details such as ProductCode, UnitCost, UnitSell and ShippedQuantity)
linked by the OrderID.

Each line in the report is a summary of a sale (displayed in the OrderID
Footer Section) consisting of the OrderID, OrderDate, SalesPerson,
CustomerCode, TotalSaleValue, CostofGoodsSold and DeliveryCharge where the
TotalSaleValue and CostofGoodsSold controls are calculated using the Sum
function.

The Details section visible property is set to false.

There is a Grand Totals line displayed in the ReportFooter section
displaying ReportTotalSalesValue, ReportTotalCosts and
ReportTotalDeliveryCharges. The ReportTotalSalesValue, ReportTotalCosts are
calculated using the sum function.

My problem is displaying the total delivery charges for the report. This is
because for those sales which have a delivery charge it is repeated in the
query for each item sold under that OrderID, where as it applies to the
entire sale not each item in the sale.

So my question is how do I go about putting a calculated control in the
report footer to sum the delivery charges, or is there another way to
properly calculate the total of the delivery charges and display that total
in the Report Footer

My apologies for the long winded description however any help would be
appreciated.

Cheers,

 

[Marshall Barton]

I have a sales summary report based on a multi-table query (with date range
criteria) which includes tblOrders (one off order information such as
OrderID, CustomerCode, ShippedDate, DeliveryCharge etc) and tblOrderDetails
(order details such as ProductCode, UnitCost, UnitSell and ShippedQuantity)
linked by the OrderID.

Each line in the report is a summary of a sale (displayed in the OrderID
Footer Section) consisting of the OrderID, OrderDate, SalesPerson,
CustomerCode, TotalSaleValue, CostofGoodsSold and DeliveryCharge where the
TotalSaleValue and CostofGoodsSold controls are calculated using the Sum
function.

The Details section visible property is set to false.

There is a Grand Totals line displayed in the ReportFooter section
displaying ReportTotalSalesValue, ReportTotalCosts and
ReportTotalDeliveryCharges. The ReportTotalSalesValue, ReportTotalCosts are
calculated using the sum function.

My problem is displaying the total delivery charges for the report. This is
because for those sales which have a delivery charge it is repeated in the
query for each item sold under that OrderID, where as it applies to the
entire sale not each item in the sale.

So my question is how do I go about putting a calculated control in the
report footer to sum the delivery charges, or is there another way to
properly calculate the total of the delivery charges and display that total
in the Report Footer

Add a text box (named txtRunFreight) to the OrderID group
footer section. Set its control source to DeliveryCharge
and its RunningSum property to Over All.

Then a report footer text box can display the freight grand
total by using the expression =txtRunFreight

 

[Guest]

Marsh,

Fantastic!!

I thought I may have had to play with queries to get it right. This is great.

Thanks again

Cheers,