Using access to store data for excel dashboard
- Thread starter Kamarlon
- Start date
Hi Kamarlon,
Yes I do and I beleieve that this is an excellent approach to take.
An Access database is the best place to store and enrich large sets of data. An Excel spreadsheet is one of the most flexible and efficient tools for presenting data. Combining the two gives you the best of both worlds.
For very large datasets, I store the data in Access tables and use queries to enrich and summarise the data if necessary. I then use Pivot Tables and Pivot Charts in Excel to pull the data from the relevant Access table and create my dashboards.
Advantages
I would recommend "The Excel Analyst's Guide to Access" by Michael Alexander to anyone wanting to work in both Excel and Access.
And his blog: http://www.datapigtechnologies.com/blog/
Yes I do and I beleieve that this is an excellent approach to take.
An Access database is the best place to store and enrich large sets of data. An Excel spreadsheet is one of the most flexible and efficient tools for presenting data. Combining the two gives you the best of both worlds.
For very large datasets, I store the data in Access tables and use queries to enrich and summarise the data if necessary. I then use Pivot Tables and Pivot Charts in Excel to pull the data from the relevant Access table and create my dashboards.
Advantages
- Large data sets are stored in Access rather than Excel - therefore avoiding huge and unstable Excel files
- Enrichment is performed in Access which reduces the risk of user error (eg overtyping formulae etc)
- Calculation is much quicker
The data can even be stored in cloud based databases and pulled down into Excel
Disadvantages
Excel is a universally accepted tool, most people in business are comfortable with using it to at least a basic level. However Access remains under used in business and is seen as a bit of a mystery by most.
I would recommend "The Excel Analyst's Guide to Access" by Michael Alexander to anyone wanting to work in both Excel and Access.
And his blog: http://www.datapigtechnologies.com/blog/
dan_l
Active Member
I really like using access for my projects. In addition to Clarity's comments, I'd add the following to the advantages:
-It's almost always easier to do prep work on your data using Access than Excel. This applies especially to large data sets.
-When you get cute with the automation options and SQL, there's several simple dashboard elements that can virtually make themselves.
-Maybe it's just me, but it seems that using access (or any DB, really) drastically reduces development time. Your mileage may vary.
To get started:
Learn about MS Query (http://office.microsoft.com/en-us/excel-help/use-microsoft-query-to-retrieve-external-data-HA010099664.aspx)
Learn about Paramater Queries (http://office.microsoft.com/en-us/access-help/queries-iii-create-parameter-queries-that-prompt-users-for-input-RZ001097065.aspx)
Mike Alexander's book has several other ways to skin the cat and MS Query is quite depreciated at this point, but it's still a really simple and fairly effective approach.
-It's almost always easier to do prep work on your data using Access than Excel. This applies especially to large data sets.
-When you get cute with the automation options and SQL, there's several simple dashboard elements that can virtually make themselves.
-Maybe it's just me, but it seems that using access (or any DB, really) drastically reduces development time. Your mileage may vary.
To get started:
Learn about MS Query (http://office.microsoft.com/en-us/excel-help/use-microsoft-query-to-retrieve-external-data-HA010099664.aspx)
Learn about Paramater Queries (http://office.microsoft.com/en-us/access-help/queries-iii-create-parameter-queries-that-prompt-users-for-input-RZ001097065.aspx)
Mike Alexander's book has several other ways to skin the cat and MS Query is quite depreciated at this point, but it's still a really simple and fairly effective approach.
Dan_I: Thank you for further insight. Found the website useful. Following Clarity's comment I downloaded an Access Tutorial Video and have been watching to reaquaint myself with Access. I also got Mike's book, which I'll go through a I have a little concern.
Let me ask though in Access, do you have a table for each KPI or one table with all the KPIs? Do you enter actual and target data?
Let me ask though in Access, do you have a table for each KPI or one table with all the KPIs? Do you enter actual and target data?
dan_l
Active Member
Hiya Kamarlon,
Typically, any KPI or metric that can be made into columns and rows, I'll put into access. I do this because I'm fundamentally lazy and I'm prone to losing things
So in many cases, I have a table called say, "sales". I have an additional table called "daily_sales_targets". Depending on the dashboard element, I'll figure out how it needs to end up in my workbook. Sometimes it's a query that combines both. Sometimes it's just one or the other.
I think I know where you're heading here, so I'll just preempt it: don't fear having multiple queries in the same workbook. It seems counter intuitive, because if you're working with your data just in excel you're probably trying to minimize the number of data sources. When you're using a db, it's safe to have a couple of things going on.
This is a good question. Maybe chandoo should think of a post on this?
Typically, any KPI or metric that can be made into columns and rows, I'll put into access. I do this because I'm fundamentally lazy and I'm prone to losing things
So in many cases, I have a table called say, "sales". I have an additional table called "daily_sales_targets". Depending on the dashboard element, I'll figure out how it needs to end up in my workbook. Sometimes it's a query that combines both. Sometimes it's just one or the other. I think I know where you're heading here, so I'll just preempt it: don't fear having multiple queries in the same workbook. It seems counter intuitive, because if you're working with your data just in excel you're probably trying to minimize the number of data sources. When you're using a db, it's safe to have a couple of things going on.
This is a good question. Maybe chandoo should think of a post on this?
Dan: Thanks. It's just so unbelievable how different persons go about things in different ways. I was not thinking at all about having a table for each KPI, but this is another approach I can consider.
I would have to find a way to make it easy for users to enter the data in the different tables as there are like 30 KPIs to be entered by different users.
I think you did preempt me by saying its ok to have several queries going, so thanks for that.
You have me thinking like crazy now. My other question for you is. In my research there are several ways to export data from access to excel. Which method do you use and why?
You read these things in books but its different when you talk to a user of the softwares. Thank you very much for your insights so far.
I would have to find a way to make it easy for users to enter the data in the different tables as there are like 30 KPIs to be entered by different users.
I think you did preempt me by saying its ok to have several queries going, so thanks for that.
You have me thinking like crazy now. My other question for you is. In my research there are several ways to export data from access to excel. Which method do you use and why?
You read these things in books but its different when you talk to a user of the softwares. Thank you very much for your insights so far.
dan_l
Active Member
Maybe post how the KPI's are entered now?
The nice thing about using access in general is that you have quite a bit of flexibility in how you store your data. It's perfectly reasonable to have what would be a nightmare of a collection of data in Excel and make everything right with the world with your query.
Usually, I use MSquery. More likely than not it probably solves your needs without much trouble. In particular, I like the fact that I can set up a parameter to be linked directly with a cell. The query automatically updates when changes occur to that cell. Not like you couldn't achieve that a dozen other ways, but stuff like that eliminates some of the work in the project.
The nice thing about using access in general is that you have quite a bit of flexibility in how you store your data. It's perfectly reasonable to have what would be a nightmare of a collection of data in Excel and make everything right with the world with your query.
Usually, I use MSquery. More likely than not it probably solves your needs without much trouble. In particular, I like the fact that I can set up a parameter to be linked directly with a cell. The query automatically updates when changes occur to that cell. Not like you couldn't achieve that a dozen other ways, but stuff like that eliminates some of the work in the project.
Ok. Well I'm not familiar with what MSquery is, so I'll research that one.
With regards to how I enter the data now in excel, I have the name of the KPI spanning cell A1 to B1 (i merged the cells) (example Sales), then in A2 i have the word "Actual" and in cell B2 i have the word "Target". Then the data would be entered from row 3 downward. It's monthly data.
With regards to how I enter the data now in excel, I have the name of the KPI spanning cell A1 to B1 (i merged the cells) (example Sales), then in A2 i have the word "Actual" and in cell B2 i have the word "Target". Then the data would be entered from row 3 downward. It's monthly data.
I think I'll be holding off on Northwind for now. Won't make a decision right now. But thanks for your offer of assistance. It's appreciated.
Dan I wanted to ask you, when you enter the data in Excel using SQL is the data automatically updated when you add data in Access. Example say in Acces you have a table for actual sales figures up to January. You proceed to go in Excel and import that data. Come February and you go back in Access to update the sales data, do you have to again go into Excel and Import all the data upto and including February? Or is the system smart enough to know you added additional figures in Access which is then automatically updated in Excel? Hope I explained myself clearly.
Dan I wanted to ask you, when you enter the data in Excel using SQL is the data automatically updated when you add data in Access. Example say in Acces you have a table for actual sales figures up to January. You proceed to go in Excel and import that data. Come February and you go back in Access to update the sales data, do you have to again go into Excel and Import all the data upto and including February? Or is the system smart enough to know you added additional figures in Access which is then automatically updated in Excel? Hope I explained myself clearly.
NARAYANK991
Excel Ninja
Hi ,
An import is a manual activity , which will have to repeated as and when you want to do so.
However , if you can set up the connection between the data in Excel and the data in Access , then you can specify how and how often the refresh should happen.
1. http://stackoverflow.com/questions/630766/using-excel-as-front-end-to-access-database-with-vba
2. http://mc-computing.com/Databases/LinkToExcel.html
3. http://office.microsoft.com/en-us/excel-help/about-exchanging-data-between-excel-and-access-HP005234970.aspx
4. http://www.connectionstrings.com/excel
5. http://office.microsoft.com/en-us/excel-help/overview-of-connecting-importing-data-HP010201710.aspx#BMunderstanding_data_connections
6. http://www.tek-tips.com/faqs.cfm?fid=5844
The above links may be helpful.
Narayan
An import is a manual activity , which will have to repeated as and when you want to do so.
However , if you can set up the connection between the data in Excel and the data in Access , then you can specify how and how often the refresh should happen.
1. http://stackoverflow.com/questions/630766/using-excel-as-front-end-to-access-database-with-vba
2. http://mc-computing.com/Databases/LinkToExcel.html
3. http://office.microsoft.com/en-us/excel-help/about-exchanging-data-between-excel-and-access-HP005234970.aspx
4. http://www.connectionstrings.com/excel
5. http://office.microsoft.com/en-us/excel-help/overview-of-connecting-importing-data-HP010201710.aspx#BMunderstanding_data_connections
6. http://www.tek-tips.com/faqs.cfm?fid=5844
The above links may be helpful.
Narayan
I want to ask another question. Tell me what you think of this idea. I'm thinking to use infopath to create a user interface in sharepoint,users would use this to enter the kpi data. Have infopath send the data to access. The data will then be exported to excel from access to create the dashboard. The dashboard will be uploaded to sharepoint for users to see. Is this too much? Can it be made simplier. The thing is I don't want users trafficking in the excel sheet and entering data. additionally most persons are not familiar with access, so that's why i'm thinking of creating the interface in infopath. What do you think?
dan_l
Active Member
Well. Wait. Let me be precise:
In my experience, sharepoint adds a layer of issues and quirks that, for somebody who isn't a Sharepoint expert, can be a bit frustrating. I would much rather just plop everything on a network drive somewhere.
Just a comment: people don't need to be real familiar with access. You can just put together a quick switchboard and nobody will ever have to realize that they're using it.
In my experience, sharepoint adds a layer of issues and quirks that, for somebody who isn't a Sharepoint expert, can be a bit frustrating. I would much rather just plop everything on a network drive somewhere.
Just a comment: people don't need to be real familiar with access. You can just put together a quick switchboard and nobody will ever have to realize that they're using it.
Dan: I just finished watching some videos on switchboards. I think that's exactly what I wanted and it can be used.
I want your opinion on something. Now remember I said there are like 30 KPIs. Now lets say there are 5 users, each updating 6 KPIs monthly. I'm trying to think how I would make the switchboard look.
I can only think to have a button for each KPI, so there will be a long list of KPIs or to have them arranged in like 6 rows and 5 columns. The user would then click on the KPI which he/she has responsibility to update. The challenge I see with this is that after updating one KPI the user will have to go back to the switchboard to click the 2nd KPI. After updating the 2nd KPI he will then go back to the dashboard to click on the 3rd KPI. I think this is a bit tedious. I surely wouldn't want to do it.
If you utilize a switchboard how do you arrange your commands? What's the best way to do it? Is the way I described the best way?
I want your opinion on something. Now remember I said there are like 30 KPIs. Now lets say there are 5 users, each updating 6 KPIs monthly. I'm trying to think how I would make the switchboard look.
I can only think to have a button for each KPI, so there will be a long list of KPIs or to have them arranged in like 6 rows and 5 columns. The user would then click on the KPI which he/she has responsibility to update. The challenge I see with this is that after updating one KPI the user will have to go back to the switchboard to click the 2nd KPI. After updating the 2nd KPI he will then go back to the dashboard to click on the 3rd KPI. I think this is a bit tedious. I surely wouldn't want to do it.
If you utilize a switchboard how do you arrange your commands? What's the best way to do it? Is the way I described the best way?
dan_l
Active Member
Well, I guess it depends on what the KPI's are. If it's a single value 30 times over (or 6 per person) you could probably consolidate it into one form. If it's 100 lines probably having an exclusive data entry environment is preferred.
But yeah: I think it would still be tedious. But then again, so would any other method. 30 kpi's worth of work is, after all, 30 kpi's worth of work.
Although, if these kpi's share enough commonality, nothing would stop you from having one data entry form with a drop down box.
But yeah: I think it would still be tedious. But then again, so would any other method. 30 kpi's worth of work is, after all, 30 kpi's worth of work.
Although, if these kpi's share enough commonality, nothing would stop you from having one data entry form with a drop down box.
I just saw a video which shows that Access 2010 has what is called a Navigation Form. I believe it replaces the switchboard. I'm thinking it can be used. I'll have the list of KPIs on buttons to the left and when they click they get to view the form to enter the data for that KPI. So there will be no need for them to have to click a button to get back to the list of KPIs. The list will always be there to the left.
I forgot to mention that the end user wants to enter the Corporate Objective and the Balance Score Card (BSC) perspective which the KPI falls under. Example say the KPI is "Sales" it is linked to the Corporate Objective of "Increasing revenue by 12% by December 2012" and the Balance Score Card Perspective is "Financial"
Now I don't want the person entering the monthly data to have to input this all the time since its not something that changes. Is there a feature when you create a table that the information such as the objective and BSC Perspective would just repeat from the row above. I would like when the persons are in the form view they already see that the KPI is linked to the Financial perspective and the objective of increasing revenue.
Hope I explained myself well.
I forgot to mention that the end user wants to enter the Corporate Objective and the Balance Score Card (BSC) perspective which the KPI falls under. Example say the KPI is "Sales" it is linked to the Corporate Objective of "Increasing revenue by 12% by December 2012" and the Balance Score Card Perspective is "Financial"
Now I don't want the person entering the monthly data to have to input this all the time since its not something that changes. Is there a feature when you create a table that the information such as the objective and BSC Perspective would just repeat from the row above. I would like when the persons are in the form view they already see that the KPI is linked to the Financial perspective and the objective of increasing revenue.
Hope I explained myself well.