How can I calculate the number of days between two delivery dates?

[Kim Streker]

I am using Access 2000 and have run into a slight problem: I need to
calculate the number of days between delivery dates in different
records. I know that I can use the DateDiff() function in EXCEL to do
this, but I was really hoping to do this on a report in Access.

The table looks like this:

DeliveryID CustomerID DeliveryDate QtyDelivered
2004-01 01 2-jan-2004 150
2004-02 02 3-jan-2004 150
2004-03 01 22-jan-2004 150

For Customer number 1, the two deliveries were separated by 20 days,
but I am unsuccessful getting this onto a report.

I think this problem requires working with a recordset - which I find
a bit intimidating without guidance.

Any suggestions would be greatly appreciated!

Kim Streker

 

[Ken Snell]

You could use a query that has a calculated field in it to calculate the
difference (using DateDiff function). But, will there only ever be two
records with the same CustomerID? If there can be more, how do you want to
choose which records are to be used for the calculation?

Give us some more info about your data and let's see what we might identify.

 

[Kim Streker]

Thanks for your reply!

There will be several deliveries to each customer. I have a query
that pulls out this years data for one particular customer, and sorts
them by ascending delivery date (which is the same as sorting by the
DeliveryID). This query currently gives me the data that I can then
export to Excel, and three quick formulas later have the # of days
elapsed between each delivery, the average number of days elapsed, and
the next predicted delivery. I want this same information in a report
(a form would work as well) in Access that any user can just open,
instead of me having to do the export and calculations manually.

Does this help?

My current "algorithm" (aka plan of attack) for this is as follows:

1. For a specified customer
2. Find all deliveries between beginning and ending dates
3. Sort by DeliveryDate
4. This becomes a record set
5. Call a function that:
a)finds the first date in the list=>becomes PreviousDate
variable
b)finds the next date in the list=>becomes NextDate variable
c)finds the DateDiff() between PreviousDate and NextDate
d)stores this value in an array
e)cycles through each record pair in the set to fill the array
f)finds the average of all the values stored in the array
g)returns the average to the program
6. Use the Average to determine the next predicted delivery
a)find the last date in the list=>becomes LastDate variable
b)use DateAdd()function to add the Average to the LastDate
c)return the PredictedDate to the program
7. Print the next predicted delivery date on the report.

Once I conquer the "make it a recordset" and "fill the array" parts of
this, I think I can then calculate the average consumption (150 in 20
days = 7.5 per day) to then make an estimated guess on the annual
requirements (7.5 per day *365 = 2737.5 per year)

Thanks for any advice!

Kim

 

[Ken Snell]

From your description, I'm understanding that your records would not ever
have more than one delivery per day for a single customer. It may be easier
overall to assume that this might happen and set up your table structure
accordingly. Meaning, add another field to the table that will be a
"sequence" number and then increment that sequence number for each delivery
for a customer. Using this, if you never reset the sequence for a customer,
you can sort on that field to always ensure that you're getting the
deliveries in the chronological order, and you can more easily "identify"
which record comes after another for the purpose of calculating your results
(you simply sort on the customer and the sequence number). If you decide to
reset the sequence for each delivery day, it can be used in a similar manner
but not as straightforwardly (because you then must sort on the customer and
the delivery date and the sequence number).

