Hi All,
This will be a long question/description so bear with me. First things first I guess I will attempt to describe what I am trying to do. Every week I am responsible for assembling and evaluating data for every machine center and then compiling that data into a "Dashboard" spreadsheet in Excel 2010. There are roughly 10-15 different performance metrics that are measured for each machine center. Currently what I am doing is running a separate query in Access 2010 for each performance metric and then pasting the results from the queries into the corresponding areas of the Dashboard. The current Access database from which the queries are written draw the information from an SQL server which has the requisite tables that all of the measurable information from the machine centers is stored. As you can imagine this is a very manual process and one that is very time consuming. On top of all of this I had 0 experience with Access or SQL prior to starting with my new company as well as very little programming experience. I know just enough stuff if VBA to make very simple programs, which honestly is not saying much. What I would like to do is make this process as painless as possible, especially for other Users who will at some point need to update the Dashboard when I am not around to do it. My vision is to have the User open the Dashboard and then hit an update "button" which then fills in the necessary information, or at least something along those lines that is simple to do and gives the User the information they are seeking.
So, now for the question(s). As I have researched the different forums and message boards the two questions I have for moving forward is:
1.) Would it be easier to pull the SQL table into a spreadsheet in Excel and then reference that table from the Dashboard to pull the information in to?
- If I do this I would like to filter the information from the SQL table to only have the information for either the past year or machine center or both before it is "pasted" into the Excel worksheet. Is this possible? The information in the SQL table goes back almost 10 years so there are A LOT of data points and it would be necessary to isolate only the information that is relavent to the current project.
or
2.) Would it be easier/more strightforward to run the Access queries from Excel?
- This was my first thought when I was given this project. The problem that I have been running into is that most of the queries use a date parameter when you run it. The query(s) when run in Access prompt the user to input a Start Date and End Date and ideally I would like to be able to do this from Excel. So far I have struck out here as all of my attempts fail when it gets to the Date parameter.
Any help or suggestions here would be MUCH MUCH appreciated.
Thanks
This will be a long question/description so bear with me. First things first I guess I will attempt to describe what I am trying to do. Every week I am responsible for assembling and evaluating data for every machine center and then compiling that data into a "Dashboard" spreadsheet in Excel 2010. There are roughly 10-15 different performance metrics that are measured for each machine center. Currently what I am doing is running a separate query in Access 2010 for each performance metric and then pasting the results from the queries into the corresponding areas of the Dashboard. The current Access database from which the queries are written draw the information from an SQL server which has the requisite tables that all of the measurable information from the machine centers is stored. As you can imagine this is a very manual process and one that is very time consuming. On top of all of this I had 0 experience with Access or SQL prior to starting with my new company as well as very little programming experience. I know just enough stuff if VBA to make very simple programs, which honestly is not saying much. What I would like to do is make this process as painless as possible, especially for other Users who will at some point need to update the Dashboard when I am not around to do it. My vision is to have the User open the Dashboard and then hit an update "button" which then fills in the necessary information, or at least something along those lines that is simple to do and gives the User the information they are seeking.
So, now for the question(s). As I have researched the different forums and message boards the two questions I have for moving forward is:
1.) Would it be easier to pull the SQL table into a spreadsheet in Excel and then reference that table from the Dashboard to pull the information in to?
- If I do this I would like to filter the information from the SQL table to only have the information for either the past year or machine center or both before it is "pasted" into the Excel worksheet. Is this possible? The information in the SQL table goes back almost 10 years so there are A LOT of data points and it would be necessary to isolate only the information that is relavent to the current project.
or
2.) Would it be easier/more strightforward to run the Access queries from Excel?
- This was my first thought when I was given this project. The problem that I have been running into is that most of the queries use a date parameter when you run it. The query(s) when run in Access prompt the user to input a Start Date and End Date and ideally I would like to be able to do this from Excel. So far I have struck out here as all of my attempts fail when it gets to the Date parameter.
Any help or suggestions here would be MUCH MUCH appreciated.
Thanks
