Direction on a project

[MJuric]

I'm looking at using access to create a DB for our tooling. Last time I used Access seriously was over a decade ago and either I've forgotten everything and or it's significantly different. That being said I'm looking for general direction, ideas, approaches, tutorials etc etc on how I might accomplish the following.



We have hundreds of tools. We identify each tool with an eight digit code. This eight digit code is not unique and represents some characteristics of the tool. Each Tool will have a unique serial number. Each same tool number will be identical to any other tool number.

What I would like to do is the following.

1) have something, a form possibly, where I can add, look up etc tools by thier tool number.
2) When a tool number is pulled up it will list all the serial numbers associated with that tool number.
3) Be able to select a serial number and then a bunch of other information about that particular tool will pop up.
4) I would like to be able to enforce a unique serial number, no duplicates

I have an Excel Spread sheet with around 300 tools in it. It would be nice to have the Excel and Access linked but not expressly necessary, It's just easier to edit, enter, copy similar etc in Excel.

I've been messing with Access for a couple days and can import the data, link it, make forms etc. The main area I'm not being successful with is having the tool number pull up and list all the serial numbers. I can do it with a query directly but can't seem to figure out how to enter the tool number in a form or other place and have the results of the query be listed and certainly can't figure out how to be able to open more information based on selecting the results of that query.

So any information that might get me headed in the right direction would be appreciated.

Thank You

[CJ_London]

Access is not excel so need to know what is your table structure? I would expect at least two tables, one for the 8 digit tool code (as PK) & description and another for serial numbers which would include an FK back to the other table. (PK is Primary Key and unique to each record, FK is Foreign Key and links related records back to the record with the same PK value). If you have other data such as who/where a tool has been issued to, that would be in another table which would contain the serial number, date issued, date returned and perhaps the FK to a location/person - which would be a 4th table

Recommend before you start using forms, etc you need to get your data structure right so google 'database normalisation' to get an understanding of how data is related in a database

[Micron]

Some sample tool id data would help, as some of that doesn't make sense.

Each Tool will have a unique serial number.

yet somehow, some/each tool has an identical number.

Each same tool number will be identical to any other tool number.

I also fail to see how it is possible to identify a tool when it can/does have the same code as some other tool

We identify each tool with an eight digit code. This eight digit code is not unique

[CarlettoFed]

It would be helpful if you attached the Excel file to see how it is currently structured so that we can propose you an example in Access.

[CJ_London]

Suspect the 8 digit code is a tool type
Big hammer
Little hammer
Big wrench
Etc

But no point in guessing

[orange]

I agree that some sample data would be helpful to readers wanting to offer focused advice. It would also be helpful if you could tell us briefly about your "business". Something like "a day at the tool shed", or "this is what happens to a tool during its lifetime".
Try to work from a who, what, why, when, where, how much and how often theme to put the pieces into context.

You may get some help from this free data model from Barry Williams' site. Change Asset to Tool as a starting point.

For practical experience / learning of tables and relationships spend 45-60 minutes and work through one or two of these tutorials from RogersAccessLibrary.
ZYX Laboratories
Class info system
Catering Business
Widgets

[June7]

Review http://allenbrowne.com/ser-62.html and https://www.fmsinc.com/MicrosoftAcce...cascading.html

[MJuric]

These tools are going into CNC machines. As an example we have a machine that has a 305 tool robot matrix. In that matrix you could have a qty of 20 tools 11111111. The programs that are run call tool 11111111. Each tool however has a lifespan, they wear out. So each tool must be uniquely identified. That happens with serial number. So in the machine we would have tool 11111111 serial 10000,10001,10002...and so on. When tool 11111111, 10000 has run for two hours the life expires and the machine swaps it with tool 11111111, 10001 and so on.

The tool number itself is a non exact description of the tool something like type of tool, diameter of tool, length of tool.

So we will and can have 10 of the same tool numbers in the machine at the same time but can not have duplicate serial numbers.

I can post more info on this when I'm back in the office on Monday if needed.

Thank you.

[MJuric]

I recently moved from a continuous improvement position to plant manager. My goal here is to create a centralized system to track and control our machine tools in an attempt to decrease tooling cost, duplicate tools etc etc.

Our business is manufacturing. We have several product lines and a few non product lines as well as doing general contact work. In short were take bits of raw material and turn them into things that people use in cars, planes, medical equipment etc etc. These tools are the bit that removes material from the raw stock to get to the final part.

I'll definitely look at the tutorials.

Thank You.

[MJuric]

I definitely need to look at structure. Right now it's one big single sheet excel file.

Each tool/row has, tool#, serial#, what machine or location it is in and MANY columns of information about the tool.

I'll attach the spreadsheet on Monday.

Thank you.

[CJ_London]

one thing to include in your description of your business. You said

Each tool however has a lifespan, they wear out.

Does this mean they then get thrown away or can be resharpened/refurbished/repurposed?

Many years ago I worked in a business that made machines out of other machines, One part in particular was a template. Once the template had 'worn out' for machine A it could be repurposed for machine B, the same went for assorted guide wheels/profile presses, etc

[MJuric]

Adding Excel Sheet. This has two tabs. The first tab "Master" is essentially all my raw data. the second one "Import" is what I've been playing with for importing, adding/Removing, columns for getting different DB's, different information etc.

[MJuric]

The tools/Tool Numbers are never thrown away. All the tools have multiple parts which consist of the holder/Extensions and the actual cutting portion of the tool. The cutting portion get's replaced, indexed, resharpened but that has no effect on the tool number.