Excel Query

I am trying to add a field reference or range name to the selection criteria in an excel query but don't see a way to do so. Here is the simple sql statement:

SELECT `Test Eq`.`Site ID`, `Test Eq`.Tag, `Test Eq`.Make, `Test Eq`.Model, `Test Eq`.Serial, `Test Eq`.Location, `Test Eq`.Belts, `Test Eq`.`Belts Qty`, `Test Eq`.Filters, `Test Eq`.`Filters Qty`
FROM `Test Eq.csv` `Test Eq`
WHERE (`Test Eq`.`Site ID`=100004)

I'd like the WHERE to read  `Test Eq`.`Site Id`= `nameofcurrentworkbook`.`nameofrange`

Is this possible?
jbervinAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

yo_beeDirector of Information TechnologyCommented:
Are you dealing with 2 tables?
From the looks of your Where Clause it does.  you need to create a join between the two tables

For example

Select  *
From table1 as t1 inner join table2 as T2 on t1.id = t2.id
where  t1.id = '100004'
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PortletPaulfreelancerCommented:
I believe it can be done but through a VBA macro that allows you to combine spreadsheet cell values into the sql.

Here is a previous answer to a similar requirement that might assist that contains a vba script  that constructs a sql string.
http://www.experts-exchange.com/Database/Oracle/Q_23717335.html#a22432717

Using this "dynamic sql" approach you can concatenate spreadsheet cell values into the sql.

---------
regarding the use of an inner join, I think your sql query is using an alias rather than a second table hence a join won't be needed.

---------
I would suggest you try to avoid using names that contain spaces. For example the alias "Test Eq" would be easier as just "T"

Avoiding spaces in names applies to column names too.

--------------
I don't think backticks will be permitted in VBA (they are used in MySQL queries) but if you are reading from a CSV file as if it is a table then MySQL syntax isn't relevant.
0
Ejgil HedegaardCommented:
In MS Query you can assign a criteria to a parameter, and link the parameter to a cell in the worksheet.
Give the parameter a name like SiteID, and enclose in [ ] brackets, like this [SiteID].
When you do that, MS Query ask for the criteria value.
Fill in and the table in MS Query update.
Return the result to Excel.
Right click the table, select Table, and Parameters, and assign the parameter name to the worksheet cell with the value.
Activate automatic update when the cell value change, and then the query runs when the cell value change.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

jbervinAuthor Commented:
Thank you both for your help. I can't use VB script because of some server to tablet limitations, but i will try the assignment approach.
0
jbervinAuthor Commented:
To Yo BEE: thanks for your response, but it really doesn't address the problem: I don't want to have to enter the site id, i want to get it from a cell on the worksheet.
0
jbervinAuthor Commented:
Not sure if the one I awarded most points to will work, but i want to close the question.
0
yo_beeDirector of Information TechnologyCommented:
@JBERVIN
I am not sure what you mean by enter the SiteID

Do you have an example of the data you are trying to query?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.