Updating multiple related tables via form/subform

[jdraw]

It is much clearer now. You are building a separate database in Access that deals with captured crocodiles. Part of your gathered information can be related to data stored in the SNAP database.

?? What if you had an Access odbc link to the SNAP database??

Any way, you have given your issue considerable thought and are progressing, so good luck.

 

[Sharkman1885]

An odbc would be awesome to have. But there would be way to much red tape to get through because of the SNAP data being on a state server and those that are higher up will not let me touch anything in the way of linking. Unless there was a way to make an actual url link to the data. That is for another time, once I get what I have squared away. So unfortunately I am stuck with the SNAP ref # for now.

I appreciate all your help!

 

[Sharkman1885]

Pat,
I will most likely keep the biology and sightings separate. As far as the narrow scope, the only crocodilian we keep track of, as far as tag and release, are crocodiles. Caimans are invasive in Florida, and Alligators are regulated via hunting, trapping and farming records. I would be happy to go over how we get those records if needed, but for now Ill just say that the measurements for alligators and caiman are gotten via a culled animal. Crocodiles are a threatened species and alligators are classified as "threatened in appearance" meaning they look like the threatened crocodile. Because of those similarities, hunting and harvesting of alligators is tightly regulated. The program used to keep track of these harvested animals is called LEX and does not contain anywhere near the amount of information that we keep for crocodiles.

As a side note, any suggestions on making a form to enter data?

 

[plog]

As a side note, any suggestions on making a form to enter data?

Yes, do it last.

Set up your tables properly, make sure you can create the queries and reports using them to get the data you want out of your database. Then, after those 2 things are set up, work on your forms.

So many people seem to think Access is Excel Plus Forms and develop with forms first in mind. They make a ton of structural mistakes and then try to hack forms and queries together to overcome those structural deficiencies.

 

[Sharkman1885]

Quick update. I have rectified the date issue by creating a single date field in Location, still need to change that table name. Changed the coding of my query to reflect the change and everything still works.

Checking the Joins in the query, which is where I may have some issues but hopefully I can figure it out

 

[Sharkman1885]

Ok question I am trying to make sure my tables are correct. The title of location has not been changed yet. Under Biology and Otherinfo it is not letting me remove CrocID as it is one of the indexes. I have attached to relationships and also included my INNER JOIN code

FROM (((Crocodile INNER JOIN ID ON Crocodile.CrocID = ID.CrocID) INNER JOIN Location ON Crocodile.CrocID = Location.CrocID) INNER JOIN Biology ON Biology.LocationID = Location.LocationID) INNER JOIN OtherInfo ON Location.CrocID = OtherInfo.LocationID

The code works, I am just making sure it is the correct way to do the Join and will work when it comes to updating data

 

[Sharkman1885]

Pat,
I checked the relationships. When I clicked on "All Relationships" nothing unexpected showed up. I also created a relationship report and nothing unexpected showed up either.

I am not sure if I need to change the relationship as the sightings are of the crocodile itself, the ID methodology of he sighting, though it can change, is still a sighting of the crocodile. I wont be at my desk long today but will try making more reports and think more on the relationship you mentioned. Thanks!

[EDIT] In the office today. Looking at your suggestion I think that makes sense. When the croc is sighted it is identified via the tags or SCP. Because of this the observer does not know the crocodile's ID only the identifiers the croc may or may not have attached to it

 

[Sharkman1885]

Ok while looking at this relationship further, I dont have an identifier FK for location. I can go in manually for each croc and add one if you believe it is necessary and it may be, the more I look at it the more I am beginning to think that adding that FK is the way to go.

 

[Sharkman1885]

Sorry for the delay all. I got a bit busy at work. I should have everything working correctly, and normalized.

 

[Sharkman1885]

Oh and Pat, I tried your suggestion but it messed up the queries. It was mixing data from tables.

 

[plog]

CrocIds still in too many tables.

And I just don't understand how an Encounter record can have more than Biology record. Seems that's relationship is 1-1 which means all that data goes together in the same table.

OtherInfo may fit there as well.

Lastly I think I see two Handling fields (of course Handeling my have to do with music composition, in which case, disregard).

 

[Sharkman1885]

Plog,
There is not biological information taken at each encounter as some encounters are just sightings. A single location can also have multiple crocodiles. Without the CrocID FK in biology and other info the query returns the wrong data and 10 times the returns it should. With the CrocID FK everything works.

 

[plog]

If an encounter can entail more than one crocodile, then you haven't set up your tables correctly. You need one more.

First let's define some things, and seperate the idea of a Location from an Encounter. A lot of the issue (for everyone involved in this thread) is not realizing that those are 2 distinct things:

Crocodile - 1 reptilian animal
Location - A specific place at a specific time
Encounter - A crocodile at a location (using definitions from above)

You have many crocodiles which can be at many locations. You have many locations which can have many crocodiles. To sort out which crocodile is at which location on a specific day you need a new table to do so. Thus the Encounter table.

Here's the overview of how I see these 3 tables:

Crocodile
CrocID, sex, other data related to the crocodile throughout its existance

Location
LocationID, LocationDateTime, GPS, City, state, zip, all other data related to that specific place and time

Encounter
LocationID, CrocID, Alive, Height, Weight, all other data related to that crocodile at that location at that time

That sorts out what croc was seen where and when. You can't do it in just 2 tables if a location can have multiple crocs.

 

[Sharkman1885]

So are you saying to combine the biological data with the encounter data and put the location data (GPS ect) in a separate table?

Im not sure I understand. In your example above you included biological measurements in the encounter table. Each encounter happens at one location but not every encounter includes biological measurements. Many encounters are just sightings and would not have any biological information taken.

 

[plog]

I would put encounter and biology data in the same table. However, its not wrong to put the biology data in its own table.

The relationship between Encounter and Biology is really a 1 -> 0/1 relationship. At most there is 1 record per Encounter, it's possible there are 0 records per encounter. I would put the biology data in with the encounter data.

 

[jdraw]

I agree with plog with 1 exception.
I would not include DateTime with Location..
DateTime seems to related to Encounter.
That is-
I Encountered this CrocID at this LocationID at this DateTime X.
You may or may not include biological info at each and every Encounter.
The latest biological info would be available from the most recent Encounter of this Croc where biological info was recorded.

 

[Sharkman1885]

Ok thanks guys. Ill work on the tables a bit more.

 

[plog]

Not only do I think jdraw makes a very good point by agreeing with me. He makes one when he disagrees with me.

Date/time goes in the Location table if you infrequently use the exact same place. It depends on how granular you are with the term 'place'. You are storing GPS coordinates, so I assumed that a place was so specific it wouldn't be used multiple times.

If, a place is used frequently then the date/time would go in the Encounter table. To be fair, it wouldn't be wrong to put it in Encounters anyway.

 

[Sharkman1885]

We do try and get accurate GPS locations but sometimes we use an individuals address to get an approximate GPS. Most times a crocodiles location is referenced by the neighborhood that they are in. Because of that I think I will place the date/time in the encounter table.

You guys are life savers thank you so much. I am learning a ton

 

[Sharkman1885]

Thanks Pat and will do. As far as the sex goes they are sometimes unknown, if the croc was hit by a car and beyond recognition or if the biologist didnt have the tool required to sex the animal (cloacal probe) it would be unknown.