Top 3 dates in calculation Function / Query?

[c8tz]

Hi,

This is a query I have created that picks up the top 3 dates for this
data (for example) :

Tree Pos2 Date
1 15 23/08/2005
1 20 12/02/2006
1 32 15/10/2006
2 17 23/08/2005
2 18 12/02/2006
2 20 15/10/2006

The top 3 dates are 23/08/2005(date1) ; 12/02/2006(date2) ;
15/10/2006(date3)

Using this data with the dates, I need do a calculation such that it
counts the number of days between date2 and date1 and also date3 and
date2.

Using this : it must do another calculation : noof days/pos2

I've tried using a crosstab query but how do i use the column headings
of the date?
I tried writing a function and using queries but got lost halfway...
but it wasn't taking the pos2 data well - it needed it in a
calculation..


My programming skillz isn't there yet - please help!

Thanks heaps in advance!!

 

[Michel Walsh]

SELECT a.tree, MAX(a.pos2), a.date, MIN(b.date) - a.date
FROM myQuery AS a LEFT JOIN myQuery AS b
ON (a.tree=b.tree AND b.date>a.date)
GROUP BY a.tree, a.date

should do.


Hoping it may help,
Vanderghast, Access MVP

 

[c8tz]

Hi Michel,

Thanks for your help -

This picks up the difference of Max Date - date and Max Date - min
date but not date - min date

i tried a.date - b.date but it won't accept.


thank you very much for your assistance.

 

[Michel Walsh]

You have to read the query starting by the GROUP BY clause. Given a record,
"a", then we look its a.date value. Since "b" is limited to rows occurring
later than a.date (... ON ... b.date>a.date ...) , then MIN(b.date) is the
earliest, there first, date occurring after the given a.date, and thus
MIN(b.date) - a.date should give the interval. Max(b.date) would always
refer to the latest date, so it does not make real sense to use it. Using
MIN or MAX on a.date does not produce much either, since a.date is a single
value (due to its appearance in the GROUP BY list). So I fail to see what
you look for.


SELECT a.tree, MAX(a.pos2), a.date, MIN(b.date)
FROM myQuery AS a LEFT JOIN myQuery AS b
ON (a.tree=b.tree AND b.date>a.date)
GROUP BY a.tree, a.date

with

Tree Pos2 Date
1 15 23/08/2005
1 20 12/02/2006
1 32 15/10/2006


should return:

1 15 23/08/2005 12/02/2006
1 20 12/02/2006 15/10/2006
1 32 15/10/2006 (null)


and the difference between the 4th column and the third give the interval.
If this is not what you want, what is it, with that data? ( I assumed your
date format is dd/mm/yyyy, NOT mm/dd/yyyy )


Vanderghast, Access MVP