[AccessD] Combo to Combo

Probably the line continuation character (" _ ")  ... Its only for code, not
a control property. Sorry, that's on me; as I got to the right edge of the
email, I put it in out of habit.  Make the  whole sql string string into one
line.

Hth
Steve

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Hollis, Virginia
Sent: Monday, October 03, 2005 8:33 AM
To: accessd at databaseadvisors.com
Subject: [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

 

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com