If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
Relationships?
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 |
#3
|
|||
|
|||
Relationships?
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 |
#4
|
|||
|
|||
Relationships?
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 |
#5
|
|||
|
|||
Relationships?
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 |
#6
|
|||
|
|||
Relationships?
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 |
#7
|
|||
|
|||
Relationships?
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 |
#8
|
|||
|
|||
Relationships?
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 |
#9
|
|||
|
|||
Relationships?
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. |
Thread Tools | |
Display Modes | |
|
|