FK With Tables

[jo15765]

Does Microsoft Access have the ability to set-up a Foriegn Key when using a many-to-many relationship?

[orange]

Possibly, but not directly. Many to Many relationships in a relational database are usually decomposed into 2 One to Many relationships.
Many--------------Many
Student>------<Course
becomes

One----------Many---------------------One
Student---<StudentTakesCourse>--- Course

[CJ_London]

you can set relationships by going into the relationships window on the database tools tab.

[jo15765]

my thought was....
Machine Table
--Each Machine gets MachineId
Parts Table
--Each Part Gets PartId
Machine Parts Table
—where we map the MachineId to the PartId
—this is where many to many relationship comes to play
—example
MachineId | PartId
1 | pt1
1 | pt2
2 | pt4

next create a Vendor Part table and map just like we did in the Machine Parts table

DOes this make sense?

[orange]

Yes.

Can you describe the "business rules" involved?

There may be 1 or many Machines
1 Machine can make 1 or many Parts
a Vendor may require 0,1 or many Parts

or whatever relates your Machines, Vendors, Parts....

[jo15765]

There may be 1 or many Machines
1 Machine can make 1 or many Parts
a Vendor may require 0,1 or many Parts

or whatever relates your Machines, Vendors, Parts....

Yes, that sums it up nicely.

There will be ONE machine - but a machine is made up of many parts
ONE part can be used for MANY machines

Then to the Vendor/Part relationship.
One Part can be ordered from Multiple Vendors

That's why I thought of using the Machine Parts Table and Appliance Parts Table as sort of a "mapping" to "tie" it all together

[orange]

What is Appliance? How is it different than Machine?
How about describing an example?

Machine X is made up of Parts A, B,C and is ordered by Vendor q and R

[jo15765]

Sorry - changing terminology as it goes. Machine = Appliance. I changed it to Machine as it made more sense.

Example:
tblMachine --
MachineId | MachineName
wm1 | Washing Machine
dw2 | Dishwasher

tblParts
PartId | PartName
or1 | ORing
bh2 | Black Hose

tblMachineParts
MachineId | PartId
wm1 | or1
wm1 | bh2
dw2 | or1


Does that make sense?

[orange]

Yes. Now where does Vendor fit?
This model may help.

[davegri]

When you finalize your MTM table design, you'll need a way to enter and maintain data in those tables.
Have a look at this:

https://www.accessforums.net/showthread.php?t=79814

[jo15765]

I like this set-up! Esp how you tied the vendor to partId.

Seems very very similar to how I will be setting things up.

One thing I will note tho, is that you have a FinalProduct table, and that ties to the Customer (who purchased). I'm trying to wrap my mind around how to have this work in my scenario, as we currently have an inventory of Machines already built and we will be selling them as orders come in.

[jo15765]

When you finalize your MTM table design, you'll need a way to enter and maintain data in those tables.
Have a look at this:

https://www.accessforums.net/showthread.php?t=79814

Ohhhhh with a little tailoring, I think this would suit my needs great. Thanks for sharing!