Background:
Developing a DB to track purchase orders. Each order can contain one or more items.
Table Design/Relationship:
I have two tables 1) Order and 2) OrderDetails in a one to many relationship. The Order Table contains general order info and has a field "GrandTotal" which is the sum of all the item prices in the order. The OrderDetails Table has a record for each item in each order.
Objective/Problem:
I have a report that joins the two tables and displays the current months orders and the contents of each order. The report is grouped by order number and displayed in the group footer is the GrandTotal field. The order contents (the items making up the order) is in the details section. In the report header i want to display the the total of all the orders. In a text box i placed =Sum([GrandTotal]) but the grandtotal of each item gets multiplied by the number of items in the order. For example, if there is one order with a GrandTotal of $2 and in that order there are two items, Sum([GrantTotal]) returns $4 instead of $2.
Does anyone know how I can display the sum of all the order's GrandTotals in the report header?
Developing a DB to track purchase orders. Each order can contain one or more items.
Table Design/Relationship:
I have two tables 1) Order and 2) OrderDetails in a one to many relationship. The Order Table contains general order info and has a field "GrandTotal" which is the sum of all the item prices in the order. The OrderDetails Table has a record for each item in each order.
Objective/Problem:
I have a report that joins the two tables and displays the current months orders and the contents of each order. The report is grouped by order number and displayed in the group footer is the GrandTotal field. The order contents (the items making up the order) is in the details section. In the report header i want to display the the total of all the orders. In a text box i placed =Sum([GrandTotal]) but the grandtotal of each item gets multiplied by the number of items in the order. For example, if there is one order with a GrandTotal of $2 and in that order there are two items, Sum([GrantTotal]) returns $4 instead of $2.
Does anyone know how I can display the sum of all the order's GrandTotals in the report header?