Design Ideas Please...

[Hoopper]

I would like to produce a report that shows the Lots to be processed daily.

My environment is as follows:

1. Each Lot goes through all the different stages of production - 5 stages - namely; A, B, C, D and E

2. Each lot contains anywhere from 1 up to 40 items

3. At any given time each stage houses anywhere from 0 up to 20 Lots (0 to [40*20] items)

The available attributes are as follows:

1. ORDNUM - lot number

2. OPRSEQ - each unique stage number e.g A or B

3. QTYREM - number of items remaining in each lot

Report Spec:

I have a maximum number of items that should be processed at each stage which can be hard coded, say 60 items. Hence, from 0 up to 60 items should be processed at each stage daily. This 60 items can be selected from whatever number of Lots that equate to 60 items; method of Lot selection is below.

The report will itemize, by stage number, the Lot numbers to be processed at all stages. In the case where there isn't any Lot at a given stage, the report will not show any Lot number at that particular stage.

Most importantly, the oldest Lot numbers will be selected first, thus using "first in first out" (FIFO) method.

Your suggestions will be highly appreciated.

thanks!

Hoopper

 

[hilfy]

If you'll give me some info about the structure of your data tables, I might be able to help you with this....

-Dell

A computer only does what you actually told it to do - not what you thought you told it to do.

 

[Hoopper]

Thank you for responding!

I am running Crystal Reports XI, using ODBC of course...


1. ORDNUM - lot number - string

2. OPRSEQ - each unique stage number e.g A, B, etc - string

3. QTYREM - number of items remaining in each lot - double

All 3 attributes are housed in the same table, Job_Progress. Let me know precisely what other info, if any, you would like.

Also, please be a bit precise with the syntax as my strength lies in Java.

I do be more than happy to return the favour should you need a or another suggestion in Java.

Thank you in advance for your time.

H

 

[hilfy]

If all of your data is in the Job_Progress table only while the lots are in process, you will not be able to show a stage that doesn't have any lots using just this table. Do you have a "lookup" type table with just the stages in it? If so, linking from that table (I'll call it Stage_List) to the Job_Progress table with a left outer join will be the only way to get all of the stages, even those without lots. There may be another way to do this if you have historical data available.

Now, where does your date/timeframe information come from? If you want to know how many were processed in a day, you have to know how many there were at the start of the day and how many at the end of the day.

-Dell

A computer only does what you actually told it to do - not what you thought you told it to do.

 

[Hoopper]

Those 3 attributes are also housed in the Transaction_History table, so this can serve as the look-up table.
In addition, the Transaction_History table can be queried for quantity processed daily by noting the daily sub_total change at each stage.

Other relevant attributes found in the tables include:

PartNum - lot number's part number - String (both tables)
QTYREM - quantity left in a lot - double - (Job_Prog only)
TNXDATE - date processed - Btrievedate (Trans_History only)
TNXQTY - quantity processed - double (Trans_History only)
TNXCDE - type of transaction - String (Trans_History only)

thanks!

H

 

[hilfy]

Ok, this is going to be a little strange....I think you'll need to includ the Transaction_History table twice - the second time you try to add it, Crystal will ask you for an "alias" for it. I'll call it "Stage_List".

You'll link from Stage_List to Job_Progress on OPRSEQ with a left outer join and then from Job_Progress to Transaction_History on ORDNUM and PartNum, also with a left outer join.

In the Database menu, turn on Select Distinct Records.

Set up the following groups:
{Stage_List.OPRSEQ}
{Job_Progress.ORDNUM}
{Job_Progress.PartNum}

Add an additional descending sort on {Trans_History.TNXDATE}.

From there, it shouldn't be too difficult to pull the info you need for the report.

-Dell

A computer only does what you actually told it to do - not what you thought you told it to do.