Thread: Best way to interrogate database through Excel...?

Hello,

I have just downloaded and installed the SQL Drill software in the hope that it will allow me to interrogate databases I use.

Basically, I often have a situation where I need to retieve data from database tables that matches partial data I already have.

I work in the energy industry and often need to retrieve things such as customer signup date, customer name, supply status etc. etc. for a list of customer account numbers for example.

so I may have a list of several thousand customer account numbers in Excel that I need to retrieve various other bits of associated data for.

What is the best/easiest way of doing this with SQL Drill?

Many thanks in advance for any help you may be able to offer.

Hi AJ

Hope all is cool.

One way that might be pretty good would be to set up a forumula that would dynamically build a SQL statement - based on some cell values (for Customer Name Date, Required Field etc..). Then you would have a fast way to build the SQL statement and just paste it into the SQL Drill form. This would be a simple way but it would probably not be ideal if it is something you need to do alot.

Best way I think - if you know VBA would be to set up some new user defined functions that would retrieve the data you want based on some input parameters. For example, set up a new function GetCustomerNameByCustomerID which would accept the ID as an input and return the name. So could have something like = GetCustomerNameByCustomerID (123) in a cell and when Excel calculates - it would retrieve the data. This is a very flexible type of arrange if you set it up as you will be able to retrieve data easily.


Another way that would be good too I think would be to use VBA in conjunction with the "Embedded Query" functionality that Excel has. So you could have a macro that will dynamically build a SQL Statement / refresh an embedded query. This could be a good way I think.. Someone just recently posted up an example of this way on the forum..

Thats all I can think of. Hopefully we get a few more ideas. Hope that helps a bit in any case. Have a nice day
Al