Use a database function without importing the data

P

Paul W.

Hi,
using Excel 2003

I have a sheet in which I use a database sum function.The database is
actually a CSV file.
Is there a way to access the data and display the function result without
having to import it into Excel ?

I could probably use a minature Pivot table for each result but that would
maybe a bit laborious.

I want to display and there are about 20 or 30 results.
The way it's done now is quite smart (not my idea) in that it uses a line
based data table which refers to a cell which contains the db function.The
user just has to key in one key search criteria and the result appears on
the line below.

But to do that, the data has to be pumped into a neigbouring sheet via a
reference to external data, becuase I don't know how to get my db function
to reference the CSV file. (Nor do I know if it's possible.)

So, can it be done, using the BD function to directly access the CSV file
without having to load the data into the worksheet?

Paul W.
 
B

Beege

Paul

Set up in Windows/Administrative Tools/Data Sources (OBDC)
Start with a User DSN, add Microsoft Text Driver, supply the info asked for

From EXCEL, Data/Import External Data/New Database Query, Find you CSV file,
and follow the road...

Beege
 
P

Paul W.

Yes, I know that I can do that but that's exactly what I want to avoid.
I don't want to import the data into a sheet.
I want the bdsum to read the csv file without loading it into a sheet.

Paul.
 
B

Beege

Paul

You don't have to load any data into the sheet. Use MSQuery to just supply
the sum of whatever you're looking at, no fields imported. In XL query
properties, you uncheck the use field names box to hide the "Sum of *". Sum
of, count of, avg, max and min are available there. (Sorry, I'm not VBasic
literate, or SQL for that matter)

Beege
 
P

Paul W.

OK. Thank you for your patience.
I finally understood. I was confusing two different menu items.
External data and Import external data.
I now have the data from the CSV in the Excel cells.

But
the data is being filtered on a criteria that I had to supply in MS Query.
I would prefer that the Excel user be able to key in a filter (such as the
product number) and Excel would request the filtered data (such as the
yearly sales) from the base and dispaly it on the cell below.

Is that possible ?

Paul.
 
P

Paul W.

In fact the best way of explaining what I want to do is maybe this :
I have two rows
On row 1, (the request row) for each cell, I want to input a product number.
On row 2, (the dislay row) for each product number in the cell above,
I want Excel to display the sum of sales from my CSV
database.

I have managed to do it for one column using the ? in Query which then
allowed me to refernce a cell once I arrived back in Excel.
Is there a smart way to copy the defind request & diplay into the adjacent
cells ?
I need to be able to do about 30 different requests & displays.
Or, does each one have to be defined separately in MS query ?
TIA
Paul.
 
Top