Forge Die tracking Database

[Mcahill]

I was given a project for my company and need some help on the overall structure of the database and some help with macro.

Project overview:


My company wants to track the amount of parts produced on a specific die. I work in a forging plant and we have a verity of dies that produce specific parts. A little background on forging. Dies are given a certain number of parts it can produce before the die cavity begins to expand causing the parts produce to fall out of spec. The number of parts it can produce is referred to as its (Die life) and once the cavity begins to expand its referred to as (washed out) once the cavity is washed out the die block is then machined to sink the cavity about .1 deeper into the block, this is called (re-sinking). Now that that is explained let me get into the chunk of the project.

1.Identifying the specific Die blocks:
Every part produce has a part number example-0000F. Every die block number indexes once it has been resunk. There is a maximum of 4 die blocks that make the same part. The format for the die block looks like this (#-#-Letter) The first number indicates how many times that die block has been replaced. After so many resinks the die is not thick enough to withstand the force and has to be scrapped out. The second number indicates how many resinks have been machined on that specific die Block. The letter indicates how many dies relate to a specific part number. For example lets just say we are on the second die block and its been through 4 resinks and there is 2 dies that make the same part. on the side of the die it would look like 2-4-A. And lets say the second die is on its 4th die block with 2 resinks it would look like 4-2-B. The main point in explaining this in detail is that I need to come up with a code to auto increment this number once the die has been resunk or scrapped out.

2. Tracking the life of the die:
Our operators keep count on how many parts they produce per shift. This number needs to be imputed into the database and totaled. Once the total number is greater then its die life there needs to be an email sent to our machine shop letting them know a die is ready to be picked up and machined.

Summary:
Basically what i need is the code to index the numbers, a way to tell once the die has reached its die life. forms to input the data into a table. I have intermediate knowledge of Access and SQL but need some guidance and suggestions on how this database will be set up. Thanks for reading!

[June7]

A conventional db approach would be to assign the die an identifier that never changes. Then each time the die is re-sinked, would mean a new record in a table designed to track this history. The history records would be associated with the die ID. Your 'auto-incrementing' concept that changes the die ID is an auditor's nightmare. There is no audit trail. If the value is accidentally incremented, where is the history to support correcting the value - not in the db.

Code is run in some event - such as a button Click, form Open, textbox AfterUpdate. What if someone accidentally clicks the button twice (finger twitch or whatever) and nobody notices? Oooops - the increment is wrong and how should it be corrected, if the error is even ever caught?

[NTC]

I will add this: Your principles are one thing (i.e. how many number of parts before out of spec, or how many resinks). Nothing can or should be done about principles. But your identification system is arbitrary - and I'll add the comment - old and analog. Really what you are doing is counting. Counting parts, counting resinks, etc. Autonumber provides integers and you cannot have leading 000s. You will spend an inordinate amount of effort/time attempting to develop any type of automatic analog identification sequencing - - you are much better off dealing in straight forward counting which can be done with the autonumber field type.

[Mcahill]

Thanks for the response and the help.

Just wanted to give an update on the project. I threw out the old way of tracking the dies and began to serialize the die blocks. This way I can pull up records with the same serial number.The tables I created look like this

Inventory
*Die ID
*Part Name
*Part #
*Machine Size (Size of the Hammer the dies go into)
*Category
*Die Life
*Status

History
*Die ID
*Replacement # (Number of time the die block has been replaced)
*Sink #

Current
*Die ID
*Parts Ran
*Date

Now I need to develop a way to search for the die block, keep a running record for the number of parts on that die, then update the history table once the die has reached its die life. Want to make it as automatize as possible so our operators just update the number of parts ran, then management can see a list of what dies are ready to resink and auto populate a email to notifiy our machine shop that the dies are ready to be picked up. Any information on what the best way to do this would be greatly appreciated.

Thanks!

[June7]

Advise no spaces or special characters/punctuation (underscore is exception) in naming convention. Better would be Part_Num or PartNum. Also avoid reserved words as names. Date is a reserved word.

Methods for search:

1. intrinsic tools from the ribbon and right click

2. http://www.datapigtechnologies.com/f...tomfilter.html

3. http://www.allenbrowne.com/ser-62.html

[Mcahill]

Thanks for the help again June7!

My next roadblock that is causing me some frustration is I need to make a list of the dies that have surpassed their die life. I have tried to make a query with the [Parts Ran] and [Die life] fields but I was unsuccessful. I need a total of all the parts ran on that die and compare that to the Die life of that die, If the number is greater then the die life the Die ID will show up on a list. I need to know what the best way to go about doing this. I have a understanding of VBA but only what I have read on other forums. Thanks again for reading!

[June7]

So you need to do a count of the PartsRan records for each die? If that count meets/exceeds the DieLife then show DieID?

SELECT DieID, Count(PartsRun) AS CountRun FROM Current GROUP BY DieID;

Now join that query to Inventory table linking on DieID field. Filter criteria under DieLife field: >= CountRun

Alternative uses DCount function but domain aggregates can be slow performers.

[ssanfu]

I would be interested in seeing your current dB..... with some records (data).

I read your first post several times and still trying to understand what the "Die Life" is. Is it a future date or the total number of pieces created from the 4 die blocks after all of the resinks that make the same part?

[Mcahill]

Thanks june7 you really helped me out on this project. If I could ask one more question. I need to know how to set the parts ran back to 0 after that die has been resunk. I have made a query with [DieId] [CountRan] [Date] [Sink#] but cant figure out the code. So every time the [sink#] indexes I need the count to go back to 0 thus restarting its die life. This way I can know how many parts per resink and once a Die comes back from machining we are able to run another say 10,000 parts (for example).

And for ssanfu: The Die life is the theoretical number of parts a die can produce before the cavity inside the die starts to get deformed causing the parts produce from that die to fall out of tolerance.

[June7]

I am getting a bit confused, but maybe:

SELECT DieID, [Sink#], Count(PartsRun) AS CountRun FROM Current GROUP BY DieID, [Sink#];

Then will need another query based on that one to pull just the most recent Sink# record. This starts to get complicated. Perhaps TOP N per group query will accomplish. Review http://allenbrowne.com/subquery-01.html#TopN



Should avoid spaces and special characters/punctuation in naming convention. Better would be SinkNum or Sink_Num.

[orange]

As ssanfu said it would be nice to see your tables and relationships.

Further to June's post, it seems you are doing Counts on a Die & Sink# combination. When you determine it's time to do another Sink let's say this is Sink#3 for this Die XX, then it is Die XX Sink#3 that gets reset to 0.

[Mcahill]

Ok So I have attached my relationships and tables for more information on this project. The format for the DieID looks like this XXXXL-LX ( The first 4 digits and letter equal the part number-The next letter indicates the number of blocks that make the same part number, and the last digit indicates what range of die letters to stamp into the die cavity. An example of a DieId would look like this 9999A-A1, 9999A-B2, 9999A-C3. So Die Block 9999A-A1 is 1 of 3 and uses die letters A-I) Eventually I want to turn this into bar codes for easy identification for our operators but that will be another phase of this project.

The next problem I am having revolves around the comobox. In my Die Inventory table I have a status field which will show the location of the die block. I want to be able to update this field once the status changes Example: Inventory > In-Production > Machining. I want a combobox to select the DieID then in another combo display the current status of that block and if changes are made update the status field in the Die Inventory table. Once again I am great-full for all the help and support on the thread THANKS!