Help on Capacity Control Program needed

[AAVF IT]

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

[Michel Walsh]

Hi,
That is a hard problem, but not by its technical solution, it is a
matter of a simple ORDER BY.

Technically, the quantity is relatively irrelevant to produce the
sequence, if a started job has to be completed, and if there is a priority
order (some clients before some others) and, in case of equality, take the
one with the smallest ExpectedDate, you have your schedule with:

SELECT *
FROM whatever
ORDER BY JobStarted ASC, Priority DESC, ExpectedDate ASC

The quantity is only involved in knowing the "time of production", not
in producing the ORDER of the jobs among themselves, in the schedule.

A running sum of the quantity over these ordered records would tell you
in which day ( 0-170 = one day , 171-340 = two days) a scheduled
production would occur.

To make a running sum, of value, given an ordering, you can use the
following template:
SELECT a.id, SUM(b.value)
FROM myTable As a INNER JOIN myTable A b
ON ( a.criteria1>b.criteria1 ) OR
( a.criteria1=b.criteria1 AND
(( a.criteria2> b.criteria2 ) OR
( a.criteria2 = b.criteria2 AND
(( a.criteria3>criteria3 ) OR
( a.criteria3=b.criteria3 ...
) )) ))

GROUP BY a.id


So, in your case, SUM(b.Value) \ 170 would give the number of day of
production left to complete the job a.id, given the ordering criteria ( use
< or > as appropriate ).
Now, that result does not take in account broken machinery, or human
intervention ( "pushing" a job in front of another one, failing to comply
with the logical criteria we put in place, but in accordance with the "human
intervention", here, the will of the boss). And that, that is the hard part.
Your application should be able to handle it, probably. Hint: now you may
see why I included a "priority" field ...


Hoping it may help,
Vanderghast, Access MVP

"AAVF IT" <it***********@aavf.co.uk> wrote in message
news:10***************@ananke.eclipse.net.uk...

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

[AAVF IT]

Thanks Michel

This has given me something to think I about. I will give your ideas a try.

Regards

Tom

"Michel Walsh" <vanderghast@VirusAreFunnierThanSpam> wrote in message
news:%2***************@TK2MSFTNGP11.phx.gbl...

Hi,
That is a hard problem, but not by its technical solution, it is a
matter of a simple ORDER BY.

Technically, the quantity is relatively irrelevant to produce the
sequence, if a started job has to be completed, and if there is a priority
order (some clients before some others) and, in case of equality, take the
one with the smallest ExpectedDate, you have your schedule with:

SELECT *
FROM whatever
ORDER BY JobStarted ASC, Priority DESC, ExpectedDate ASC

The quantity is only involved in knowing the "time of production", not
in producing the ORDER of the jobs among themselves, in the schedule.

A running sum of the quantity over these ordered records would tell you in which day ( 0-170 = one day , 171-340 = two days) a scheduled
production would occur.

To make a running sum, of value, given an ordering, you can use the
following template:
SELECT a.id, SUM(b.value)
FROM myTable As a INNER JOIN myTable A b
ON ( a.criteria1>b.criteria1 ) OR
( a.criteria1=b.criteria1 AND
(( a.criteria2> b.criteria2 ) OR
( a.criteria2 = b.criteria2 AND
(( a.criteria3>criteria3 ) OR
( a.criteria3=b.criteria3 ...
) )) ))

GROUP BY a.id


So, in your case, SUM(b.Value) \ 170 would give the number of day of
production left to complete the job a.id, given the ordering criteria ( use < or > as appropriate ).
Now, that result does not take in account broken machinery, or human
intervention ( "pushing" a job in front of another one, failing to comply
with the logical criteria we put in place, but in accordance with the "human intervention", here, the will of the boss). And that, that is the hard part. Your application should be able to handle it, probably. Hint: now you may
see why I included a "priority" field ...


Hoping it may help,
Vanderghast, Access MVP

"AAVF IT" <it***********@aavf.co.uk> wrote in message
news:10***************@ananke.eclipse.net.uk...

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