Table association Or dependent on field value

[NickWren]

I need a way of separating different types of tests results (2 different tables) but having them linked to the sample status table (tblSample). Test 1 (in our case NETest) has a result that is nonidentical to Test 2 (Colon).
All the sample information is the same (ID, SampleName, Location, TestType, ReceivedDate, and so on...)
However now depending on the field "TestType" if it says (NET or COL or more if added) go to the respected table with the ID, SampleName, and Location.
FinalDatabaseForWebQ.zip

[Perceptus]

I tried, but cant open. I may need to upgrade out of the past lol.

[NickWren]

O, that is odd. Is that on your half or something I did when saving the database?
Also I was wondering if someone can help me out with a search form. I have a search form inside this database, but it doesnt do what I want. You will see a form in the database called "SampleSatusForm" I want to fill in the any of the fields (Wren ID and SampleName, and use the parameters Facility, TestType, and ReceivedDate), and have the rest autopopulate with the information from the table. And then I can edit the rest after that.

[ItsMe]

If I read post #1, I can only guess at what the issue is. Considering only post #1, I would guess you are trying to change the recordsource of something by referencing one table or the other. If this is the case, I would say that your tables are not structured correctly and do not follow some basic normalization rules.

After I took a quick look at the DB, it seems you have different test types. Because of this, you are creating two tables, one for each test type. I notice there are different columns defined for each of the types. Because of this, it may seem rational to have two tables.

My best bet is that you need to reorganize your tables. You need to apply rules of normalization. Then, I would combine colon and NE into one table. I would use another table or two as child tables to the new table. This child table would replace the need for columns, data1, data2, data3, etc.

Whatever it is you are currently struggling with, I believe it is caused by your table structure.

[NickWren]

How can I make the tblNETestData and tblColonData "child tables" for the tblSample, which I'll make another column just called like data and have that column reference those child tables.

Actually I did it, however since I want the facility, testtype, and samplename to all be the sample how can I transfer that information over when first entering the sample would this be done in vba? I guess I want either both tables to be populated once a sample is entered or I want to have the samples information be transferred once the data is ready to be entered.

[June7]

Table links must utilize primary key fields on one side and foreign keys on other. Your designated primary keys are not involved in the Relationships. Change the PK or save the designated PK as FK.

There are only 2 fields different between tblColonData and tblNETestData. I recommend 1 table. Yes, there could be lots of records with empty fields, but I would (and do) live with that. IMO, it is a balancing act between normalization and ease of data entry/output. I have a laboratory sample tracking db that violates a lot of 'rules' but it all works to meet our primary goal, which is to output a report of the sample testing data. We do very little aggregate analysis with the data, just report each sample.

Consider using form/subform arrangement when entering/editing sample and related data.

What is the relationship here - can each sample have multiple related records in tblColonData and tblNETestData (1-to-many)? If not and this is a 1-to-1 relationship, might not even need the separate data tables. Combine with tblSample.

In my db, the Sample table has 1-to-1 relationship with each of over 100 test data tables. Each test is significantly different from others in data collected. You have only 2 very similar data tables which could be combined and maybe even combined with tblSample to greatly simplify structure. Whether or not the multiple Data_ fields are split out to a dependent table is another issue.

[ItsMe]

Really, in order for me to be of any assistance, I need to understand the business rules. Right now, all I can say is you need to normalize your data. any recommendation on how to normalize the table structure will be a guess.

When I look at your tblSample, I see two Primary Keys defined. In Access, this is done for a specific reason. When I look at tblNETestData and tblColonData, I see columns with names of, data1, data2, data3. This indicates you should have another table for "data events" that contains many records for each data event. Additionaly, it seems your tblNETestData and tblColonData should be combined as one table.

So, after my best guesses, I have no idea what the business rules are and if I am guessing correctly. Simply, looking at your DB is not providing me with the insight necessary. Since you know the business rules, you would need to apply database theory to the process of creating an entity relationship diagram.

It sounds crazy, but an ERD is really what you need. So, it may be beneficial to study up on normalization or techniques for creating an ERD.

[NickWren]

These are only example tables for the tblNETestData and tblColonData, in reality they are complete different, I work for a molecular biologist lab which have identified different markers for these two types of tests, these markers will be given different CT means which are then stored on this page. I cant have the Colon data and the NETest data on the same table, due to NETest has over 60 results while colon only has 8. so i need these to be separated.

I am trying to just get it so that when a New NETest sample is entered(tblSample <- uses a form) how to have the data be put in as blanks or default values till later edited (tblNETestData <- another form called "NETDataForm") I need these to be linked and be auto-populated when TestType = "NET"

samething with a new colon sample (entered on tblSample using a form) and has the data be entered as blank till the tests are run (later in the day we will add the CT values) uses tblColonData from form "ColonDataForm"

[ItsMe]

I cant have the Colon data and the NETest data on the same table, due to NETest has over 60 results while colon only has 8. so i need these to be separated.

This may be true. This is why I mentioned that I do not understand the business rules, perfectly. Even If I understood the business rules, I would strive to place these into a single table. I would look for a way to store the 60 results, as well as the 8 results in a single table. So I do not unnecessarily confuse the issue, I will leave it at... There are examples of survey/questionnaire databases that violate Normalization Rules. You have to work through the process and see what works best in your circumstance.

