Re: QueryTables - Retrieving Data

From: onedaywhen (onedaywhen_at_fmail.co.uk)
Date: 04/30/04 

Date: 30 Apr 2004 01:36:51 -0700

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' 

--
patrick <<patrick.15hst5@excelforum-nospam.com>> wrote in message news:<patrick.15hst5@excelforum-nospam.com>...
> I have created a query to download data from a sql datasource.  I have
> retrieved the correct data from the database and have it in place.  I
> now am trying to use the formula =sumproduct() to retrieve the
> necessary data that fit within two criteria.
> 
> My problem is with the way that the data is brought into the
> spread***.  The column has an eight digit account code that I need to
> reference to retrieve the dollar amount for the code and year.  The
> formula does not recognize the account code but will recognize the date
> and amount columns.  When I 'double click' a cell in the accountnumber
> column, the formula will evaluate that cell appropriately and the
> formula will work.
> 
> I need a way to bring in the raw data, formatted so that any formula
> can read and retieve data at will.  Previously we used an Access
> database to retieve the data, save it as a ascii-text format then bring
> the data into the work***.  That is too time consuming and tedious.
> 
> Does anyone have any experience with such a task, if so, Help Please.
> 
> Thanks so much.
> 
> 
> ---
> Message posted from http://www.ExcelForum.com/