Skip to Content
Former Member
May 13, 2009 at 05:44 PM

Designing simple sales report


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.