Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-access > #11997

Re: Elapsed Time Between Records

Show all headers | View raw

Am 23.08.2016 um 19:51 schrieb Mike P:
> On Tuesday, August 23, 2016 at 5:43:18 PM UTC+1, croy wrote:
>> On Tue, 23 Aug 2016 05:36:31 -0700 (PDT), Mike P
>>> Hi croy,
>>>
>>> Could you try this......?
>>>
>>> SELECT T.MyDateField, (select top 1 int( datediff("n",T.mydatefield,tblSurv.mydatefield)/60) & ":" & format(datediff("n",T.mydatefield,tblSurv.mydatefield) mod 60,"00") from tblSurv where tblSurv.mydatefield > T.mydatefield order by mydatefield) AS HHMM
>> >FROM tblSurv AS T
>>> ORDER BY T.MyDateField;
>>>
>>> Replace MyDateField with the name of your date field in tblSurv.
>>> The HHMM column will give the difference between the date in that row and the date in the next row.
>>
>> Thanks.  With my try on that, I get an error message I
>> haven't seen before:
>>
>> "At most one record can be returned by this subquery."
>>
>> ... and then
>>
>> ************************
>> SurveyDate	HHMM   *
>> #Name?		#Name? *
>> ************************
>>
>> Here is my rework of your offering:
>>
>> SELECT T.SurveyDate, (select top 1
>> int(datediff("n",T.SurveyDate,tblCountSurv.SurveyDate)/60) &
>> ":" &
>> format(datediff("n",T.SurveyDate,tblCountSurv.SurveyDate)
>> mod 60,"00") from tblCountSurv where tblCountSurv.SurveyDate
>>> T.SurveyDate order by SurveyDate) AS HHMM
>> FROM tblCountSurv AS T
>> ORDER BY T.SurveyDate;
>>
>> -- 
>> croy
> Hi croy,
>
> Seems OK to me. It seems that the "top 1" is not working which would explain the error message.
> Are there records in tblCountSurv that have identical values for SurveyDate?
> Just to prove that that is the cause could you run the query on a copy of the table with records with unique values for Survey Date?
> Perhaps you could supply the table definition for tblCountSurv to see if another field could be used for uniqueness.
>   
> I copied your sql and it ran OK in Access on my data.
>
> Mike P.
>
> 23/8/16
The top 1 clause sometimes returns more than one line as expected, 
especially when the selected fields are not unique. So insert an 
additional  unique field to the sort order to determine the order of the 
records and the top 1 expression will work correctly.

Ulrich

Back to comp.databases.ms-access | Previous | Next — Previous in thread | Find similar | Unroll thread

Thread

Elapsed Time Between Records croy <croy@invalid.net> - 2016-08-22 13:04 -0700
  Re: Elapsed Time Between Records Patrick Finucane <patrickfinucanetx@gmail.com> - 2016-08-22 13:49 -0700
  Re: Elapsed Time Between Records Ulrich Möller <knobbi38@arcor.de> - 2016-08-23 12:57 +0200
    Re: Elapsed Time Between Records croy <croy@invalid.net> - 2016-08-23 09:59 -0700
      Re: Elapsed Time Between Records Ulrich Möller <knobbi38@arcor.de> - 2016-08-24 14:14 +0200
        Re: Elapsed Time Between Records croy <croy@invalid.net> - 2016-08-24 10:52 -0700
          Re: Elapsed Time Between Records Ulrich Möller <knobbi38@arcor.de> - 2016-08-24 23:38 +0200
  Re: Elapsed Time Between Records Mike P <mikewpayne@tiscali.co.uk> - 2016-08-23 05:36 -0700
    Re: Elapsed Time Between Records croy <croy@invalid.net> - 2016-08-23 09:43 -0700
      Re: Elapsed Time Between Records Mike P <mikewpayne@tiscali.co.uk> - 2016-08-23 10:51 -0700
        Re: Elapsed Time Between Records croy <croy@invalid.net> - 2016-08-23 12:21 -0700
          Re: Elapsed Time Between Records Mike P <mikewpayne@tiscali.co.uk> - 2016-08-24 02:14 -0700
        Re: Elapsed Time Between Records Ulrich Möller <knobbi38@arcor.de> - 2016-08-24 11:25 +0200

csiph-web