QueryTables - Retrieving Data

[patrick]

I have created a query to download data from a sql datasource. I hav
retrieved the correct data from the database and have it in place.
now am trying to use the formula =sumproduct() to retrieve th
necessary data that fit within two criteria.

My problem is with the way that the data is brought into th
spreadsheet. The column has an eight digit account code that I need t
reference to retrieve the dollar amount for the code and year. Th
formula does not recognize the account code but will recognize the dat
and amount columns. When I 'double click' a cell in the accountnumbe
column, the formula will evaluate that cell appropriately and th
formula will work.

I need a way to bring in the raw data, formatted so that any formul
can read and retieve data at will. Previously we used an Acces
database to retieve the data, save it as a ascii-text format then brin
the data into the worksheet. That is too time consuming and tedious.

Does anyone have any experience with such a task, if so, Help Please.

Thanks so much

 

[onedaywhen]

You may benefit from changing your approach. Rather than get lots of
data in Excel then filter it using cell formulas, pass your criteria
to the data engine so that it only returns the data you need. The best
way to do this is to create a stored procedure on the server and pass
your criteria as parameters but this depends on your DBMS product
('sql datasource' is particularly vague; if you mean SQL Server then
say so).

Here's my standard SQL Server example:

CREATE PROCEDURE MyStoredProc (@start_date DATETIME, @end_date
DATETIME) AS
SELECT RefID, DateEffective, Earnings
FROM EarningsHistory
WHERE DateEffective BETWEEN @start_date AND @end_date

Then this in the SQL window of MS Query:

EXEC MyStoredProc '01 JAN 2002', '01 JAN 2003'