Querying database in Excel 2

[folk754]

I am working in designing a query tool for a flat file, and want to get any suggestions. I have a database spreadsheet with three fields, say country, product and sales. And I want to show summarized tables of this data. Is there a straight forward way to do this using combo boxes? I know about pivot tables, but the issue with them, for example is handling year-over-year growth rates, among other things.

Thanks for your advice.

Jota

 

[rush]

You can summarize the tables by running a query on the table with the data. Then display the results of the query on another sheet. You can do this with VBA also.

Bill

 

[pipewelder1999]

If you have MS access the ability exists to link to the spreadshet. The query designer in access is easier to use as are the forms for viewing/editing the data.

Its an easy for me in access compared to excel but that is due to my preference for access. I'd be glad to help if I can.

Good day
G Austin

Gerald Austin
Iuka, Mississippi

http://www.weldinginspectionsvcs.com

 

[folk754]

Austin:

Thanks for your suggestion. Unfortunately, I am designing this as a deliverable for some of the internal customers, and the issue with access is that yu need to have the application to run any queries. Unfortunately, not everybody has Access on its computer, so I might stick to Excel. Thanks again.

Bill:

I think I am gonna try your suggestion instead. I recorded a macro and got the basic coding in VBA to query the data. I will see what is the best way to program the inputs using ComboBoxes.

Regards,

Jota

 

[savbill]

Another very useful tool for getting external data is Microsoft Query. You can use this to bring live data into a spreadsheet or pivot table. Access MS Query by selecting 'Data->Get External Data>New Database Query' then follow the Query Wizard to define the datalink. After you have a query defined to a spreadsheet you can change many options using the External data Toolbar.

With MS Query you can retrieve data from an Access.MDB to Excel without having Access installed on the client machine.

You can open MS Query as a stand alone program to view and edit Database information or configure a query. Just make a link to the EXE in your MS Office folder.

"C:\Program Files\Microsoft Office\Office\MSQRY32.EXE"

With Excel Visual Basic you can run DAO or ADO directly from Excel and return the results to a spreadsheet. With DAO you will have to set the 'Microsoft DAO 3.6 Object Library' in the References attached to your Workbook to have the functionality.

Bill

 

[rush]

Another alternative for summary data if the data is already in an Excel spreadsheet is to use the sumif function. Sometimes you can accomplish a query by parameter using this function.

Bill