Thermodata query problem

[Guest]

I have a program (Thermodata Suite) that has been designed to automatically
download information from temperature data recorders (i buttons). The default
database with this program is an ACCESS database. It has 3 tables:
Loggers – this table records information about each logger using LoggerID as
the primary key. It has only one row for each logger that is used for the
study and a field for the LoggerLocation.
Readings – This table has one row for each time a logger is downloaded into
the database. The primary key is ReadingID, another field for LoggerID (links
with referential integrity to the Loggers table) and also has its own field
for the LoggerLocation. This table also has the reading date and time, and
the date and time of the start and end of the logging period for each time a
logger is downloaded.
LogData – This contains the actual logged temperature data. It has a primary
key that is made up of the LoggerID and the date/time field and is linked
only to the Loggers table through LoggerID. This table does not contain any
information regarding the ReadingID for the downloaded data (unfortunately ïŠ
).

This database structure works fine if each logger is only used in one
location. However, we have used the same logger in different locations. The
location field in the Loggers table contains only the most recently used
location. The Readings table contains all of the locations each logger was
used. I need to write a query that gives me the actual location that the
temperature data was collected for each logger (LoggerID) - ie the location
information from the Readings table, not the Loggers table – but these two
tables are not directly linked in any way. When I make this query, with all
three tables in the design, I can not get it to give me anything…

Does anyone know how I could write such a query?

 

[Tom Ellison]

Dear Fish:

Is there any place where it is recorded what time a Logger is in any
particular Location? If so, this could be associated through the date/time
of the readings. Could someone begin recording this and we could add that
table to the database? Is there any way you can look at the data and tell
from the data what the locations are?

You say the Readings table does contain all the locations, but does it
record the date and time or any other way to make an association.

Tom Ellison

 

[Guest]

Hi Tom,
the Readings table as two date/time fields - the start and end date/time for
the records downloaded for that reading. This encapsulates the range of dates
in the LogData table, which has a single field containing the date/time
information for each individual temperature reading. Does this help?
Cheers,
Fishcakes.

 

[Tom Ellison]

Dear Fish:

I asked three related questions. You gave me some kind of specifics, but I
do not feel my questions were answered. From what you posted before, I do
not understand that you can tell the location of a reading from the Readings
table. I do not know how to ask a question more simply and specific.
Please read this again:

- Is there any place where it is recorded what time a Logger is in any
particular Location?

- Could someone begin recording this and we could add that table to the
database?

- Is there any way you can look at the data and tell from the data what the
locations are?

