Hello,
I am trying to build a database for animals in many lakes. The goal for the database is to be a base for creating overviewmaps where you can see which species exist or have existed in different lakes with specified properties.

I imagine the structure of the database in this way:
Table 1: Lakes with names and basic data about lakes in different fields. LakeID is primary key.
Table 2: Physical and chemical properties in different fields. FysChemID is primary key and LakeID is foreign key.
Table 3: Different species names in one field. SpeciesID is primary key.
Table 4: Different methods for catching the animals in one field. Methods ID is primary key.
Table 5: Year for catching the animals in one field. YearID is primary key.
Table 6: Name of the person who catched the animals in one field. PersonID is primary key.
Table 7: Different fields about the existence of animals, for example "have you ever noticed the animal in the lake", "Which year was the last you noticed the animal", "never noticed".
Table 8: A many to many table where all the primary keys above is foreign keys. Primary key is ManyID. (I have even tried to use composite primary key instead of foreign keys).

I have tried to make a composite form for reading data and to write data into the tables. I have tried with and without subforms, but I have got problems.

My questions are:
How to design the database with good relations and keys?
How to build good forms for reading and writing?

I don´t have long experiences of making databases, but I am very interested in learning more.

Bjorn

Hello,
I am trying to build a database for animals in many lakes. The goal for the database is to be a base for creating overviewmaps where you can see which species exist or have existed in different lakes with specified properties.

