Calculated Field

[abernut]

Hello,
I started a post here that was extremely helpful in getting most of my new DB created and the needed forms.

I am to a point where I "Think" I need some sort of calculated field.

I have a Molds table which contains the following:
Mold_No *
Part_No
Part_Name
Maint_Req_at
Current_Cycles

There are two other tables:
MoldReqWork_Ord_No *
Mold_No (Pulled from Molds table)
Part_Name (Pulled from Molds table)
Part_No (Pulled from Molds table)
Cycles (this is where the user enters how many cycles he ran the mold for)
etc....

MoldLogLog_ID *
Work_Ord_No (Pulled from MoldReq table)
etc...

The way the process works is like this....
Someone requests X number of parts be made using mold number X. They fill out a mold request from, enter all the information along with how many cycles they ran it for.


If they have any issues they fill out a comments section and send it to the mold maintenance guy.

He reads their comments and performs whatever maintenance is required to make the mold usable.

If the mold ran with out any issues it goes back on the shelf. Each mold has a set number of cycles it can be ran before maintenance is required.


My thinking is that when the user fills out the Mold Request Access Form and enters the number of cycles they ran that mold for, it will update the Current_Cylces field in the Molds table. But this value has to be some how incremental and keep track of the total number of cycles the mold has ran since the last maintenance.

Then the Mold Maintenance guy fills out his Access form stating that he performed the maintenance. Every time that form is used the Current_Cycles would be reset to zero.

That all sounds hankey dorey but I have no idea if it is possible or where to start.

Thanks,
mike

[ssanfu]

Can you/will you post your dB with some test data?

[rpeare]

One point of order before I comment, in your MOLDREQ table, you do not need to store anything but the PK from the MOLDS table (the part name and part no can be found through linking the tables).

Secondly, unless all your orders consist of using one mold you likely want a structure that is more like:

Work_Orders
WO_ID Cycles_Needed ------> other work order related fields, like date, customer etc

Work_Orders_Detail
WOD_ID WO_ID Mold_ID Cycles_Used ----> other detail related fields like number of copies required

so let's say you start an order with a specific mold but something happens and you need to replace that mold in the middle of the operation you cycles needed may not match your cycles actually used for a specific mold but you could sum the detail items of a work order to find that the order had been met in any query or report.

Now what you're asking to do is really a modified inventory management type system, but you do NOT want to store calculated values in your table.

In your molds table if you have a field that shows the maximum number of molds possible between maintenance cycles that is really all you need as long as your maintenance table and all tables that record the use of the mold have a date the mold was used/maintained in them. From there you can calculate how many cycles a mold has on it since it's last maintenance cycle plus you retain the ability to audit the maintenance schedule of a particular mold. Let's say that you retire a mold after 50,0000 uses, having this type of log would allow you to accurately calculate not only the short term maintenance cycle but the long term retirement of old molds.

[abernut]

Can you/will you post your dB with some test data?

Data base is Attached.

Mike

[abernut]

One point of order before I comment, in your MOLDREQ table, you do not need to store anything but the PK from the MOLDS table (the part name and part no can be found through linking the tables).

Secondly, unless all your orders consist of using one mold you likely want a structure that is more like: From my understanding a mold request will never have more than one mold. If something goes wrong, work is stopped, maintenance is done on the mold, a new mold request is submitted with a new work order number.

Work_Orders
WO_ID Cycles_Needed ------> other work order related fields, like date, customer etc

Work_Orders_Detail
WOD_ID WO_ID Mold_ID Cycles_Used ----> other detail related fields like number of copies required

so let's say you start an order with a specific mold but something happens and you need to replace that mold in the middle of the operation you cycles needed may not match your cycles actually used for a specific mold but you could sum the detail items of a work order to find that the order had been met in any query or report. It sounds like I need to create a pre-run and post-run form/table. The pre-run is the request (Work Order, Mold Number, Part Name, Part Number, Date, etc...). The post-run would contain (Comments, Cycles, etc...)

Now what you're asking to do is really a modified inventory management type system, but you do NOT want to store calculated values in your table.

