Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
MS Query Alternative
Hi,
I'm bound to Excel for purposes of making my output usable by normal human beings and I generally use MS Query to query our data warehouse (SQL Server). The thing is that Microsoft has totally neglected MS Query for well over a decade. It is primitive, inflexible and just doesn't do the job. Presuming that nobody has heard MS saying that they're going to either dust it off or replace it, so failing that has anybody found a better alternative as an excel add in? Happy Christmas all. Tom. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
MS Query Alternative
I notice no one has reponded to your request. So, here's my attempt to help.
Have you tried using MS Access unstead of MS Query. MS Access in far more superior than MS Query. I'm running 2007 & use it all the time. If you've never used Access, LINK to your SQL tables though an ODBC connection. Create (or copy/paste) your queries in Access, then import your data into Excel using the From Access connection wizard. The same way your importing the data now with MS Query. If you haven't already, try using the SQL server wizard (From Other Sources in 2007)? My not sure how it works, I don't have direct access to the SQL server. -- Thanks, Kevin "mr tom" wrote: Hi, I'm bound to Excel for purposes of making my output usable by normal human beings and I generally use MS Query to query our data warehouse (SQL Server). The thing is that Microsoft has totally neglected MS Query for well over a decade. It is primitive, inflexible and just doesn't do the job. Presuming that nobody has heard MS saying that they're going to either dust it off or replace it, so failing that has anybody found a better alternative as an excel add in? Happy Christmas all. Tom. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
MS Query Alternative
If you've never used Access, LINK to your SQL tables though an ODBC
connection. Hi, Could you give detailed instructions on how to do this? There is File - Import Data - Link Tables but I don't think this is what you mean. I use MS Query all the time from Excel 2003 to query our ERP database (Informix SQL) through an ODBC connection. While I have not run into the limitation the OP talks about, I'm always open to new alternatives that may be better. "AFSSkier" wrote: I notice no one has reponded to your request. So, here's my attempt to help. Have you tried using MS Access unstead of MS Query. MS Access in far more superior than MS Query. I'm running 2007 & use it all the time. If you've never used Access, LINK to your SQL tables though an ODBC connection. Create (or copy/paste) your queries in Access, then import your data into Excel using the From Access connection wizard. The same way your importing the data now with MS Query. If you haven't already, try using the SQL server wizard (From Other Sources in 2007)? My not sure how it works, I don't have direct access to the SQL server. -- Thanks, Kevin "mr tom" wrote: Hi, I'm bound to Excel for purposes of making my output usable by normal human beings and I generally use MS Query to query our data warehouse (SQL Server). The thing is that Microsoft has totally neglected MS Query for well over a decade. It is primitive, inflexible and just doesn't do the job. Presuming that nobody has heard MS saying that they're going to either dust it off or replace it, so failing that has anybody found a better alternative as an excel add in? Happy Christmas all. Tom. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
MS Query Alternative
Marcotte,
1st thing you need to do is go to your MS Access data: €¢ Link (do not import) to your data source tables through your odbc connection. €¢ Create your query in Access. 2nd, in Excel: €¢ Go to Data, Import External Data, Import Data. €¢ File name: Access Databases (*mdb; *.mde) or **(use alternative below) €¢ Find & select you Access Database file. €¢ Select your query or table within Access. €¢ Select the cell in Excel where you want to import the data ** An alternative to the File name process above, you can also select "MS Access Database (not sharable).dsn. The rest is the similar to above. Create your cell reference filtering criteria the same way you do with MS Query. €¢ Edit Query, Add Criteria, Enter the user parameter prompt like [Enter Customer ID]. File, Return Data to MS Office Excel, Parameters. €¢ Get value from the following cell, choose cell in Excel, Ok. If you choose "Refresh automatically when cell value changes", the Excel spreadsheet will be refreshed with filtered data based on the cells criteria. I hope this help you, Kevin -- "Marcotte A" wrote: If you've never used Access, LINK to your SQL tables though an ODBC connection. Hi, Could you give detailed instructions on how to do this? There is File - Import Data - Link Tables but I don't think this is what you mean. I use MS Query all the time from Excel 2003 to query our ERP database (Informix SQL) through an ODBC connection. While I have not run into the limitation the OP talks about, I'm always open to new alternatives that may be better. "AFSSkier" wrote: I notice no one has reponded to your request. So, here's my attempt to help. Have you tried using MS Access unstead of MS Query. MS Access in far more superior than MS Query. I'm running 2007 & use it all the time. If you've never used Access, LINK to your SQL tables though an ODBC connection. Create (or copy/paste) your queries in Access, then import your data into Excel using the From Access connection wizard. The same way your importing the data now with MS Query. If you haven't already, try using the SQL server wizard (From Other Sources in 2007)? My not sure how it works, I don't have direct access to the SQL server. -- Thanks, Kevin "mr tom" wrote: Hi, I'm bound to Excel for purposes of making my output usable by normal human beings and I generally use MS Query to query our data warehouse (SQL Server). The thing is that Microsoft has totally neglected MS Query for well over a decade. It is primitive, inflexible and just doesn't do the job. Presuming that nobody has heard MS saying that they're going to either dust it off or replace it, so failing that has anybody found a better alternative as an excel add in? Happy Christmas all. Tom. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Alternative to Concatenate | Excel Discussion (Misc queries) | |||
Alternative to IF | Excel Worksheet Functions | |||
alternative cells | Excel Worksheet Functions | |||
If alternative | Excel Worksheet Functions | |||
Any alternative to Microsoft Query? | Excel Discussion (Misc queries) |