A
AAVF
Hi
I need some help in getting round the problem of reading a single record and
acting on the result before reading another.
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 think what I need to do is to be able to read the 'balance' remaining on
an order and, if it is less than 170, write the work date and balance of
zero to a temporary table, then read a new order record. If the balance is
greater than 170, subtract 170 from it, write the new balance and the work
date to a temporary table,
read the next work date and read the new balance - et cetera.
then 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?
Once I have the data in either queries or tables, the reports are no
problem. Its just the queries that are baffling me at the moment.
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
I need some help in getting round the problem of reading a single record and
acting on the result before reading another.
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 think what I need to do is to be able to read the 'balance' remaining on
an order and, if it is less than 170, write the work date and balance of
zero to a temporary table, then read a new order record. If the balance is
greater than 170, subtract 170 from it, write the new balance and the work
date to a temporary table,
read the next work date and read the new balance - et cetera.
then 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?
Once I have the data in either queries or tables, the reports are no
problem. Its just the queries that are baffling me at the moment.
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