Updating multiple related tables via form/subform

[Sharkman1885]

Hey all,

I have a database composed of 5 tables (ID, Location, Biology, Other, Crocodile) as well as a join table called Lookup. The tables are self explanatory for the info in them. The crocodile table has each individual animal(as an auto number) and its sex. ID table includes markings tags ect. each crocodile can have a one to many relationship to each table and I have already made a select query and form that allows me to search the database, which works perfectly.

My issue is when it comes to updating. I am unsure how to go about making updates to the tables. For instance if an animal that has not been tagged is caught then I want to be able to add a whole new animal with the various measurements and notations. If a previous animal is handled I want to be able to add the new data to that animal. I have a form and subform setup for the various tables and I made update queries for each subform but have not run them yet as I am not confident with my coding. as an example this is my code for updating via the biological info subform:

INSERT INTO Biology
SELECT TL AS TL, SV AS SV, Weight AS Weight, TailGirth AS TailGirth, HeadLength AS HeadLength, Weight AS Weight
FROM [Biological Information];

each table has its own primary key and also the crocodile number and a date(not sure if date is needed)

I can provide my database if needed

Thanks guys

 

[Minty]

Can you post up a image of the tables and their relationships ?
Not convinced its correct from your description?

 

[plog]

Action queries (UPDATE, INSERT, DELETE, etc.) are usually hacks around poor design. I don't see why you should be using them.

Instead, you create a form system, have them be based on tables, and directly add/update using the form.

