Average event between dates

[Francisco]

I've a db that records diferent values of an event in diferent dates for
severall persons, and I want to know:

1 - All the average value between dates (ex: (Value2-Value1)/(Date2-Date1)
for each person)

2 - As the total records for each person is not equal, this is, I've 2
records for person A, and 12 record for person B, Iwant to know also the
first average value (of the first two dates I recoorded values) and the last
average value (of the last two dates I recorded values)


---
I've allready made two cross reference query that give me one the date for
each frequency (order number) for person:
f1 f2 f3 f4
albert 5/5/98 6/6/98 4/7/98
juan 7/8/99 9/9/99 3/10/99 5/11/99
melissa 21/7/06 21/08/06

And Values for the frequency (order number)

f1 f2 f3 f4
albert 225 234 242
juan 212 218 226 238
melissa 198 215

And Know I've (avg per day)

Avg1 Avg2 Avg3
albert 0.28 0.29
juan 0.18 0.33 0.36
melissa 0.55

And I wa't to selct automaticly:

First Avg Last Avg
albert 0.28 0.29
juan 0.18 0.36
melissa 0.55 0.55


I'm so sorry for such a long post and probably confused.

Hopefully someone can help me.

Thank you all in advance

Francisco

 

[Michel Walsh]

You should work on the original table, not from the crosstab(s).


I assume the 'values' are strictly increasing, for a given user, as dates
increased (a little bit like an odometer of a car ).

SELECT a.user, a.date
MIN(a.date-b.date) AS diffDate,
MIN(a.value-b.value) AS diffValue,
MIN(a.value-b.value)/MIN(a,date-b.date) AS ratio
FROM myTable AS a RIGHT JOIN myTable AS b
ON a.date > b,date

GROUP BY a.user, a.date


saved it as qu1. It gives each ratio, for each user, each date. Next:



SELECT user, AVG(ratio)
FROM qu1
GROUP BY user


will then the average, by user (the average is over all the dates, by user)



Hoping it may help,
Vanderghast, Access MVP

 

[Francisco]

Unfortunely it's not working, the diffValue field gives a negative number,
and also irrealistic as I've got my "real values" that I can compare, I've
been trying to work around your solution, but couldn't.

This is my SQL:

SELECT a.NumSIA, a.Date, Min(a.Date-b.Date) AS DiffDate,
MIN(a.Value-b.Value) AS DiffValor, Min((a.Valor-b.Valor)/(a.Date-b.Date)) AS
ratio
FROM csltPesosDatas AS a RIGHT JOIN csltPesosDatas AS b ON a.Data>b.Data
GROUP BY a.NumSIA, a.Data;

------
and with NumSIA as user id.

The value doesn't necessery increase, as, unfortunely, they can sometimes
decrease.

It also takes a bit of time, about 5-7 minutes on my db (with aprox 8000
diferent records)

Thank you again, and hope somebody coan be of my assitance.
Francisco T. B.
Universidade de Évora

 

[Michel Walsh]

I was missing something important in the ON clause:

ON a.NumSIA=b.NumSIA AND a.Date>b.date

should have been typed, instead of just ON a.Date > b.Date. And use
GROUP BY NumSIA, date, not GROUP BY NumSIA, data.

Anyhow, if the value don't necessary increase, as for an odometer, we have
to use a more complex formulation, which, on the other hand, will take even
much more time to execute, unless we can create a new table, same fields as
the original table, plus an autonumber field. Then, fill that temp table
with data with an INSERT INTO ... ORDER BY, like:


INSERT INTO temp(NumSIA, date, data) SELECT NumSIA, date, data FROM
csltPesosDatas ORDER BY NumSIA, date ASC



Next, add an index on the autonumber field (once the data is appended into
the table).


Finally, using the temp table should do the trick (relatively fast):

SELECT a.NumSIA, a.date, (a.data-b.data)/(a.date-b.date)
FROM temp AS a INNER JOIN temp AS b
ON a.autonumberField = 1+ b.autonumberField
AND a.NumSIA=b.NumSIA




Hoping it may help,
Vanderghast, Access MVP

 

[Francisco]

Excelent, seems to work and very fast!

Thank you very much for your help.
Francisco T. B.
Universidade de Évora

 

[Michel Walsh]

The final query is fast, but to be 'honest', we have to take into account
the creation of the table, appending data, creating the index, and all that
kind of management in the "total time it takes".



Vanderghast, Access MVP