473,396 Members | 2,020 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,396 software developers and data experts.

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
Nov 12 '05 #1
2 1721
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

Nov 12 '05 #2
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


Nov 12 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: Kyle Sheldon | last post by:
I'm currently working on a class "Course" and I'm getting an unresolved external error when trying to compile. I understand that unresolved externals are caused by declaration of methods that...
28
by: stu_gots | last post by:
I have been losing sleep over this puzzle, and I'm convinced my train of thought is heading in the wrong direction. It is difficult to explain my circumstances, so I will present an identical...
0
by: AAVF IT | last post by:
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...
4
by: Leonardo Hyppolito | last post by:
Hello, I am trying to write a multithread program that simulates producers and consumers. My program can have many producers and many consumers (each in a separate thread). It has a storage...
11
by: DrNoose | last post by:
Hi! I've got a program that's almost done, but I'm getting compile errors in two lines: 317 & 319. I think the main error has to do with the Truck Class. I'm a newbie and keep looking at the...
17
by: Jess | last post by:
Hello, The iterator adaptor "back_inserter" takes a container and returns a iterator so that we can insert elements to the end of the container. Out of curiosity, I tried to look at what element...
6
by: Minor | last post by:
I am a beginning student in java and really need some help. I am totally lost and don't know where to begin. This is my last assignment and I just need to submit something. I have decided computer...
7
by: Luna Moon | last post by:
#include "stdafx.h" #include <iostream> #include <string> using namespace std; int main() { string cc(31, 'c'); string bb=cc.assign(3, 'dd');
2
by: hcaptech | last post by:
This is my Test.can you help me ? 1.Which of the following statement about C# varialble is incorrect ? A.A variable is a computer memory location identified by a unique name B.A variable's name...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
0
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...
0
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
0
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
0
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.