Relationships?

  • Thread starter Thread starter bbrazeau via AccessMonster.com
  • Start date Start date
B

bbrazeau via AccessMonster.com

Is it possible, and more importantly is it correct, to create a one to many
relationship between a table "Tools" with a multiple <pk> consisting of
Toolnumber and Insertnumber, and a table "ToolInsertHistory". I must add that
there is a table "Products" with <pk> ProductID that has a one to many
relationship to the Tools table. This is nessesary because a product may be
made in more than one tool insert combination and multiple products may be
made in the same tool but never the same tool insert combination. what I'm
trying to do is track tool repairs and modifications by both tool and insert
which is the only way I see to assure I don't get duplicte infomation.If I
track by ProductID I would get more than one toolinserthistory. If I track by
Toolnumber alone I would get more than one products history. Please excuse my
long winded explanation and thanks for any help.
 
Is it possible, and more importantly is it correct, to create a one to many
relationship between a table "Tools" with a multiple <pk> consisting of
Toolnumber and Insertnumber, and a table "ToolInsertHistory". I must add that
there is a table "Products" with <pk> ProductID that has a one to many
relationship to the Tools table. This is nessesary because a product may be
made in more than one tool insert combination and multiple products may be
made in the same tool but never the same tool insert combination. what I'm
trying to do is track tool repairs and modifications by both tool and insert
which is the only way I see to assure I don't get duplicte infomation.If I
track by ProductID I would get more than one toolinserthistory. If I track by
Toolnumber alone I would get more than one products history. Please excuse my
long winded explanation and thanks for any help.
Click to expand...


Just something to consider as I do not really understand the situation.

Create a new database, create a new module, copy subprogram code into the module
and save it. In the Immediate Window type call CreateTables and press enter. Go
back to the database window and click the relationships icon. Arange the tables
so you can understand the diagram. Is it even close to what you want?


Sub CreateTables()
With CurrentProject.Connection

..Execute _
"CREATE TABLE Products" & _
"(ProductID VARCHAR (10) NOT NULL PRIMARY KEY);"

..Execute _
"CREATE TABLE Tools" & _
"(Toolnumber VARCHAR (20) NOT NULL PRIMARY KEY);"

..Execute _
"CREATE TABLE Inserts" & _
"(Insertnumber VARCHAR (20) NOT NULL PRIMARY KEY);"

..Execute _
"CREATE TABLE ToolInserts" & _
"(Toolnumber VARCHAR (20) NOT NULL" & _
" REFERENCES Tools (Toolnumber)" & _
",Insertnumber VARCHAR (20) NOT NULL" & _
" REFERENCES Inserts (Insertnumber)" & _
", PRIMARY KEY (Toolnumber,Insertnumber));"

..Execute _
"CREATE TABLE ProductTools" & _
"(ProductID VARCHAR (10) NOT NULL" & _
" REFERENCES Products (ProductID)" & _
", Toolnumber VARCHAR (20) NOT NULL" & _
", Insertnumber VARCHAR (20) NOT NULL" & _
", CONSTRAINT fk_product_tools_tool_inserts" & _
" FOREIGN KEY (Toolnumber,Insertnumber)" & _
" REFERENCES ToolInserts (Toolnumber,Insertnumber)" & _
", PRIMARY KEY (ProductID,Toolnumber,Insertnumber));"

..Execute _
"CREATE TABLE ToolInsertHistory" & _
"(Toolnumber VARCHAR (20) NOT NULL" & _
", Insertnumber VARCHAR (20) NOT NULL" & _
", CONSTRAINT fk_tool_insert_history_tool_insertss" & _
" FOREIGN KEY (Toolnumber,Insertnumber)" & _
" REFERENCES ToolInserts (Toolnumber,Insertnumber)" & _
", RepairDate DATETIME NOT NULL" & _
", RepairReason VARCHAR (100) NOT NULL" & _
", PRIMARY KEY (Toolnumber,Insertnumber,RepairDate));"

End With
End Sub
 
Wow! Michael that was way cool!! Is there an easy way to do this in reverse
so I can send my present relationships layout back? I have not had time to
input in some data and test the relationships in what you posted yet, but it
seems like the relationships are more complicated than I need. I read my 1st
explanation again and think I may have described it less clearly than I could.
I think that what I need to do is remove <fk> ProductID and Insertnumber from
my Tools table making it a single <pk>Toolnumber. Next create a junction
table ToolsProducts that has 2 <pk>s ProductID and ToolNumber, and fields
ToolProductID (unique), and Insertnumber (Insertnumbers will repeat often in
this table and will generally be 1-20,and never > 99). Finally a new table
ToolProductHistories that has a <pk> EventID and <fk> ToolProductID. My
description gave too much importance to the Insertnumber field as ToolProduct
will always be unique since the same Product will never be in the same Tool
for more than one instance.
Question: Should or shouldn't ToolProductID be an autonumber? I've made it
Required, Indexed(no duplicates) in ToolsProducts, and Required, Indexed(yes
duplicates) inToolProductHistories.
 