In your molds table if you have a field that shows the maximum number of molds possible between maintenance cycles that is really all you need as long as your maintenance table and all tables that record the use of the mold have a date the mold was used/maintained in them. From there you can calculate how many cycles a mold has on it since it's last maintenance cycle plus you retain the ability to audit the maintenance schedule of a particular mold. Let's say that you retire a mold after 50,0000 uses, having this type of log would allow you to accurately calculate not only the short term maintenance cycle but the long term retirement of old molds.

Sounds good, just not sure how to accomplish. I will add the additional tables and modify the ones I currently have.

[rpeare]

Your employee list should be in a table of it's own. You shouldn't limit your combo boxes to a static list of items unless that list is never going to change (like, say for yes/no/N/A type answers)

I suggest you adopt a structure like I laid out, you have a work order number in two different tables which means you're going to be relying on two different sets of data entry to be 100% correct when entering data instead of having it entered 1 time. You also have data entry for fields you do not need (part No or name) when all you have to do is store the foreign key to the MOLDS table.

[rpeare]

If the system is as simple as you say it is then you actually don't need all the separate tables at all, your current two tables can be combined into one, you would just have to store your maintenance schedule on a separate table that had the mold identifier, the maintenance date and all the relevant information relating to the maintenance itself. (NOTE: NOT the number of cycles used, you can get that from your main table)

I have a hard time believing it's that simple though, nothing ever is!

[abernut]

If the system is as simple as you say it is then you actually don't need all the separate tables at all, your current two tables can be combined into one, you would just have to store your maintenance schedule on a separate table that had the mold identifier, the maintenance date and all the relevant information relating to the maintenance itself. (NOTE: NOT the number of cycles used, you can get that from your main table)

I have a hard time believing it's that simple though, nothing ever is!

Now I am really confused. Some say I need an additional table now you're saying I can do this with just one.

Here is my new table structure based on the first suggestion. This is based on a meeting I just had and everything they said they need.

MoldReq
1. Work_Ord(Pri Key)
2. Material (Combo Box to select “PVC” or “NON-PVC”)
3. Date
4. Machine Number (There are eight machines so this could be a combo box or another table I guess)
5. Comments
6. Mold_Installed_By
7. Mold_Pulled_By
8. Cycles
9. Checked_By


MoldLog
1. Log_ID (Pri Key)
2. Log_Date
3. Maint_Date
4. Time_In
5. Time_Out
6. Hours (this is can probably be some sort of calculated field based on Time_In and Time_Out
7. Maint_Type (Combo box: Repair, New Work, Revision, Maintenance)
8. Lube_Used (Combo box: Donnelly Grease, STP/Donnelly)
9. Comments
10. Attachment
11. Tips_Tricks
12. Work_Comp_By
13. Finish_Date


Molds
1. Mold_No(Pri Key)
2. Part_Name
3. Part_No
4. Maint_Req_At


Employees
1. Emp_ID (Pri Key)
2. First_Name
3. Last_Name


This structure seems pretty straight forward. The difficult part for me is going to be creating an easy to use form which will allow the user to do a Mold Request, then a form that keeps track of the maintenance, and a way to keep track of the cycles of each mold.


Once again, thanks for all of your help. But after this project... I am sticking to my routers and switches :-)

[abernut]

crap...
I think I just created a bunch of tables with no way to form a relationship.

[rpeare]

How you construct your tables depends on the nature of your business.

You said that only one mold is used for any given job, and if that mold has to be removed from the job then the job is closed and a new job is opened with a different mold. Therefore there's no real reason to provide the ability to have multiple molds used in any given job (a one to many relationship of jobs to molds used in that job). My original post was assuming you would need or could have multiple molds used for any given job.

At first glance the structure you showed in your last post would work just fine, you just have to add the MOLD_ID to both the MOLDLOG and MOLDREQ tables.

As an aside, ANY time you plan to use a combo box that *can* change over time I would use a table to populate the values, for instance your greases, you have a current list, but what happens if next year a supergrease is invented and you start using that, you don't want to have to reprogram all your combo boxes and reports you just want to add the new supergrease to one table and everything you have programmed will still work. The only time I ever use static values in a combo box is when I know that list will never ever change (for instance yes/no/NA questions).

[abernut]

You said that only one mold is used for any given job, and if that mold has to be removed from the job then the job is closed and a new job is opened with a different mold.

