Using Tables and Forms

[Menglish]

I am creating a database that will store analytical data from a variety of sites. Each site has different monitoring requirements. Each file number will have many sample locations. I would like to have a macro to look up the file number and then display the form with required monitoring. Each form would represent one sample event. After filling in the data, I would like to be able to go to the next record and have the same information come up on the form (preferably without doing another search).



My initial thought was to have a table with each file number and required monitoring and use this to look up the information for the form. Then have the form populate a master table with all of the data from each file number's multiple monitoring locations. However, a brief search online looks like there may be a better way to do this.

Thank you for any help!

[alansidman]

Before you begin, I think it may be helpful to read this short white paper on database design.

http://www.deeptraining.com/litwin/d...aseDesign.aspx

[Dal Jeanis]

Also check out the tutorials and example problems over at Access MVP Roger Carlson's site at http://www.rogersaccesslibrary.com/. It's the process that Carlson goes through, not the specific examples, that matter.

Here are the various potential entities listed in your description. There are probably subsidiary entities within these, especially the actual monitoring requirements, that have not yet been identified:
* Analytical Data
* Site
* Monitoring Requirement
* File Number
* Sample Location
* Sample Event

Here are some underlying assumptions in your description of your business requirements:
* a macro to look up
* the form with the required monitoring
* the next record, the same information
* a master table
* all the data from each file number's multiple monitoring locations

Here are some explicit process questions I had that will affect the design:
* Is a site identified by "file number", or is there some other relationship between file and site?
* Are the actual locations to be sampled within a site fixed, or random?
* Do all fixed locations at a site receive the same monitoring frequency, or might they be different?
* Are future monitoring events to be explicitly scheduled in the database?
* What happens if the date of a future sample event needs to change, for practical reasons?

Without those answers, and at a blind guess, I see roughly the following breakdown of entities, as an initial sketch:
* Site (which I assume is identified by a File number and address)
* Sample Location (identified by the site and a location or GPS description)
* Monitoring requirement (identified by location, frequency, and sample type to be taken)
* Sample event (identified by location, date/time, sample type, and results information)
* Sample type (codes for the various possible things to sample)

This rough-in is based on assuming that all monitoring requirements are just different types of samples that can be identified with a few similar fields. The more different a particular monitoring requirement is from all other monitoring requirements, the more likely it will require a separate table. Use your own experience and understanding, as informed by alan's link above and/or carlson's explanations, to determine how to alter my guesswork.