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    
post Nov 4 2019, 03:05 PM

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
tina t
post Nov 4 2019, 03:38 PM

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

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

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

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)

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.


"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