Lookup fields use to group data

[Guest]

I have a DB with a table of fungi records containing a number of fields
listing information on each fungi found (the ‘Record’ table). Three of these
fields are "Family - Genus - Species". These are each populated in the
"Record" table from a separate lookup table. When a report is constructed
using all fields without "grouping", the output contains the correct family,
genus and species name for each record. The users have asked that the Report
group the records by the hierarchy "1st Family – 2nd Genus – 3rd Species".
Here the output prints the value assigned in the original lookup table
instead of the name. Any suggestions how I can print the taxonomic name not
the number? Many thanks for the time and any suggestions. (PS I have posted
this in the MS Access Yahoo group but so far haven't had any responses!)

 

[Duane Hookom]

If you are using lookup fields in table design, you might want to read
http://www.mvps.org/access/lookupfields.htm.
If you want the name, not the number, add the lookup table to the report's
record source and join the appropriate fields.

 

[Guest]

Thanks Duane, this solved my immediate problem but now I'm a little concerned
that I seem to have setup the data incorrectly. With taxonomic data the
names are often long, unusual and unfamiliar (Bolbitiaceae, Elasmomycetaceae
and so on) thus difficult for 'lay' data input operators and also frequently
change as advances in knowledge about the classifications are made. I
thought the lookup tables were ideal as they helped with the data input, and
made correction or changes to large record sets simple. Am I wrong? Is
there a better way to do this? Your suggestions for further reading would be
greatly appreciated.

If you are using lookup fields in table design, you might want to read
http://www.mvps.org/access/lookupfields.htm.
If you want the name, not the number, add the lookup table to the report's
record source and join the appropriate fields.

 

[Duane Hookom]

You definitely should use lookup tables. The link I provided criticizes the
use of lookup fields in table/field definitions. You can use combo boxes on
forms.

--
Duane Hookom
MS Access MVP

Thanks Duane, this solved my immediate problem but now I'm a little
concerned
that I seem to have setup the data incorrectly. With taxonomic data the
names are often long, unusual and unfamiliar (Bolbitiaceae,
Elasmomycetaceae
and so on) thus difficult for 'lay' data input operators and also
frequently
change as advances in knowledge about the classifications are made. I
thought the lookup tables were ideal as they helped with the data input,
and
made correction or changes to large record sets simple. Am I wrong? Is
there a better way to do this? Your suggestions for further reading would
be
greatly appreciated.

If you are using lookup fields in table design, you might want to read
http://www.mvps.org/access/lookupfields.htm.
If you want the name, not the number, add the lookup table to the
report's
record source and join the appropriate fields.