Drop-down links to other tables

[JenniferDances]

I am trying to create a database to record Zoo transactions. What I want to
be able to do is be able to view information in other tables from a main
table.

Example:

Main table would have

Animal ID #
Common Name
Species Name
Institution
Transaction Type
ISIS #
Date of Transaction

And I'd like to be able to link to a sub-table "Insitution" that would have
the institution name, address, contact person's name and information, and to
link to a "Transaction" sub-table that would have type of transaction (loan
in/out, purchase/sale, donation in/out, etc), date initiated, date completed,
approved by, etc, and to a "Identifier" sub-table that would have animal
name, #, species, common name, ISIS number, etc. WITHOUT having to go from
one table to another.

Is this even possible? If so, how do I do it? If not, what's an
alternative way to organize the information so I don't have to click through
table after table or form after form to get to the information I need for a
particular animal?

 

[John W. Vinson]

I am trying to create a database to record Zoo transactions. What I want to
be able to do is be able to view information in other tables from a main
table.

Ummmm...

No. You don't.

Table datasheets are of VERY limited utility. Recent versions of Access are
(misguidedly, in my opinion) making it easier to use them, but they still
quickly run out of steam. Tables should be used for data STORAGE only, not for
display.

Instead, use a Form with subforms and/or combo boxes. A Combo Box can have
code in its "Not In List" event to (say) add a new species to the Species
table.

Example:

Main table would have

Animal ID #
Common Name
Species Name
Institution
Transaction Type
ISIS #
Date of Transaction

You're not using Access relationally, it seems. The common name and species
name should exist ONLY in the species table; if you have six raccoons in the
zoo, you would have a record in the Species table for Procyon lotor, common
name raccoon, etc.; your animal-transactions table would need only a field for
the SpeciesID (is that the ISIS#??? I don't know that term).

And I'd like to be able to link to a sub-table "Insitution" that would have
the institution name, address, contact person's name and information, and to
link to a "Transaction" sub-table that would have type of transaction (loan
in/out, purchase/sale, donation in/out, etc), date initiated, date completed,
approved by, etc, and to a "Identifier" sub-table that would have animal
name, #, species, common name, ISIS number, etc. WITHOUT having to go from
one table to another.

Again... *you would never open tables*. All data interaction would be via
Forms.

 

[JenniferDances]

Ummmm...

No. You don't.

Table datasheets are of VERY limited utility. Recent versions of Access are
(misguidedly, in my opinion) making it easier to use them, but they still
quickly run out of steam. Tables should be used for data STORAGE only, not for
display.

Instead, use a Form with subforms and/or combo boxes. A Combo Box can have
code in its "Not In List" event to (say) add a new species to the Species
table.


You're not using Access relationally, it seems. The common name and species
name should exist ONLY in the species table; if you have six raccoons in the
zoo, you would have a record in the Species table for Procyon lotor, common
name raccoon, etc.; your animal-transactions table would need only a field for
the SpeciesID (is that the ISIS#??? I don't know that term).


Again... *you would never open tables*. All data interaction would be via
Forms.

Okay...so how do I create a form? The wizard for that say I have to have
data in a table before I can use it. Do I have to create a table and THEN
create a form? And where do queries come into this? Do I have to create
those before I create a form? And if I do, can I have multiple subforms from
one form? "Access 2003 for Dummies" isn't very helpful here.

 

[John W. Vinson]

Okay...so how do I create a form? The wizard for that say I have to have
data in a table before I can use it. Do I have to create a table and THEN
create a form? And where do queries come into this? Do I have to create
those before I create a form? And if I do, can I have multiple subforms from
one form? "Access 2003 for Dummies" isn't very helpful here.

Forms are *just windows*, tools to manage data stored in tables. Yes, you must
create the tables first; if you're building a house, you pour the foundations
before you start to assemble the walls and windowframes!

Tables store data.
Queries let you select, combine, and sort data.
Forms let you display and edit data.
Reports let you print data.

You'll create them in basically that order.

Access for Dummies is pretty good *for what it's written to do* - which is
basically to teach you how to USE an existing Access application. It was never
intended to teach you relational design theory or how to create a database.

See some of the resources here, especially the tutorials in the last two:

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

A free tutorial written by Crystal (MS Access MVP):
http://allenbrowne.com/casu-22.html

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials