Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / Database Design / November 2005

Tip: Looking for answers? Try searching our database.

Creating relationships with time series

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Susan - 29 Nov 2005 02:56 GMT
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 - 29 Nov 2005 04:55 GMT
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 <vjohns@alumni.caltech.edu>
  Please feel free to quote anything I say here.

> Hello,
>
[quoted text clipped - 27 lines]
>
> Susan
mnature - 29 Nov 2005 15:28 GMT
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.

> Hello,
>
[quoted text clipped - 27 lines]
>
> Susan
Vincent Johns - 30 Nov 2005 08:33 GMT
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 <vjohns@alumni.caltech.edu>
  Please feel free to quote anything I say here.

> 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
[quoted text clipped - 40 lines]
>>
>>Susan
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.