how do i assign a relationship to a table with 2 prime keys

[Guest]

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.

 

[Rick Brandt]

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.

 

[Chris2]

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.

 

[Jamie Collins]

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.

--