By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,665 Members | 2,763 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,665 IT Pros & Developers. It's quick & easy.

On Time Delivery Query

P: 42
I’m trying to create a query or Report for “On Time Delivery”. I have query fields of PO, Date Received, Due Date and Date Shipped. I’d like to calculate EARLY (shipped 3 days early from the due date), On Time (Shipped on Due Date or 2 days early) and LATE (Shipped past Due Date) and on a business days only (excluding Saturday and Sunday). I would appreciate any ideas.
Thanks,
John

Access 2000
Vista
Feb 13 '08 #1
Share this Question
Share on Google+
2 Replies


Delerna
Expert 100+
P: 1,134
Here is the query I came up with


Expand|Select|Wrap|Line Numbers
  1. SELECT Table1.PO, 
  2.     Table1.Date, 
  3.     Table1.Received, 
  4.     Table1.DueDate, 
  5.     Table1.ShippedDate, 
  6.     Weekday([DueDate]) AS DueDay, 
  7.     DateDiff("d",[DueDate],[ShippedDate]) AS NumDays, 
  8.     IIf([DueDay]+[NumDays]<0 Or [DueDay]+[NumDays]>7,2,0)*IIf([NumDays]<0,-1,1) AS Weekends, 
  9.     [numDays]-[weekends] AS WorkDays, 
  10.     IIf([Workdays]<=-3,"Early",IIf([workdays]>0,"Late","On Time")) AS Timeliness
  11. FROM Table1;
  12.  
Basically I calculated
weekday is the day number of the delivery date
NumDays is the number of actual days it took to deliver
but you don't want to count the weekend so
if early then DueDay+Numdays will be less than 0 if there is at least 1 weekend
if late then DueDay+Numdays will be greater than 7 if there is at least 1 weekend
If either of the above are true i return 2 otherwise 0 and then adjust it to be either negative or positive depending on whether the delivery is early or late as weekends
Now all i needed to do is subtract weekends from NumDays to get the number of WorkDays used to make the delivery

I then used the value of WorkDays to return Ealy,On Time or Late according to your specs

Hope it helps
Feb 17 '08 #2

P: 42
Thanks for help but I got an answer from another forum with this code below for query and it works pretty good. But I want to modify the "On Time=Shipped on DueDate or 2 days early and 2 days later from due date".

Currently with this code is EARLY (shipped 3 days early from the due date), On Time (Shipped on Due Date or 2 days early) and LATE (Shipped past Due Date)

IIf([ShipDate]<=DateAdd("d",-3,[DueDate]),"Early",IIf([ShipDate]>=DateAdd("d",-2,[DueDate]) And [ShipDate]<=[DueDate],"On Time","Late"))

Any help would be much appreciated.
Thanks,
John


Here is the query I came up with


Expand|Select|Wrap|Line Numbers
  1. SELECT Table1.PO, 
  2.     Table1.Date, 
  3.     Table1.Received, 
  4.     Table1.DueDate, 
  5.     Table1.ShippedDate, 
  6.     Weekday([DueDate]) AS DueDay, 
  7.     DateDiff("d",[DueDate],[ShippedDate]) AS NumDays, 
  8.     IIf([DueDay]+[NumDays]<0 Or [DueDay]+[NumDays]>7,2,0)*IIf([NumDays]<0,-1,1) AS Weekends, 
  9.     [numDays]-[weekends] AS WorkDays, 
  10.     IIf([Workdays]<=-3,"Early",IIf([workdays]>0,"Late","On Time")) AS Timeliness
  11. FROM Table1;
  12.  
Basically I calculated
weekday is the day number of the delivery date
NumDays is the number of actual days it took to deliver
but you don't want to count the weekend so
if early then DueDay+Numdays will be less than 0 if there is at least 1 weekend
if late then DueDay+Numdays will be greater than 7 if there is at least 1 weekend
If either of the above are true i return 2 otherwise 0 and then adjust it to be either negative or positive depending on whether the delivery is early or late as weekends
Now all i needed to do is subtract weekends from NumDays to get the number of WorkDays used to make the delivery

I then used the value of WorkDays to return Ealy,On Time or Late according to your specs

Hope it helps
Feb 18 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.