For me to help you, I'd like you to try to answer some of the above. As I
understand your problem, the Loggers table doesn't record the varying
location of loggers over time. So, you cannot associate the readings of a
specific logger with any location because you don't have a record of where
the logger was at the time the readings were taken. If that is all there is
to this problem, then you must either add that information to the database
(if it really isn't already there) or you can give up on making the
association of a reading with a location. The computer can't figure out
where a logger was on a certain date, you must tell it. The computer cannot
solve problems that are beyond the mind of man except to the extent that its
speed is the solution. So, if this information isn't in the computer where
you can use what's there to figure it out yourself, then the information is
not good. You must supplement the existing information with the missing
pieces or abandon any hope of a solution.

If the information is there, please show me. I cannot see it myself.
That's what my questions are for, to get you to look and report back what is
there. If there is sufficient information to solve the problem, even if
indirectly, then it is possible a query can be crafted to do this. If the
information is not there, but can be added, then we can probably solve it.
If there is not sufficient information available, then it cannot be solved
(well, that's pretty much the definition of insufficient information, isn't
it. I'm talking in circles now!)

Let me know so I can try to help.

Tom Ellison

 

[Guest]

Hi Tom,
Sorry my response to your question was not specific enough, I will try to do
better. I understand what you say about the information needing to be in the
database – However, I did not design this database, it is supplied by the
manufacturer (whom I have also emailed regarding this problem but have
received no response). There are two problems – Firstly, if I alter the
database in a way so that the location information is recorded for each piece
of downloaded data, then I can no longer use the manufacturer supplied
software to automatically download the data and I would have to manually
enter the information for 1000’s of records. Secondly, years worth of data
has already be downloaded into the current database and this information
needs to be extracted. This is why I am asking for any help regarding trying
to extract the data given the information that is currently supplied in the
database. I do not need a lesson on good database design…

I will try and answer your questions as best as possible:
- Is there any place where it is recorded what time a Logger is in any
particular Location?
As I said in my previous post, the Readings table as two date/time fields -
the start and end date/time for the records downloaded for that reading. This
information does tell you where each logger was placed over each period of
time of downloaded data.

- Could someone begin recording this and we could add that table to the
database?
We have only the information that has already been recorded, as above.

- Is there any way you can look at the data and tell from the data what the
locations are?
There is no way to tell from the actual logged temperature data what the
locations are, the temperatures overlap. The logged data has a temperature
field, the loggerID (linking it to the logger table) and the date/time of the
reading. As I said in my original post, there is no link to the Readings
table which contains the actual locations of each logger over time.

I hope this clarifies…

I think I want to write a query that looks at the date of the readings in
the LogData table and uses the location from the Readings table for that
loggerID as the final location of the reading. I am unsure of how to do this.

Regards,
Fishcakes.

 

[Tom Ellison]

Dear Fish:

I'm going back to your original post to see where I went wrong.

I think I understand much of this on closer inspection. Now, please tell
me, what are the columns you want in the report? From what tables do they
come? What is the query you have written so far?

Now, Logger X could be at location A all morning, location B in the
afternoon, and back at A this evening. Is it the case that you want to show
3 rows for Logger X, being at A starting at 2 AM, then at B starting at
noon, then at A again starting at 7 PM? Is that the sort of thing?

Please give some sample data and the results you want to see.

Tom Ellison

 

[Guest]

Hi Tom,
Here are some sample data tables:

Loggers table:
LoggerID* LoggerLocation^
log1 site1
log2 site6
log3 site7
log4 site5

Readings table:
ReadingID* LoggerID^ LoggerLocation^ ReadingFrom ReadingTo
1 log1 site1 march 3, 2004 march 10, 2004
2 log2 site2 march 3, 2004 march 10, 2004
3 log3 site4 march 3, 2004 march 10, 2004
4 log4 site5 march 3, 2004 march 10, 2004
5 log2 site6 march 12, 2004 march 15, 2004
6 log3 site7 march 12, 2004 march 15, 2004

LogData table:
LoggerID^ LogUTC TemperatureC
log1 march 3, 2004 23.2
log1 march 4, 2004 25.4
log1 march 5, 2004 23.5
log1 march 6, 2004 23.7
log1 march 7, 2004 25.4
log1 march 8, 2004 23.6
log1 march 9, 2004 23.3
log1 march 10, 2004 223.4
log2 march 3, 2004 28.2
log2 march 4, 2004 26.4
log2 march 5, 2004 23.5
log2 march 6, 2004 22.7
log2 march 7, 2004 25.4
log2 march 8, 2004 23.6
log2 march 9, 2004 23.9
log2 march 10, 2004 23.3
log2 march 12, 2004 25.7
log2 march 13, 2004 26.8
log2 march 14, 2004 25.4
log2 march 15, 2004 24.3
log3 march 3, 2004 31.4
etc (for all records downloaded for each logger)

I can also easily make a table of Locations, that can link with a primary
key to the Readings and Loggers tables:

LoggerLocation*
site1
site2
site3
site4
site5
site6
site7

I have simplified things a bit, as the temperature data is actually recorded
over seconds and minutes, but in this example I have made it so that it is
only recorded once every day. In the example above I have put an * showing
the primary key for each table, and an ^ to indicate that a field is a
foreign key that is linked (with referential integrity) to the primary key of
another table. The example tables are tab delimited (I don’t know how the
formatting will look once I post this).

What I want is a table that has the LoggerID, the LoggerLocation from the
readings table, the LogUTC and the temperatureC from the LogData table.
ie:

LoggerID^ LogUTC TemperatureC Readings.LoggerLocation
log1 march 3, 2004 23.2 site1
log1 march 4, 2004 25.4 site1
log1 march 5, 2004 23.5 site1
log1 march 6, 2004 23.7 site1
log1 march 7, 2004 25.4 site1
log1 march 8, 2004 23.6 site1
log1 march 9, 2004 23.3 site1
log1 march 10, 2004 223.4 site1
log2 march 3, 2004 28.2 site2
log2 march 4, 2004 26.4 site2
log2 march 5, 2004 23.5 site2
log2 march 6, 2004 22.7 site2
log2 march 7, 2004 25.4 site2
log2 march 8, 2004 23.6 site2
log2 march 9, 2004 23.9 site2
log2 march 10, 2004 23.3 site2
log2 march 12, 2004 25.7 site6
log2 march 13, 2004 26.8 site6
log2 march 14, 2004 25.4 site6
log2 march 15, 2004 24.3 site6
log3 march 3, 2004 31.4 site3
etc

I tried doing a query to that effect:
SELECT Readings.LoggerID, Readings.LoggerLocation, Loggers.LoggerLocation,
LogData.LogUTC, LogData.TemperatureC
FROM ((locations INNER JOIN Loggers ON locations.LoggerLocation =
Loggers.LoggerLocation) INNER JOIN Readings ON (Loggers.LoggerID =
Readings.LoggerID) AND (locations.LoggerLocation = Readings.LoggerLocation))
INNER JOIN LogData ON Loggers.LoggerID = LogData.LoggerID;

But I only get the readings for the most recently entered LoggerLocation in
the Loggers table (ie only site6 would appear for log2, with no rows for
site2).

I hope this makes sense and that the tables posted ok…
Regards,
Fishcakes.

 

[Tom Ellison]

Dear Fish:

OK, now that's some real information. First, your posted query:

SELECT R.LoggerID, R.LoggerLocation, L.LoggerLocation,
LD.LogUTC, LD.TemperatureC
FROM ((locations
INNER JOIN Loggers L
ON locations.LoggerLocation = L.LoggerLocation)
INNER JOIN Readings R
ON (L.LoggerID = Readings.LoggerID)
AND (locations.LoggerLocation = R.LoggerLocation))
INNER JOIN LogData LD
ON L.LoggerID = LD.LoggerID;

I have reformatted this according to my reading preferences, and added
aliasing for some of the tables.

Now, the first think I notice is that you have included the table locations.
This table provides no information (no columns in the SELECT) and is used
only to provide a column LoggerLocation in the JOINs with Loggers and
Readings tables. Also, this is a table you do not describe in your nice,
long narrative. This has me wondering what part this table plays in giving
the results you want, or alternatively, in making the problem you have.

Also, what purpose does the Loggers table serve. I know you have it
providing one of the values for LoggerLocation, but that's not valid for
anything, is it? You said the loggers move around over time, and this value
of location is only the most recent. I'm going to omit this for now. We
can try adding it back later if needed, but I'd like to try to get something
working at least partially first.

May I ask you to please try this:

SELECT R.LoggerID, R.LoggerLocation,
LD.LogUTC, LD.TemperatureC
FROM Readings R
INNER JOIN LogData LD
ON R.LoggerID = LD.LoggerID
WHERE LD.LogUTC BETWEEN R.ReadingFrom
AND R.ReadingTo
ORDER BY R.LoggerID, LD.LogUTC

Do these results make some sense? If you need, we can build from this to
add other information you need. If you have difficulty with this, I'll see
what I can do if you reply.

Tom Ellison

 

[Guest]

Hi Tom,
this appears to work fine... Basically you have directly joined the Readings
table to the LogData table with LoggerID - this join is not on the supplied
database. I will add it and check that the software still enables the
automatic dowloads... If not, i can leave the join just as part of the query?
Thanks so much for your help, and i really appreciate your patience...

 

[Tom Ellison]

Dear Fish:

Using this join in a query should not be able to break anything.

Glad it helped. Kind of a simplified approach I expect. That's me -
simple. Well, hopefully, cut through the garbage and get to the core. But
simple.

Tom Ellison