Thread: Is SQLDrill for Me ... ?

Hello,

I am wondering if I can use SQLDrill to fill a need. We currently use a backend SQL Database to handle our financials, inventory, service, projects, etc. Unfortunately, it does not provide a very good mechanism for using automation technologies and barcodes to perform physical counts, sales, receipts, etc.

What we do today is we export (using a tool) the data from the SQL Database into Excel. This contains differnet things, but for the purposes of my example, we'll use inventory items. So, I'll end up with an excel sheet that contains, by row, part number, location, description, quantity, price, etc.

We then perform our counting function with a standard wireless barcode reader and dump that same data into another sheet in excel. Using various vlookup functions in Excel, it compares the counted data (via the barcode reader) to the quantities in the dump worksheet and returns overages/underages/etc.

The problem is this is both cumbersome and static. Everytime we wish to do a count, we need to export the data, save it to a specific file name, and note what we want to count via a copy/paste. Not only that, but the counts are based on the point in time of the export.

What I would like is to be able to do something along these lines:

Sheet1 : Data from Barcode Scanners will be dumped here
Sheet2 : A location will be input in one cell and SQLDrill will hit the SQL database and return the items that match that location along with their part numbers, description, quantities, etc. A simple COUNT Excel formula will be used to count items in Sheet1 based on the part number returned by SQLDrill

I then want to use some conditional formating and simple math to highlight under/over issues.

The idea is that this way, the data is coming straight from the database. I don't have to worry that my export is dated and might not be accurate, nor do I need to waste the time performing it and linking everything together.

So, my question:

Can SQLDrill do this relatively easily?

If so, any suggestions would be greatly appreciated.

Thanks in advance!

Hi Jay

Sorry for the delay in replying


For linking a query to a cell you could do this if you use an embedded query (embedded into an excel worksheet).

you need to use a small VBA macro to do this.

The basic idea, is that you use SQL Drill (or MS Query) to setup a query and then user the "Embed" option to embed the query into an excel worksheet.

The query that is embedded will be static.. So the next thing is to add in a small VBA macro that will allow you to dynamically update the query. This macro is then typically attached to a button in a worksheet - so the button will update the SQL Statement (based on cell values) and then update the results on the worksheet.

Please let me know if you need help in setting this up. I will try to help out as much as I can.

Thank