having trouble filling unique ids with forms

[DNRTech]

I've changed the structure of my db so that there are not one to one relationships anymore, my question still exists about autofilling unique IDs, but I'm not sure how to phrase my question under the new structure. Sorry for being "all over the place".

I'll try to explain this the best I can, sorry ahead of time for the ambiguity.



I have environmental data broken up into several tables. To avoid null values, I have created three tables of data with one-to-one relationships. The reason for this is some of our sample sites have certain analytes that others do not. If all the data were to be in one table, those analytes would be null in records concerned with other sites.

So, for the analytes that only apply to certain sites, I need to have unique IDs generated that match the IDs for the sample days and locations found in the sample day/location table. I would like the table to "take" the unique ID form the sample day/ location table only when data is entered for that site in the form. The sample day/ location table unique id is generated using AutoNumber.

To summerize: I'm looking for a way to leave some records on a form blank but when the record is filled in a unique ID is generated for that record that matches the unique Ids of the other records on that form.

Thanks for the help! Please, let me know if I need to clarify anything.

[jzwp11]

Welcome to the forum!

Do the analytes remain constant for each site (at least for the most part)?

If so, you could pre-define those analytes and then append them to the sample(s) when they are received.

...those analytes would be null in records concerned with other sites.

I'm not sure I quite understand the above statement. Are you saying that you have a field for each analyte? If so, then your tables are probably not structured correctly. Each analyte should be a record in a table not a series of fields in a table. Since a sample can have many analytes, that is a one-to-many relationship. Also, an analyte may be requested on many samples which is another one-to-many relationship. Two one-to-many relationships between the same two tables requires a junction table.

Here is a basic table structure of what I mean

A table to hold the sample info

tblSamples
-pkSampleID primary key, autonumber
-lngSampleNumber
-dteSample (sample date/time)

A table to hold all possible analytes
tblAnalytes
-pkAnalyteID primary key autonumber
-txtAnalyteName


A table that joins the specific analytes of interest to each sample (the junction table)

tblSampleAnalytes
-pkSampleAnalyteID primary key, autonumber
-fkSampleID foreign key to tblSamples
-fkAnalyteID foreign key to tblAnalytes
-spResult

[DNRTech]

Yes, you were correct in thinking I had a table for each analyte. It was pointed out to me though that keeping all of my results in one table and have each result record be associated with a test type would be the proper way to organize the data. I have reorganized it this way which seems to take care of the null value issue.

We measure the same analytes at the different sites, with a few exceptions:

1. One analyte is only taken at certain sites, so it's missing entirely from many of the sites.

2. Occasionally samples are lost or data is deemed invalid for quality control reasons.

Relating the result table to an analyte table and to a site/date/time table is how it is set up at the moment, but I haven't tested it yet.

I have attached my db to this post. I think it is normalized well, except for the table I used to join the sample sites and the date. Would this be where I would want to use a compound key?

I'm still confused about generating ID's from form entries, but now that I have a more normalized db, I think I need to research more on forms before I try to ask anymore questions about them.

Thanks for your input!

[jzwp11]

I'm not following your structure. I don't see your samples. Do you only collect 1 sample at a site?

[DNRTech]

The sample results are stored in the results table. Each result record is related to a test type (pH, DO, TP, ect) with the ID_test in tblTEST to the ID_test in the tblRESULTS in a one-to-many relationship.

We collect many samples at many sites. The test results for those samples are stored in tblRESULTS. Each record in tblRESULTS is also related to a site and and a date (I'm seeing now I need to add a sample time in tblSITE_DATES too).

I'll reply more later. Thanks for your help.

[jzwp11]

We collect many samples at many sites.

This describes a one-to-many relationship between the sites and the samples, but really you probably have multiple sampling events (i.e. quarterly, semi-annually, monthly etc.) at each site and each event has many samples. Additionally, a sample can have many tests (analytes) i.e. a one-to-many relationship. You may conduct the same test (i.e. pH) on other samples, so another one-to-many relationship. When you have 2 one-to-many relationships between the same two tables (in opposite directions) you need a junction table.

tblSites
-pkSiteID primary key, autonumber
-txtSiteName

tblSamplingEvents
-pkSamplingEventID primary key, autonumber
-fkSiteID foreign key to tblSites
-dteSample (sample event date--will apply to all related samples)

tblSamples
-pkSampleID primary key, autonumber
-fkSamplingEventID foreign key to tblSamplingEvents
-txtSampleName
-tmeSample (time sample was collected)

tblAnalytes (or tests whichever you want to call it; this table will hold the parameters for all of the tests you need to conduct)
-pkAnalyteID primary key, autonumber
-txtAnalyteName
-fkMethodID foreign key to a table that holds the analytical method. I assume that some methods can cover many analytes such as EPA624, SW846-8260)

Now the junction table to relate the tests pertinent to each sample. If a test can yield only 1 result, you would include that result here. Also, you will need to capture the units of measure for the analyte. I would set up a table that holds all of them and just use a foreign key.

