CNC Machine Tool Usage Database design

[843Hammer]

I am new to Access and have been tasked with designing a shopfloor facing data entry form that does the following:

I have 25 different machines, the most tools any machine can hold is 40 Tools; but the Tool Life for any given tool might be different, in some cases they are the same. I am trying design it as such that I don’t have to have laminated card broken down by machine with Tool life’s listed and in the event of any improvements made the projected tool life in the MachineTbl can be increased.

The adaptability needs to be:
1. add additional machines in the event more machines come online or more are purchased.
2. Tool Numbers might be moved around, rare but possible
3. Expected Tool Life values might increase or decrease based on real world results.


At the moment I am trying to just get all my inputs structured so next:

1. I can make a Data entry form that has the Product Line, Machine Number, Tool Number as selectable Combo Boxes.
2. Based on what Machine Number and Tool Number is selected a box on the form would have the "Expected Tool Life" populated automatically.
3. The user would then input what the life was if changed early and the reason for the change


4. A "submit" button would be on the form to then populate the usage data.
5. Which is exported via Excel to be analyzed and graph usage on monthly basis.

[orange]

Can you describe Tool and Tool Life with an example? Is Tool (eg an external cutting tool) just a type of tool or is it individually identifiable?
When a Tool on a Machine is considered "no longer usable", you remove the tool and insert/add another tool of the same type, right?
If so, does that make the combination of Machine and Tool "type" something you need to record?
Should Tools be more explicitly defined /named--rather than Tool1, Tool2....?

The more info you van provide in context, the more focused the responses from readers.
Remember, we don't know you or your business, so for us to help you, you need to educate us.

[mike60smart]

Hi
Can you upload a screenshot of your table relationships or better still upload a copy of the actual database.

[Welshgasman]

I already posted in your other thread.
https://www.accessforums.net/showthr...786#post521786

[843Hammer]

Understood,
The only info on the tool need is the the tool number in the machine and expected (100% usage).
The tool type and all that is of no need in the database structure.
On output of data entry form I would capture Date, Shift, Machine Number, Tool Number changed, Expected Tool Life, Actual Tool life.
Selections need to dropdowns with exception of Actual Life achieved, everything else is standardized to an extent.

[mike60smart]

Hi

Are the Tool Names actually named Tool1, Tool2 etc...

[843Hammer]

ToolSheet Dbase.zip


Here is my database file in its current form
I haven't had a chance to go through the links Micron sent which is my homework tonight!

[843Hammer]

Correct that just the Tool Pot location in each machine, and as long I can sort by machine I can identify problem machine

[mike60smart]

In order for you to be able to create a data Entry Form which allows you to select a Machine Number & Tool Number and then have the Life expectancy
populate automatically, you first need to setup forms which allows you to keep track of which Machine Number is associated with which Tool List.

This for would be along the lines of that shown in the attached screenshot.

[mike60smart]

Hi

Your Machinetbl is not correct.

Look at the following Relationship Diagram which shows how all of the tables are related.

The Colour Green in the main Form based on the tblMachines is the PK
In the related table tblMachineNumbers the FK is coloured Green

[843Hammer]

Mike,
That helps me a lot! I've been trying to learn by looking at some our prev databases and reverse engineer them. But as I have learned they were poorly designed as well which explains why there were always problems associated with them!

OK so that makes sense, the relationships are as follows:???

tblMachines to tblMachineNumbers is 1 to many
tblMachineNumbers to tblNumberTools is 1 to many
tblNumberTools to tblToolNumbers is 1 to many

But the tblNumberTools is a linking table?

[mike60smart]

Hi

Here with an example of your Production Form together with the new Relationship Diagram.

The Combobox's on the Form Cascade so when you select a Machine the Combobox for Machine Numbers
only Machine Numbers associated with the machine will appear in the dropdown.
The same applies for Machine Numbers to Tools

[mike60smart]

Hi
tblNumberTools allows for the Many Machine Numbers can have Many Tools

This is called a M to M relationship or 2 x 1 to Many relationships

[843Hammer]

Hi Mike,
That will be enough to point me in right direction.

Thanks alot!

[mike60smart]

Hi
No problem
Glad to help.