How do I make a MS Access Subform, A Special Case...] [modified]
-
I am working on a MS Access database that has a central table with a composite key consisting of seven elements, and another table also with the same seven composite key elements. The central table composite key is unique thereby making it a composite primary key. The other table has multiple records of the composite key. In other words, there is a one to many relationship between the main table and the other table using composite keys. I need to create a form with the composite key elements on the top of the form and a subform with the multiple records matches to the composite key from the main form. The problem is the MS Access only provides the ability to create a subform using three elements from the seven elements of the composite key. What would be the solution to allow me to create a subform that matches all seven elements of the composite key form the "many" table to all seven elements of the primary composite key from the central table? :confused::confused::confused:
modified on Thursday, December 3, 2009 9:26 PM
-
I am working on a MS Access database that has a central table with a composite key consisting of seven elements, and another table also with the same seven composite key elements. The central table composite key is unique thereby making it a composite primary key. The other table has multiple records of the composite key. In other words, there is a one to many relationship between the main table and the other table using composite keys. I need to create a form with the composite key elements on the top of the form and a subform with the multiple records matches to the composite key from the main form. The problem is the MS Access only provides the ability to create a subform using three elements from the seven elements of the composite key. What would be the solution to allow me to create a subform that matches all seven elements of the composite key form the "many" table to all seven elements of the primary composite key from the central table? :confused::confused::confused:
modified on Thursday, December 3, 2009 9:26 PM
A seven field composite primary key X| - sounds like a disaster has found somewhere to happen. This is a BAD idea, I have no knowledge of the performance issues with such a mess but the sheer cumberomeness of forever doing a 7 key join would drive me to drink. I recommend that you insert a primary key into the central table and populate it, then turn autonumber on. Now add the foreign key field to the other table(s) and use the 7 key horror to populate the field. Make your relationship using these fields. You must make sure you support the change within your CRUD code.
Never underestimate the power of human stupidity RAH