Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Worksheet protect with query-based table | Excel Discussion (Misc queries) | |||
another macro query - deleting a worksheet within a query | Excel Discussion (Misc queries) | |||
VB way to remove the query from a Query Table? | Excel Programming | |||
Query returning more data than will fit on worksheet with VBA DB query... | Excel Programming | |||
Linking a table in Access to a table in Excel using MS Query | Excel Programming |