Results 1 to 6 of 6
  1. #1
    RachelO is offline Novice
    Windows 10 Office 365
    Join Date
    Jul 2020
    Posts
    2

    Setting up a database and creating relationships

    Hi I am new to Access and learning as I go.

    I want to create a database for vegetation monitoring data. At the moment I have the following tables:

    Sites (a list of the monitoring sites)
    This table has a site ID field only

    Species (the species and density recorded at each site over multiple dates).
    This table has site ID, species, density and date fields. Because I am monitoring the same sites over multiple dates this is in a long list format (i.e. the site ID and data are repeated for each species and date entry)

    Environment (environmental conditions recorded at each site over multiple dates)
    This table has site ID, date, dust and condition fields. Similar to the Species table, the site ID is repeated for every date of monitoring.

    I don't know how to make relationships between them. I thought that the Site ID would be the primary key - that is possible for the Sites table but not the Species of Environment table because the Site ID is duplicated in those tables (for each monitoring date).

    Thanks in advance!
    Do I have my data set up incorrectly or is there another way to join the 3 tables?



    The purpose of putting the data in Access is so i can create reports that filter and combine the data from the 3 tables. For example, I want to be able to make a report that shows the species data and environment data for one site on one particular date.

  2. #2
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Is data for "Species" and data for "Environment" both recorded each time a "Site" is monitored
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  3. #3
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    I think you need a 4th table of the intersection of all.
    ID (autoNum) , species,env,site,date,etc.

    of the plants you are monitoring. The other tables will feed this one.

  4. #4
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Since you are new to Access and learning as you go, I recommend that you work through 1 or 2 tutorials from RogersAccessLibrary. You will learn and experience a process for designing tables and relationships to meet your requirements. And that process can be used with any database. But you have to work through the tutorials to gain the experience. Each tutorial will take about 45 minutes to 1 hour - and you will learn.

    Here are links to the tutorials:
    Zyx Labs
    Class Info System
    Catering Business

    There is more info at the Database Planning and Design link in my signature.
    Good luck and enjoy the tutorials.

  5. #5
    RachelO is offline Novice
    Windows 10 Office 365
    Join Date
    Jul 2020
    Posts
    2
    Not necessarily - I may record Species data and Environment data on different dates and at different sites

  6. #6
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Welcome to the forum.


    So I agree with @orange - work through at least 2 of the tutorials. It will help you a lot (BTDT)

    Also, you should draw out your database table design using pencil & paper, a whiteboard, cardboard & crayon, magic marker on your walls (NOT really - can't erase - have to repaint )

    From what you posted, yo could start with:

    For relationships

    One site can have many Vegetation observations and one Vegetation observation will be for one site. This is a 1 to many relationship.

    One site can have many Environmental observations and one Environmental observation will be for one site. This is also a 1 to many relationship.


    tblSites
    --------------
    SiteID_PK - Autonumber
    SiteName - Text


    tblSpecies
    ---------------
    SpecieID_PK
    SiteID_FK - Number - Long Integer (Links to SiteID_PK)
    Species - Text (?) (*** see below)
    Density - Text (?)
    ObservDate - Date/Time (date of observation)


    tblEnvironment
    ---------------------
    EnvironmentID_PK - Autonumber
    SiteID_FK - Number - Long Integer (Links to SiteID_PK)
    Dust - Text (?)
    Condition - Text (?)
    ObservDate - Date/Time (date of observation)



    For the fields Species, Density, dust and condition, are there a list of options to choose from? If yes, then you could add 4 more table to select the options you want for each observation.

    Note: "Date" is a reserved word in Access and shouldn't be used for object names.




    Good luck with your project.....

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

Similar Threads

  1. Replies: 9
    Last Post: 11-22-2017, 09:13 AM
  2. Confused about setting up tables and relationships
    By Vetgeorge in forum Database Design
    Replies: 4
    Last Post: 08-26-2017, 11:55 AM
  3. Replies: 2
    Last Post: 03-05-2015, 04:26 PM
  4. Setting correct relationships
    By rfalv32 in forum Access
    Replies: 1
    Last Post: 10-22-2011, 07:59 PM
  5. Setting Up Complex Relationships
    By seanbhola in forum Access
    Replies: 3
    Last Post: 05-15-2011, 11:47 PM

Tags for this Thread

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