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
|
|||
|
|||
how do i assign a relationship to a table with 2 prime keys
im creating a database in school (university). i have 4 tables. one table
has two primary keys. each of those keys are seperate primary keys in other tables. first question is how do i show this as a relationship? two, when i run the report each event in the db has all 6 members. some events should only have four. btw, its a db for a bike club. thank you. |
#2
|
|||
|
|||
"Chris2" wrote in message
... "D L Barnard" wrote in message ... im creating a database in school (university). i have 4 tables. one table has two primary keys. each of those keys are seperate primary keys in other tables. first question is how do i show this as a relationship? two, when i run the report each event in the db has all 6 members. some events should only have four. btw, its a db for a bike club. thank you. D L Barnard, A table may not have two Primary Keys. It's literally impossible. MS Access (and SQL Server, Oracle, DB2, Informix, Sybase, MySQL, etc.) will not process any attempt to create a second Primary Key on the same table (SQL will generate an error message, and using the GUI interface to make a new Primary Key deletes the old one). I'm sure that what the OP has is a single PK that consists of 2 fields. -- I don't check the Email account attached to this message. Send instead to... RBrandt at Hunter dot com |
#3
|
|||
|
|||
"D L Barnard" wrote in message ... im creating a database in school (university). i have 4 tables. one table has two primary keys. each of those keys are seperate primary keys in other tables. first question is how do i show this as a relationship? two, when i run the report each event in the db has all 6 members. some events should only have four. btw, its a db for a bike club. thank you. D L Barnard, A table may not have two Primary Keys. It's literally impossible. MS Access (and SQL Server, Oracle, DB2, Informix, Sybase, MySQL, etc.) will not process any attempt to create a second Primary Key on the same table (SQL will generate an error message, and using the GUI interface to make a new Primary Key deletes the old one). Sincerely, Chris O. |
#4
|
|||
|
|||
I'm sure that what the OP has is a single PK that consists of 2
fields. So then something like this: Sub test() With CurrentProject.Connection .Execute _ "CREATE TABLE Table1 (" & _ " table1_ID INTEGER NOT NULL PRIMARY KEY);" .Execute _ "CREATE TABLE Table2 (" & _ " table2_ID INTEGER NOT NULL PRIMARY KEY);" .Execute _ "CREATE TABLE RelationshipTable1 (" & _ " table1_ID INTEGER NOT NULL," & _ " table2_ID INTEGER NOT NULL," & _ " PRIMARY KEY (table1_ID, table2_ID), " & _ " FOREIGN KEY (table1_ID) REFERENCES Table1 (table1_ID)" & _ " ON UPDATE CASCADE ON DELETE CASCADE," & _ " FOREIGN KEY (table2_ID) REFERENCES Table2 (table2_ID)" & _ " ON UPDATE CASCADE ON DELETE CASCADE);" End With End Sub Jamie. -- |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Design help, please | SillySally | Using Forms | 27 | March 6th, 2005 04:11 AM |
Update - If statement | Dan @BCBS | Running & Setting Up Queries | 13 | December 14th, 2004 06:02 PM |
Table Wizard Does Not Set Relationship if Foreign Key and Primary Key Name Do Not Match Exactly in Case. | HDW | Database Design | 3 | October 16th, 2004 03:42 AM |
Complicated Databse w/many relationships | Søren | Database Design | 7 | July 13th, 2004 05:41 AM |
Name not showing ID is | René | Setting Up & Running Reports | 11 | June 29th, 2004 01:40 AM |