Designing simple sales report

[Former Member]

I am trying to create a Sales report from two tables: PRODUCT table and ORDER DETAILS table.

The Order Details table has the following fields: Order ID, Product ID, Unit Price and Quantity. The Order ID field is a primary key in another table called ORDERS.

The columns I want in my report are: Product code, Unit Price, Quantity Shipped and Sales. Product code and Unit Price are fields in the Product table and I can show them easily in my report. The problem I am having is displaying the Quantity Shipped and Sales columns. I have created two formulas to calculate this information.

Quantity Shipped formula: Sum ({Orders_Detail.Quantity},{Product.Product ID} )

Sales formula: {Product.Price (SRP)} * (Sum ({Orders_Detail.Quantity},{Product.Product ID} ))

These formulas seem to work well. The problem is that when I place them in my report, I get three rows for every product id. So I end up with three rows of the same information for every product in my report.

I am practicing with the xtreme.mdb that I downloaded from this website. Any help that you can provide would be much appreciated.

[Former Member · Accepted solution]

Place the info that you wish to see once in the group header then if you need to total values in the group header you will need to use a running total to total the field.

[Former Member · Accepted solution]

HI,

Thanks for the info.

Placing the information in the group header solved the problem of having rows appear multiple times. Now each product item appears only once.

HOwever, I am still unable to figure out how to show the quantity shipped for each item. I guess I will have to use the Quantity field in the Order_Details table. Doing a running total on this field in the group header just does a running total on the quantity field without being tied to specific product.

Your help is much appreciated.