SQL Excel

Hi,
Newbie at this. I am using Excel 2007, and installed the add in fine. I'm doing something simple, but just need a little help. I understand from the video how to retrieve all records etc from a table, but that's not what I need.

In a nutshell, my database contains orders (similar to northwind db) it has order id, name, address, etc.

On my spreadsheet I want to retrieve order fields by entering a specific order number.

Example I input order id 100322, and it pulls in the customer name and address for that record only.

It would look very basic on my spreadsheet


ORDER ID (enter here)

(retrieved after entering order ID)
CUSTOMER NAME
CUSTOMER ADDRESS 1
CITY, STATE, ZIP

This would be just 1 record displayed at a time. What I'm doing here is printing a purchase order that is sent to a supplier. I'm using oscommerce and no expert on PHP, so I'm linking my mysql database of orders to Excel. Hope someone can assist me. Thanks very much!

Hi !

You can do that with the addin but in an indirect kind of way.

I would:

(1) Set up the SQL to import all the customers information - if they have an order
Query would be something like this (use inner joins as need to to link the tables)

Code:

Select customers.customers_firstname, customers.customers_lastname, orders.customers_id, dublinguitars.orders.orders_id From customers Inner Join orders On orders.customers_id = customers.customers_id Where orders.orders_id = 1

(2) Embed the query into a worksheet by using the "Embed" button on the main form of the addin So now you will have all the relevent records. With the embed feature you will be able to use right click and refresh to get the data uptodate.. (3) Use excel's filtering tools to filter down the database based on the order number. So you could use the Autofilter feature where you can pick the order number in the dropdown. Or you could use the advanced filter where you enter it into a cell (you can get a bit more control with the advanced filter). Good idea might be to write an excel macro to refresh the data, read the order id from the cell and then paste the fields into the purchase order. Then you could just enter the order number in a cell and hit a button to do it.. Hope that helps.. Thanks, Al