Go Back   Access World Forums > Microsoft Access Discussion > Tables

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 07-16-2019, 01:07 AM   #1
ScubaAinz
Newly Registered User
 
Join Date: Jul 2019
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
ScubaAinz is on a distinguished road
Lightbulb Using access to store chemistry experiment results

Dear Access World Users,

I am hoping to use Access to create a database of all of my experimental results.

The experiment monitors temperature every second over a one hour period.

In my experiment table, I have a unique experiment ID, date, conducting scientist, but am struggling to then add the reaction results to the record?

My aim is to have a single experiment record for each experiment which contains all of the values collected by the data logger.

Any help would be much appreciated and thank you in advance for taking the time to read this.

ScubaAinz is offline   Reply With Quote
Old 07-16-2019, 02:29 AM   #2
isladogs
High Noon Moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 11,962
Thanks: 120
Thanked 3,331 Times in 3,005 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: Using access to store chemistry experiment results

Welcome to AWF

Whilst you could do this in Access it sounds like you want to create a giant spreadsheet.
Using Access, each temperature measurement (all 3600 of them) should be in a separate record (row). Queries would then be used to analyse the data as required.

You cannot create a table with that number of fields to store the data.
'horizontally' and even if it were possible maintaining such a structure would be a nightmare.
Perhaps Excel might be better for this?
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Colin (Mendip Data Systems)
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Spam, spam, spam, spam, spam, spam, spam (Terry Jones - RIP)
isladogs is offline   Reply With Quote
Old 07-16-2019, 02:45 AM   #3
ScubaAinz
Newly Registered User
 
Join Date: Jul 2019
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
ScubaAinz is on a distinguished road
Re: Using access to store chemistry experiment results

Hey Isladogs,

First, thank you for your reply. I think you may well be right. I have been trying all sorts of weird and wonderful methods to do this but as you said, I think Excel might be the solution.

Will leave the thread open in case anyone does have a solution

Best wishes

ScubaAinz is offline   Reply With Quote
Old 07-16-2019, 03:05 AM   #4
isladogs
High Noon Moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 11,962
Thanks: 120
Thanked 3,331 Times in 3,005 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: Using access to store chemistry experiment results

No problem
You can certainly do this in Access but with 3600 records for all your data
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Colin (Mendip Data Systems)
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Spam, spam, spam, spam, spam, spam, spam (Terry Jones - RIP)
isladogs is offline   Reply With Quote
Old 07-16-2019, 03:17 AM   #5
Uncle Gizmo
Nifty Access Guy
 
Uncle Gizmo's Avatar
 
Join Date: Jul 2003
Location: Newbury Berks UK
Posts: 10,540
Thanks: 597
Thanked 1,008 Times in 950 Posts
Uncle Gizmo is a jewel in the rough Uncle Gizmo is a jewel in the rough Uncle Gizmo is a jewel in the rough
Send a message via Skype™ to Uncle Gizmo
Re: Using access to store chemistry experiment results

Could you post a sample of the data produced by the data logger?

Sent from my Pixel 3a using Tapatalk
__________________
Code:
                 |||||
               @(~^~)@