However, in the absence of a sequencing field, you may be able to use a
query with your current setup. If you want to do this calculation in a
query, returning a calculated field's value that might be the "days between
this delivery and the previous one", you probably can do this using a
subquery...however, as you add records, you'll find that this query will run
slower and slower. Following is an example of what you might try that will
return the number of delivery days between the "displayed" delivery date and
the previous delivery date using the chronological order of delivery
dates(warning: I've not tested this thoroughly):

SELECT T.CustomerID, DateDiff("d", (SELECT TOP 1 W.DeliveryDate
FROM TableName AS W WHERE W.CustomerID = T.CustomerID AND
W.DeliveryDate < T.DeliveryDate
ORDER BY W.DeliveryDate Desc), T.DeliveryDate) AS DaysSinceLastDelivery
FROM TableName AS T
WHERE (DateDiff("d", (SELECT TOP 1 W.DeliveryDate
FROM TableName AS W WHERE W.CustomerID = T.CustomerID AND
W.DeliveryDate < T.DeliveryDate
ORDER BY W.DeliveryDate Desc)) Is Not Null
ORDER BY T.CustomerID, T.DeliveryDate DESC;

 

[Kim Streker]

Thanks again!

From your description, I'm understanding that your records would not ever
have more than one delivery per day for a single customer.

That is correct - usually about one delivery to a customer every 2 to
3 weeks.

Following is an example of what you might try that will
return the number of delivery days between the "displayed" delivery date and
the previous delivery date using the chronological order of delivery
dates(warning: I've not tested this thoroughly):

SELECT T.CustomerID, DateDiff("d", (SELECT TOP 1 W.DeliveryDate
FROM TableName AS W WHERE W.CustomerID = T.CustomerID AND
W.DeliveryDate < T.DeliveryDate
ORDER BY W.DeliveryDate Desc), T.DeliveryDate) AS DaysSinceLastDelivery
FROM TableName AS T
WHERE (DateDiff("d", (SELECT TOP 1 W.DeliveryDate
FROM TableName AS W WHERE W.CustomerID = T.CustomerID AND
W.DeliveryDate < T.DeliveryDate
ORDER BY W.DeliveryDate Desc)) Is Not Null
ORDER BY T.CustomerID, T.DeliveryDate DESC;

I am familiar with some *simple* SQL statments and have a general idea
what is happening in the example, but, I am not sure what the T. and
W. are used for. I have looked in my usual SQL references, but am
still confused/inexperienced.

My guess is: you are using the DateDiff function to fill the new
field DaysSinceLastDelivery by using a sub-query (?) that compares the
dates for the same customer (this is where I get confused as to what
is happening), and where the datediff is something rather than
nothing. Am I close?

Thanks again for your help with this!

Kim

 

[Kim Streker]

Thanks again!

From your description, I'm understanding that your records would not ever
have more than one delivery per day for a single customer.

That is correct - usually about one delivery to a customer every 2 to
3 weeks.

Following is an example of what you might try that will
return the number of delivery days between the "displayed" delivery date and
the previous delivery date using the chronological order of delivery
dates(warning: I've not tested this thoroughly):

SELECT T.CustomerID, DateDiff("d", (SELECT TOP 1 W.DeliveryDate
FROM TableName AS W WHERE W.CustomerID = T.CustomerID AND
W.DeliveryDate < T.DeliveryDate
ORDER BY W.DeliveryDate Desc), T.DeliveryDate) AS DaysSinceLastDelivery
FROM TableName AS T
WHERE (DateDiff("d", (SELECT TOP 1 W.DeliveryDate
FROM TableName AS W WHERE W.CustomerID = T.CustomerID AND
W.DeliveryDate < T.DeliveryDate
ORDER BY W.DeliveryDate Desc)) Is Not Null
ORDER BY T.CustomerID, T.DeliveryDate DESC;

I am familiar with some *simple* SQL statments and have a general idea
what is happening in the example, but, I am not sure what the T. and
W. are used for. I have looked in my usual SQL references, but am
still confused/inexperienced.

My guess is: you are using the DateDiff function to fill the new
field DaysSinceLastDelivery by using a sub-query (?) that compares the
dates for the same customer (this is where I get confused as to what
is happening), and where the datediff is something rather than
nothing. Am I close?

Thanks again for your help with this!

Kim

 

[Ken Snell]

T and W are being used as aliases for the "full" table names...makes the SQL
easier to read and a bit shorter. See the "FROM TableName AS T" and "FROM
TableName AS W". Also, because this sample SQL statement is using a subquery
that is based on the same table as the main query, it's necessary to
separately identify the "same" table in the two queries.

This SQL is a bit complicated, but here is brief summary: The subquery is
finding the "most recent prior" delivery date compared to each delivery date
in the table, and then using that "most recent prior" date as the "start
date" for the DateDiff function, and the delivery date from the record is
used as the "end date" for the DateDiff function. Thus, the DateDiff
function tells you how many days between the delivery date in each record
and the delivery date that occurred chronologically just before that
delivery date. This SQL will return one less record than you have in the
table, because the last delivery date (the earliest one) has no "prior"
delivery date from which to calculate a date difference. Thus, the earliest
date in the query's records will be the second oldest delivery date in the
table for that customer. I eliminate this last record by using the Is Not
Null part of the last WHERE statement, because the last record will have a
Null return from the subquery -- there are no records with a delivery date
earlier than the earliest delivery date!

The subquery does this: It finds all the records that have a delivery date
earlier than the delivery date in the current record, orders them in
descending chronological order (newest date is the first record in the
query's records), and then selects the Top 1 (newest / latest date) and
returns that date to the DateDiff function. Thus, in the subquery, the WHERE
statement
WHERE W.CustomerID = T.CustomerID AND W.DeliveryDate <
T.DeliveryDate
joins on the customer ID so that only delivery dates for the customer are
selected, and then selects only the records with a delivery date earlier
than the main query's record's delivery date.

Hope that this makes sense. It's essentially doing a "ranking" action by a
self-join for the table (joining the table to itself).