Consolidation

[trans]

I have 3 columns of data, Ship Date, Destination City, and Weight. I am
looking to consolidate shipments, so if there are 2 small shipments going to
the same destination city I want to turn it into 1 large shipment. I was able
to do this by grouping by Ship Date and Dest City, and getting a sum of the
weight, but only for same day shipments. I want to expand it by allowing
shipments leaving within 1 day of each other (also going to the same
destination city) to be combined. Any ideas? Thank you.

 

[pietlinden]

I have 3 columns of data, Ship Date, Destination City, and Weight. I am
looking to consolidate shipments, so if there are 2 small shipments going to
the same destination city I want to turn it into 1 large shipment. I was able
to do this by grouping by Ship Date and Dest City, and getting a sum of the
weight, but only for same day shipments. I want to expand it by allowing
shipments leaving within 1 day of each other (also going to the same
destination city) to be combined. Any ideas? Thank you.

Only thing I can think of is grouping by pairs of days, so you could
create a query that did something like

SELECT tblShipment.PackageID, tblShipment.ShipDate,
tblShipment.DestinationCity, tblShipment.Weight, DatePart("ww",
[ShipDate]) AS WeekNo, (DatePart("w",[ShipDate])+1)\2 AS DayNo
FROM tblShipment;

This groups the shipments so they go on every other day. Then you
could base your query/report on that query. I guess you could tweak
it to use a WHERE clause to define a "small shipment". Not sure how
you'd do it for Day +/- 1. That's a bit harder.