So what you have is a spreadsheet containing a dataset of 500 rows and 3
columns, and you want to join that to a table in SQL Server and create a
pivot table?

You could use Access to link to both sources (Excel dataset and SQL Server
table) and create a crosstab query. You will have to be careful of datatype
errors though, spreadsheets can be a bad choice for storing data sometimes.

MH

"steveh" wrote in message
...
Yes. For one, it is a proprietary database that does not allow any data to
be written to it outside of the application. If I were to use another
database, it seems like I would run into the same issue of essentially not
being able to connect to two different databases. Or am I perhaps missing
something (which is what I hope)?

There are ways to enter the data into their database via the application,
but it would require them to go to many different windows instead of being
able to enter the data all on one screen.

There are other workarounds I could use. As a last resort, I could scrap
the pivot table and just subtotal the data instead. But I'm leaning
towards using a select/case function in the query. There is not a great
deal of data they would enter on the spreadsheet (no more than 500 rows
and three columns), but I'm not sure how slow that would make the query.

Steve


MH wrote:
Is there any reason you are not storing this data in a database?

MH

"steveh" wrote in message
...
In Access, it is easy to create a table, let a user add data to it, and
include that table in a query joined to one or more tables in a SQL
Server database.

Is it possible to do something similar with Excel, using a separate
worksheet in the same workbook instead of an Access table? If so, what
would be the basic steps? (I have no problems querying the SQL Server
database.)

I would like to create an outer join to the worksheet data. The results
would be used in a pivot table. If it matters, I'm using Excel 2003.

Thanks for looking!

Steve