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 |
|
researcy
Starting Member
6 Posts |
 Posted - 2007-11-13 : 16:15:58
|
| First off I'm using the MS Query with Excel. I have a potential list of 1000 account numbers. I'm trying to set up my query so it will only pull data back when it finds a match on one or more of a list of 30 account numbers. I can create the list of 30 in Excel, but how can I set up my query to look at these 30 and not pull all of the accounts. Hope this makes sense. |
|
|
researcy
Starting Member
6 Posts |
Posted - 2007-11-13 : 16:56:02
|
| Just an update, with some more info. I know I can add a clause like WHERE vendor.vendor_id=$156 Or vendor.vendor_id=$84 Or vendor.vendor_id=$1256but the list may change and I would like to be able to tell the query where to look to find these numbers. If I can direct it to an excel sheet, then I could add and remove account numbers as needed. |
 |
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2007-11-13 : 17:01:17
|
Option1:--You have to set up a parameterized query.--To do this, edit your existing query and change the criteria for the account numbers to a  (with brackets)--Then run the query (enter in any account number the first time).--When the query is returned, go to the query parameter icon on the toolbar and open that up. There you have the option of pointing the parameter to a cell in excel and specifying how and when you want the data refrehsed (on open, on cell change etc).--In the cell you point it to, you want to concatonate all of your account numbers into an IN() statement. be sure to format the account numbers properly (with single quotes for text etc).--Once the cell has a proper IN() statement, this can become your criteria using the parameter.Option2--Create a named range in the excel file for the list of 30 accounts.--IN the MS Query, select ADD tables and point to the excel file (this may be tricky..but it can be done, I have done it many times).--Once you point the existing query to the excel file, you can JOIN the table already there on Account number, thus making it a query based on the join.I use MS Query to feed a lot of reports, so I have tried and implemented both of the above.
Poor planning on your part does not constitute an emergency on my part. |
|
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2007-11-13 : 17:02:05
|
quote:
Originally posted by researcy
Just an update, with some more info. I know I can add a clause like WHERE vendor.vendor_id=$156 Or vendor.vendor_id=$84 Or vendor.vendor_id=$1256but the list may change and I would like to be able to tell the query where to look to find these numbers. If I can direct it to an excel sheet, then I could add and remove account numbers as needed.
You can set up parameterized queries which hook into excel cells. See my last response..we overlapped in posting.
Poor planning on your part does not constitute an emergency on my part. |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
|
|
|
|
|
Pull data from select criteria