Updating multiple related tables via form/subform (1 Viewer)

Sharkman1885

Registered User.
Local time
Yesterday, 20:44
Joined
Dec 5, 2017
Messages
85
Oh and Pat, I tried your suggestion but it messed up the queries. It was mixing data from tables.
 

plog

Banishment Pending
Local time
Yesterday, 19:44
Joined
May 11, 2011
Messages
11,613
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

Registered User.
Local time
Yesterday, 20:44
Joined
Dec 5, 2017
Messages
85
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

Banishment Pending
Local time
Yesterday, 19:44
Joined
May 11, 2011
Messages
11,613
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

Registered User.
Local time
Yesterday, 20:44
Joined
Dec 5, 2017
Messages
85
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

Banishment Pending
Local time
Yesterday, 19:44
Joined
May 11, 2011
Messages
11,613
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

Super Moderator
Staff member
Local time
Yesterday, 20:44
Joined
Jan 23, 2006
Messages
15,364
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.
 

plog

Banishment Pending
Local time
Yesterday, 19:44
Joined
May 11, 2011
Messages
11,613
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

Registered User.
Local time
Yesterday, 20:44
Joined
Dec 5, 2017
Messages
85
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
 

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 20:44
Joined
Feb 19, 2002
Messages
42,981
Sharkman,
You are getting conflicting advice and the thread is too long to digest. I suggest closing this one and moving on. When you start a new one, just add a link to this one in case people want to refer to it. But I think your initial problem is solved.

I've taken the last database and modified it to MY liking which will conflict with what others have suggested. I cleaned up the tables by removing the unnecessary instances of CrocID. I populated the IDKey so that the join works correctly between IDKey and Encounter.

I fixed the query to remove the fields that got removed from the tables. I also cleaned it up a little bit and removed some of the "LIKE" operands. You can only use LIKE when the data type is text. You cannot use LIKE on numbers or dates. You will get strange results.

Rather than using like at all except possibly on Pit, I would make combos and select only full values.

I took a look at the data and see gaps. This is going to be a problem for your search form. If you are looking for females, do you want to bring back females AND crocs with null in the Sex code? You will have to change the query to add an "or fldA IS Null" inside each of the compound conditions. So for this example,

...
And (Crocodile.Sex = Forms!CrocID!Sex Or Forms!CrocID!Sex Is Null OR Crocodile.Sex Is Null)
...

Try to clean up the data if you can before moving on. It helps with design if you have firm rules such as Sex is required. You might want to allow M, F, U (for unknown) but the point is a croc has a biological sex (unlike humans in this day of political correctness. True hermaphrodites do exist but are quite rare so you could include a fourth category if that makes sense) and if you examine the croc, you should be able to determine it.
 

Attachments

  • Croc Database 2_Pat.zip
    58.7 KB · Views: 96

Sharkman1885

Registered User.
Local time
Yesterday, 20:44
Joined
Dec 5, 2017
Messages
85
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.
 

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 20:44
Joined
Feb 19, 2002
Messages
42,981
You're welcome.

That's why there is almost always a third option for Sex - unknown. The question remains, What happens with the unknown values when you are searching? Leaving the field null has an advantage since the query will always look for Is Null. if you want to include unknown values. If you assign an unknown value, the query would have to reference that. In most of my applications, I prefer to leave unknown values as Null so only required fields always have a value. It simply depends on what you want when you search and what others will understand when they search.

I tried your suggestion but it messed up the queries. It was mixing data from tables.
I'm not sure what this is referring to. Please check again now that I have fixed the join.
 

Sharkman1885

Registered User.
Local time
Yesterday, 20:44
Joined
Dec 5, 2017
Messages
85
Your changes work perfectly! Unknown sex will stay null value so the query will always include unknown sex. The mixing of the data was most likely from a bad join or relationship that I had. Thank you again for the normalization.

Ill have to go back and fix my original database and make sure everything works (it should) then Ill start working on how to update. I will post a separate thread when I am ready to do that. Thank you again!
 

Users who are viewing this thread

Top Bottom