Using access to store chemistry experiment results (1 Viewer)

ScubaAinz

New member
Local time
Today, 13:11
Joined
Jul 16, 2019
Messages
4
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.
 

isladogs

MVP / VIP
Local time
Today, 13:11
Joined
Jan 14, 2017
Messages
18,186
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?
 

ScubaAinz

New member
Local time
Today, 13:11
Joined
Jul 16, 2019
Messages
4
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
 

isladogs

MVP / VIP
Local time
Today, 13:11
Joined
Jan 14, 2017
Messages
18,186
No problem
You can certainly do this in Access but with 3600 records for all your data
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 13:11
Joined
Jul 9, 2003
Messages
16,244
Could you post a sample of the data produced by the data logger?

Sent from my Pixel 3a using Tapatalk
 

ScubaAinz

New member
Local time
Today, 13:11
Joined
Jul 16, 2019
Messages
4
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
 

Attachments

  • test1.xlsx
    64.8 KB · Views: 65

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 13:11
Joined
Jul 9, 2003
Messages
16,244
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
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 13:11
Joined
Jul 9, 2003
Messages
16,244
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
 

isladogs

MVP / VIP
Local time
Today, 13:11
Joined
Jan 14, 2017
Messages
18,186
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
 

ScubaAinz

New member
Local time
Today, 13:11
Joined
Jul 16, 2019
Messages
4
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
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 08:11
Joined
Feb 28, 2001
Messages
26,997
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.
 

Micron

AWF VIP
Local time
Today, 09:11
Joined
Oct 20, 2018
Messages
3,476
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.
 

Users who are viewing this thread

Top Bottom