Results 1 to 11 of 11
  1. #1
    TorridgeYeti is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2021
    Posts
    4

    Storing analytical data from multiple samples

    Hi, I am chasing my tail here trying to figure out the best table design to store analytical data. A brief example is below. For 1000's of samples, duplicate analysis can be carried out on the same sample at different times. Each analytical run measures readings (up to 100) for up to 8 different component types. Readings from each run are used to produce statistics for each component (min, max, average, st dev etc). Someone had suggested storing individual readings for each component as a string but that just seems plain wrong to me.


    Any ideas on how best to break this up into tables? Thanks

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    why isn't it 1 table?
    datetime, component, reading, this , that, theOther

    then run analytics on it.
    (your table is not attached)

  3. #3
    TorridgeYeti is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2021
    Posts
    4
    Weird, I did paste a snapshot. Let's try again...
    Click image for larger version. 

Name:	Readings example.jpg 
Views:	16 
Size:	97.1 KB 
ID:	45267

  4. #4
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    Hi, we have been using an analytics application for our lab results for years now, here included you'll find a simplified database schema of the application. Hope this gives you an idea.
    Attached Thumbnails Attached Thumbnails LabResultsSchema.jpg  

  5. #5
    TorridgeYeti is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2021
    Posts
    4
    Hi NoellaG. Thanks for your schema. I was thinking along similar lines but the bit I have a problem with is the AnalysisResult. In your SampleAnalysis table is the Result simply a single value? What I want to do is store all of the individual measrements from the raw data that go to make up the final AnalysisResult (e.g. a mean value). I'm not sure how I would achieve this using your schema.

  6. #6
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    it is a simple value: for each type of measurement there is a value in table Analysis. In our case (concrete analysis) this is for example Density, Pressure resistance, Water content, Setting time , ...
    For each analysis, we enter the sample number, which test, who performed the test, date/time test, result and unit (example density has another unit than setting time). So if we do 3 density tests: there are three records for that sample and analysis type each with their own ID, date/time and result. In a query grouping per sample and analysis we can then easily calculate the statistics we want (mean value, variance, standard deviation, ...) of these results.

  7. #7
    TorridgeYeti is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2021
    Posts
    4
    OK. So, if I have a sample where I measure say 1..100 area percentages for mineral1, mineral2, mineral3 etc. all in one run on the same date, then I will need 100 records for each mineral, possibly a separate table for each mineral in this. Or am I misunderstanding you?
    BTW, have a great weekend.

  8. #8
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    in principle yes. Minerals are the samples in my example: so there you have the samples table. And each analysis is one record. In my example, we have about 100 plants in both countries which take each +/- 25 samples/ a day and on each sample min 10 different analysis are performed in double, so we have +/- 100 * 25 *10 *2 = 50.000 entries/working day.

  9. #9
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    Ahh, see this site is so great bring two Mineral Samples Geeks together like this

  10. #10
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    But seriously, NoellaG your db or a sample of it might be something good to add to the Samples Database section.

  11. #11
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    I'm afraid that it's a bit to complicated for that: aside of the analytics it contains all the tables for concrete recipes, norms by country, ect. and it would take a lot of time to remove all confidential data. Overall it contains over 100 tables and twice that much views. As you probably guessed it's not an access database but a SQL server, which is hard to post here.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Help storing Time with multiple users
    By bobrulz in forum Programming
    Replies: 1
    Last Post: 11-30-2014, 09:05 PM
  2. Newbie: Print Analytical Report
    By frosty in forum Access
    Replies: 5
    Last Post: 09-20-2011, 02:12 AM
  3. Need help storing multiple answers per question
    By Buakaw in forum Database Design
    Replies: 6
    Last Post: 08-03-2011, 08:18 PM
  4. Replies: 3
    Last Post: 08-01-2011, 09:18 PM
  5. Database samples on SPC?
    By soundmann in forum Access
    Replies: 0
    Last Post: 11-27-2005, 07:43 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums