Cascading Lookup tables...help!

[Bec_FS]

I am using Microsoft Access 2003 Beg-Intermediate user with a small! amount
of programming knowledge.

I need some direction about some lookup tables that I need to create. I am
going to be entering some insect data that has been identified to different
taxonomic levels. What I was wondering is if there is a way to Cascade
lookup tables. I have several lookup tables with different insect taxonomic
groups. Class, order, family, genus, and species. Some insects might only
get identified at the Family level, and some may get identified at the Genus
level, and some can be identified all the way down to species. So if I
enter the species, I would like to know all the relevant taxonomic
information, if I enter Genus, then I would like it to only give me the
taxonomic levels above it, etc. I am not really sure how to approach
this...I could create multiple lookup tables for each taxonomic category or
create one big lookup.

And that brings up another question...when entering the data...since it can
be at different levels...I am not sure how to approach the table design for
field entry. If anyone has dealt with this before and can give me some
direction...I would greatly appreciate it.

 

[Jeff Boyce]

I may be reading too much into your description...

It sounds like you are trying to do this directly in the tables. If so,
STOP IT!

An Access table may look a little like a spreadsheet, but Access is a
relational database (and development environment).

Access tables STORE data, Access forms (and reports) are used to display
data.

If "relational" and "normalization" are unfamiliar, plan on spending the
time to learn your way up this curve before trying to get Access to do
things for you. After all, if you had just stepped up to a power saw for
the first time, you'd probably want to learn a bit about it before turning
it on, right?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[John W. Vinson]

I am using Microsoft Access 2003 Beg-Intermediate user with a small! amount
of programming knowledge.

I need some direction about some lookup tables that I need to create. I am
going to be entering some insect data that has been identified to different
taxonomic levels. What I was wondering is if there is a way to Cascade
lookup tables. I have several lookup tables with different insect taxonomic
groups. Class, order, family, genus, and species. Some insects might only
get identified at the Family level, and some may get identified at the Genus
level, and some can be identified all the way down to species. So if I
enter the species, I would like to know all the relevant taxonomic
information, if I enter Genus, then I would like it to only give me the
taxonomic levels above it, etc. I am not really sure how to approach
this...I could create multiple lookup tables for each taxonomic category or
create one big lookup.

You can do this on a Form - but you CANNOT and should not do it in a table,
nor should you even ever SEE a table in production use. And I'd avoid ever
using the "Lookup Field" misfeature:
http://www.mvps.org/access/lookupfields.htm
offers a critique.

It's not hard to create Combo Boxes ("lookups") based on queries which
reference another combo box. You could have a combo box cboOrder on the form,
based on a table of Orders; in that combo's AfterUpdate event you could set
the Control Source of an adjacent Families combo box to a query selecting only
those families in the selected order, etc.

And that brings up another question...when entering the data...since it can
be at different levels...I am not sure how to approach the table design for
field entry. If anyone has dealt with this before and can give me some
direction...I would greatly appreciate it.

There are some taxonomic databases around but I don't have a reference handy.
I'd be inclined to have six fields for the six taxonomic levels (and probably
other fields for date and site collected, comments, etc.)