UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
> Linking Tables By 2 Fields In Parent Table, Access 2016    
 
   
JLusk
post Nov 4 2019, 03:05 PM
Post#1



Posts: 5
Joined: 4-November 19



I have a Access database containing data that is collected at specific sites on multiple days. I created a table (Table 1) with information about the site on a specific date (SiteID, Date, StartTime, EndTime, Temp, CloudCover, WindVel) and a table (Table 2) that contains the records of observations made at each location (SiteID, Date, Species, Distance, ObsAngle). I had linked the two tables by SiteID. But now when I attempt to add new records for the same SiteID, but for different dates, the record in Table 2 gets overwritten by the new information. I tried adding a second link using Date, but the behavior didn't change. I thought maybe a Junction Table would work. Is that the right way to go? The Junction table would contain SiteID and Date, where each row would be a unique combination of SiteID and Date, but SiteID and Date wouldn't be unique within this third table. Any suggestions?
Go to the top of the page
 
 
Start new topic
Replies
tina t
post Nov 4 2019, 03:38 PM
Post#2



Posts: 6,691
Joined: 11-November 10
From: SoCal, USA


you need to clearly define your entities, or "subjects". each entity is a table. from what you posted, it seems clear that you need a table of sites, as

tblSites
SiteID (primary key)
SiteLocation (GPS reading? or description? or...?)
<other fields as needed to describe a site, but nothing else.>

again, from your post, you may need a child table describing the site conditions. something like

tblSiteConditions
SiteConID (primary key)
SiteID (foreign key from tblSites)
ConDate (don't use the word "date" by itself - it's an Access Reserved word)
StartTime
EndTime
Temp
CloudCover
WindVel

i'm not sure, because i don't really know the purpose of the data described above. but as child table, the above allows for multiple records describing the conditions at the site, on specific dates.

and again from your post, a child table describing observations at the site. such as

tblSite Observations
SiteObsID (pk)
SiteID (foreign key from tblSites)
ObsDate
Species
Distance
ObsAngle

here also i'm not sure, because i don't know if there is a real-world relationship between site conditions on a given date, and site observations that may be on the same date.

what you really need to do is close your database, and close Access, and read up/more on relational design principles. then define your entities, what data you need to know about each entity, and how these entities may relate to each other. then you'll be ready to design your tables and their relationships. and after that, once the tables/relationships are correct, then you'll be ready to think about forms for data entry.

process analysis has never been my favorite part of developing a database, but it has to be done, and done first, and done well, or you'll have nothing but trouble all through the rest of the development process. and since you know more about the process you want to support, than anyone you can find on the internet, you're in the best position to do it well, once you've learned how.

hth
tina

--------------------
"the wheel never stops turning"
Go to the top of the page
 
orange999
post Nov 4 2019, 03:52 PM
Post#3



Posts: 2,114
Joined: 10-February 08
From: Ottawa, Ont, Canada; West Palm Beach, FL


JLusk,

Further to tina's comments, can you step back and describe to readers in simple, plain English (no database terms) what it is that you are capturing and recording (attempting to automate). In effect, what is the "business" involved? Lead us through a day or few observations, so we can understand what you are dealing with. Clarity in the requirement is critical.

Update to show meaning of transect.
noun
noun: transect; plural noun: transects
/ˈtransekt/

a straight line or narrow section through an object or natural feature or across the earth's surface, along which observations are made or measurements taken.
This post has been edited by orange999: Nov 4 2019, 04:25 PM

--------------------
Good luck with your project!
Go to the top of the page
 
JLusk
post Nov 4 2019, 04:34 PM
Post#4



Posts: 5
Joined: 4-November 19



Orange999, yes, I had originally thought making the post as simple as possible would make it less confusing. Hopefully the detail I provided in my response to Tina will be sufficient to provide clarity.
Go to the top of the page
 
orange999
post Nov 4 2019, 04:40 PM
Post#5



Posts: 2,114
Joined: 10-February 08
From: Ottawa, Ont, Canada; West Palm Beach, FL


Based on your description I have drafted some "business facts" that may help with database design. I expect there will be several tables.

A Region has many Sites
A Site has many Transects
Each Transect has a Start point and a Stop point ( a Point is a Lat/Long reference)
Observations of butterflies/species and activity are made and recorded.
A recording includes species, distance from transect/line of site distance, angle of observation, date/timestamp, weather conditions,activity
There are 1 or many recordings for a particular site per day

I also think you record the biologist info in your records somewhere??
This post has been edited by orange999: Nov 4 2019, 04:55 PM

--------------------
Good luck with your project!
Go to the top of the page
 
JLusk
post Nov 4 2019, 05:18 PM
Post#6



Posts: 5
Joined: 4-November 19



Yes, observer is recorded with in the site condition table, since there is only one observer per site per observation day. We don't collect any other information about the observers. The only other change would be that on some site/days, no observations were made. So there could be zero to many recordings for a particular site per day.
Go to the top of the page
 
orange999
post Nov 4 2019, 08:10 PM
Post#7



Posts: 2,114
Joined: 10-February 08
From: Ottawa, Ont, Canada; West Palm Beach, FL


JLusk,

I created a draft model that may help with table design. You can adjust, or ignore as you see fit.

Attached File  ButterflyStudyDraftModel.PNG ( 42.37K )Number of downloads: 10

--------------------
Good luck with your project!
Go to the top of the page
 
JLusk
post Nov 5 2019, 10:42 AM
Post#8



Posts: 5
Joined: 4-November 19



Orange999, thank you so much for the assistance! I appreciate it very much. This was very helpful. It looks like my mistake was separating out the weather data/description from the observation data. I thought that because the weather description didn't vary across butterfly observations on a given transect-day, that it belonged in a separate table. Otherwise, my relationships look very much like what you provide (except that I don't have a region table, since we only currently have once region, but if the project expands to other regions, this would be important). Out of curiosity, if I did keep the weather data in a separate table and created a transect_dayID variable (unique to each transect and survey_date combination) in this child table and in the parent table (Observation, in your schematic) would that solve the issue? Thank you so much once more!
Go to the top of the page
 
mempie
post Nov 15 2019, 05:04 PM
Post#9



Posts: 277
Joined: 27-September 01



When the observer does a transect and records observations, is the weather recorded along with the observation? If so, the weather is part of the observation data, and goes in the same table. If not, where does the weather data come from? Does it really have start and end times, as it appears in your first post? If the weather data is from a separate source (an automated weather station with data in "time bins"?), it might belong in a separate table, in order to be fully normalized. However, to combine weather and observation tables, you would probably be joining on date/time fields, which can be awkward. You would likely be using non-equi-joins for that. Having the weather data be fields in the observation table would certainly be simpler.

If new data entry is overwriting an old record, that sounds like a problem with the design of the data entry form and maybe its recordsource. It might not be caused by bad table design.
Go to the top of the page
 

Posts in this topic



Custom Search


RSSSearch   Top   Lo-Fi    12th July 2020 - 06:05 PM