-------------oOo---U---oOo-------------
|                                     |
|      Uncle Gizmo              |
|  
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
| | Ooo | |_________________ooO____( )________| ( ) ) / \ ( (_/ \_)
Uncle Gizmo is offline   Reply With Quote
Old 07-16-2019, 03:55 AM   #6
ScubaAinz
Newly Registered User
 
Join Date: Jul 2019
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
ScubaAinz is on a distinguished road
Re: Using access to store chemistry experiment results

Dear Uncle Gizmo,

I have attached a sample dataset. At each second interval a temperature and depth reading is taken.

I have 12 data loggers and will get 150 sets of these datasets per week.

Was hoping to use Access to keep tabs on this rather than an excel spreadsheet.

Looking forward to your reply
Attached Files
File Type: xlsx test1.xlsx (64.8 KB, 9 views)
ScubaAinz is offline   Reply With Quote
Old 07-16-2019, 03:57 AM   #7
Uncle Gizmo
Nifty Access Guy
 
Uncle Gizmo's Avatar
 
Join Date: Jul 2003
Location: Newbury Berks UK
Posts: 10,540
Thanks: 597
Thanked 1,008 Times in 950 Posts
Uncle Gizmo is a jewel in the rough Uncle Gizmo is a jewel in the rough Uncle Gizmo is a jewel in the rough
Send a message via Skype™ to Uncle Gizmo
Re: Using access to store chemistry experiment results

I know there's a way to get MS Access to load data automatically from a (CSV) comma separated value file. And also detect when there's a new file (I think)...

Sent from my Pixel 3a using Tapatalk

__________________
Code:
                 |||||
               @(~^~)@
-------------oOo---U---oOo-------------
|                                     |
|      Uncle Gizmo              |
|  
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
| | Ooo | |_________________ooO____( )________| ( ) ) / \ ( (_/ \_)
Uncle Gizmo is offline   Reply With Quote
Old 07-16-2019, 04:01 AM   #8
Uncle Gizmo
Nifty Access Guy
 
Uncle Gizmo's Avatar
 
Join Date: Jul 2003
Location: Newbury Berks UK
Posts: 10,540
Thanks: 597
Thanked 1,008 Times in 950 Posts
Uncle Gizmo is a jewel in the rough Uncle Gizmo is a jewel in the rough Uncle Gizmo is a jewel in the rough
Send a message via Skype™ to Uncle Gizmo
Re: Using access to store chemistry experiment results

I reckon we posted at the same time!

I'm looking at the data on my mobile! They are getting really clever, these mobiles!

Sent from my Pixel 3a using Tapatalk
__________________
Code:
                 |||||
               @(~^~)@
-------------oOo---U---oOo-------------
|                                     |
|      Uncle Gizmo              |
|  
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
| | Ooo | |_________________ooO____( )________| ( ) ) / \ ( (_/ \_)
Uncle Gizmo is offline   Reply With Quote
Old 07-16-2019, 04:57 AM   #9
isladogs
High Noon Moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 11,962
Thanks: 120
Thanked 3,331 Times in 3,005 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: Using access to store chemistry experiment results

The Excel file format you uploaded is exactly what your Access table should look like. It will be easy to import and you can easily add additional fields such as date when you do so. You can also automate the import very easily as mentioned by UG should you wish to do so.

Once imported you can then use queries to filter the data or display summary data including the use or aggregate queries and crosstab queries.

So I think Access should actually be fine for storing and manipulating your data
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Colin (Mendip Data Systems)
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Spam, spam, spam, spam, spam, spam, spam (Terry Jones - RIP)
isladogs is offline   Reply With Quote
Old 07-16-2019, 05:32 AM   #10
ScubaAinz
Newly Registered User
 
Join Date: Jul 2019
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
ScubaAinz is on a distinguished road
Re: Using access to store chemistry experiment results

Dear Isladogs,

Would that mean that I end up with a table for each experiment?

I worry because over the course of a year I expect to collect the results of over 3000 experiments and a table for each might then become messy.

Best wishes
ScubaAinz is offline   Reply With Quote
Old 07-16-2019, 12:40 PM   #11
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 15,098
Thanks: 102
Thanked 1,896 Times in 1,731 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Using access to store chemistry experiment results

ScubaAinz,

The way we usually would do this is to have a table of experiments. Each experiment has an ID number. (Could be Autonumber.) You would have the date, the start time, and any other descriptive data in the experiment record.

Next, you would have a table of Observations which had the experiment number, the timestamp, and the observation. (If multiple observations for the same time, then more than one observation field.) Having 3600 of these would require maybe 20 bytes per record or 72 Kb total. It would be possible (depending on the nature of the input) to use less space for each record.

Two tables. One for experiments. One for observations. One relationship between experiment number as Prime Key of the experiment table and the experiment number that is a foreign key that is the same for every observation made for that one experiment.

Doing it that way, you minimize the storage space requirements. But a query can rejoin the experiment data to the observation data using the prime key/foreign key relationship.

The only thing you worry about with Access is that it is most efficient when you make things in vertical rather than horizontal lists.

You might want to study "database normalization" to understand why and how this works. For what it is worth, I have a PhD in analytical chemistry (hence the "Doc" in my screen name.) Trust me, this works for storing what you want.
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
Old 07-16-2019, 01:15 PM   #12
Micron
AWF VIP
 
Join Date: Oct 2018
Location: Ontario, Canada
Posts: 1,518
Thanks: 12
Thanked 292 Times in 275 Posts
Micron has a spectacular aura about Micron has a spectacular aura about
Re: Using access to store chemistry experiment results

Quote:
If multiple observations for the same time, then more than one observation field.)
I'd say "depends". If 1st observation field is temp and the 2nd observation field is mass, then no. Every record where one applies but not the other means empty fields. Plus, what happens if a new observation becomes the norm for a different type of experiment, add a new field to the table? I know you know the answer to that is that such a table was either not properly normalized at worst or suffered from lack of foresight at best.

__________________
Sometimes I just roll my eyes out loud...
Windows 10; Office 365 (Access 2016)
Micron is offline   Reply With Quote
Reply

Tags
table access , table data , table desgin

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
An interesting experiment! Need others opinion on different ODBC query behaviors. Banana Queries 12 07-01-2008 08:48 PM
Access DB to store and grade exam results jokeman3000 Tables 4 04-13-2006 05:17 AM
Query a remote access DB and store results suzie_q Modules & VBA 1 12-09-2002 03:52 AM
store query results... j6niner Queries 1 02-13-2002 06:43 AM




All times are GMT -8. The time now is 06:19 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World