Results 1 to 3 of 3
  1. #1
    Menglish is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Jun 2013
    Posts
    1

    Using Tables and Forms

    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!

  2. #2
    alansidman's Avatar
    alansidman is offline Indifferent
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,536
    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

  3. #3
    Join Date
    May 2013
    Location
    Dallas TX
    Posts
    1,742

    Rough Guess

    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.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. How To Tables and Forms
    By Jonah74 in forum Access
    Replies: 3
    Last Post: 12-17-2012, 12:43 PM
  2. Forms and Tables
    By Tiffy in forum Database Design
    Replies: 3
    Last Post: 02-10-2012, 05:27 PM
  3. Replies: 10
    Last Post: 07-12-2011, 11:09 PM
  4. forms and tables
    By Roberta in forum Access
    Replies: 2
    Last Post: 09-02-2010, 02:48 AM
  5. Forms and Tables
    By Nixx1401 in forum Access
    Replies: 7
    Last Post: 03-02-2010, 11:51 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums