"Carl" <carl.barrett@newcastle.gov.uk> wrote in
news:1151062771.390511.276250@y41g2000cwy.googlegr oups.com:
[color=blue]
> Hi,
>
> I have a query that produces an output similar to the sample
> shown below. These records are based on certain criteria and
> a date range selected from a form. I need to create some kind
> of calcualtion or record manipulation to do the following:
>
> I need to subtract dates from one another, for example for ID
> 1 I want to find the difference between 24-Jun-04 and
> 25-Jan-06 for the 2 applications for Person1.
>
> Or, I need to find 730 days (2 years) from the newest date and
> if the second date is within this range I need the record
> displayed.
>
> This becomes even trickier if an ID has more than two
> applications as I need to use the difference bewtween dates 2
> and 3 and so on.
>
> What I basically need is to be able to calculate between 2
> dates where each is on a different record.
>
> Cheers.
>
> ID Forename1 Surname ApplicationNumber
> FinalDecisionDate 1 Person1FN Person1SN 216869761
> 24-Jun-04 1 Person1FN Person1SN
> -761922672 25-Jan-06 2 Person2FN Person2SN
> 1109951655 06-Apr-05 2 Person2FN Person2SN
> 1733776761 22-Jul-04 3 Person3FN Person3SN
> -1502540739 29-Jun-04 3 Person3FN Person3SN
> 1103313929 11-Mar-05 4 Person4FN Person4SN
> -501941607 15-Apr-05 4 Person4FN Person4SN
> -1230039876 22-Feb-06
>[/color]
Ohkayy. What you need to do is create a new query, with two
copies of your existing query or table. Alias the first as A,
the second as B.
then join A to B on personFN and PersonSN, Put the fields from A
down and the applicationNumber, finaldecisiondate from B as
well.
Now create a new field with the expression
DateDiff("d", a.finaldecisiondate, b.finaldecisiondate)
The criteria for that field should be BETWEEN 0 and 730
You also want to put <> B.applicationNumber as the criterion for
A.ApplicationNumber, so you dont compare a date agains itself.
--
Bob Quintal
PA is y I've altered my email address.
--
Posted via a free Usenet account from
http://www.teranews.com 