Wow! Michael that was way cool!! Is there an easy way to do this in reverse
so I can send my present relationships layout back? I have not had time to
input in some data and test the relationships in what you posted yet, but it
seems like the relationships are more complicated than I need. I read my 1st
explanation again and think I may have described it less clearly than I could.
I think that what I need to do is remove <fk> ProductID and Insertnumber from
my Tools table making it a single <pk>Toolnumber. Next create a junction
table ToolsProducts that has 2 <pk>s ProductID and ToolNumber, and fields
ToolProductID (unique), and Insertnumber (Insertnumbers will repeat often in
this table and will generally be 1-20,and never > 99). Finally a new table
ToolProductHistories that has a <pk> EventID and <fk> ToolProductID. My
description gave too much importance to the Insertnumber field as ToolProduct
will always be unique since the same Product will never be in the same Tool
for more than one instance.
Question: Should or shouldn't ToolProductID be an autonumber? I've made it
Required, Indexed(no duplicates) in ToolsProducts, and Required, Indexed(yes
duplicates) inToolProductHistories.
Click to expand...

I believe there are expensive diagramming tools that will create DDL from Access
tables.

It is usually difficult for me to understand a problem presented as a narrative;
I thought an Insert was an actual thing. So, there is to be no Inserts table;
insertnumber is an attribute of the ToolsProducts table.

Using Autonumbers here is mostly a matter of style. I do not see that it gains
you anything in your case.

CREATE TABLE ToolsProducts
(ToolProductID IDENTITY (1,1) NOT NULL PRIMARY KEY
, Toolnumber VARCHAR (10) NOT NULL
REFERENCES Tools (Toolnumber)
, ProductID VARCHAR (10) NOT NULL
REFERENCES Products (ProductID)
, Insertnumber INTEGER NOT NULL
, UNIQUE (Toolnumber, ProductID, Insertnumber));

CREATE TABLE ToolProductHistories
(EventID IDENTITY (1,1) NOT NULL PRIMARY KEY
, ToolProductID INTEGER NOT NULL
REFERENCES ToolsProducts (ToolProductID)
, Eventdate DATETIME NOT NULL
, UNIQUE (ToolProductID, Eventdate)); -- maybe
 
From your description, a product may contain a Tool only or it may contain a
Tool and a Tool Insert.
Is a Tool Insert always a Tool in the Tools table or is a Tool Insert a
separate thing?
The combination of Tool and Tool Insert together is unique.

Jeanette Cunningham
 
Hi Michael, I Copied and pasted your code into the previous module, and after
some editing, got it to work. A couple questions:
1). What did "-- maybe" mean (was it a comment)?
2). I tested some data in the tables and found: You can't repeat the same
combination of Toolnumber,ProductID, and Insertnumber (good). You can,
however repeat Toolnumber and ProductID if Insertnumber is different, and you
can repeat Toolnumber and Insertnumber if ProductId is different (both bad).
My question.What field property does the UNIQUE condition you set in code
affect, and could UNIQUE be used to address the problems I mentioned?
Thanks again for yours and everyone elses comments and suggestions.

Sub CreateTables() ' Instructions: Open the immediate window by pressing
Ctrl+G Then type in: call CreateTables and press "Enter"
With CurrentProject.Connection

..Execute _
"CREATE TABLE Products" & _
"(ProductID VARCHAR (10) NOT NULL PRIMARY KEY);"

..Execute _
"CREATE TABLE Tools" & _
"(Toolnumber VARCHAR (20) NOT NULL PRIMARY KEY);"

..Execute _
"CREATE TABLE Inserts" & _
"(Insertnumber VARCHAR (20) NOT NULL PRIMARY KEY);"

..Execute _
"CREATE TABLE ToolsProducts" & _
"(ToolProductID IDENTITY (1,1) NOT NULL PRIMARY KEY " & _
", Toolnumber VARCHAR (10) NOT NULL " & _
" References Tools(Toolnumber)" & _
", ProductID VARCHAR (10) NOT NULL " & _
" References Products(ProductID)" & _
", Insertnumber INTEGER NOT NULL" & _
", UNIQUE (Toolnumber, ProductID, Insertnumber));"

..Execute _
"CREATE TABLE ToolProductHistories" & _
"(EventID IDENTITY (1,1) NOT NULL PRIMARY KEY " & _
", ToolProductID INTEGER NOT NULL " & _
" References ToolsProducts(ToolProductID)" & _
", Eventdate DATETIME NOT NULL " & _
", UNIQUE (ToolProductID, Eventdate));"
'-- maybe"

End With
End Sub
 
A couple questions:
1). What did "-- maybe" mean (was it a comment)?
2). I tested some data in the tables and found: You can't repeat the same
combination of Toolnumber,ProductID, and Insertnumber (good). You can,
however repeat Toolnumber and ProductID if Insertnumber is different, and you
can repeat Toolnumber and Insertnumber if ProductId is different (both bad).
My question.What field property does the UNIQUE condition you set in code
affect, and could UNIQUE be used to address the problems I mentioned?
Thanks again for yours and everyone elses comments and suggestions.
Click to expand...

UNIQUE (ToolProductID, Eventdate)
I do not know whether a tool can be repaired more than once each date. If a
tool can come in for repair more than once a day, then UNIQUE (ToolProductID,
Eventdate) is not appropriate, unless EventDate also has a time part. Maybe
there should be an EventInDate and an EventOutDate instead of just an
EventDate. You are the only one who can know this, because I do not really know
the situation.
 
Back
Top