Arthur Fuller
artful at rogers.com
Sat Oct 1 13:30:06 CDT 2005
This comment is perhaps better directed at the thread on relational databases, but since here is a splendid example I will post my comment here. One of Codd's rules, I forget its number, is that you should not have two multi-valued columns in a single row. The front end is one thing, but the back end is another. Let me change examples to illustrate the point. Suppose you have three tables: Cities, Regions (provinces/states/cantons etc.) and Countries. I often see tables containing all three of these columns, and to my way of thinking that violates the rule just stated. All you really need in the table is the CityID, because London, Ontario is situated in Canada, while London, England is situated in England (I'm afraid I don't know its region). You could get away with this design by asking for the country first, then listing its regions, and finally the city in the selected region. But you at least risk the possibility that someone could enter nonsense, such as New York, Arizona, Norway. In part the reason why I prefer the "one multivalued column" approach is that then I can offload the responsibility to the db engine itself, rather than coding its logic in one or more front-ends that talk to the database. So by all means, go with the approach Steve suggests, but that occurs in the front-end. I see no reason to carry the redundancies into the back end. My $.02. Arthur -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Steve Conklin Sent: September 30, 2005 2:49 PM To: 'Access Developers discussion and problem solving' Subject: Re: [AccessD] Combo to Combo Virginia: You need a many-to-many table to relate the Systems to the Failure Types ... tblSystem: PK=systemID 1 (Alarm) 2 (Dryer) tblFailType: PK=FailTypeID 1 (Age) 2 (Wear) tblSystemFailType: FK1=SystemID FK2=FailTypeID Alarm Wear Alarm Shorted Dryer Wear Dryer Age Then on the form, after user selects system from combo1, requery combo 2 to show the applicable failure types Combo 2 source = "SELECT FailTypeID, FailType FROM tblFailType WHERE FailTypeID IN " & _ " (SELECT FailTypeID FROM tblSystemFailType WHERE SystemID=" & me.combo1 & ")" Hth Steve -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Hollis, Virginia Sent: Friday, September 30, 2005 2:27 PM To: accessD at databaseadvisors.com Subject: [AccessD] Combo to Combo I need to create a combo box that lists system identifications. When the selection is made from the system combo, the 2nd combo only lists related failures. The problem is the System and the Failure are repeated. An Alarm can have the same type of failure as a Dryer. How do I set up the tables & the form for the combos to list the Systems with the matching Failure types when they are all repeated? Example: System, Failure Alarm, Age Alarm, Normal Wear Alarm, Corrosion Battery, Overload Battery, Age Dryer, Normal Wear Dryer, Age Dryer, Obstruction Virginia -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com