Creating relationships with time series

[Susan]

Hello,

I have an instrument that records the amount of oxygen delivered to
individual cells (which I have 24 of). The instrument creates a txt file for
each cell thus there are 24 files for each run (a run can last several
months). A new row of data is appended to the txt file every half hour with
the new data.

Each file is named after the cell and contains the column header in the
first row. Then each row is the data. Headings include, Hour, mL of O2, mL
kg-1, etc

I have linked the txt files in a database, each as a new table allowing the
database to remain up to date when the instrument appends each new row of
data.

I have also created a table with all the details in it including - cell
name, treatment, start time etc

I need to be able to create a query that extracts the hr and mL from each
table, relates it to the table name (cell name)...and be able to graph time
vrs oxygen delivered.

My problem is that all 24 tables contain the exact same column headings.
The table name is the only real difference.

Is there away around this?

Thanks

Susan

 

[Vincent Johns]

I made up some data to simulate what you said you have. There are about
21 Tables missing, but the remainder are left as an exercise for the
reader.

The example Tables look like these:

[Cell01] Table Datasheet View:

Hour mL of O2 mL kg-1
----- -------- -------
436.5 15.1 17
437 18.2 19

[Cell02] Table Datasheet View:

Hour mL of O2 mL kg-1
----- -------- -------
436.5 17.1 17
437 18.2 19

[Cell24] Table Datasheet View:

Hour mL of O2 mL kg-1
----- -------- -------
436.5 22.1 17
437 19.2 19

I combine them using the following Query (which you'll have to complete
yourself to add the other 21 Tables, but it's an easy pattern):

[Q_TimeVsO2] SQL:
SELECT T.Hour, 01 AS Cell, T.[mL of O2]
FROM Cell01 AS T
UNION SELECT T.Hour, 02 AS Cell,T.[mL of O2]
FROM Cell02 AS T

UNION SELECT T.Hour, 24 AS Cell, T.[mL of O2]
FROM Cell24 AS T
ORDER BY T.Hour, Cell;

This produces the following combined results:

[Q_TimeVsO2] Query Datasheet View:

Hour Cell mL of O2
----- ---- --------
436.5 1 15.1
436.5 2 17.1
436.5 24 22.1
437 1 18.2
437 2 18.2
437 24 19.2

If you need to do any filtering, transformations, &c., I suggest you
leave [Q_TimeVsO2] alone (it's long enough already) and write another
Query based on it that you can use for graphing.

What I did with [Q_TimeVsO2] was to set up a line Chart with 24 data
series (well, actually only 3 ... use your imagination) and based on the
following Query:

[Q_PlotData] SQL:

TRANSFORM Avg(Q_TimeVsO2.[mL of O2]) AS [AvgOfmL of O2]
SELECT Q_TimeVsO2.Hour
FROM Q_TimeVsO2
GROUP BY Q_TimeVsO2.Hour
PIVOT Q_TimeVsO2.Cell;

That Avg() function could just as easily have been Max() or Min(), as
there's only one of each value to be plotted.

The values were plotted in multiple series (more imagination needed to
see the graph here), but in tabular form they look like this:

[Q_PlotData] Query Datasheet View:

Hour 1 2 24
------ ----- ----- -----
436.50 15.10 17.10 22.10
437.00 18.20 18.20 19.20

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[mnature]

It sounds as if you are keeping 24 separate tables, one for each cell. I
would suggest another way of dealing with your data. Keep all of the data in
one table, but add another column, which will contain the cell number. When
you create your text files, make sure you include that extra column there, so
as you append new data, it contains the cell number for each record.

When you create your graph, use the cell number field for filtering, and
then choose which cell number to graph out, or select several if you want to
compare them.

Hope this makes sense. The important thing for a database, is to let the
database do as much of the work as possible.

 

[Vincent Johns]

My understanding of Susan's posting was that there aren't 24 Tables in
the database, but rather that there are 24 links to files that are
maintained elsewhere. If I inferred this correctly, that would limit
one's options to combine the data into one Table (which I would
otherwise wish to do).

Using a Query to combine them allows them to stay up to date, with no
further maintenance required. (The Query that I suggested looks kind of
messy, but once it's written it can be used many times without change.)

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.