Help on Capacity Control Program needed

Hi

I am developing an application in MS Access 2000 that is to bolt on to an
existing Access database, that links via ODBC to our UNIX-based factory
management system. I need some help in this area as am not a VB programmer,
and I try (where possible) to keep everything to simple queries etc.

We have orders that have to be scheduled for manufacture on a specific date.
However, the area in which they are manufactured has a capacity limit of 170
units per day. If the requirement for a day exceeds 170, it has to run on to
the next day. If there are multiple orders for the same day, they are
treated on a priority basis (ie, date required).

An example

Limit of 170 units per day

ORDER_NO QTY_REQD DATE_REQD WORK_DATE QTY_MADE QTY_CARR_FWD
SPARE_CAPACITY COMPLETED?
A1231 150 12-Jan-2004 12-Jan-2004
150 0 20
Y
A1230 250 12-Jan-2004 12-Jan-2004
20 230 0
N
A1230 230 12-Jan-2004 13-Jan-2004
170 60 0
N
A1230 60 12-Jan-2004 14-Jan-2004
60 0 110
Y

At the moment, the kind of information I have in tables is as follows.

1). The order details ([ORDER_NO], [QTY_REQD], [DATE_REQD])
2). A calendar of work days (ie, excluding weekends and holidays:
[WORK_DATE])

I need to be able to produce reports based on the example above that allows

a). our manufacturing controller to see his manufacturing plan
b). sales personnel to be able to enter a date and a quantity and to be
advised (i) is their date required realistic? and (ii) what is the earliest
date for that quantity?

I would be very grateful for any ideas in this area. I know Excel may be a
better tool, but we have a good sized Access 2000 in house database system
and it would be better to try and keep it in the same application.

Regards

Tom Millington