sql query from Excel using range

[ofcabbagesandkings]

I am given a spreadsheet with a column of item numbers that changes each week. I need to take those item numbers and compare them to the same data in another database. I can connect to the database AS400(DB2) fine and return single rows of data. Rather than using the three options from the query wizard: select by string, select by hard value or select by cell, is there a way to return multiple values such as A1:A75?

 

[Fazza]

hi

I'm not familiar with the three options described. Maybe you're in a newer Excel version than me? (Excel 2003)

Some thoughts.

1. Within MS Query can you add another table? So, one table will be the AS400(DB2) and the other will be the range of cells A1:A75 (which I assume has a header in A1). Then join the tables on the common field and the returned dataset should be only the records from AS400(DB2) matching the entries in range A1:A75. Joining the tables should be possible in the GUI or otherwise edit the SQL. SQL could be like "SELECT whatever FROM AS400, ExcelRange WHERE AS400.commonfield = ExcelRange.commonfield". Or as below, you can make a string (via worksheet formulas maybe) and paste that to an "IN (list)" construction.
2. Via the VBE (visual basic editor) edit the query tables SQL to suit. It might be like the SQL above except where I've written ExcelRange I think you'd need a fully qualified path. Or another way would be to loop through the range and create a string with all the values. If the entries are all numbers, the SQL clause will be like "SELECT whatever FROM AS400 WHERE AS400.filterfield IN (123, 345, 378, 382, 399)"

HTH

 

[ofcabbagesandkings]

Thank you FAZZA for your suggestions. To make it easy for the user I am forced to concatenate "or itmnum?" in the select statement, but I will try one of your suggestions for my own sake.