Database relationships

[radex7]

Hello,
My name is Radek. I am learning Access myself from books/internet and by trial and error. I think I have been doing fine so far, but I am getting to the point where actual expertise would help me a lot. My work in forestry research provided me with plenty of data to play with, and I took it upon myself to ditch excel spreadsheets and create a database. I had a few functional versions already running, but in the end I always think it could have been better. And that is why I am here, hoping that if I present you with my efforts this community would provide me some ideas to improve and perfect this db.

Let me describe you the project first so you understand what those things mean: We experiment with forest treatments as a way to manage and deal with forest disease. We have several areas that we experiment on, each area is divided into units dedicated to one forest treatment (such as cutting, burning, herbicide treatment). Each unit contains a network of permanent plots that we visit on annual basis and record the same sets of data. This allows as to track changes in forest structure as well as effectiveness of our treatments. The set of tables I created to keep the data kinda follows that order: unchanging variables:
Area>Unit>Plot>Trees (Species, Location, Designations, etc.)
and variables that are recorded on annual basis:
Surveys>TreeData (Tree health, size, treatment change, etc.), Sapling>Seedlings>Sprouts




So, lets start with basics. Relationships. Take a look at my most recent take on table arrangement and relationships, and tell me if you see any issues with that arrangement.

Attachment 1117

New data is entered through set of forms, and hot-linked excel spreadsheet for TreeData. It spits out nice reports and data collection sheets for future sampling. I can also run queries to run rudimentary analysis and data modification prior statistical analysis. Since I build it, I know how to deal with little quirks and limitations, but I am trying to make it foolproof and intuitive for anyone else to use, thus expanding automation is a necessity.

[TheShabz]

One thing I would do is combine your Seedling Sapling and Sprout tables into one table. It all contains the same info (shouldnt Subplot be connected to all 3?). I would just add a field to indicate which type it is and use query criteria to choose which one. Outside of that, I really cant tell you much more without being familiar with what the data really is.

[Access_Blaster]

Is it me or does his relationships look like a Spiro Graph?

[TheShabz]

lol you should see the ERD they handed me my first day on the job.

[radex7]

Is it me or does his relationships look like a Spiro Graph?

This looks kinda artsy so I will take it as a compliment. TheShabz, I cannot combine seedling counts with anything else because for each plot there is 4 smaller seedling sub-plots. Saplings and sprouts, on the other hand do come from the same subplot. Actually let me attach plot design picture, that should help with picturing the process.

Attachment 1160

This is how we do it. Yellow circle represents 1/10th of an acre plot within which we collect all of the tree info. Red circle is 1/20th of an acre subplot within which we collect sapling and sprout data. 4 green circles in cardinal directions are 1/100th of an acre each, and they are used for collecting seedling data.

Current Issue:

I am trying to spiffy up my data entry forms and I think I would find a good use for resizable forms. What I have in mind is a portion of a form containing 3 or 4 for text fields, combo boxes etc. for crucial information for a quick entry, however, upon button or link press I would like to be able to expand this form to enter more detail. I was thinking about sub-forms but all of the info collected here will end up in one table, and I see no point of creating additional table for details only. I came across something called MoveSize (ActiveX???) that seem to have have the functionality to perform what I need it for, but I need to read up a lot on it. Any input?

[TheShabz]

I dont know much about resizing forms, so I'll leave that to someone else. However, as far as the sapling, sprout, and seedling tables, they can still be merged. leave the subplot field in and only enter a value for it if it is a seedling record. Also, I'm curious as to why you are using a compound PK for those tables. Any reason you havn't made SeedlingID as a PK and used the current PKs as individual FKs? Going further, combining the 3 tables as I mentioned earlier. I still think it can be done, even though in a practical real-life sense, it doesnt fit right.

[radex7]

There are many ways to skin a cat my friends. I went with a very snappy split form and disappearing subforms. But that is not why I am here.

I am inputting some of my data through Excel (because of nature of the data it is just easier this way). I managed to automate my spreadsheet in an efficient way, but there is one more thing I would like to add to my VBA code in a macro that establishes connection with Access and transfers the data:

Set cn = New ADODB.Connection
    cn.Open "Provider=Microsoft.ACE.OLEDB.12.0; " & _
        "Data Source = PathToMyDatabase;"

And that works like a charm, however, this file will travel from a computer to a computer, and it would be a hassle for me to explain how to re-write this macro to other users. Instead, I would like to take the

PathToMyDatabase

outside to the spreadsheet and dedicate a cell that would contain address of the database. Something like my other constants use:

Range ("A10")

I just cannot figure out how.

Any ideas?
Thanks

[TheShabz]

store the contents of the cell as a variable. refer to the variable.

[radex7]

store the contents of the cell as a variable. refer to the variable.

You are absolutely correct, but it took me a while to sort it out. Sadly I am not much of a coder, I am better at googling...

Original code I was working with:

Dim cn As ADODB.Connection, rs As ADODB.Recordset, R As Long
    ' connect to the Access database
    Set cn = New ADODB.Connection
    cn.Open "Provider=Microsoft.ACE.OLEDB.12.0; " & _
        "Data Source=PathToMyDatabase;"

Here is what I've done to solve this issue:

Dim cn As ADODB.Connection, rs As ADODB.Recordset, R As Long, dbPath As String
dbPath = "Data Source=" & Range("A10")

In a snippet above you will see a new variable dbPath I added. The path alone is contained in a cell A10 of my spreadsheet, however cn.Open statement calls for a phrase "Data Source=" to be present in front of the path, and that is why I made it a part of my dbPath variable.

 Set cn = New ADODB.Connection
    cn.Open "Provider=Microsoft.ACE.OLEDB.12.0; " & _
dbPath & ";"

Here I am just calling my variable and closing the statement. And that is it!!!

You guys are probably smirking right now thinking what's the big deal, but I tell you to me it was an accomplishment, and I did pat myself on the shoulder .

[xinxin]

I like your article very much. With your rich knowledge, we can learn more from your wonderful post.This is a really useful post. I didn’t need the information really, but it definitely helps to know. Learn something new every day… that’s what I always say!

[radex7]

I like your article very much. With your rich knowledge, we can learn more from your wonderful post.This is a really useful post. I didn’t need the information really, but it definitely helps to know. Learn something new every day… that’s what I always say!

Thanks!
And now I am back with a new puzzle!
I want User to be able to specify how many records he/she is going to enter, lets say in a text box. After clicking a command button I want a subform to preload numbered rows with values from 1 to number specified in a text box.

Here is an example to visualize the problem:
I need to enter 35 stem diameters, and that is it. Since my stem number is a part of a Composite Key (I need it) I need to enter each record separately, like:
Stem#: 1, Diameter: 2.5, next row
Stem#: 2, Diameter: 1.3, next row
Stem#: 3, Diameter: 3.4, next row...
all the way to 35th stem.

That's bit mundane, and it would be easier and not to mention quicker to just type in diameters, if the stem numbers were already there.

Because of other info and nature of this data I cannot define my stem number as auto increment.
I am open to suggestions?