tblSampleAnalytes (the junction table I mentioned above)
-pkSampleAnalyteID primary key, autonumber
-fkSampleID foreign key to tblSamples
-fkAnalyteID foreign key to tblAnalytes
-Result
-fkUnitOfMeasureID foreign key to a table that holds all possible units of measure



Now all of that handles the structure needed to capture the results, but early on you mentioned the following:

So, for the analytes that only apply to certain sites, I need to have unique IDs generated that match the IDs for the sample days and locations found in the sample day/location table. I would like the table to "take" the unique ID form the sample day/ location table only when data is entered for that site in the form. The sample day/ location table unique id is generated using AutoNumber.

What I understand from the above, is that for each sample at a particular site, it would be nice not to have to add the same analytes over and over again for each sampling event in preparation to receive the results for those event/sample/analytes. So, you could set up a series of other tables that basically hold the template of test/analytes required for each site/event/sample. Then when you actually collect a sample, all you would have to do is to click a button to add the pre-defined tests to that sample. If you want to do something along those lines it can be done, but I'll wait to hear back from you.

[DNRTech]

Thanks again for your clear and thoughtful assistance.

Before addressing the data entry issues, I have a few questions about the table structure you have outlined.

What is the "txtSampleName" field for? What kind of data do you see this as and how is it different than just site/time/date? Also, I see that the tblSamplingEvents has a sample date for each SiteID. Given that there is several sites per date might I relate tblSites (the site name table) to tblSamples directly, like so:

tblSites
-pkSiteID primary key, autonumber
-txtSiteName

tblSamplingEvents
-pkSamplingEventID primary key, autonumber
-dteSample (sample event date--will apply to all related samples)

tblSamples
-fkSiteID foreign key to tblSites
-pkSampleID primary key, autonumber
-fkSamplingEventID foreign key to tblSamplingEvents
-txtSampleName
-tmeSample (time sample was collected)

If this is not a good idea, I would like to know why for my own edification.

As for the unique ID inquiry, this came from me trying to build a form that populated data into the database correctly. My predecessor had me entering UniqueIDs by hand into several tables to set relationships. This caused many problems as you might imagine.

What I want is a form, that when I select an analyte for a result record, that UniqueID associated with the analyte (in our case pkAnalyteID) is automatically populated into the fkAnalyteID field, without me having to open the table and place that value into the table so that the relationship is set.

Thanks again, this has been great feedback.

[jzwp11]

As for the unique ID inquiry, this came from me trying to build a form that populated data into the database correctly. My predecessor had me entering UniqueIDs by hand into several tables to set relationships. This caused many problems as you might imagine.

What I want is a form, that when I select an analyte for a result record, that UniqueID associated with the analyte (in our case pkAnalyteID) is automatically populated into the fkAnalyteID field, without me having to open the table and place that value into the table so that the relationship is set.

I understand your ultimate goal of having a form for data entry, but it is essential that the table structure is set up correctly. If you have the tables and relationships set up correctly, the unique ID's will be handled by Access automatically and you never have to mess with them!

In fact for any relational database whether it is done in Access, Oracle or SQLServer, the table structure is critical.



"txtSampleName" is just a text field (txt stands for text) to hold the name of the sample. For example, I used to work at an environmental lab and we would receive a group of samples from a site. Each sample had its own name such as Well 2, Well 1 etc. So for a site, there were many wells to sample. Furthermore, samples had to be taken from each well on multiple occassions usually quarterly (so many sampling events).

I have been giving suggestions, you will have to adjust them for how you do your work. So in your terminology, is a site a location/area or a specific point at which a sample is taken such that the site name= sample name? I interpreted a site as a location and at that location there are many things to be sampled. So if my interpretation was wrong, please let me know.

Not knowing the specifics of how you do your work, the following table structure may work, but I think it may need to be tweaked in light of your statement: Given that there is several sites per date might I relate tblSites (the site name table)

tblSites
-pkSiteID primary key, autonumber
-txtSiteName

tblSamplingEvents
-pkSamplingEventID primary key, autonumber
-dteSample (sample event date--will apply to all related samples)

tblSamples
-fkSiteID foreign key to tblSites
-pkSampleID primary key, autonumber
-fkSamplingEventID foreign key to tblSamplingEvents
-txtSampleName
-tmeSample (time sample was collected)

Since a sample event involves multiple sites:

tblSamplingEvents
-pkSamplingEventID primary key, autonumber
-dteSample (sample event date--will apply to all related samples)

tblSites
-pkSiteID primary key, autonumber
-txtSiteName

tblSampleEventSites
-pkSampleEventSiteID primary key, autonumber
-fkSampleEventID foreign key to tblSampleEvents
-fkSiteID foreign key to tblSites

tblSamples
-pkSampleID primary key, autonumber
-fkSamplingEventSitesID foreign key to tblSampleEventSites
-txtSampleName (a sample name; if not needed just remove)
-tmeSample (time sample was collected)

[DNRTech]

