G
Guest
Hi.
I need to go back to the proverbial well again. Thanks for being here...
I am creating a tooling database to track programs, tools, and part numbers.
I cannot seem to figure out how to set up the relationships to get my forms
to work properly. First let me explain the relationship between tools and
part numbers is this:
a) 1 part number can be associated with 1 tool
b) Multiple part numbers can be associated with 1 tool
c) 1 part number can be associated with many tools
d) Multiple part numbers can be associated with multiple tools.
All of these combinations will be associated with 1 program name.
Here we go...
I think that I need 4 tables:
1) Program Name table (with an autonumber primary key - Program ID)
autonumber primary key = Program ID - one to many relationship to Program ID
in table "2)".
text = Program Description.
etc.
2) Association (joiner) Table
autonumber Primary Key = Joiner ID - One to many relationship to both Joiner
IDs in table "3)" and "4)".
Program ID = many (in this table) to one relationship with Program ID in
table "1)"
(Each program can have many associations between part numbers and tools)
3) Part Number Table:
primary key Joiner ID = many (in this table) to one relationship with Joiner
ID in table "2)"
text primary key = PN
other p/n info etc...
(2 primary keys so p/n can only be listed once per tool group (which is
related by Joiner ID)
4) Tool Group Table:
primary key Joiner ID = many (in this table) to one relationship with Joiner
ID in table "2)"
primary key = Tool Number
other tool info etc...
Once general overall program info is entered into table #1, I want to be
able to load into table #3 one or many p/ns. Then I want to load into #4 the
associated tools that are related to the p/ns, one or many tools; Table #2
would keep the association between the p/n table and the tool table for this
program. Then I would like to be able to increment the Joiner table (#2) and
load another set of p/ns into table #3 and their associated tools into table
#4.
I would like to do this with a main form and 2 sub forms; one subform for
p/ns and one subform for tools.
They only way that I can think to do this is to use VBA to keep the
relationships and set the values into the joiner table #2.
Please help.
Thanks
Steve
I need to go back to the proverbial well again. Thanks for being here...
I am creating a tooling database to track programs, tools, and part numbers.
I cannot seem to figure out how to set up the relationships to get my forms
to work properly. First let me explain the relationship between tools and
part numbers is this:
a) 1 part number can be associated with 1 tool
b) Multiple part numbers can be associated with 1 tool
c) 1 part number can be associated with many tools
d) Multiple part numbers can be associated with multiple tools.
All of these combinations will be associated with 1 program name.
Here we go...
I think that I need 4 tables:
1) Program Name table (with an autonumber primary key - Program ID)
autonumber primary key = Program ID - one to many relationship to Program ID
in table "2)".
text = Program Description.
etc.
2) Association (joiner) Table
autonumber Primary Key = Joiner ID - One to many relationship to both Joiner
IDs in table "3)" and "4)".
Program ID = many (in this table) to one relationship with Program ID in
table "1)"
(Each program can have many associations between part numbers and tools)
3) Part Number Table:
primary key Joiner ID = many (in this table) to one relationship with Joiner
ID in table "2)"
text primary key = PN
other p/n info etc...
(2 primary keys so p/n can only be listed once per tool group (which is
related by Joiner ID)
4) Tool Group Table:
primary key Joiner ID = many (in this table) to one relationship with Joiner
ID in table "2)"
primary key = Tool Number
other tool info etc...
Once general overall program info is entered into table #1, I want to be
able to load into table #3 one or many p/ns. Then I want to load into #4 the
associated tools that are related to the p/ns, one or many tools; Table #2
would keep the association between the p/n table and the tool table for this
program. Then I would like to be able to increment the Joiner table (#2) and
load another set of p/ns into table #3 and their associated tools into table
#4.
I would like to do this with a main form and 2 sub forms; one subform for
p/ns and one subform for tools.
They only way that I can think to do this is to use VBA to keep the
relationships and set the values into the joiner table #2.
Please help.
Thanks
Steve