Joining two queries of unequal rows

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi. I have a table [2004Samps] consisting of a field of numerical data
[Samp] and a date/time field [Tsamp]. Data were collected every two minutes
for a year, yeilding ~240,000 rows of data.

What I'm trying to do is determine the COUNT of values for the entire year
that are equal to the daily average value, equal to the daily average minus
one, the daily average minus 2, etc.

First Query:
Since the daily average is different every day, the first thing I've done is
queried the table to give me the daily average value [DAVSamp] for each day
[DAVDAY]. Thus my query produces 365 rows of data. One for each day of the
year.

Second Query:
Since the data in my original table was in a combined date/time format
[Tsamp], I ran a query to pull out all 240,000 [Samp] data points but only
have the date [SampDay] listed for each one instead of the date and time. I
did this using SampDay: DateValue([Tsamp])

Third (and final?) query:
NOW what I'm trying to do is run a third query to generate a value
[SampDiff] for each sample that is an expression of the difference between
that value [Samp] and the average value for that day [DAVSamp]. So I've got
365 rows in one query that I'm trying to relate to 240,000 rows in the other
to give me 240,000 rows of [SampDiff] data.

Unfortunately I cannot get this third query to join the first two at all.
Lots of "type mismatch in expression" errors. At first I thought the problem
was that both of the first two queries were derived from the same original
data table. I therefore made a copy of the original data table, and re-ran
the first query off of that.

It made no difference whatsoever.

Any help or suggestions would be greatly appreciated.
 
You will need to define first your criteria to determine "equality" (to
daily average, daily average - 1, daily average - 2). Since the daily
averages will most like be decimal numbers (the average function probably
returns a Variant of Double subtype) and 2 Double numbers are rarely equal.
Remember that the numbers are represented by binary digits internally and
binary repsentations are not exact due to conversion and truncation errors.

What I meant is that you cannot rely on the comparison operator "=" in your
query. You will need to use some sort of "acceptable range" that if the
Samp value is in the range, it is considered as equal to your base point
(e.g. daily average).

Post you criteria to determine "equality" and someone should be able to help
you with the SQL String.

HTH
Van T. Dinh
MVP (Access)
 
Back
Top