Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
toniolo
Starting Member
14 Posts |
 Posted - 2007-11-16 : 05:23:12
|
| Hi,I have a database in SQL 2000 with several tables and data.I am able to query the database fine with Transact SQL.Also, I have a spreadsheet in Excel with some parameters (ranges of numbers) that I would like to use in my queries.I am able to use the Data Query in Excel to run a query in the spreadsheet and it works great however there's a problem.How can I make the queries to capture the parametes from the spreadhseet so I don't need to type them ?Just explaining it better.The spreadsheet has 2 columns with almost 1000 rows of data. One column is the START number and the other the END.I would like to create a query per row (therefore almost 1000 queries) where the query is going to capture the START and END from the spreadsheet and give me the result I want per row.Is this possible ?I managed to achieve what I want but I need to type the parameters on the query and I can't do this 1000 times.Thanks in advance for any suggestion.Marcelo |
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2007-11-18 : 14:51:56
|
In MSQuery you have the option of adding parameters, and once returned to excel, you can specify cell locations for them.In the MS QUery you would place brackets areound the criteria so that at first run (or return to excel) you get promted. At this point just enter values to return some data.Once returned to excel, the external data toolbar will have an extra active icon with a ? mark on it. Click that and you will see the option of "get the value from a call" where you can identify either a cell location.
Poor planning on your part does not constitute an emergency on my part. |
 |
|
|
toniolo
Starting Member
14 Posts |
Posted - 2007-11-20 : 14:09:05
|
| Hi dataguruThanks a lot for your reply.I followed what you explained and it works, however it didn't solve my problem.The "get a value from a cell" only allows me to get 1 cell per time. The Wizard box shows Parameter 1 and Parameter 2 and allows me to pick one cell each, not a range.As I mentioned before, I have 1000 pairs of data and I need to get results for each one of them, at the same time.Anyway, I am not sure if it will be possible to do this in Excel.Please see this example:Column A Column B Result of Query Date Result of Query Code1000 1999 03/24/2006 17882000 2999 10/07/2006 26543000 3999 01/08/2007 32884000 4999 04/09/2007 4988My database in SQL contains information about a range of codes that are given to certain customers.For example, the range 1000 to 1999 was given to the customer ABC, the range 2000 to 2999 to the customer KHG, and so on. What I need to know is when was the LAST time certain customer used a code on its range and the LAST code the customer used.As I said before I am able to get the correct results either in a SQL Query Analyzer and even in Excel.What I mean is, I don't have any problem in creating the query.The problem is, how to create this query in Excel so I can easily query 1000 pairs of parameters ?It works fine for 1 pair.Do you have any suggestion ?Thanks again. |
|
|
|
joldham
Wiseass Yak Posting Master
300 Posts |
Posted - 2007-11-21 : 13:25:15
|
| The only way I can think of that you could get this to work is to do a little Macro coding. You will want to loop through the rows in the table and set the parameters through VBA. I would suggest moving away from using MS Query as you currently are and connect to the database through your VBA code. MS Query is good if you have one set of parameters. If you write your VBA code smartly, you can have a dynamic number of parameter sets in your worksheet. I hope this helps.Jeremy W. Oldham |
|
|
|
toniolo
Starting Member
14 Posts |
Posted - 2007-11-21 : 13:43:14
|
| Hi Jeremy Thanks a lot for your reply.I had a thought that MS Query would not be able to give me what I need. Unfortunately I don't know much about VBA in order to create this code.How about MS Access ? Do you think that the project I described above would work in Access instead ?Thanks.Marcelo |
|
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2007-11-21 : 16:06:40
|
YOu would have to set up the list of codes in a secondary table and JOIN to that table using MS Query (which might take some patience to get the hang of. (Same would be true in access, but likely easier than MS Query). Realistically, you don't have to go record by record if done right.
Poor planning on your part does not constitute an emergency on my part. |
|
|
|
|
|
|
|
|
Microsoft EXCEL and TRANSACT SQL