[AccessD] Combo to Combo

Thanks so much, I think that is what has me thrown off on this whole
thing - the repeated values in the tables. I could not figure out a way
to make them match without repeating everything.
 
Steve,
When I used the Select, I get an error - Invalid SQL statement, Delete,
Insert, Or Select... It highlights the first part of the statement, to
the IN.
 
What am I doing wrong?
 
tbl_Identification (Alarm, Battery, etc)
IdentID (autonumber)
Identification (text)
 
tbl_Mechanism (Age, Wear, etc)
MechanismID (autonumber)
FailureMechanism (text)
 
tbl_IdentMechanism
IdentID (number)
MechanismID (number)(these are the primary key numbers from the
Identification & Mechanism tables)
 
The form has 2 combos, cboMechanism & cboIndentifcation
 
"SELECT MechanismID, FailureMechanism FROM tbl_mechanism WHERE
MechanismID IN " & _ 
" (SELECT MechanismID FROM tbl_IdentMechanism WHERE IdentID=" &
me.cboMechanism &")";
 
 
Virginia
***************
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
<http://databaseadvisors.com/mailman/listinfo/accessd> 
[mailto:accessd-bounces at databaseadvisors.com
<http://databaseadvisors.com/mailman/listinfo/accessd> ] 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