So in your terminology, is a site a location/area or a specific point  at which a sample is taken such that the site name= sample name?

Yes, exactly. I see now what you mean for a sample name field. In your terms, for this db there is only one site, a lake and its watershed. Within that location there are several sample names.

Because there will always be only one site, the lake and its watershed, I might remove the SampleName Field and place the sample time in tblSampleEventsSites. I think this will be best because for any given dteSample there will not be a duplicate txtSiteName, and for each pkSampleEventSiteID there will be only one tmeSample. This should reduce the database you proposed by one table. The db I'm thinking of would look like: (sorry if this is getting confusing, I can barely follow it myself!)

tblSamplingEvents
-pkSamplingEventID relates to tblSampleEventSites
-dteSample

tblSites
-pkSiteID relates to tblSampleEventSites
-txtSiteName

tblSampleEventSite
-pkSampleEventSiteID relates to tblSampleAnalyte
-fkSampleEventID
-fkSiteID
-tmeSample

tblSampleAnalyte
-pkSampleAnalyteID
-fkSampleEventSiteID
-fkAnalyteID
-Result

tblAnalyte
-pkAnalyteID relates to tblAnalyte
-txtAnalteName
-ftMethodID

I see that tblSampleAnalyte,our join table, facilitates a many to many relationship with each unique entry containing one result, this is cool. Does the removal of tblSample (and moving the tmeSample) make good sense given light to there not being a difference between sites and samples for this project?

[jzwp11]

Does the removal of tblSample (and moving the tmeSample) make good sense given light to there not being a difference between sites and samples for this project?

Now that I understand your definition of site, then yes, the changes you have made look appropriate.

Now, the other thing you mentioned was a shortcut so that you did not have to enter every analyte for each site at every sampling event. You can actually do this in 1 of 2 ways. The first way is to enter them once and then use a query to select and then append those analytes to their respective sites under the next sampling event. This technique would work as long as your list of analytes/site remain constant AND if your users do not accidently delete an analyte at any time for any of the sites that you select from. The other alternative is to set up a few additional tables that pre-define the analytes for each site. Again, your list would have to stay constant-relatively. Then when you have a new sampling event just select from these tables and append to the records into you existing structure. Your users would not see these other tables and thus could not change them.

You can of course, add additional analytes that are non-routine if you want to. You would do that directly in the structure you have created.

[DNRTech]

Many of our sites are seasonal and due to the occasional sample loss from holding time violations, lost in transit, or lab errors, I couldn't make the assumption that the analytes will be consistent for each site. What I mean is, although we are trying to collect the same analytes for each site, we are not always successful and the occasional sample gets lost by some means. Also, the seasonality of the sites makes consistency difficult. Summers are dry and half of the sites get dropped for flow reasons.

What I envision for a data entry process is one that mirrors the Data Reports that come from the contracted lab. Because these reports have to be hand keyed, it would be awesome if all the user had to do was fill out a form that looked identical to the lab report.

These reports list sites in order of sample times with the analytes below in a generally consistent order.

In this case, the user would just fill in the blanks of the form using drop down menus that lookup from the site and analyte tables, enter the values for the results, without null values being placed anywhere in the database. Null value would interfere with calculations like means, correct?

ID values for foreign keys should be filled automatically, right? Is there a process to get that to happen or will the program know to do this from the table structure. This is super important. How will referential integrity play a role in this system?

[jzwp11]

In this case, the user would just fill in the blanks of the form using drop down menus that lookup from the site and analyte tables, enter the values for the results, without null values being placed anywhere in the database. Null value would interfere with calculations like means, correct?

You would control this through your query; you can select records that do not have null values (even if they are present in the database) and then calculate the mean. It really depends on your business rules, but ultimately you have the control via the queries you create.

ID values for foreign keys should be filled automatically, right? Is there a process to get that to happen or will the program know to do this from the table structure. This is super important. How will referential integrity play a role in this system?

The ID's for foreign key should be filled in automatically as long as you set up the relationships in the relationships window and check the box to enforce referential integrity which I strongly suggest you do before you start creating any forms. If you have your relations established and then put a subform within a main form, Access will automatically link the main and subforms. Then when a record is added to the subform (for a record in the main form), Access will automatically populate the foreign key field.

Since some values in analytical chemistry are reported as < a detection limit, I would suggest having a text field to hold the < symbol, the result field itself should be a number datatype field (either single or double precision), since you will be using the value in calculations. You should also have a units of measure field (or a foreign key referencing a record in a table that holds all units of measure that you many encounter). You may also need a field to hold any comments made by the lab conducting the analysis.

[DNRTech]

This is good advice. We indeed deal with some numbers that involve greater than/less than symbols (mpn for fecal coliform). It was on my list of things to figure out how to treat in the db. I image that through query language I can isolate those numbers and treat them special from the other values according to standard methods (I haven't read the methods yet).

I'm off to learn forms now. Thank you very much. I've learned a lot.

[jzwp11]

You are welcome. Good luck with your project.