Form input by combobox value

[843Hammer]

I am familiar with Excel but kinda lost when it comes to Access.

I am building a Tool Usage access sheet for operator to use to track use.
I have multiple machines but some tools are the same so the life value is not always the same.
I am trying to automatically fill in the project tool life based on the selected Machine number and Tool number.

example:
M1 ; Tool 1 ; (expected tool life) 1200

M28; Tool 1; (expected tool life) 1400



I am trying to make the sheet so they select the Machine, Tool, and input what life was achieved. So when datasheet is generated I can calculate the life percentage.
Is there a way to lookup a field column based a value selected by the Combo box.
I have a MachineTbl that has all the Machine numbers and the each Tool Field names the life values are the are the records entered on the data sheet
I have a ToolNumTbl the has the Tool Numbers the same as the Field names on the Machine Tbl.

I have some VBA exper but no where near enough apparently!!!

[Micron]

It would be good to see a pic of your table relationships if you have created them. Access is nothing like Excel, and that has likely got you off on the wrong foot. In addition, calculations are rarely stored in Access tables because of volatility of the inputs, so calculations are done on the fly in queries and shown on reports and forms. A calculated table field is another matter, but it's something I rarely use. If this db is to be used by others, it is also ill advised to allow them to work directly in tables as if they were spreadsheets.

See "How to attach files" at the top of the page for help on posting pics. You just can't paste them in a post on this site.

[843Hammer]

Thanks for the response!

Here is just screengrab of the start. I am still the foundation building of the database. My intention will be to export the datasheet result to excel and perform the calculations and generate usage graphs and such. I still have more machines and life data to add but trying to figure out the format in case I need to restart from scratch. I need to make this work

[Micron]

It is as I thought. My advice is to research normalization and redesign, otherwise you will continue to struggle. Your original question can likely be answered, but it will be a work around of sorts. The insidious part of what you have there is that if you get a new tool you will have to redesign that table and all the forms/reports/queries/code that interact with it. That is the #1 sign of faulty design. My recommendation is to read as much of this as you can before going any further. At a minimum, get to understand (not just read) normalization. You can also research to find your own sources if you prefer. You probably should start a new thread if you have design questions afterwards.

Normalization Parts I, II, III, IV, and V
http://rogersaccessblog.blogspot.com...on-part-i.html
and/or
http://holowczak.com/database-normalization/

Entity-Relationship Diagramming: Part I, II, III and IV
http://rogersaccessblog.blogspot.com...ng-part-i.html

How do I Create an Application in Microsoft Access?
http://rogersaccessblog.blogspot.com...cation-in.html

Naming conventions
- http://access.mvps.org/access/general/gen0012.htm
- https://www.access-programmers.co.uk...d.php?t=225837

What not to use in names
- http://allenbrowne.com/AppIssueBadWord.html

About Auto Numbers
- http://access.mvps.org/access/general/gen0025.htm

The evils of lookup fields - http://access.mvps.org/access/lookupfields.htm
Table and PK design tips - http://www.fmsinc.com/free/newtips/primarykey.asp
About calculated table fields - http://allenbrowne.com/casu-14.html
About Multi Value Fields - https://www.isladogs.co.uk/multivalu...lds/index.html

Last/First/DLast/DFirst Explained
- https://codekabinett.com/rdumps.php?...ted-result-set

[Micron]

Addendum to the above: I take it that the zeros and nulls (empty fields) in the pic above mean that a machine on that record does not have that tool. Also, I forgot to mention that you could lose history based on that design (hard to say without knowing exactly what the db is supposed to support). That, or I imagine that once a tool is out of service it gets replaced and your intent is to add another field for it.

[mike60smart]

Hi
So are you saying that a Specific Machine Make ie Kitamura has Multiple Machine Numbers and EACH Machine Number has a Set Life Expectation??

If this is true then you need a Main Form to deal with the Machine Makes
A subform to deal with the Multiple Machine Numbers with
Another Subform to deal with the Specific Life Expectancies.

[843Hammer]

Addendum to the above: I take it that the zeros and nulls (empty fields) in the pic above mean that a machine on that record does not have that tool. Also, I forgot to mention that you could lose history based on that design (hard to say without knowing exactly what the db is supposed to support). That, or I imagine that once a tool is out of service it gets replaced and your intent is to add another field for it.

Thanks for that! I used the form attached to populate the MachineTbl. The number of tools would never change, the amount of Machines or Machine Number.
I will research the links you sent and see if that helps me.

[Micron]

I have to go out for the afternoon; can't review any further at this time. Here's what I was mulling over:

I think you need these tables:
tblProductLine (ProdIDpk, ProdLine, anything else about the product line)
tblMachine (MachineIDpk, Make, Model, MachineNum, anything else about the machine)
tblTools (ToolIDpk, ToolNum, anything else about the tool)
tblReasons (ReasonIDpk, Reason)

If a particular tool can be swapped to another machine, or if a tool is replaced and you want to maintain history you will need a junction table like tblMachineTool. This one may also need a field to capture when a tool is no longer on a machine due to swapping or replacing. However, it may be better to "archive" a tool that's no longer being used with a field in tblTools. That will also allow you to return only machines with active tools, or if you want to look at machine history, archived tools - or both. All that depends on the answer to the first sentence of this paragraph.

If a replacement can have 2 or more reasons then you will need a junction for that as well.

Last point for now - I don't see a table for relating replacement reasons to tools. Perhaps you were just not there yet. Gotta run.

[843Hammer]

Hi
So are you saying that a Specific Machine Make ie Kitamura has Multiple Machine Numbers and EACH Machine Number has a Set Life Expectation??

If this is true then you need a Main Form to deal with the Machine Makes
A subform to deal with the Multiple Machine Numbers with
Another Subform to deal with the Specific Life Expectancies.

Almost;
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 dont have to have laminated card bnroken down by machine with Tool lifes listed and in the event of any improvements made the projected tool life can be increased.

Per Micron i will repost this to the design subject forums.

[843Hammer]

I appreciate it, the ReasonTbl (3rd window on the 1st screen I posted) was one that I populated without a form just inputting directly to the datasheet. 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 the what Machine 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.

As suggested I will post this in the design portion going forward

[Welshgasman]

You will at least need a table for
Machines.
Tools
MachineTools linking Machines and Tools

You would only have records in the last table for any tools used in the Machine, not 40 all the time.

Those links Micron posted should explain why.

You will likely have a mainform and subform combination.
Mainform would hold a combo for the machines.
The subform would hold the MachineTool records linked to a machine.