Relating fields to other fields

[plubbr06]

Problem Setup:
I have a Wildflower database that I have created, with each wildflower
having a unique ID (as well as unique common and latin name). Each
wildflower belongs to a family, which I want to be able to select based on
either the common or latin family name. Families names are stored in a
different table with a unique ID for each family.

Question:
How do I add both the common and latin family name to the wildflower record
based on the selection of either one (which would automatically select the
other)? In Excel, I would just write a formula for the cell or column
(field) which would sort through and find the corresponding Family ID and
paste the associated family names to the other table. In C++, it would be
even easier, using a loop until the value of the Family ID from the
wildflower record matched the value of the Family ID from the Family table,
then assigning the associated family named to the wildflower record.

It seems like it should be fairly easy, but I have yet to find a way that
works. Any help (preferably detailed, not just "use a query" or something)
would be very much appreciated.

 

[Albert D. Kallal]

Assuming you just need to store the correct "id" from your wildflower table,
simply use the combo box wizard.

Your first combo box will be based on the wildflower table (id + Latin name
column). This combo box will thus allow you to view/search by Latin name,
but will return "id" and store it in your field (lets called it
wildflower_id).

Do the above exact same for the common name (id + common name will be the
two columns the combo box is based on.). Bind this combo box to the SAME
wildflower_id as above. This will result in BOTH combo boxes following each
other and you be storing the "one" SAME wildflower id into the wildflower_id
field.

So, you not have to write one line of code here or even write an expression.
You can do this with the combo box wizards....

 

[Ken Sheridan]

Its even easier in a relational database like Access:

In the WildFlowers table include a foreign key column FamilyID say, which
references the primary key of the families table. Do not include columns in
the WildFlowers table for the common or Latin names; that introduces
redundancy and opens the door to inconsistent data.

In a form bound to the WidFlowers table include two combo boxes with
RowSource properties respectively:

RowSource: SELECT FamilyID, CommonName FROM Families ORDER BY CommonName ;

and:

RowSource: SELECT FamilyID, LatinName FROM Families ORDER BY LatinName ;

In each case the controls' other properties will be:

ControlSource: FamilyID

BoundColum: 1
ColumnCount: 2
ColumnWidths: 0cm;8cm

If your units of measurement are imperial rather than metric Access will
automatically convert the last one. The important thing is that the first
dimension is zero to hide the first column and that the second is at least as
wide as the combo box.

You can then select from either combo box, or type the name in, in which
case the control will go to the first match as each character is typed. The
other control should then automatically update to show the corresponding
common or Latin name.

Remember that table or column names which include spaces or other special
characters must be wrapped in square brackets in code or SQL, e.g. [Latin
Name].

Ken Sheridan
Stafford, England

 

[plubbr06]

Thank you both a lot for the help. I've done what you have suggested (I
think they were pretty much the same thing), and I have the relation between
the two tables as I wanted, as well as the ability to select either common or
latin name. After playing around with reports a little bit more, I was able
to get the output I wanted. Thank you both so much!

Brandon