When you have a one-many relationship, that corresponds to a form (based on the one) and sub-form(based on the many). You related them via a child/parent relationship (https://support.office.com/en-us/ar...any-form-ddf3822f-8aba-49cb-831a-1e74d6f5f06b).

It your instance it sounds like the main form will be Crocodile and you will have a subform for every other table on it. I suggest using a tab control.

 

[jdraw]

Please provide the database AND the business rules or requirements that depict what this database is suppose to support.

Your SQL has piqued my interest---

TL AS TL, SV AS SV, Weight AS Weight, TailGirth AS TailGirth, HeadLength AS HeadLength, Weight AS Weight

Why all the aliases? Did you do this for some reason?
Why 2 of Weight AS Weight?

 

[Sharkman1885]

Minty I have attached a screenshot of the table relationships.

Plog, thanks I will look into that. When I was researching this I came across that link but wasnt sure if that was the method I should use or not.

jdraw the database is for tracking crocodiles. The SQL code for the biological info uses shorthand for TL (total length) SV (Snout to vent length). Let me purge some of the sensitive info and Ill upload some of it

 

[plog]

Let me purge some of the sensitive info

We would hate to violate a reptile's HIPPAA rights.

Looks like you have some work to do on your tables yet before you move on to forms. Actually, reports should be what you tackle next, before forms.

From what I can understand of your data, I see 2 issues:

1. Circular relationships. There should only be 1 way to travel between tables--you've created a loop with Lookup, Crocodile and OtherInfo. One of those relationships needs to go. I don't know which, but you shouldn't have a circular relationship like that.

2. Use Date/Time fields. Store dates in fields specially build for them. Don't store the Year in one field, month in another and Day in a third.

Further, why are some measurements (TL, SV, etc.) in ID table, while others are in the Biology table?

 

[Sharkman1885]

Here is the database. I removed all the location information as well as notes so when running the select query it will throw up the windows asking for the various data i removed but still runs correctly. Use the form CrocIDlookup to run the select query. I need to rearrange the forms for my update form based on plog's suggestion as well.

With the lookup table I can probably get rid of that without any issues, I think, as long it still pulls up the right info Ill have to try that.

As far as the date fields, I have the issue of incomplete historical data to deal with. I may only have a year in the field or just a month an year. Plus pulling the date out into its components helps when it comes to making reports based on fiscal years or specific months. At least that's what I have found

The fields in the ID table are identifiers PIT(pit tag similar to micro chipping a pet) S, DL, DR are scute pattern identifiers (the crocodiles' scutes are clipped in a pattern on the tail, this does not harm the animal) all of the info under biological information are all based on various measurements of the animals exterior characteristics.

Let me know if there are any other questions about the data.

Any other suggestions are welcome and thank you all so very much.

 

[Sharkman1885]

not sure if it posted or not. if not here it is again

 

[jdraw]

Is this part of the ongoing saga that was solved at
https://www.access-programmers.co.uk/forums/showthread.php?t=297322
or is this a new issue?

My first impression is design is less than optimal, but I don't know the requirements for this database.

I see no reason to break date into 3 fields month day and year, and have those stored as Text data type.
There is no description for any table so we are left to "imagine" what these represent.

Good luck with your project.

 

[Sharkman1885]

Same data, but because of it now being normalized I believe it is a different issue. The normalization process was rough since I inherited the database and had to go through and normalize it to the best of my ability. Plus I believe the previous post was for the select query whch was indeed solved and solution carried over to the normalized database

 

[Sharkman1885]

Database requirements for this project are:
1. Store data on crocodile location and biological information as well as attached reports
2. Search database given few variables to determine history of crocodile or area in question
3.Produce reports for management

I can add descriptions if needed for each of the table fields. I thought that the table titles were self explanatory but I may be wrong there. I have only been working with access for a few months, as evidence of my previous post on the forum so I do apologize about being naive with SOP when it comes to database design.

 

[jdraw]

sharkman,

Not a problem. You have inherited this and only been working with access for a few months-- we understand your situation -- most have been there.

You have some familiarity with the tables and fields, but readers do not. So any info you have that can define/describe the tables and fields will help you get more focused advice.
I like to see a description of what the database is intended to support- an overview and then some details. Some samples of expected output (reports, summary queries or forms..) are useful to show some of the uses of the data/database.
I think it would be a good exercise for your to describe each table and each field--- it will help you with design (tables/relationships/queries...) more than you think.

As plog indicated earlier, the table structure is confusing without some business rules to validate.

There are many fields that have no values in several records. How do you report on those "crocs"?
When you don't have a complete date and you have some reports by fiscal year --how do you determine where such data goes?
I would suggest where date data is unknown that you use some standard such as Jan 1/ whatever year or July 15 --but you know your situation better than we do.

 

[Sharkman1885]

I think I am a bit fuzzy as far as what is meant by "business rules."

A better description of the end goal of the database. I want to be able to lookup the history of any crocodile in the database by any of the identifying characteristics (this currently works as desired). For instance if I were to look up the crocodile with a Blue 22 cattle tag it will result in the historical information about this crocodile (see attachments 1a[form] 1b[query result]).

Prior to 2015 reports were done half-a**ed and records were kept in a rarely updated excel file with no real means of querying. Most, if not all records post 2015 utilize a standardized form that field biologists fill out with all the required information. Unfortunately we also work with non biologists who do not have access to the standardized form or only give partial information such as just a total length and a location. As long as the crocodile can be identified by either external means (cattle tag or scute clip pattern) or internal means (PIT tag) then information should be update-able with just the GPS or any other information that may be provided. If the external tag breaks off (hasnt happened but could) that should be able to be updated. New crocodiles should also be able to be entered.

Fiscal years are July 1 - June 30. Those crocs without a full date are prior to 2015 so those fiscal year reports have already been completed.

My predecessor and one other started trying to create an access database which stalled until I took over a few months back. The database was a huge mess.

 

[jdraw]

Here's a link to some business rule info.

That site has many data models and articles that are often referenced.

I'm going to attach this link to some info on database planning and design.

If you spend 45-60 minutes working through one or two of the tutorials from RogersAccessLibrary referenced in the info, you'll experience and understand business rules and relationships.

 

[Sharkman1885]

jdraw,

Thank you so much for your help and patience. Ill work through the tutorials and get back to you with the info that you requested. If not by the end of the business day today then tomorrow

 

[Pat Hartman]

Sharkman, you still have some distance to go to get to normalized.
1. Lookup doesn't make any sense and it creates a circular relationship. I would delete it.
2 I'm not sure why ID and Crocodile are separate tables. Can a croc have multiple ID's. If it can, then you need two tables. If it can't, these two should be merged.
3. I assume the Biology table contains data at a point in time. As has already been mentioned - do not store a date in parts. Store it as a DateTime field. Call it SightingDate or EncounterDate. FYI; Year, Month, and Day are function names and when you name your objects with reserved names such as these, you end up with potentially serious confusion. The minimum is the inconvenience of ALWAYS having to encapsulate the names in square brackets. I'm pretty lazy and I hate having to use the square brackets because they are unnecessary if you use proper naming conventions plus all the square brackets in a query string make it harder to read.
4. OtherInfo - the Handling reports should be either stored in the Biology table or linked to it rather than crocodile assuming they data is collected at the same time. Also, I wouldn't use the attachment data type. I would simply include a row with a full document path for each document you want to link to. This will also keep the database smaller.
5. Location should be just a lookup table of city and community locations. The other location data should be stored in Biology as well as the ID of the Location. But, if you are recording sightings or other encounters that don't result in the collection of biology then the relationship should be Crocodile --> SightingLocation --> Biology

And finally. I'm sure that your project is simply dealing with croc's but it is painful for people who work with properly normalized schemas to see a table name that includes data. A more appropriate name would be Animal with a type code field = Crocodile. I'm not saying that you have to change this but if you foresee any expansion of your charter to monitoring other animals, you should definitely consider it.

 

[Sharkman1885]

Hey Pat,
1. I will delete Lookup. i think i created it because I wasnt sure how all my PKs would interact and I thought I was going to have a many-to-many relationship.
2. I have crocodile and ID separate because a crocodile can theoretically have multiple ID characteristics. For example, a tag can break and therefore replaced with a different tag or a PIT tag could cease to function. Scute clip pattern can also change due to an injury sustained in the wild.
3.The biology table does in fact store data based on a date and time. the problem comes with historical data being incomplete with regards to the date. As far as encapsulating the names in brackets, I used to have all the names with spaces so every single thing was encapsulated. A few encapsulations dont bother me yet...
4.The Other info includes notes and original documentations pulled from various sources. These notes have things to do with both location and biology as well as any interactions with the public e.g. notes on by standards or if animal was being fed ect. As such I kept them separate as they are not directly tied to biology and not every entry will have attachments. With regards to using attachments, a link wont work as the attachments are stored in various locations and not all on the same network. As such the only way to easily access all files pertaining to a crocodile is via attachments. Even if this increases the size of the database.
5. Locations are tricky here. The only consistent thing is the GPS data and the community, for recent data. City is just the closest city and can sometimes be interchangeable if no community was recorded. Sighting information is also recorded, if so then no new biological information would be recorded

The program is exclusive to crocodiles and has no need or intention to expand into other species. The closest would be another monitoring agency copying the design. Though right now the database design is still a mess

 

[Pat Hartman]

Biology is a child of location:
Crocodile --> SightingLocation --> Biology

Biology should not contain CrocID, it should contain the foreign key of SightingLocation. You would always have a location if you are actually going to interact with the Croc. If you have old data where this is not consistant, create dummy SightingLocations.

Also - do NOT mush multiple attributes into a single field. Always store them separately. You will have a much easier time searching when dealing with separate fields. For display purposes, there is nothing wrong with concatenating them with slashes to separate.

 

[Sharkman1885]

I cant really make dummy locations unless I use locations that do not actually exist, as one way you can look up a crocodile is by its location. Example: you want to know how many crocodiles were at location x for as data has been taken. You can run the search query for that. I guess I could make the location "No location recorded"

If I remove the croc ID foreign key and have two or more crocs in the same place at the same time, then there is no way to separate that data out, at least to my understanding.
One location can have multiple crocs at the same time but a only one croc can be in one place at one time. Using that then one croc at one location at a given time can only have one set of measurements. Im guessing that is what you are referring to.

Thinking like a biologist that didnt click in my head as I was looking at biological information attached to the animal no matter where it was at a time

As far as the "mush" Im not sure what you are referring to. I thought I separated everything out

Thank you for your suggestions it is greatly appreciated and will be implemented.

[edit] rereading this I realized that as I was typing it my understanding of database design increased which is why it looks like im confused in the beginning. sorry about that

 

[Pat Hartman]

unless I use locations that do not actually exist

That's my definition of "dummy".

You are changing the relationship. Instead of
Crocodile --> SightingLocation
Crocodile --> Biology

You are making Biology a subset of SightingLocation so the Biology table points to its IMMEDIATE parent rather than its top parent. Making the hierarchical relationship will make it easier to create reports on what you did at what location.

Crocodile --> SightingLocation --> Biology

This is the mushing: