?
Solved

SQL Data in Excel

Posted on 2007-07-21
7
Medium Priority
?
193 Views
Last Modified: 2016-08-29
Hi Everyone-

I am using excel(2003 and 2007 versions) to upload to and display informtion from a SQL(2005) table.  The SQL stores budget information.  There are two main identifers in the table: 1) a Service Code and 2) a specific Project Code.  When the person uses the excel file, they upload information regarding a specific project and they go to another sheet that shows totals from a number of different projects that correspond to that Service.  Right now to display the overall budget info, I have it set up like this:
     1) I excel bring in the entire SQL table (which has about 100 rows/60 columns of data
     2) Based on a project code field that that it is in a excel cell as well as the SQL table, I have a number     of "SumIfs" formulas to populate certain cells in excel that display the overal totals for different areas for that service based on the individual projects.

Needless to say, the excel file is getting very large.  Is there a way to have these "sumifs"/queries occur on the SQL side so the only information brought it is based on the "Service" code that is selected by the user.  Also, say I want cell C6 on the "Budget Summary" sheet to display the grand total for the Service from the Office Expenses column in the SQL table, is that possible?

I hope I made sense.  Thank you for your help, this is all new to me.  
0
Comment
Question by:bclivell
  • 3
  • 2
  • 2
7 Comments
 
LVL 93

Accepted Solution

by:
Patrick Matthews earned 750 total points
ID: 19539292
Hello bclivell,

Possibly.  For example, SQL views could be created to perform some of the calculations
on the server side, and/or you could have stored procedures calculate the results, and
use ADO to retrieve those results.  Without knowing more about your specific
situation, it is hard to get more concrete than that.

Also, you may be able to use a PivotTable on the Excel side instead of SUMIF().

Regards,

Patrick
0
 
LVL 17

Expert Comment

by:inthedark
ID: 19539307
Why don't you do the Sum within the SQL....

Select Sum(MyField1) As Total1, Sum(MyField2) As Total2, Sum(MyField1) + Sum(MyField2) As TotalAll Form [MyTable] Where [Project = 'MyProject';
0
 

Author Comment

by:bclivell
ID: 19539399
matthewspatrick:-
Thank you for the quick response.  Please let me know what other information I could provide.  I am not sure how to create SQL views to make the caculations.  Thank you!

inthedark:-
Thank you for your quick response as well.  Where and how would I do what you are refering to.  Also, how would I display that info the a specific cell in Excel?  Thank you!
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 19539427
bclivell,

Well, it would be useful to see a small subset of the raw data and
the calcs you are looking to do.  Please upload a sample file to an
accessible web site, and come back here to post a link to the file.

If you do not have your own web space, you can use
http://www.ee-stuff.com.  Please be advised that you will have to
zip your file(s) before uploading if you use that service.

Regards,

Patrick
0
 

Author Comment

by:bclivell
ID: 19539495
Patrick-  I have uploaded the files.  Here are the links

View all files for Question ID: 22711552
https://filedb.experts-exchange.com/incoming/ee-stuff/4085-SQL-to-Excel-for-EE-7-21BL.zip 

Direct link to your file
https://filedb.experts-exchange.com/incoming/ee-stuff/4085-SQL-to-Excel-for-EE-7-21BL.zip 

Some cells will have "ref" errors in them, b/c I had to strip it down for privacy reasons.  The bugdet summary is where I am trying to display the info.  The SQL Data tab is where I have the table that is displayed to the SQL table.  All data in there is dummy data.  Thank you so much!!
0
 

Author Comment

by:bclivell
ID: 19539514
Patrick-
My apologies, I did not expain the budget summary page well enough.  

The "Client" section will be based on another SQL table that I have not gotten to yet so it is empty.  Under that, there are the "Office Expenses", "Vendor Expenses" and Team Fees section.  The column that is "overall" is where I am trying to fill in the grand totals for the budget info.  I hope this helps.  Thank you again!!
0
 
LVL 17

Assisted Solution

by:inthedark
inthedark earned 750 total points
ID: 19545600
If you have microsoft access, make links to your database, crerate the query you need using access.  Then click view sql and you can copy/paste into your application.
0

Featured Post

Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

599 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question