need help to have the time difference interval

**NeoPa** · May 18 '10, 12:39 PM

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** · May 19 '10, 09:03 AM

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** · May 19 '10, 01:05 PM

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** · May 19 '10, 05:17 PM

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

Code:

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** · May 19 '10, 09:07 PM

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** · May 25 '10, 03:39 PM

Code:

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** · May 25 '10, 04:31 PM

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.

need help to have the time difference interval

need help to have the time difference interval

Comment

Comment

Comment

Comment

Comment

Comment

Comment