×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Querying database in Excel
2

Querying database in Excel

Querying database in Excel

(OP)
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

RE: Querying database in Excel

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

RE: Querying database in Excel

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

RE: Querying database in Excel

(OP)
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

RE: Querying database in Excel

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

RE: Querying database in Excel

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources