top 3 dates in calculations : Function

[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!!

 

[Guest]

c8tz,

Here is an example you can use without using VBA coding. If you would like
to have VBA coding let me know.

1) First create a query which select the top 3 dates - Call it TOPDATES
"SELECT TOP 3 [Dates] from YourTable"
2) Create a query to get the top date - MAXDATE
"SELECT MAX([Dates]) From TOPDATES"

3) Create a query with the lowest date - LOWDATE
"SELECT MIN([Dates]) FROM TOPDATES

4) Create a query with the middle date
SELECT [DATES] FROM TOPDATES WHERE [DATES] not in [MAXDATE.[DATES],
LOWDATE.[DATES])

Via this 4 queries you can perform any calculation you need.

HTH.

 

[c8tz]

hi Brotha,

I tried this but for the last query middle_date , it prompts me for
the maxdate and the min date - how can I have this put in
automatically.

Thanks for your help.

c8tz,

Here is an example you can use without using VBA coding. If you would like
to have VBA coding let me know.

1) First create a query which select the top 3 dates - Call it TOPDATES
"SELECT TOP 3 [Dates] from YourTable"
2) Create a query to get the top date - MAXDATE
"SELECT MAX([Dates]) From TOPDATES"

3) Create a query with the lowest date - LOWDATE
"SELECT MIN([Dates]) FROM TOPDATES

4) Create a query with the middle date
SELECT [DATES] FROM TOPDATES WHERE [DATES] not in [MAXDATE.[DATES],
LOWDATE.[DATES])

Via this 4 queries you can perform any calculation you need.

HTH.

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!!- Hide quoted text -

- Show quoted text -

 

[Guest]

Hi c8tz,

Sorry think change the following

1) First create a query which select the top 3 dates - Call it TOPDATES
"SELECT TOP 3 [Dates] as TOPDATES from YourTable"
2) Create a query to get the top date - MAXDATE
"SELECT MAX([Dates]) as MAXDATES From TOPDATES"

3) Create a query with the lowest date - LOWDATE
"SELECT MIN([Dates]) MINDATES FROM TOPDATES

4) Create a query with the middle date
SELECT [DATES] FROM TOPDATES WHERE [TOPDATES] not in [MAXDATE.[MAXDATES],
LOWDATE.[MINDATES])

Hope it works now

hi Brotha,

I tried this but for the last query middle_date , it prompts me for
the maxdate and the min date - how can I have this put in
automatically.

Thanks for your help.

c8tz,

Here is an example you can use without using VBA coding. If you would like
to have VBA coding let me know.

1) First create a query which select the top 3 dates - Call it TOPDATES
"SELECT TOP 3 [Dates] from YourTable"
2) Create a query to get the top date - MAXDATE
"SELECT MAX([Dates]) From TOPDATES"

3) Create a query with the lowest date - LOWDATE
"SELECT MIN([Dates]) FROM TOPDATES

4) Create a query with the middle date
SELECT [DATES] FROM TOPDATES WHERE [DATES] not in [MAXDATE.[DATES],
LOWDATE.[DATES])

Via this 4 queries you can perform any calculation you need.

HTH.

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!!- Hide quoted text -

- Show quoted text -