Access Conceptual Table Idea

[NickWren]

I need help setting up my database tables having them all connect. I work for a molecular lab, and we get blood samples; a blood comes in with a Location, type of tumor, and a name. I need a system that can track the bloods through our process, then store the data with the status of the sample. I cannot connect our results excel spreadsheet output (importing data) with the sample we just entered into the database.

This is my structure so far...

tblTest
-TestType [ex. NETest, Colon, Mylenoma, Multiple Melanoma, Bronchopulmonary, ect...]
-TestAbbreviation [ex. NT, CL, ML, MM, BP, ect...]           - PK

tblFacility
-FacilityName [ex. Poland]
-FacilityAbbreviation [ex. PL]  -PK
-PhoneNumber
-ContactName
-Address
-Email

tblSample
-WrenID (Calculated Field - [FacilityAbbreivation] & "-" & [CalcID] & "
-CalcID (Hidden formats the SampleID to look like "0000#")
-SampleID (autonumbering)   }
-SampleName                     }
-FacilityAbbreviation         FK} Composite Key           (COMBOBOX LOOKUP FIELD - tblFacility)
-TestAbbreviation            FK}                                (COMBOBOX LOOKUP FIELD - tblTest)
-SecondaryTest                                                   (COMBOBOX LOOKUP FIELD - tblTest) - if we do multiple tests on one Sample (a multiple value combobox - cannot call upon the values)
-DateReceived
-FreezerLocation
-BloodProcessed
-DateBloodProcessed
-PCR
-DatePCR
-DataAnalyzed
-DateDataAnalyzed

Now for a results I dont know how the table should look. I've been told I should normalize my data, however our results from excel will not import properly into access when normalized.
As of now I got it to show up in a query using the SampleID (autonumbering system) and an ID as a Primary key when uploading the PCR results (autonumbering system) using a one-to-many.

The Excel spreadsheet for TestType = NETest

-SampleName [Will be our WrenID made from access calculated field above]
-MATLAB [#]
-Progressive [#]
-NETScore [#]
-HouseKeepingGene [Short Text]
-SecondaryKeepingGene [Short Text]
-PositiveControl1 [#]
-PositiveControl2 [#]
-PositiveControl3 [#]
-PositiveControl4 [#]
-MainProliferome [#]
-GeneProliferome1 [#]
-GeneProliferome2 [#]
-GrowthFactor [#]
-ProgOme [#]
-GenOme [#]
-Epigenome [#]
-Apoptome [#]
-Plurome [#]
-SOme [#]
-SOme1 [#]
-SOme2 [#]
-SOme3 [#]
-SOme4 [#]
-ROC [#]
-2D [#]
-Gene1 [#]
-Gene2 [#]
-Gene3 [#]
-Gene...ect [#]
-Gene52 [#]

Excel SpreadSheet for Colon Test

-SampleName (WrenID)
-Gene1 [#]
-Gene2 [#]
-Gene3 [#]
-Gene4 [#]
-Gene5 [#]
-Gene6 [#]
-Gene7 [#]
-Gene8 [#]

So storing this data in my database what is the best approach?
I was told I need to normalize my data, however I dont see how that works really with our results.

tblNormalizedData
-DataID
-SampleName (WrenID)
-GeneName (lookup field from a gene table that has all the genes for each test)
-GeneValue [#]

But now, do I call this in a query? I dont really see how this applies to our database.


The basic information is put into tblSample - this gives us a WrenID and tells us where we are with the sample.
But the results to the sample dont come in till later. I cannot really get the results to be connected to that sample, unless I put the results right after that sample, however if the sample doesnt have certain tests run then those tests results will be blank.

[June7]

IMO, it is a balancing act between normalization and ease of data entry/output.

I have a laboratory testing database (construction materials) that violates several relational db principles, including normalization. Each sample can have various tests associated. The raw test results data are saved into separate tables for each test. We also save calculated data so that if formulas change the reported calculations of existing records are not altered.

All of this works to accomplish primary goals which are to track sample status and publish a report for each sample results. We do very little statistical analysis.

[NickWren]

Here is the attached database:
sorry if any of my explanation is confusing I will try to be a bit more specific right now.

Sample In ->

• Sample Name
• Facility
• Test Type
• Date Received

This all goes in tblSample (This is the entry information, nothing more is in put at this time) - [frmSampleEntry will be made for this information]
Later we will go back and check off that we did part of the processing - frmSampleStatus - this will use a query to search for a WrenID or a field above
Lastly when the sample is done being processed we need to put the results which is that long list of numbers for each gene. [This is where I struggle, because the results for different tests have different looking outcomes from 60 numbers to 8, and some have multiple results]


WrenDatabase.zip

[NickWren]

is there a way you can show me how you associate the relationship between the samples in and the results for the different tests? I am confused about the relationship I guess. I can easily do this for one test, but then adding in multiple tests for one sample, I havent figured out what the composite key should be, maybe I need to change the results output, because right now, all we get is a SampleID which = a unique WrenID however since the WrenID is a calculated field it cannot be a primary key... is there a way I can make the WrenID a primary key thats can solve my problem.

[June7]

I have code that assigns a custom unique identifier to each sample at login. This is used as the PK/FK (not using autonumber as PK). User selects tests to associate and code saves record with this constructed value as FK into each relevant test data table. It does get rather complicated behind the scenes but appears so simple to the user.

If you want to download db, should be at http://www.box.net/shared/r8nea07sng

It is split design. If I remember correct, just place both files at the C:\ root level and the db should run. Hold down shift key when opening front end so you have full access to everything then open the Login form first to start interacting with db like a user would.

[NickWren]

There was an error trying to open up material database 'AcroRd32.dll' version 1.1. But Maybe you can suggest a better way to setup the database, because right now I want to use the WrenID as a PK or way of identifying the sample is not working due to it being a calculated field, I cannot call upon it (maybe if I use vba code I can actually call upon this value). and I can't use a composite key due to the results not having the same fields, the only field that is related to the access database is Sample ID which will be our WrenID. Which is unique per sample but wont let it be a primary key ( but I do make the ID for both tables the PK so that atleast they have some reference point) so on tblSample #1 = #1 on tblNETResults... but tblSample WrenID does not = SampleID tblNETResults which is what I truly want.

[June7]

Oops, forgot about the module that creates and manipulates PDF files.

Open frontend with shift key bypass and in VBA Editor delete the OutputToPDF module and uncheck the Adobe Acrobat 10.0 Type Library reference. Otherwise, explore db without opening forms/reports or running code.

My database calculates and saves the SampleNum into table (is not a Calculated field). This is one option for you. Other options are compound primary/foreign keys or use autonumber as PK/FK.

[NickWren]

I need a way to make a calculated field the PK is the problem, I cannot use a composite due to the fact that the results sheet only comes out with a SampleName to reference the Sample. SampleName in the Results = WrenID in tblSample.


Also how do I save the Database so it is like this? is this an object? sorry I am still new to access.

[June7]

A table Calculated field cannot be defined and used as PK. Period.

The Database is a file. Database has tables, queries, forms, reports, code.

Have you completed any tutorials or classes on Access? Here is one good site http://www.rogersaccesslibrary.com/