Using a worksheet as a table in an SQL query
 

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

Using a worksheet as a table in an SQL query
 

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

Using a worksheet as a table in an SQL query
 

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

Using a worksheet as a table in an SQL query
 

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

Using a worksheet as a table in an SQL query
 

If Access was an option, I would put the data in a table. They don't
have Access and don't want to buy it.

I don't think my original idea is possible. I'll probably just dump the
SQL Server data to a spreadsheet and join the two spreadsheets. I think
that should work.

Thanks for your time, MH.


MH wrote:
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

Using a worksheet as a table in an SQL query
 

If you want to do the whole thing in SQL Server you have a couple of
options:

1. Openrowset / Openquery (see BOL)
2. Use a "Linked Server"
3. Use OpenDataSource command:
SELECT *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\MyExcel.xls";
User ID=Admin;Password=;Extended properties=Excel 8.0')...Book1$

If you have any questions about the above, you should create a new post in:

microsoft.public.sqlserver.programming

MH

"steveh" wrote in message
...
If Access was an option, I would put the data in a table. They don't have
Access and don't want to buy it.

I don't think my original idea is possible. I'll probably just dump the
SQL Server data to a spreadsheet and join the two spreadsheets. I think
that should work.

Thanks for your time, MH.


MH wrote:
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