Tooling Database for Machine Shop

Question

Looking for help in the logic necessary to build the following database:

-Building a Tool Database for Machine Shop: Hundreds of Tools (some will be identical but allocated in several places), numerous Machines/Storage Locations, numerous Employees to be able to relocate tools from Storage to Machines...

-"Tools" are assemblies of numerous parts (holder, cutter, extensions, etc.) and one Tool Number describes an assembly

-Will have several Tables with various Inputs that can be selected from Combo box in a Tool Table to create the assembly.

-Ideally, nine individual Tables will be made for Tool Subcategories (different types of cutters)

-These will have similar but different data

I'm having trouble making a Form, Report or Query to show ALL tool subcategories at once.

-I would like a Query to show all 9 subcategories of tools but only show the same fields from each sub.

• -This would show me what the entire shop's tools and where they're at

From there, I would like to make Buttons to quickly look at numerous Locations to see what Tools (if any) are in that location only.

-Ex: I want to click a button for "Machine 1" and see what tools are stored in it.

Also need a way for Shop Floor Employees to quickly move Tools from one Location to another and not accidentally (or intentionally) destroy the database.

-Ex: Machine 1 needs a tool currently in Storage Location 1...

-Need quick and easy process for employees to relocate these Tools on the database prior to doing so in the machines.

Your replies will be much anticipated and appreciated!

Answer 1

............and am not sure what I1 and I2 refer to in the SQL code

They are references to two instances of the Items table.  Because the subquery is correlated with the outer query on PublicationID and ItemNumber, it is necessary to differentiate between the two instances of the table.  This is done by giving each a distinct alias 'Items AS I1' and 'Items AS I2'. The subquery is then restricted to the current copy (item) of the publication in the outer query:

    WHERE I2.PublicationID = Publications.PublicationID

    AND I2.ItemNumber = I1.ItemNumber

There is no need to give the Publications table an alias, as this table is not used by the subquery, so the Publications table in the outer query can be referenced by its name in the subquery.

The alias you use is up to you.  I follow the convention of using the initial letter (or sometimes letters) of the table name followed by a number.

Answer 2

> My inclination would be to use a Unique Identifier such as Tool Number, Machine Number, Storage Location, etc. rather than this type of ID.

That is called a "natural key". StateAbbreviation is the classic example.
But what if a new manager comes around who will rename the tools, machines, and locations? That would mean changing PK values, which is typically frowned upon.
If ToolNumber is just another member field in tblTools, and ToolID is the autonumber, changing the value is trivial, and everything continues to work just fine.

Answer 3

Yes. Normalization puts a name on an issue I'm facing. Thank you!

---

I am trying to prove the concept on a small scale and have minimal data in my tables for tools (and tables for the numerous variable fields within), machines, storage etc.

I will work on Normalization and work more on Primary Keys...

-I'm struggling to understand the use of AutoNumber and using "ID" in each table. My inclination would be to use a Unique Identifier such as Tool Number, Machine Number, Storage Location, etc. rather than this type of ID.

Answer 4

You should also search for sample databases and templates that parallel your requirements.

Again, start with tables in any such template you do find.

Answer 5

Start with tables not forms. Forms are way down the road.

And in order to understand how to create your tables, you must learn what Normalization is and how to apply it.

Resources include this document and many, many others. Search for "Database Normalization" to get only relevant hits.

The YouTube videos hereare pretty good on-ramps for learning about database normalization, but they are essentially introductions, not technical references.

Again, don't worry at all about forms and queries at this point. You must have a solid foundation under the tables to proceed to that stage of development.