Relationships?

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.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...esign/200801/1

On Fri, 11 Jan 2008 16:22:15 GMT, "bbrazeau via AccessMonster.com" u18997@uwe
wrote:

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.


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 pkToolnumber. Next create a junction
table ToolsProducts that has 2 pks 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.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...esign/200801/1

On Sat, 12 Jan 2008 16:13:55 GMT, "bbrazeau via AccessMonster.com" u18997@uwe
wrote:

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 pkToolnumber. Next create a junction
table ToolsProducts that has 2 pks 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.

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

"bbrazeau via AccessMonster.com" u18997@uwe wrote in message
news:7e0e852afa60c@uwe...
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.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...esign/200801/1

On Sat, 12 Jan 2008 13:48:10 -0600, Michael Gramelspacher
wrote:

On Sat, 12 Jan 2008 16:13:55 GMT, "bbrazeau via AccessMonster.com" u18997@uwe
wrote:

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 believe there are expensive diagramming tools that will create DDL from Access
tables.


See:
http://www.bestsoftware4download.com...-vnbcjjoa.html

(I have no affiliation with this software...)

--

remove uppercase letters for true email
http://www.geocities.com/jacksonmacd/ for info on MS Access security

On Sun, 13 Jan 2008 14:19:25 GMT, jacksonmacd
wrote:

On Sat, 12 Jan 2008 13:48:10 -0600, Michael Gramelspacher
wrote:

On Sat, 12 Jan 2008 16:13:55 GMT, "bbrazeau via AccessMonster.com" u18997@uwe
wrote:

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 believe there are expensive diagramming tools that will create DDL from Access
tables.


See:
http://www.bestsoftware4download.com...-vnbcjjoa.html

(I have no affiliation with this software...)


Oooops... this is a direct link:
http://dbweigher.com/index.php

--

remove uppercase letters for true email
http://www.geocities.com/jacksonmacd/ for info on MS Access security

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

--
Message posted via http://www.accessmonster.com

On Tue, 15 Jan 2008 14:22:21 GMT, "bbrazeau via AccessMonster.com" u18997@uwe
wrote:

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.

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.