By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
457,997 Members | 1,260 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 457,997 IT Pros & Developers. It's quick & easy.

Using Date in Crosstab Column Heading in Calculation

P: 1
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..


Need help.

I am using Access 2003 ; WindowsXP
Apr 17 '07 #1
Share this Question
Share on Google+
1 Reply


Rabbit
Expert Mod 10K+
P: 12,427
This is difficult. You're going to have to rank the top 3 dates in one query.

In a second query you'll use DLookups to calculate the difference between the dates and then calculate your other formula.

Or you could use code to do it, in which you'll have to open a recordset and loop through the top 3 records and do your calculations that way.
Apr 20 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.