I am trying to just get it so that when a New NETest sample is entered(tblSample <- uses a form) how to have the data be put in as blanks or default values till later edited (tblNETestData <- another form called "NETDataForm") I need these to be linked and be auto-populated when TestType = "NET"

samething with a new colon sample (entered on tblSample using a form) and has the data be entered as blank till the tests are run (later in the day we will add the CT values) uses tblColonData from form "ColonDataForm"

I am having a hard time understanding the desired function. Because it is not clear and I believe there are issues with normalization, I revert to "normalization". In other words, when I am creating an ERD and I implement a diagram as a model, if the model breaks I will revisit normalization or maybe de-normalization.

So, the very fact that you are feeling the need to jump through hoops (create blank records, for example) could be an indication something is broken with you model (tables).

I will say that, if I had a better understanding of the relationship between NETest and ColonData, I might have an opinion. I just do not know enough to help. For instance, what is Data? Are NETest and ColonData totally separate Entities? Do either of these entities share the same Data? It seems you may be dealing with a special type of relationship (database theory) that is rather advanced.

[June7]

Sounds similar to what I do in my db. User inputs Sample 'header' info and selects which tests out of the 100+ to perform. VBA code writes records to the associated test data tables. After tests are run, appropriate form opens bound to a query that links the Sample with test tables for data input to the already existing records.

No, query does not include 100+ tables (not possible). Tests are classified by a material type and forms are built based on that. Each sample is associated with a material type and this determines the set of tests available for selection and which form to open for data entry of test info.

Code to add record to related table like:

CurrentDb.Execute "INSERT INTO tblColon(SampleID) VALUES(" & Me.SampleID & ")"

The real trick is figuring out what event to put code into.

[NickWren]

I mean I think I can do it on form load or after I hit a button, it should be fine, would I just add an if than statement to it to determine the table it should be entered it so like
If TestType = "COL" Then
CurrentDb.Execute "INSERT INTO tblColon(SampleID) VALUES(" & Me.SampleID & ")"
CurrentDb.Execute "INSERT INTO tblColon(Facility) VALUES(" & Me.Facility & ")"
....
ElseIf TestType = "..." Then
CurrentDb.Execute "INSERT INTO tbl...(SampleID) VALUES(" & Me.SampleID & ")"
CurrentDb.Execute "INSERT INTO tbl...(Facility) VALUES(" & Me.Facility & ")"
...
End If

[June7]

If you did that, you would get a record for each INSERT, you want one record. More than one field can be in an INSERT action. Review http://www.w3schools.com/sql/sql_insert.asp

Concatenate literal commas into the VALUES clause. Also, if any fields are text type, the parameters need apostrophe delimiters, dates use #. If you have more than two tests, recommend a Select Case structure.

Dim strTbl As String
If Not IsNull(Me.TestType) Then
Select Case Me.TestType
    Case "COL"
        strTbl = "tblColon"
    Case "something"
        strTbl = "tblSomething"
    Case "something else"
        strTbl = "tblSomethingElse"
End Select
If strTbl <> "" Then CurrentDb.Execute "INSERT INTO [" & strTbl & "] (SampleID, Facility) VALUES(" & Me.SampleID & ", " & Me.Facility & ")"
End If

[NickWren]

This wont duplicate if we go back to a sample to edit it right?

I need a way of saying if it is there then just edit the data, otherwise just add the new SampleName, WrenID, Facility... then be able to edit it from that form.


From tblSample I need it to check the TestType to see which table it needs to go in.
Next check the SampleName and WrenID to see if they are in the table that it just refrenced if they are there then open existing record, if not there then add field values (SampleName, WrenID, Facility, TestType) from the previous form (SampleStatusForm) and open to edit.

[June7]

Depends where you put the code - in what event. If it is behind a button, what prevents user clicking button multiple times? If you want this to happen without user action, then maybe code in the form AfterUpdate event:

If Me.NewRecord Then
...
End If

Another Select Case structure to locate the correct test table and open appropriate form filtered to desired record.

This is all like what I do in my db.

[NickWren]

Well it opens up the form and that's why I asked if it duplicated samples...
So I want to be able to not only add new data, but go back and look or edit the data.

Private Sub Command92_Click()
ResultsWrenID = Me.WrenID
ResultsSampleName = Me.SampleName
ResultsTestType = Me.TestType
ResultsFacility = Me.Facility

If ResultsTestType = "NET" Then
DoCmd.OpenForm "NETDataForm"
If (Need code to search "WrenID" in the table "tblNETData") = ResultsWrenID Then
(Open form with this WrenID and it's data for editting)
ElseIf
ResultsWrenID=NewRecord
(tblNETdata("WrenID") = ResultsWrenID)
(tblNETdata("SampleName") = ResultsSampleName)
(tblNETdata("Facility") = ResultsFacility)
(tblNETdata("TestType") = ResultsTestType)
End If
ElseIf ResultsTestType = "COL" Then
DoCmd.OpenForm "ColonDataForm"
If (Need code to search "WrenID" in the table "tblColonData") = ResultsWrenID Then
(Open form with this WrenID and it's data for editting)
ElseIf
ResultsWrenID=NewRecord
(tblNETdata("WrenID") = ResultsWrenID)
(tblNETdata("SampleName") = ResultsSampleName)
(tblNETdata("Facility") = ResultsFacility)
(tblNETdata("TestType") = ResultsTestType)
End If
End If
End Sub