[X]My Assistant

[JLusk]

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?

[tina t]

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

[orange999]

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

[JLusk]

Tina t, thank you for your response. Yes, I'm a biologist, not a database scientist, unfortunately, but I have the most experience here with Access and time "was of the essence", so...(you may know how that goes ) Perhaps some background will be helpful. These data are being collected as part of a pollinator conservation effort. There are multiple sites across the region, each with multiple transects. Each site/transect combination has a different code. I do have a separate table with the stop and start points (Lat/Long) for each site/transect. Biologists walk these transects several times over the course of the summer looking for butterflies and recording species, distance from transect, and angle of observation. Transect conditions (site ID, survey_date [I pared down my variable names in my original post, sorry], start/end time, and weather conditions) don't vary within date. As such, I created the Table 1 described in my previous post, hoping that this would speed up data entry (the data common to each observation record for that site/date would need only be entered once). As biologists traverse the transect and observe a butterfly they make an observation record for it consisting of the species of butterfly, its' activity, and the line of site distance and angle from the transect. Each observation record includes the date. There can be several such observations for a particular site/date combination. So basically if I were to record all the data in one table it might looks something like this:

Site Y, Date X, StartTime, EndTime, Temp, Wind, Cloud, Species Detected #1, Activity, Distance, Angle
Site Y, Date Y, StartTime, EndTime, Temp, Wind, Cloud, Species Detected #1, Activity, Distance, Angle,
Site Y, Date Y, StartTime, EndTime, Temp, Wind, Cloud, Species Detected #2, Activity, Distance, Angle
Site Z, Date X, StartTime, EndTime, Temp, Wind, Cloud, Species Detected #1, Activity, Distance, Angle
Site Z, Date Y, StartTime, EndTime, Temp, Wind, Cloud, Species Detected #1, Activity, Distance, Angle

Hopefully this is clear. The issue I am having is when Date X transitions to Date Y for the same site; Access overwrites the Date X species detected #1... with the data for Date Y.

If I am understanding what you wrote below correctly, I seem to have made the wrong table the parent table. I should use the Table with Lat/Long coordinates as the parent table and have both the site conditions and observation records tables as child tables. Is that what you are suggesting?

[JLusk]

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.

[orange999]

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

[JLusk]

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.

[orange999]

JLusk,

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

 ButterflyStudyDraftModel.PNG ( 42.37K )Number of downloads: 9

[JLusk]

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!

[orange999]

JLusk,

I wasn't sure how to handle weather. I foresaw groupings that could be separate fields in the observation table:

clear vs cloudy
rain vs sun
wind speed
wind direction
temperature

You could add thee fields to the observation table. Then test wwith some ample data.
If inputting via a form then user would select clear or cloudy for field value; then rain vs sun for field value;
wind speed, wind direction and temperature would be individual fields with values.

This set up would seem to assist analysis since each field is valued directly in each observation.

Again, you know the environment (pardon the pun) better than readers, so try the designs with test data and decide based on your review.

[mempie]

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.