A
Arvi Laanemets
Hi
Let's have an Excel workbook SQLTest with a table on Sheet1: Key, Times (in
columns A and B).
In column Key are some key values, p.e. "AAA", "BBB", "CCC"
In column Times are some time values, p.e. working hours. You may format the
column as "[hh]:mm".
Define named range XXX p.e. as Sheet1!$A1:$B10, i.e. it contains both
columns Key and Times.
Save the workbook.
On Sheet2, Create an ODBC query from range XXX like:
SELECT XXX.Key, Sum(XXX.Times) AS Hours
FROM `FilePath\SQLTest`.XXX XXX
WHERE (XXX.Key<>'')
GROUP BY XXX.Key
Now, whenever all time values on Sheet1 are less than 24 hours, the query
works OK. But when you enter times >=24 hours, for every such entry the
query adds extra 24 hours!
I tested this on 3 different computers - 2 with Office2000 and one with
Office2007 - with same result.
(Analysis Toolpack Add-In was installed and activated on all those
computers.)
Has someone had same problem before? And is there some remedy for it?
Thanks in advance!
Arvi Laanemets
Let's have an Excel workbook SQLTest with a table on Sheet1: Key, Times (in
columns A and B).
In column Key are some key values, p.e. "AAA", "BBB", "CCC"
In column Times are some time values, p.e. working hours. You may format the
column as "[hh]:mm".
Define named range XXX p.e. as Sheet1!$A1:$B10, i.e. it contains both
columns Key and Times.
Save the workbook.
On Sheet2, Create an ODBC query from range XXX like:
SELECT XXX.Key, Sum(XXX.Times) AS Hours
FROM `FilePath\SQLTest`.XXX XXX
WHERE (XXX.Key<>'')
GROUP BY XXX.Key
Now, whenever all time values on Sheet1 are less than 24 hours, the query
works OK. But when you enter times >=24 hours, for every such entry the
query adds extra 24 hours!
I tested this on 3 different computers - 2 with Office2000 and one with
Office2007 - with same result.
(Analysis Toolpack Add-In was installed and activated on all those
computers.)
Has someone had same problem before? And is there some remedy for it?
Thanks in advance!
Arvi Laanemets