need help to have the time difference interval

[vijaym]

Below is the raw data in scarted order
Del No Time of Call
9985528355 6/17/2010 16:21
9985528359 6/12/2010 16:21
9985528305 6/7/2010 16:21
9985528255 5/28/2010 16:21
9985523255 5/22/2010 16:21
9985523255 5/18/2010 16:21
9915528255 5/8/2010 16:21
9915523255 5/2/2010 16:21
9915523255 4/29/2010 16:21
9915523255 4/28/2010 16:21

i am looking for below calucation in Access as mention below
i hav sorted with Del no and Time of call Ascending
In duplicate column formula is =A2=A1
In Diffs Hrs column formula is =+IF(B2=TRUE,C2-C1,"")
important feild is Diff Hrs
I am able to do through sub query but while exporting this table getting error that single record can be exported
Del No Duplicate Time of Call Diff Hrs
9915523255 FALSE 4/28/2010 16:21
9915523255 TRUE 4/29/2010 16:21 24:00:00
9915523255 TRUE 5/2/2010 16:21 72:00:00
9915528255 FALSE 5/8/2010 16:21
9985523255 FALSE 5/18/2010 16:21
9985523255 TRUE 5/22/2010 16:21 96:00:00
9985528255 FALSE 5/28/2010 16:21
9985528305 FALSE 6/7/2010 16:21
9985528309 FALSE 6/12/2010 16:21
9985528355 FALSE 6/17/2010 16:21

Urgent help required.
Thanx

[NeoPa]

You say Access, but this looks very like Excel data and formulas. Can you say exactly what problem you're getting and exactly what you're doing when you get it.

Welcome to Bytes!

[vijaym]

Yes its a excel data but the same i want to do in Access and my target output is Time diff
And i am able to do in Access through sub query but while exporting this table getting error as mention below
"At most one record can be returned by this sub Query"

[NeoPa]

I'm sorry Vijay, but I'm really confused as to what you're trying to do. If you have a failure, it's a good idea to give details of exactly what you're trying. As you still haven't really explained whether it's an Excel or Access problem I can't even tell you what you need to include very easily. If it's a query (as you say) then at least we'd need the SQL of the query and the error message exactly as it appears.

[vijaym]

Hi Yes its a Access query problem
below is the query that i am trying to Execute but if its a small database query datasheet get export but while its a big database in lakhs than i gets a error as mention below in the Quotes
"At most one record can be returned by this sub Query"
Query

SELECT DatePart("h",DiffVal)  & ":"& 
        DatePart("n",DiffVal) & ":" & 
        DatePart("s",DiffVal) AS DiffVals, *
FROM (SELECT  t1.*, 
                    [t1].[Time of Call]-
                    (
                        SELECT  TOP 1 
                                Time of Call 
                        FROM    Tagging_Dump 
                        WHERE   Tagging_Dump.Del no = t1.Del no 
                        AND     Tagging_Dump.Time of Call < t1.Time of Call 
                        ORDER BY    Tagging_Dump.Time of Call DESC
                    ) AS DiffVal 
            FROM    Tagging_Dump AS t1
        ) AS Tables

I am able to get the same in Excel but due limitation of row i want in Access

[NeoPa]

There's a problem here Vijay.

Clearly somebody who was involved in designing this SQL doesn't need much help with SQL. There is some complicated (and clever) stuff in here. However, I can see that this SQL (as you've posted it) cannot run. Not because of any fundamental logic problem, but because some of the fields have spaces in their names without being enclosed in braces ([]), so they will be entirely misinterpreted by the SQL parser.

What is going on? Why is this SQL not what you have testing in the database? I cannot work with the wrong SQL.

[vijaym]

SELECT DatePart("h",DiffVal)  & ":"&
        DatePart("n",DiffVal) & ":" &
        DatePart("s",DiffVal) AS DiffVals, *
FROM    (
    SELECT t1.*,
           [t1].[Time of Call]-
           (
        SELECT   TOP 1
                 [Time of Call]
        FROM     Tagging_Dump
        WHERE    Tagging_Dump.[Del no] = t1.[Del no]
          AND    Tagging_Dump.[Time of Call] < t1.[Time of Call]
        ORDER BY Tagging_Dump.[Time of Call] DESC
        ) AS DiffVal
    FROM   Tagging_Dump AS t1
    ) AS Tables

here is the correct code earlier braces were missing as i took this as a example
my problem is that unable to export the data if its in thousands else if it is a 10 to 20 rows data easily gets exported

[NeoPa]

I've looked at this SQL and, not only do I see no problem with it, I also consider this pretty good work. Whoever produced this knew his stuff clearly. I can see no reason why there should be an error.

Can you try something for me and post the result here. I'd like you to paste this SQL exactly as is, into a new QueryDef in your database and try to execute it. Post here exactly what happens. If there is an error message then reproduce it exactly. If there is anything else that you notice, please post that too.