Newbie confused about relating tables and unique IDs

[DNRtech]

I am Using Access 2007. I have tables with environmental data separated by data type (i.e. turbidity). I want to relate those tables to a table containing sample site names and date times. I have a unique ID as a primary key linking the date time table to the tables containing the environmental data.

My question is basic but are not the tables supposed to be linked by a common field? So that the record in one table has a field where the value is the same as the value in a field in another table with the same record? Isn't this what the relationship is based on? Where the tables are "joined" by this field so to speak. I'm a bit confused about this.

I have a few more questions, I just wanted to clarify this part first.

Thanks a lot for the help!

 

[dhookom]

Typically you have a primary key (unique value) in a main table with a foreign key (same value) stored in a field in the related table.

I typically use an Autonumber primary key in a main table with a long integer field in related table(s).

If you provide some information about the information you want to store, I expect you could get some better recommendations.

Duane
Hook'D on Access
MS Access MVP

 

[hoofit]

Environmental data in your case could be broken down into site names and times using a PK / FK to tie the 2 together. For example, a single site may have many turbity levels at different times, thus a one to many relationship. Just need more info.....

hoof

 

[DNRtech]

Great, I'm glad I got the key business correct. I've been working in databases for the last couple of months and I can't seem to get past not seeing the data in tabular format. Up until now I have been using excel to store data and separating the data from its time and location is a bit scary at the moment.

I'm currently designing a new database and would like some feedback on its structure.

Here are the details:

We collect water quality data, specifically turbidity, total phos, total nitrate/nitrogen, secchi depth, fecal coliform, and probe data from a YSI instrument (pH, DO, temp, conductivity, ect.). Each sample activity (date, time, location) is associated with all to none of these analytes depending on various scenarios.

I have broken the data up in to several tables. One for the data we get back from a local contracted lab, one for each analyte we collect in the field (turbidity, secchi), and one for all the data we collect from the YSI probe.

I have also created a table where just the sample dates are stored and one where all the sample location names are stored. These are then related to another table where each sample instance is stored (called Site_Time). All of the water quality tables mentioned above are related to this table.

I can post a relationship report if anyone would like.

Before I came on to this project data was import to access from appending sheets out of excel workbooks where data was entered. This often cause "subscript out of range" errors or on schema problems. Data in the database was edited in the table views often. I understand that forms are the proper way to edit and enter data and thats where I'm going with this project next. The form I have built seems to work ok, but still is a finicky, so I thought I would make sure my database structure is sound first.

Thanks for the feedback, it means a lot!

 

[dhookom]

I created a similar application for various inspections conducted in a food manufacturing plant. We had some tests that were sent out to another facility for analysis and some that were evaluated locally.

The solution I created was very normalized. IMO it would be a mistake to have fields for "pH, DO, temp, conductivity". I stored every result from every test in a single field in a single record. I had a sample instance similar to your table (I think) that identified where and when a sample was taken. The primary key was samSamID. I stored all of my results in a table with a structure like:
[tt]
tblSampleResults
========================
sarSaRID autonumber primary key
sarSamID relates to tblSamples.samSamID
sarTstID relates to a table of tests "pH, DO, ..."
sarValue actual result of test
[/tt]
This solution allows me to add any number of tests or samples without ever changing table structures. I could probably change the data in my tblTests to start recording "pH, DO, ... " rather than my current tests.

Duane
Hook'D on Access
MS Access MVP

 

[DNRtech]

Besides the probe data (pH, DO, temp, conductivity) does this sound about how it should be set up?

So, how did referential integrity play out in your database? Did you use it and when would you do so?

Also, did you set up a form for your database? Excuse my ignorance here, but which table was the form based on?

 

[dhookom]

You should provide your table structures so someone can make suggestions.

I recommend setting up referential integrity.

I use a main form to select/edit the sample record. The main form has a continuous view subform for entering the individual test results related to the sample record on the main form.

Duane
Hook'D on Access
MS Access MVP

 

[DNRtech]

Thanks everyone for the help. I don't have a way to post my database and I can't explain table structure better than I already have.

Thanks again.

 

[TheAceMan1]

How are ya DNRtech . . .

For your reference:

Fundamentals of Relational Database Design

Description of database normalization basics in Access 2000

