Creating lookup fields that stem from one another 1

[Newbie456]

I am creating a database for fossil collections. I have a lot of info that needs to go with each fossil. What I want to do is have several lookup fields with values that depend on a selection in another lookup field, but I have no idea how to accomplish this. I'm also relatively new in access.

For example, if I'm looking at a specific fossil - say, a shell, I select that from the Class list - when I get to species, I only want to have to select from species within that class, not species of mammals or reptiles - that would be much too long of a list.

Help, please! I'm sure others have done this before...

 

[Remou]

Have you seen:
Combo Box - dependant on another Combo Box faq702-4289
I think it is a step towards what you want.

You might like to look at:

Did you use the dreaded anti-feature of Lookup Fields in tables?

http://www.mvps.org/access/lookupfields.htm

 

[Newbie456]

Thanks for the info - that's definately along the lines of what I want. If anyone knows of a way to do that with lookup fields in tables, I'd appreciate that too.

 

[Remou]

Did you have a chance to look at

http://www.mvps.org/access/lookupfields.htm,

which says it is not a good idea to use lookup fields in tables.

Will you not be using forms to enter your data?

 

[BobRodes]

You do that by storing your data in proper normalized format, and then using queries to get the data that you want. Tie your display artifacts to queries.

HTH

Bob

 

[LittleSmudge]

To reiterate Remou's point :-
DONT use lookup fields
DO use lookup tables


You have tables :-

tblClass
ClassId
CName
etc..


tblSpecie
SpecieId
ClassRef
SName
Description
etc..

On your form you have a combo box ( cboClass ) with tblClass as the RowSource

In the AfterUpdate event in that combo box you requery the RowSource of another combo (cboSpecie ) who's RowSource is
"SELECT * FROM tblSpecie WHERE ClassRef = " & cboClass

And so on to the next level and beyond.

Then in the table that stores the details of the individual fossils ( tblFossil ? ) you store the ClassIs, SpecieId etc by binding the comboBox to fields called ClassRef, SpecieRef etc ..

tblClass and tblSpecie are then your Lookup Tables.

'ope-that-'elps.



By the way - you can educate me on a relevant issue.
What is the correct list of groups for classifying living things ?
Class, Specie, .. .. Genus and Order come in the list somewhere I think.

I seem to remember that there's 7 layers to the thing but I can't remember what they all are.









G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[BobRodes]

kingdom, phylum, class, order, family, genus, species.

 

[Newbie456]

Thanks so much. I did look at why lookup fields are a bad idea and will follow the advice.
Thanks for the detailed advice. I've now been using Access for about two weeks - I still have a lot to learn. I'm working on making this as user friendly was possible. Many of my coworkers balk at using computers at all.
Real "dinosaurs" I know.

Thanks!!

 

[BobRodes]

To make it user friendly, organize your tables and relations properly. Don't worry about user friendliness in this phase, worry about understanding the way to organize your data. Then, make forms that present your data in a user friendly manner.

Bob

 

[Newbie456]

OKay, it worked great. I now have dropdown menus that work perfectly... but I came across another problem.

I went to make a report that will list species from a site, sorted by class, order, etc. Instead of listing the class name, it's now listing a reference number. How can I get it to display the name in the report?

 

[Remou]

You can use a query linking the relevant tables, or you can use a combobox in your report - the wizard will help.