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

[Sharkman1885]

Ohhh the mushing is actually done via the query. Each value is stored separate and displayed in the format that is user friendly once the query is run. The data in the table is stored in separate fields and as text data as the pattern is separated by "/" So field S "5/10/12" field DL "2/6" and field DDR "7" when selected by the query it is displayed as what you circled above.

Ok so I should replace the CrocID foreign key with the SightingLocation one under Biology? Is that what you mean?

 

[Pat Hartman]

Ok so I should replace the CrocID foreign key with the SightingLocation one under Biology? Is that what you mean?

Yes, the FK should always be to the next highest table rather than to the top table in a hierarchy. that of course assumes that I am correct in my assessment of the data. Remember, I don't know your business. I am looking at it with years of specific database design experience and lots of general life knowledge (that's what happens when you get old).

Mushing in the query is fine for display in a report but I think you must have another recent thread where you were having trouble with searching the mushed values and people were offering "patterns" for searching. On a form, the values should be separated.

 

[Sharkman1885]

I did have a previous thread back in December where I was having difficulty getting the select query to display what I wanted. That issue has been corrected and data split into various tables (it was all under one table previously). Im doing my best to normalize it. I will post business rules (reading about that now) probably tomorrow.

The form does have the values separated. I had to separate them in the original database due to poor design by who i inherited this from.

I really do appreciate everyone's help on this forum.

 

[Pat Hartman]

Anything to help the croc's

 

[Mark_]

If you know the size of a croc, you should be able to judge about when the report was turned in. Likewise if you still have access to the excel file you can judge (based on data in previous/subsequent row) about when a sighting was made.

Hopefully this can be used to fill in the complete dates so you can use a normal Date field rather than the Year/Month/Day structure you are currently using. More important, without a year, month and day are mostly useless, especially for trend analysis.

 

[Sharkman1885]

Mark,
Unfortunately I donot have access to the original excel sheet and it is difficult to use size as a means of estimating date as sightings lack measurement data as the animal is not "worked-up"

 

[Sharkman1885]

Business Rules:

A. Scope
A.1 Crocodile information and tracking

B. Things of interest
B1. Crocodiles
B2. Biological measurements
B3. Location
B4. Reports
B5. Notes
B6. SNAP reference number
B7. Sex
B8. Alive
B9. Date/Time
B10. Identification

C. These things are related as
C1. A Crocodile can be associated with many locations at different Date/Times
C2. A LOCATION can be associated with many crocodiles at many Date/times
C3. A crocodile can have only one set of BIOLOGICAL measurements at one Location at one Date/Time
C4. Biological measurements are associated with only one crocodile at one time at one location (measurements may not change over time)
C5. A crocodile only has one set of identifiers (may change over time)
C6. Identifiers are only associated with one crocodile
C7. A crocodile is either alive or not
C8. A crocodile is either male or female or unknown sex
C9. A crocodile can be associated with one SNAP# at any Date/Time
C10. A SNAP# may be associated with many crocodiles
C11. Notes associated with only one crocodile at Date/time
C12. A crocodile may have many notes


I hope this helps and Im sure I may be forgetting something but this should be most of the big stuff. Let me know if there are any questions. I shall get to work implementing the suggestions already made and hopefully I can get these update forms to work properly by the end of all this.

Thanks all

 

[jdraw]

Thanks for the info.
A couple of things for clarity/confirmation.

What is SNAP#?
Seems like alive or not is associated with a croc "sighting" at Location and Date
Is there any requirement to identify "who" made the sighting info or Note?

measurements may not change over time

but don't crocs grow (new born/adolescent/adult sort of thing?)

And as Pat said in an earlier post --we don't know your business. So a brief description of "a day in the life of croc monitoring" would
be helpful to you and readers. It will tie your business rules into a context. You can include some of the reports and how often these are needed.

Good stuff.

 

[Sharkman1885]

Ok a description of the business:
I am the crocodile response coordinator for FWC (state wildlife agency) When the public sees what they deem as a nuisance crocodile they call the SNAP (Statewide Nuisance Alligator Program) hotline. After that a SNAP# is created and location information and information about the "complainant" taken. Then if needed a Crocodile Response Agent (CRA) is sent to the scene to investigate and sometimes relocate the animal. During relocation measurements are taken and, if not previously handled by FWC, tagged and clipped for future identification.

As far as measurements not changing over time, if the crocodile is an adult the growth rate is much slower. If an animal is measured twice, one month apart, there will be little to no change in the measurements. Two weeks apart there would not be any change at all. Two years apart and you will definitely see a change.

