Re: Days between calculation

From: MGFoster (me_at_privacy.com)
Date: 02/26/05 

Date: Sat, 26 Feb 2005 03:12:52 GMT

MarkW wrote:
> Need help calculating 'days between' in a query.
> Example querytable looks like this:
>
> Name Last Used Date
>
> Mark 01/01/2005
> Mark 10/01/2005
> Mark 15/01/2005
>
> any ideas on
> how to calculate days bwteen dates when it is the same field so it looks
> like this
>
> Name Last Used Date Days Between
>
> Mark 01/01/2005 0
> Mark 10/01/2005 9
> Mark 15/01/2005 5
>
> any help is appreciated.Tks
>

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Something like this:

SELECT [name], [last used date],

   DateDiff("d",
   (SELECT Max([last used date] FROM table_name
    WHERE [name] = a.[name] AND [last used date] < a.[last used date]),
    [last used date]) As [days between]

FROM table_name AS a 

-- 
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/AwUBQh/pMIechKqOuFEgEQJrDQCgvFXFY4ia1PSyrVk8vwHOOzxpF/YAnikz
ePw6RO6kbgEtFaneNXZbsP2I
=Jnm0
-----END PGP SIGNATURE-----