Wrong. Sorry if I explained it incorrectly. Each mold can only make one unique part. So if the mold has to be removed from a job and sent out for maintenance, they have to wait for that mold to continue making that part. When the mold returns they open a new Work Order Number to finish the parts

At first glance the structure you showed in your last post would work just fine, you just have to add the MOLD_ID to both the MOLDLOG and MOLDREQ tables.

As an aside, ANY time you plan to use a combo box that *can* change over time I would use a table to populate the values, for instance your greases, you have a current list, but what happens if next year a supergrease is invented and you start using that, you don't want to have to reprogram all your combo boxes and reports you just want to add the new supergrease to one table and everything you have programmed will still work. The only time I ever use static values in a combo box is when I know that list will never ever change (for instance yes/no/NA questions).

I will go ahead and create a table for anything that is not a yes or no answer then. The way stuff changes around here....

[rpeare]

Wrong. Sorry if I explained it incorrectly. Each mold can only make one unique part. So if the mold has to be removed from a job and sent out for maintenance, they have to wait for that mold to continue making that part. When the mold returns they open a new Work Order Number to finish the parts

Now it's your turn to confuse me!

For each work order, how many molds can be used?
If it's 1 and only 1 your structure will work fine
If it's more than 1 you need a different structure

If a Work order stops because a mold is damaged or needs maintenance what happens?

let's say you have an order that requires 1000 cycles on a mold. At cycle 500 maintenance is needed.
From your description the work order is closed
The mold goes to maintenance.
When the mold is returned from maintenance another Work order would be opened for 500 cycles (to complete the original work order)

Is that correct?
What happens to the discrepancy in the number of cycles ordered and the number of cycles actually run? is that significant to your business? Or would the original work order number of cycles ordered be changed to match the number of cycles actually run? I guess I"m getting into the nitty gritty but the long and the short of it is that if you have a 1 to 1 relationship between work orders and molds used your structure will work with the addition of the MoldID to each of the two tables (maintenance and work order tables)

[abernut]

EDIT....
I asked the mold manager and his response was.."If the mold goes to the shop for repair and is a quick fix then the work order will remain the same. If a major repair is necessary then the work order will be closed out."

[abernut]

Now it's your turn to confuse me!

I seem to be good at that.

For each work order, how many molds can be used?

One mold for each work order number. Meaning that you can’t use multiple molds on a work order. That same mold may be used at a later date for another work order though.

If a Work order stops because a mold is damaged or needs maintenance what happens?
let's say you have an order that requires 1000 cycles on a mold. At cycle 500 maintenance is needed.
From your description the work order is closed
The mold goes to maintenance.
When the mold is returned from maintenance another Work order would be opened for 500 cycles (to complete the original work order)

Is that correct?

From what I just learned, it depends. If it is something simple, they keep the work order open and then continue once maintenance is complete. If it is something that may take some time, they will assign a new work order number and complete the cycles.

What happens to the discrepancy in the number of cycles ordered and the number of cycles actually run? is that significant to your business?

They basically just need a way to track the number of cycles on each mold. 90% of the molds have maintenance completed after each use. It’s the other 10% that I am trying to help with. When I mold gets within 5% of its required maintenance they would like to be able to see that.

[abernut]

Okay, below is my new table structure. This is so I will not have any combo boxes.
I am not sure how to set up my relationships though.


MoldReq
1. Work_Ord(Pri Key)
2. Mold_No
3. Material
4. Date
5. Machine Number
6. Req_Comments
7. Mold_Installed_By
8. Mold_Pulled_By
9. Cycles
10. Checked_By
MoldLog
1. Log_ID (Pri Key)
2. Work_Ord
3. Log_Date
4. Maint_Date
5. Time_In
6. Time_Out
7. Hours (this is can probably be some sort of calculated field based on Time_In and Time_Out
8. Maint_Type
9. Lube_Used
10. Maint_Comments
11. Attachment
12. Tips_Tricks
13. Work_Comp_By
14. Finish_Date
Molds
1. Mold_No(Pri Key)
2. Part_Name
3. Part_No
4. Maint_Req_At
Employees
1. Emp_ID (Pri Key)
2. First_Name
3. Last_Name
Materials
1. Material_ID
2. Material_Type
MaintType
1. Maint_ID
2. Maint_Type
Lubricants
1. Lube_ID
2. Lube_Type