SUM giving undesired.

[ivow]

I've got a table of shipments, with multiple orders in each shipment. I've
got shipments table and an order table.
I've created a report that displays items from the shipments table:

ShipID ShipDate ShipLocation ShipCost
802 Nov 27 Winnipeg 10.00
803 Nov 30 Regina 10.00
804 Nov 1 Toronto 10.00

Total Shipping Cost: 30.00

If I include the orders table in the query I get this:

ShipID ShipDate ShipLocation ShipCost OrderID
802 Nov 27 Winnipeg 10.00 V323
802 Nov 27 Winnipeg 10.00 V324
803 Nov 30 Regina 10.00 V325
803 Nov 30 Regina 10.00 V326
804 Nov 1 Toronto 10.00 V327

Total Shipping Cost: 50.00

Even though my shipping cost has a one-2-one relationship with my ShipID
when I do a "=sum(ShippingCost)" on my report I get the sum of all the
shipping costs.
Ideally, I would like to display something like this:

ShipID ShipDate ShipLocation ShipCost OrderID
802 Nov 27 Winnipeg 10.00 V323
802 Nov 27 Winnipeg - V324
803 Nov 30 Regina 10.00 V325
803 Nov 30 Regina - V326
804 Nov 1 Toronto 10.00 V327

Total Shipping Cost: 30.00

Do I need to make a change to my query or my report? I tried using
DISTINCTROW on my query and that only gave me the first OrderID of each
ShipID.

TIA

Ivo

Ivo

 

[JeremyNYC]

What's in the orders table?

Do you have the tables joined in the query?

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developer's section of the site for some helpful fundamentals.

 

[ivow]

yes. Ivo

 

[Haitham]

Ivo

May u post ur Join SQL Statement Query here, i gues there is an error with that.

Good Luck

 

[Wocca]

You in fact appear to have a one to many between the two tables i.e. Multiple orders against one ship id so the result from the SQL is correct from that perspective.

Personally I would list the Orders gainst each ship ID in a sub report, this would provide maximum clarity.

Regards
Warwick