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
 

Posts in this topic



Custom Search


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