fancy foreign key?

[hi there]

hi everyone,

i've stumbled across a little problem with one of my forms i was hoping someone could help me with. i have a form based on a many-to-many relationship with the following tables:

1. tblEquipment (FK)
2. tblRequirement (FK)

and a third table tblCRequirement that brings the two together. the tblCRequirement's primary key is a compound key composed of the other two tables PK's. my problem is that every time a user needs to populate the form based on the many-to-many relationship they have to lookup the PK's from the other tables. this gets a little time consuming/complicated. what i would like to do is have a little command button next to each of the FK's that lauches another form based on the associated table (e.g. tblEquipment) in kind of a listbox format that allows the user to find a particular piece of equipment, select it, and have it automatically populate the control in the many-to-many form. this seems like it would be possible, but i haven't the foggest idea how to do it. could someone please help me with this. as always i am very grateful for any and all help.

thanks

 

[neileg]

Why?

Why do you need to compound the keys? This looks like a normal junction table situation. Surely you need to store the FKs from your two tables as fields in the junction table. The PK for the junction table would just be an autonumber.

Pat has shown the use of junction tables in many to many relationships, so search for her explainations.

 

[hi there]

hi pat and neileg,

thanks for the responses. pat your exactly correct that the business rules require a compound key to make a unique record. one piece of equipment can have many requirements but one requirement can also have many pieces of equipment associated with it. so by changing the requirement i want that change to cascade for each piece of equipment that has the associated requirement.

does anyone have any suggestion on making a nice GUI that allows usering to easily populate these FK's (i.e. compound PK)?

thanks

 

[hi there]

whoops. i spoke too soon. the junction table turns out to be a parent in a 1-to-many relationship, so it looks like i do need an autonumber PK. pat could you explain what you meant about using a unique index for the two FK's in the junction table?

thanks pat

 

[hi there]

thanks pat. i think you're right that a piece of equipment will never be associated to the same requirement more than once, but will indexing increase the speed of queries based on this table?

thanks

 

[hi there]

great. thanks pat.