INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Ordering of Ship Dates

Ordering of Ship Dates

(OP)
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

RE: Ordering of Ship Dates

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

CODE --> SQL

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

RE: Ordering of Ship Dates

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

CODE -->

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; 

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close