Table to use multiple other tables

[Mechixx]

Hello there everyone!

So I'm in process of creating a new database for use at work that will manage and keep track of our tooling kits. i have a pretty good idea on what tables I'll need and how they're connected except for one thing.
basically the main table i want is my kitting table, 'tblKits'

tblKits

KitID

PartNumber_FK

Op

Shelf

Location

<tbody>
</tbody>

I'll then have a bunch of other tables that will hold different types of tooling and other parts needed for jobs, ex:

tblTooling

ToolingID

ToolingDescription

ToolingCode

ToolingDrawing/StockNumber

ToolingQuantity

ToolingNotes

<tbody>
</tbody>

tblFixtures

FixtureID

FixtureDescription

FixtureQuantity

FixtureDrawing/StockNumber

FixtureNotes

<tbody>
</tbody>

This is where I'm not sure what to do. So for every Kit, it'll have the part number, the Op number, the shelf, and location, and then a whole bunch of tooling, which could include a handful of tools, multiple fixtures, and many other parts.
What would be the best way of building each kit with all these tools? i was hoping there's a way that i only need to fill out one kit, but then able to tie in as many tools as i want per kit, for example:

KitID	PartNumber_FK	Op	Shelf	Location	Tooling
1	107	10	1	A-1	Boring Bar Turning Tool Fixture Etc...

<tbody>
</tbody>

Is this possible? or will i need to basically fill out the same kit each time and tie each separate tool with it and then after maybe make a query that shows per each kit, all the tools associated with it?

Hoping this isn't too confusing lol any help would be greatly appreciated, cheers.

 

[jmacleary]

Hi there. You could have a field in your tooling table called (for example) ToolingSet. Then you could call up all the toolings in that set by just using the set number/reference. I would be inclined to try and identify some common sets and give these sensible names, then you could add specific tools as additional items.

 

[xenou]

Logically you probably have a many-to-many relationship between kits and tooling/fixutures/etc. -- one kit has many tools, one tool goes in many kits.

So you many need to implement linking tables (also called junction tables or associative tables) to resolve the many to many relationships:

tblKits_tblTooling (KitID, ToolingID)
tblKits_tblFixtures (KitID, FixtureID)

since you have several tables this is already ugly. But that's tooling for you. You really might want to look into buying a product for managing materials like this - it is not something you want to tackle lightly unless you have some experience with database development, especially if there is an inventory component to this (Parts in stock, out of stock, issued, returned, removed, ordered ... etc. etc.).

 

[Mechixx]

i think i see what you mean, but instead of having that 'ToolingSet' in the tooling table i think it would need its own table perhaps?

tblToolingSet

ToolingSetID

Kit_FK

Tooling

<tbody>
</tbody>

So within this table i would select which kit i want to add a tool to using Kit_FK which would be the KitID from tblKits, and then the tooling field i would select a tool/part to add from 5 different tables (each table will hold a different type of tool, so i have one table that holds all my cutting tools, one table for jaws, one for chucks, one for fixtures, and another for anything else that doesn't fit any of those)
Again, i guess it could save me from typing out the kit every time but id still have to create many records for each kit since there's quite a bit of tooling that goes into each one. i was hoping there was some way of selecting a kit, and saying "all these tools are apart of that kit" so i wouldn't have to select the same kit multiple times over and apply one tool at a time..

 

[Mechixx]

Logically you probably have a many-to-many relationship between kits and tooling/fixutures/etc. -- one kit has many tools, one tool goes in many kits.

So you many need to implement linking tables (also called junction tables or associative tables) to resolve the many to many relationships:

tblKits_tblTooling (KitID, ToolingID)
tblKits_tblFixtures (KitID, FixtureID)

since you have several tables this is already ugly. But that's tooling for you. You really might want to look into buying a product for managing materials like this - it is not something you want to tackle lightly unless you have some experience with database development, especially if there is an inventory component to this (Parts in stock, out of stock, issued, returned, removed, ordered ... etc. etc.).

ya i agree, i think either way i look at this, its going to get ugly lol i have a bit of experience with database development, but im sure theres software out there already that was made just for this, i just might look around for that and see my options.
i was hoping there was some way or easy way out of doing this in access that i hadn't thought of, but im realizing i dont think its going to be easy either way now lol