If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Drop-down links to other tables
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? |
#2
|
|||
|
|||
Drop-down links to other tables
On Wed, 22 Oct 2008 08:44:05 -0700, JenniferDances
wrote: 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. 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 [MVP] |
#3
|
|||
|
|||
Drop-down links to other tables
"John W. Vinson" wrote: On Wed, 22 Oct 2008 08:44:05 -0700, JenniferDances wrote: 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. 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? 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. |
#4
|
|||
|
|||
Drop-down links to other tables
On Thu, 23 Oct 2008 10:13:01 -0700, JenniferDances
wrote: 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/acc...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 -- John W. Vinson [MVP] |
Thread Tools | |
Display Modes | |
|
|