I imagine the structure of the database in this way:
Table 1: Lakes with names and basic data about lakes in different fields. LakeID is primary key.Agreed
Table 2: Physical and chemical properties in different fields. FysChemID is primary key and LakeID is foreign key.I think you need a table of chemical properties, then a way of assigning those properties to lakes - either a junction table containing lakeID and ChemID, or if the chemical properties are such that each lake can only ever have one of them, just a ChemID as a foreign key field in the lakes table.
Table 3: Different species names in one field. SpeciesID is primary key.
Table 4: Different methods for catching the animals in one field. Methods ID is primary key.Agreed
Table 5: Year for catching the animals in one field. YearID is primary key.A whole table just to store years? Wouldn't this just be better as a date field in some kind of catch/report incidents table?
Table 6: Name of the person who catchedthe animals in one field. PersonID is primary key.Agreed
Table 7: Different fields about the existence of animals, for example "have you ever noticed the animal in the lake", "Which year was the last you noticed the animal", "never noticed".This bit seems a bit raggedly-defined. if a person sights the same species in the same lake on two different occasions, will you record this at two incidents?
Table 8: A many to many table where all the primary keys above is foreign keys. Primary key is ManyID. (I have even tried to use composite primary key instead of foreign keys).If I understand you correctly, this table is the one that records individual sighting incidents? A junction table containing foreign keys sounds ideal (just do the lookups in your form, not in the table design.

Thank you for your engaged answer!
The chemical information could be connected with different dates-
The table with years should be exchnaged to a datafield as you said.
About the table with existence. I agree with you that I have not had a good idea about how to put in that information. Your question "if a person sights the same species in the same lake on two different occasions, will you record this at two incidents?" My answer is Yes.
My real cumbersome problem has been to design the form to read and write information from and to the tables. I have tried with and without subforms, but I´ve got different kind of problems like the form don´t find the information in the tables or the information is not writed into the tables.. Do you have some ideas about designing the form?


Quote:

Thank you for your engaged answer!
The chemical information could be connected with different dates-
The table with years should be exchnaged to a datafield as you said.
About the table with existence. I agree with you that I have not had a good idea about how to put in that information. Your question "if a person sights the same species in the same lake on two different occasions, will you record this at two incidents?" My answer is Yes.
My real cumbersome problem has been to design the form to read and write information from and to the tables. I have tried with and without subforms, but I´ve got different kind of problems like the form don´t find the information in the tables or the information is not writed into the tables.. Do you have some ideas about designing the form?


Quote:

I think probably your best bet would be to use the form wizard to begin with, then study the results it produces - forms based on most of your tables should be quite straightforward - just let the form wizard create them as datasheet forms.

The form for the main incidents junction table might be a bit more tricky, but still, you could start with the output of the form wizard, then convert some of the text boxes to combo boxes and get them to look up the values associated with all the foreign keys.

How you present the overall application is much a matter of choice - my personal preference for an application such as yours would be to create a big tab control and insert each of the datasheet forms as a subform into various tabs, so the first tab might be for maintaining the reports junction table, with other tabs being dedicated to maintaiing the lakes table, the people table, etc.

Thanks Atomic Shrimp! Your name suits very well for this topic!
I can send you the database stucture perhabs in an email? How to go on with the forms? I´ve tried many forms, but they will not function very well. Is it a good way to use composite primary keys?

I've never had cause to use composite primary keys myself - autonumbers have always been sufficient for my purposes.

I'm not sure how much time I can devote to actual hands-on work with your db, but if you post it here on the forum (as an attachment), I and possibly others will be able to take a look at it.

I´m very lucky for any help in this topic, but it could be easier to see a solution when the database basicstructure is visual.

This might help

Thank you very much David for giving me your advices. It looks very interesting and I´m trying to explore it so I can understand how you have built it. One good thing was the design with subforms in tableform. One interesting property was the postselector in the subforms which accompanied the navigation button when you changed it. How did you make that?

Nothing special, I just used the forms wizard to create the subforms from the queries

One interesting property was the postselector in the subforms which accompanied the navigation button when you changed it. How did you make that?

Are you talking about the combo box selectors?

Thank you. I´ve got it. I have earlier just used the columnar form in my subforms. I didn´t see the way the postselector moved. I had the "problem" that I didn´t get an overview of my inputs which, for example, made it easy to make the mistake to write the same information in two records or overwrite information because I didn´t change record before input. Your advice with the tabular subform is much better!

Now I have another designproblem. I want to count the total amount of species found in a specific lake and note if a specific fishspecies has ever been found in a lake. I want to put that information in a table with all the lakes. Can I do that in some way without making a special input for that information? Can I use the information in the existing tables?

With a small change, yes it is pretty simple to do. You should NOT store count totals in a table, these can always be calculated as required from existing data. If you include a 'sighted' (numeric field) in tblSightings, you can record how many of that species was sighted.

Later, against a date range for a specific lake, you can do a count of species sighted report

Looks something like this

Thank you David! You have really given me useful advices. I´m going on with changes in my database. I´m coming back to show my results.

Glad to help

Hello David I have some more questions,
In your database-proposal I wonder about one construction. When you related the tables tblChemical and tblSighting you used the fields LL and SL to relate to the table tblLakes. I don´t understand what happens when you use that relation instead of using the primary keys as foreign keys in tblLakes?

You showed me how to count species. If I´ve understood it right. The Species Button can be used to count how many individuals you have caught which belong to a specific species.

How can I count the number of species? For example if there is pike, perch, salmon and brown trout in a lake. I would like to make the database to count and give me the result that it exists 4 species in that lake. It could be interesting to know that if some species has disappeared from a lake.

I send you pm to continue this if you wish

Hello David I have some more questions,
In your database-proposal I wonder about one construction. When you related the tables tblChemical and tblSighting you used the fields LL and SL to relate to the table tblLakes. I don´t understand what happens when you use that relation instead of using the primary keys as foreign keys in tblLakes?

You showed me how to count species. If I´ve understood it right. The Species Button can be used to count how many individuals you have caught which belong to a specific species.

How can I count the number of species? For example if there is pike, perch, salmon and brown trout in a lake. I would like to make the database to count and give me the result that it exists 4 species in that lake. It could be interesting to know that if some species has disappeared from a lake.

You ask many questions - I have used LL and Sl as foreign keys to primary key in tblLakes (this is the primary table)

How I sighted species should make no difference - caught, viewed makes no difference - if sighted field <1 then will show up in count

If I did not observe species, does that mean it has dissappeared? NO, it just means I did not observe it - what is criteria to determine species has vanished???

The question about animals vanishing from a lake. As you said there is a problem to make a distinction between no observation and examined but not found. Perhabs I have to make a special table with a Yes/No field and a field with the date when you missed the animal for the first time. Perhabs I could write 0 in a field which means examined but not found and no writing when no examination is done.

Do you have any actual data to work with?

Yes I have lots of data, but I hesitate to make some inputs of that data until I can trust the database. It think it is easier to try and make experiments and see effects in the database if I use a limited amount of data. Just as you did in the database-proposal you presented for me. And I´m not afraid of destroying and loosing the data.

Now I´ve got some problems with one box in a mainform and one in a subform which should read names from a table/query, but they will not.
But I´m still trying to solve it. Of course I have examined your proposal.