Calculate days overdue for deliveries?

[Astrid]

Hello all, I'm hoping that you can help me out a bit. I'm using Access 2003,
and I would like to try to set up a query to calculate the days overdue that
regular shipments comes in.

As an add-on to our stock database, I have a table set up which we enter the
received date into. We know that different shipments are due on different
days (eg. 10th, 13th, 27th, and just for giggles some are weekly and some are
monthly). Is it possible to create a query which can calculate how many days
overdue a shipment is from its regular due date, without having to create an
'expected' date column and adding all the data manually each week?

I have used count tables before, but I had major problems with record
duplications, it got very complicated and to be honest I have very limited
knowledge of using VB, so I would like to avoid that option if possible.

I really need to get this sorted as we are having major problems with a few
of our suppliers being late and really having no continual record of how late
they are, so ultimatley I will create a report from this data so we can show
evidence to the board (and hopefully get new suppliers).

All help will be gratefully received.

 

[Dale Fye]

What fields do you have in your database? What is it that tells you what the
[Expected Date] would look like, if you had it? I assume that you probably
have a [Order Date] field, and that there is some other indicator ([Shipment
type]??) which indicates whether it was next day, second day, first class (up
to 5 days) or something like that that you would add to your order date to
determine your expected date. If that is the case, then you could do
something like:

SELECT DateDiff("d", dateadd("d", S.[Ship time], O.[Order Date]),
O.[DeliveryDate]) as DeliveryDelta
FROM tblOrders as O
INNER JOIN tblShipTimes as S
ON O.[Shipment Type] = S.[Shipment Type]
WHERE O.[Order Date] IS NOT NULL
AND O.[Delivery Date] IS NOT NULL
--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.