Ordering of Ship Dates

[Bennie47250]

Using Access 2000

Hopefully the length of this question will not scare most folks off.

Working on a report that shows the items on an order and when the item ships. The items in question are highly specific and not used often so we frequently ship what we have and backorder the balance. Sometime it may take us several shipments before we have the order shipped complete.

Have a manager requesting a report to show shipments of these items and he is asking for the ship dates to be listed like I have below.

Order# | Product # | Qty Ordered | Ship Date 1 | Ship Date 2 | Ship date 3 | Ship date 4 | Ship date 5 |

Looking for suggestions of how to determine which Ship date bucket to populate.

As I see it, determining the Max or Min per order # is easy but being able to determine the others is not as easy as the formula needs to know that a date has or has not been evaluated already.

One of my colleagues suggesting running a “totals query” that would give me the order numbers and the unique ship dates for each order by grouping on order # and ship date. Do this and get the desired results but new need to know how to go about getting the order assigned. This data looks like this:.
Order # | Ship Date | Ship Order
936864 8/25/16
936864 9/15/16
936864 9/25/16
958129 8/18/16
958129 8/25/16
985789 8/25/16
985421 8/16/16

One suggestion was to go thru the list and find the max ship date for each order and then someone how populate the ship order with 1. Then run a similar query that also looks for the max ship date for each order number where the ship order field is empty or <> 1 then populate the ship order with a 2. Then run a similar query that also looks for the max ship date for each order number where the ship order field is empty or <> 1 or <>2 then populate the ship order with a 3. So on and so on.

I would like to do this using a single query that could find the max ship date for each item and populate ship order with a 1. Then I would use a similar query to find the 2nd and 3rd 4th and 5th shipments. Using the access GUI tools it does not seems to be possible to do this. Group Max and update in 1 query. I know I could do it with multiple tables and multiple queries but trying to avoid all that.

Thanks

 

[dhookom]

Your table structure is faulty (not normalized). Try this normalizing union query to get moving in the right direction.

SELECT 1 As Seq, [Order#], [Ship Date 1]
FROM YourUnNormalizedTable
UNION ALL
SELECT 2, [Order#], [Ship Date 2]
FROM YourUnNormalizedTable
UNION ALL
SELECT 3, [Order#], [Ship Date 3]
FROM YourUnNormalizedTable
UNION ALL
SELECT 4, [Order#], [Ship Date 4]
FROM YourUnNormalizedTable
UNION ALL
SELECT 5, [Order#], [Ship Date 5]
FROM YourUnNormalizedTable

Duane
Hook'D on Access
MS Access MVP

 

[MajP]

If your table was normalized to start with (which you should do for many reasons)

TblOrders
Orders
ShipDate

Then you can find the ranking of the shipdates like

SELECT 
 tblOrders.Order, 
 tblOrders.ShipDate, 
 (select Count(*) + 1 from tblOrders as B where B.Shipdate < tblOrders.shipDate and TblOrders.Order = B.order) AS Rank
FROM 
 tblOrders
ORDER BY 
 tblOrders.Order, tblOrders.ShipDate;

[tt]Order ShipDate Rank
936864 8/25/2016 1
936864 9/15/2016 2
936864 9/25/2016 3
958129 8/18/2016 1
958129 8/25/2016 2
985421 8/16/2016 1
985789 8/25/2016 1[/tt]