Alive or not I thought it made sense to associate that with the crocodile itself. I guess it would make more sense to associate that with a location though. there I go again thinking like a biologist and not a data manager.

The note or sighting is recorded in a different system completely separate from this database (SNAP) and is referenced by the SNAP#

[edit] The notes are copied into Access and handling reports are submitted by CRAs after handling a croc

 

[Sharkman1885]

Oh and as far as reports. The main official one generated is every fiscal year. The database will mainly be used for querying crocodiles based on limited information. Tag color, partial SCP (Scute Clip Pattern) seen, ect.. This information helps CRAs in the field and is used in management decisions

 

[Pat Hartman]

Thanks for the attempt at business rules. I would put SNAP in the sightingLocation table and also the Alive flag if you might be interested in where the animal died. There could be some pollution or poaching that could be identified.

I might consider adding a new column to the croc table called DiedLocation and have it be the FK to the sightingLocation where the animal was found dead. This isn't technically necessary but it might simplify validation and reporting because you won't have to search through the sighting locations to identify live or dead animals. It will also easily raise a flag if someone reports a sighting for an animal previously recorded as dead. I would try without it first but if you have trouble with reports or validation, i mention it as a possibility. I would use the FK rather than a flag though since you get much more meaning from the FK than you can from a plain flag.

 

[Sharkman1885]

Pat,

Thanks for the suggestions!. I have just finished moving the Alive flag to the location table. As far as SNAP# that should be easy to move and shouldnt be a problem to do. However, I have changed the relationship to where biology and other info are linked to the Location as opposed to crocodile. Would this rectify the SNAP# location?

 

[jdraw]

What exactly is the table named "ID"? Is there a more meaningful name?

 

[Sharkman1885]

ID is a table full of Identifying characteristics. Those include the SCP (Scute clip pattern) ,the PIT tag (internal tag), and the cattle tag (external tag) info. I had it called Identification before but figured ID would be easier as long as I renamed any PKs , which I think I forgot to do with the "Otherinfo" table

 

[plog]

Year/Day/Month still 3 fields--should be one.

CrocID should not be in OtherInfo table nor Biology table

Location seems like a poor name for what data it stores. Seems that table is actually capturing the data of an Encounter. Location is part of the encounter and may deserve its own table. Further, seems Biology data is actually part of the Encounter, as might be OtherInfo.

 

[Sharkman1885]

everything is part of the encounter. That is basically what the database is. A database of crocodile encounters.

Location was separate but many other tables, as was explained to me, were based on the actual location of the encounter hence the new relationships. As far as the date fields It will take some time to correct that and make sure the query will still work right. rest assured though I will get it done. When I finally have this thing properly normalized I can get started on the entry forms or adding data.

 

[jdraw]

ID is a table full of Identifying characteristics.

Any specific record in that table refers to 1 and only 1 croc, right?
If so, then the relationship would not be 1 to Many. It would be 1 to 1, and could be collapsed into the croc table. (I'm just being devil's advocate here to make sure we're all on the same page).

Table and field names should be meaningful--- especially if you want to pass this off to someone else to maintain.

Seems that Sighting (or Encounter) better describes what you are calling Location. (eg when and where did you sight/encounter the croc)

 

[Sharkman1885]

Currently you are correct in that each specific record in the ID table refers to only one crocodile. However, it is theoretically possible that one of the identifiers could change. A tag could break off and need to be replaced, injury in the wild could change the SCP or the PIT tag could cease to function.

I wanted to go ahead and deal with that possibility now rather than have to deal with it in the future

 

[Pat Hartman]

Would this rectify the SNAP# location?

No I suggested the sightinglocation because you said the SNAP number was assigned by another group when a sighting was reported. I agree that Location should be renamed to encompass the concept of a sighting. A table named location would be expected to contain an address or a city state or something that identified whee something happened but not what happened there. Your table combines the two concepts. If it turns out that "locations" repeat, you could create a location table to make sure that the data entry is more consistent and avoid spelling errors. You would then have a FK to tblLocation from your sighting table.

I understand why you want an ID table but why is the data in it there and not in Croc or sighting or biology? It doesn't seem that the data linked to ID would necessarily change if ID changed so that would introduce unnecessary duplication.

Please keep posting your schema as you make progress.

 

[plog]

As far as the date fields It will take some time to correct that and make sure the query will still work right.

You're trying to change a flat tire while still driving? You need to open a blank database and set up your tables going forward properly. Then when that structure is correct you rebuild your queries on top of it, then your reports, then forms to interact with the data.

Then, lastly, you move the existing data to the new system and start using it.