Arthur Fuller
artful at rogers.com
Wed Jun 30 09:53:04 CDT 2004
An M:M relationship implies a pair of foreign keys, one of them typically hidden. Suppose we have, for example: Developers -- a table of Developers Skills -- a table of skills (Access, .NET, SQL Server, etc.) DeveloperSkills -- a table containing a minimum of DeveloperID and SkillID, and optionally other fields such as SkillLevel, YearsExperience, etc. This third table can be viewed from either direction, and depending upon the direction, one or the other foreign key will probably be hidden. I.e., you're looking at Developers_frm which contains a subform representing DeveloperSkills. DeveloperID will be hidden, so all you're worried about is the Skills part. Present it in a combo, with a NotInList event that opens a form to add new skills if you want. Alternatively, you're looking at Skills_frm, which contains a subform listing the Developers possessing said skill, and which hides the skill column (since it's inherited, as it were, from the parent). Present the Developers in a combo (with a NotInList if you need one). That's how I do it, anyway. HTH, Arthur -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Susan Harkins Sent: Wednesday, June 30, 2004 8:11 AM To: 'Access Developers discussion and problem solving' Subject: [AccessD] design/development question on representing mtmrelationships During a training session yesterday I had someone ask me what the easiest and best way to represent a many-to-many relationship for data entry. I admit, I was a little stumped and replied that the form/subform was probably still the standard solution but he's got me wondering -- how does everyone else handle it? I can't see any reason to really defer from the form/subform, but now I'm curious what creative solutions others might use. The other question I have -- and this one's my own -- I know there are a few easy ways to handle new primary key values when entering the many side of the relationship first -- pop up forms probably being the most common and even combo box controls that allow new values -- what do you guys do? Do any of you force the users to enter the one side first, which often seems a bit unnatural to the data entry operator? These aren't really technical questions, so much as they are just design/solution type questions. I'm interested in hearing what other people do. Susan H. -- _______________________________________________ AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com