Updating multiple related tables via form/subform (2 Viewers)

[Sharkman1885]

Pat,
I think I understand. Because of the "handling" "magnets" and alive fields there is more than just location information in that table so the table name should reflect that. Locations can in fact repeat though GPS data prevents the location overlap.
As far as the SNAP# it is generated externally and then when data is entered into the database it is included for easy reference in the SNAP database (internal database not controlled by myself) The SNAP reference number is unique for each location as it refers to the original person who called in about the crocodile.

With regards to the ID, if I put the info under crocodile then I could have a duplicate croc ID if any identifiers change. I could potentially add it to sighting info or even use a sighting FK similar to how biology and Other info is set up. If that will work. Though if that works, then I will have duplicate FKs in the table. I have 376 entries and only 151 individual crocs and thusfar only 151 identifiers. Am I thinking about this wrong?

plog,
I was afraid of losing data in the transfer and I wanted to test things while I changed them to make sure the query was working properly as everything was originally in one huge table which was imported from excel. I dont mind taking the extra time to make sure the data is right. Might not be the correct way to do things but I havent had any major issues with it yet.

 

[jdraw]

Sharkman,

You mention crocs, but don't you handle the odd rogue 'gator?

 

[Pat Hartman]

Sometimes it is difficult to identify which entity controls an attribute. For example, do you assign a new ID if any of the data in that table changes? If the answer is no, then the data does not belong there. It sounds like it can change over time so perhaps the sightings table would be the best place. Think of it like the tests made by your doctor. He weighs you, takes your temperature, takes your blood pressure and records the results even if they are the same as they were the last time you visited. If you only care what they are NOW, then they go into the Croc table. If you want historical data, then they go into the sightings table. Isn't database design fun?

 

[Sharkman1885]

jdraw,
This database is for crocodiles only. Alligators are monitored via a completely different set of databases, and regulations.

Pat,
The measurements do in fact change over time. Should the biology table actually go in the sightings table? or should the biology table just have a FK from the sightings table? Same question for the "Otherinfo" table that includes notes ect. If I were to put all this data in one table would the table then become a bit larger than what is ideal?

 

[isladogs]

This database is for crocodiles only. Alligators are monitored via a completely different set of databases, and regulations.

In that case the SNAP acronym, whilst it is 'snappy', is misnamed:

SNAP (Statewide Nuisance Alligator Program)

 

[Sharkman1885]

ridders,
LOL yes the name is a misnomer, however it was originally designed for alligators exclusively. Once the agency noticed that crocodiles were also being called in as nuisances the agency just piggybacked the crocodile calls into the same call center. Also the acronym SNAP sounds better than SNACP anyways The SNAP database is for all our nuisance crocodilians (alligators, crocodiles, and caimans) just alligators and caimans are managed differently

 

[jdraw]

This database is for crocodiles only. Alligators are monitored via a completely different set of databases, and regulations

.

The SNAP database is for all our nuisance crocodilians (alligators, crocodiles, and caimans) just alligators and caimans are managed differently

Hmmmm. You might want to review/google information silo.

As suggested throughout, it is important to have a defined requirement. This will put a scope/focus on your project. And getting business rules and processes within that scope described and vetted is critical to database design.

Good luck with your project.

 

[Sharkman1885]

jdraw,
The only thing this (the one I am working on) database does is hold information on crocodiles and allow the user to query the information to find a specific crocodile and allow a user to input information on new crocodiles or new sightings of a crocodile. All other information in the SNAP database is in a separate program, not access, and housed at the main headquarters or wherever their servers are. I am only using the SNAP ref# to allow for a query of the snap database if the user so chooses. This will also allow the user to see how many SNAP reports are made for a given crocodile.

I am unsure where the confusion lies in the scope and business rules.

 

[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!

 

[Pat Hartman]

The measurements do in fact change over time. Should the biology table actually go in the sightings table? or should the biology table just have a FK from the sightings table? Same question for the "Otherinfo" table that includes notes ect. If I were to put all this data in one table would the table then become a bit larger than what is ideal?

I would keep the sightings separate from the biology since a sighting doesn't always result in capturing the animal to take readings. You would end up with too much duplicate data or empty fields. Two tables is cleaner and more closely models the actual process.

As you can see we are all concerned that you have narrowed the scope of this database too much. To us, the great unwashed, alligators and caimans are similar enough to crocodiles that they belong in the same tracking system. I'm pretty sure their territories don't normally overlap but it seems like the characteristics would be similar enough. Being able to offer the app for free or for sale to the other groups could make you a hero.

 

[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

 

[Pat Hartman]

We're very close.
1. In the relationships window, click the took that shows all relationships. Make sure that nothing unexpected showed up. If Name Auto Correct some of us call it Name Auto Corrupt) is on, it can do unexpected things so you might want to turn it off. Looking at the Relationships window, do you see anything that shouldn't be there? When deleting things from the window, you need to delete the relationships first and then delete tables. Simply deleting the table without deleting the relationship HIDES the relationship rather than removing it.

Since Croc to ID is 1-many, how does that affect the sightings and everything else? Should the relationshps go:
Croc --> ID > sightings --> biology ?

That would mean you need to change sightings to use ID as the FK to ID rather than CrocID to croc.

It's really hard to not jump right into forms We're almost there. Making the reports first will validate the model. If you can create the report, then the schema is correct and you can create the forms. Forms tend to have validation code to ensure the data is correct so you don't want to get so invested in a particular form that you are reluctant to change it when it is wrong because the schema is wrong. When you get more experienced, you will be able to make the forms sooner because you make fewer schema design errors and you also come to understand that code is the trivial part of the process.

 

[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.

 

[Pat Hartman]

If what the people have at the sighting is the ID rather than the CrocID, then the relationship should be changed to point to the ID table instead of the Croc table. Keep in mind that when you build queries, you can link all this stuff back together again. We spend a lot of time talking about normalizing the data so you can eliminate duplication and data anomalies but we don't talk about how to now take that normalized data and turn it back into useful information. That is done with queries. When you are happy with what you have, please post the db again.

 

[Sharkman1885]

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