Calculate days overdue for deliveries?

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.

Are you saying that you have a way now to identify (or calculate) a given
shipment's "due date"? If so, how? You've asked a "how do I" question, but
haven't described the underlying data that would be needed to do that.

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

"Astrid" wrote in message
...
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.

Sorry if it was confusing, I'll try my best to get everything in.

Shipments are supposed to arrive on the same days every week/month/etc. I
have an excel speadsheet with all the dates that I check every day to see
what should be arriving. Below is the format of the speadsheet (imagine
approx 55 companies and 31 days):

Company Goods 1st 2nd 3rd 4th 5th 6th
7th 8th etc
AXIS FOOTWEAR X
X
BDG Plc JEWELLRY X

So every day I check how many deliveries are due in on that day (taking
weekends into account).

We need to be able to create a database with reports by recording the
'actual' date of delivery and compare it to when it should have been
delivered, giving us 'days overdue'.

I have already tried using a count table and append query to create these
records in advance (DueDate), allowing reminders to be displayed when the
database is opened. That went well until I added a new company/shipment to
the list, and I managed to duplicate the existing records when I created the
'DueDate' records for the new shipment.

I am not very good using VB, I can generally cope with the basics, but I
couldn't get the code right in the append query to prevent duplicates.

So I was wondering if;

1. Just using the information from the spreadsheet to create a table in
access 2003(just the day due), if I record when shipments arrive, is it
possible to create a query to automatically calculate how overdue that
shipment is without creating records in advance for each shipment (eg. 1st
Sept, 1st Oct, 1st Nov, etc).

Example query;

ShipmentReceiptID
Goods
DayDue
ShipmentReceived
OverdueBy

If this makes any sense at all, can you give me a clue whether it is
possible? Our board is being a pain, demanding reports on shipments before
they will look at changing suppliers and delivery receipts aren't enough
proof (apparently).

Thanks in advance,

Astrid

Astrid

The structure you provided certainly does look like a spreadsheet.

But Access is a relational database. If you try to feed it 'sheet data,
both you and Access will have to work much harder than if you feed it
well-normalized data.

Yes, you can just import an Excel spreadsheet ... no, it is NOT a good idea!

Regards

Jeff Boyce
Microsoft Office/Access MVP

"Astrid" wrote in message
...
Sorry if it was confusing, I'll try my best to get everything in.

Shipments are supposed to arrive on the same days every week/month/etc. I
have an excel speadsheet with all the dates that I check every day to see
what should be arriving. Below is the format of the speadsheet (imagine
approx 55 companies and 31 days):

Company Goods 1st 2nd 3rd 4th 5th 6th
7th 8th etc
AXIS FOOTWEAR X
X
BDG Plc JEWELLRY X

So every day I check how many deliveries are due in on that day (taking
weekends into account).

We need to be able to create a database with reports by recording the
'actual' date of delivery and compare it to when it should have been
delivered, giving us 'days overdue'.

I have already tried using a count table and append query to create these
records in advance (DueDate), allowing reminders to be displayed when the
database is opened. That went well until I added a new company/shipment to
the list, and I managed to duplicate the existing records when I created
the
'DueDate' records for the new shipment.

I am not very good using VB, I can generally cope with the basics, but I
couldn't get the code right in the append query to prevent duplicates.

So I was wondering if;

1. Just using the information from the spreadsheet to create a table in
access 2003(just the day due), if I record when shipments arrive, is it
possible to create a query to automatically calculate how overdue that
shipment is without creating records in advance for each shipment (eg. 1st
Sept, 1st Oct, 1st Nov, etc).

Example query;

ShipmentReceiptID
Goods
DayDue
ShipmentReceived
OverdueBy

If this makes any sense at all, can you give me a clue whether it is
possible? Our board is being a pain, demanding reports on shipments before
they will look at changing suppliers and delivery receipts aren't enough
proof (apparently).

Thanks in advance,

Astrid