Fern2408

[Fern2408]

:banghead:I am very new to databases and am struggling with the design of my database to record the process and tools needed for making parts.

So far I have put the following in one table
Date
Machine number
Part number
Description
Operation number
In another table the method includes
Tool number
Tool size
Tool position
Tool grade
The problem is for any part there can be 1 or as many as 12 of these tools used.
Am I right to use 2 tables and if so how can I bring them together in a report or query so that a sheet can be printed or records amended?
Hope I have explained it OK as I said I am very new to this.

 

[jzwp22]

Since a part requires multiple tools, that describes a one-to-many relationship. Should we assume that a tool can be used on multiple parts? If so, that is another one-to-many relationship. When you have 2 one-to-many relationships between the same two entities (parts & tools in this case), you have a many-to-many relationship which requires 3 tables as follows:

tblPart
-pkPartID primary key, autonumber
-PartNumber

tblTools
-pkToolID primary key, autonumber
-ToolNumber


tblPartTools
-pkPartToolID primary key, autonumber
-fkPartID foreign key to tblParts
-fkToolID foreign key to tblTools


If the tools are unique to a part, then you would only need 2 tables

tblParts
-pkPartID primary key, autonumber
-PartNumber

tblTools
-pkToolID primary key, autonumber
-fkPartID foreign key to tblParts


To bring the data in two (or more) related tables together you need to join the tables (via the key fields primary key to corresponding foreign key ) in a query. You would then base your report on the query.