top 3 dates in calculations : Function

C

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

Brotha Lee

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.
 
C

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.



c8tz said:
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 -
 
B

Brotha Lee

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

c8tz said:
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.



c8tz said:
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 -
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top