BTW ... for sharing your DB you can use 4Shared ... its free!

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[dhookom]

I can't explain table structure better than I already have

I took the time to post a sample table structure 12 Jan 11 9:29. I find it a little hard to understand that I can do this for your benefit but you can't.

You might want to look at faq700-6905.

Duane
Hook'D on Access
MS Access MVP

 

[DNRtech]

Thanks for your patience. I've been working with the VB script on the page with out success. I will post as soon as I get it to work.

Thanks again for your attention, and I'll post my tables and relationships as soon as I can.

 

[DNRtech]

ok here it is, no luck with VB script though. I hope I didn't miss anything, and sorry I didn't get this to everyone sooner. Thanks again for your help, it means a lot. Let me know if I need to clarify anything, and sorry about the messy field names!

TABLES

SITES
=====
Site_NM text PriKey relates to SITE_TIME.site one-to-many

SAMP_DATE
=====
sample_date Date/Time PriKey relates to SITE_TIME.sample_date one-to-many

SITE_TIME
=====
site text ForKey relates to SITES.site_NM many-to-one
time text
sample_date Date/Time ForKey relates to SAMP_DATE.sample_date many-to-one
st_ID AutoNumber PriKey realates to EDGE_DATA.data_ID; TURBIDITY.turb_ID; SECCHI_DATA.secchi.ID one-to-one

EDGE_DATA
=====
Total_NitrateNitrite decimal
Total_Phos decimal
FecalColiform decimal
Chlorophyll decimal
data_ID AutoNumber ForKey relates to SITE_TIME.stID one-to-one

TURBIDITY
=====
turb_ID AutoNumber ForKey relates to SITE_TIME.stID one-to-one
turbidity decimal

SECCHI_DATA
=====
secchi_ID AutoNumber ForKey relates to SITE_TIME.stID one-to-one
secchi_depth decimal
total_depth decimal

 

[dhookom]

IMO, I would change the table structure to my earlier suggestion. Read through the links from theAceMan to see why.

Create a table of tests
[tt][blue]
tblTests
=============
tstTstID autonumber primary key
tstTitle text values like Secchi Depth, Total Depth, Chlorophyll, etc
tstStatus numeric with 0 for inactive or -1 for active

tblTestData
=================
tedTeDID autonumber primary key
tedST_ID links to SITE_TIME.st_ID
tedTstID links to tblTests.tstTstID
tedResults decimal stores the test results
[/blue][/tt]
You can add as many tests as you ever will need.



Duane
Hook'D on Access
MS Access MVP

 

[DNRtech]

Thanks Ace for the resources, the fundamentals article is one of the best resources on normalization I have seen yet.

Yes, I see the advantage of organizing the data as you mentioned, Duane. Keeping all the results in their own table makes a lot of since, that way you don't have any null values when a site is missing a result for an analyte (another problem I keep running into).

I experiment with organizing my data tables this way. Thanks again for all the help and your time.

 

[DNRtech]

I think my new structure is much more normalized now with one exception that I can tell. It also appears more simple. Here are my tables and their relationships:

tblSITE
==========
site text prikey relates to tblSITE_DATES.ID_site

tblDATES
==========
sampledate Date/Time prikey relates to tblSITE_DATES.ID_date

tblSITE_DATES (here's where I think a problem is)
==========
ID_sitedate AutoNumber prikey relates to tblRESULTS.ID_sitedate
ID_site LongInt forkey relates to tblSITES.ID_site
ID_date LongInt forkey relates to tblDATES.ID_date

tblRESULTS
==========
ID_sitedate LongInt prikey relates to tblSITE_DATES.ID_sitedate
ID_test LongInt forkey relates to tblTEST.ID_test
result decimal


tbl_TEST
==========
ID_test AutoNumber prikey relates to tblRESULTS.ID_test


I think the problem with the tblSITE_DATES is that it doesn't follow 2NF, second normal form. The primary key, a autonumber ID field, represents the site and date ID's and is thus not an independent value, am I right? If so, is this table necessary? Would this be a time to use a composite key?

I hope I'm not asking too much and I thank you all again for your time.

 

[dhookom]

I don't know why you have tblDATES. There should be a test name field in tbl_TEST.

Duane
Hook'D on Access
MS Access MVP