Re: QueryTables - Retrieving Data

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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
> spreadsheet.  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 worksheet.  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/

Relevant Pages

  • Open Access query from Excel using parameter
    ... run sql using a cell as criteria to display the matching data. ...
    (microsoft.public.excel.programming)
  • RE: Lookup Data in two seperate Spreadsheets
    ... 'Cell Formula using the OFFSET worksheet formula with multiple criteria to ... Just as VLOOKUP will give you the FIRST value it finds that meets it's ... 'Returns a reference to a range that is a specified number of rows and columns ...
    (microsoft.public.excel.worksheet.functions)
  • RE: Wild Card Vlookups
    ... 'Cell Formula using the OFFSET worksheet formula with multiple criteria to ... 'Returns a reference to a range that is a specified number of rows and columns ...
    (microsoft.public.excel.misc)
  • Re: Sorting into different worksheets from a unique value
    ... rewrote the code and posted it at the bottom. ... don't get a headache from watching the screens bounce back and forth. ... criteria. ... this IF function determines if there is something in cell A2. ...
    (microsoft.public.excel.worksheet.functions)
  • Re: mail merge filter criteria
    ... and that it will occur as soon as you have an OLE DB connection to a data source and specify "is blank" or perhaps "is not blank" as one of the criteria in your "Advanced criteria". ... is null (which in Excel appears to correspond to a cell with no text or formula in it ... The Jet SQL that is used to get data from Excel actually retrieves both ...
    (microsoft.public.word.mailmerge.fields)