Help to manipulate dates

[Josh Murphy]

I want to create a report based on a query that will look
at past orders and their frequency of delivery to
forecast when the approximate next order delivery date
will be. I have come up with most of the essential
elements: Total all time Qty for each product,Avg qty per
delivery, and number of deliveries. My hangup is querying
to retrieve the very first delivery date and the very
last of a certain product to execute the formula:
[lastshipdate]-[firstshipdate]=[span] of deliveries. From
there I can make [span]/# of deliveries=avg span between
deliveries. Then finally [span]+[lastshipdate]=forecasted
next delivery date.

Any ideas??

Thanks ahead of time,

Josh

 

[Marshall Barton]

I want to create a report based on a query that will look
at past orders and their frequency of delivery to
forecast when the approximate next order delivery date
will be. I have come up with most of the essential
elements: Total all time Qty for each product,Avg qty per
delivery, and number of deliveries. My hangup is querying
to retrieve the very first delivery date and the very
last of a certain product to execute the formula:
[lastshipdate]-[firstshipdate]=[span] of deliveries. From
there I can make [span]/# of deliveries=avg span between
deliveries. Then finally [span]+[lastshipdate]=forecasted
next delivery date.

You can use the Min and Max aggregate functions to get those
values.

span = DateDiff("d", Min(shipdate), Max(shipdate))

Depending on how the query's grouping is set up, it may be
simple or complicated to integrate that expression into the
query.