+ Reply to Thread
Results 1 to 15 of 15

Excel Dashboard using Access DB

  1. #1
    Forum Contributor
    Join Date
    05-19-2015
    Location
    Karnataka, INdia
    MS-Off Ver
    MS Office 2010
    Posts
    277

    Excel Dashboard using Access DB

    Hello All,

    I am working on a project to build dashboard, i looking for a start over.

    What i am trying to achieve here is create Excel dashboard using Access DB as the raw data is huge and cannot be retained in excel.

    The Dashboard will talk about different metrics such as customer resolution, customer satisfaction, Ticket reopened Region wise, State wise, Month Wise, Week wise choosing Problem Type.

    e.g, For East region, Technical Problem type, what was the different Metrics - Customer resolution%, Ticket reopened %, Volume etc.

    Any Suggestion to start is mush appreciated. Please Note - All the data will be pulled from Access Database.

  2. #2
    Forum Contributor noboffinme's Avatar
    Join Date
    08-29-2013
    Location
    Melbourne, Australia
    MS-Off Ver
    Excel 2003/7/10/13/16/19
    Posts
    1,067

    Re: Excel Dashboard using Access DB

    Hi Biplab1985,

    Why do you want to do this?

    Is it because you are more experienced in Excel than Access & just want to use Access as a sort of Data Warehouse?

    I'd suggest you stay in Access & build your Dashboard there.

    Can you advise your experience level & why you want to do this?

    Thanks
    Remember you are unique, like everyone else

  3. #3
    Forum Contributor
    Join Date
    05-19-2015
    Location
    Karnataka, INdia
    MS-Off Ver
    MS Office 2010
    Posts
    277

    Re: Excel Dashboard using Access DB

    Hello,

    The dataset is huge to accommodate in excel and thus I want to use access to store the data, however the metrics dashboard(For which I will use VBA sql) and charting would be done on excel.

    any pointers on the start or any similar dashboard structure.

  4. #4
    Forum Contributor noboffinme's Avatar
    Join Date
    08-29-2013
    Location
    Melbourne, Australia
    MS-Off Ver
    Excel 2003/7/10/13/16/19
    Posts
    1,067

    Re: Excel Dashboard using Access DB

    OK, what you're doing is possible but you would have a plan on how to summarise the data in Access before you can import it into Excel.

    So are you working in Access to create tables of results & then using VBA (in Excel) to import those results into Excel?

  5. #5
    Forum Contributor
    Join Date
    05-19-2015
    Location
    Karnataka, INdia
    MS-Off Ver
    MS Office 2010
    Posts
    277

    Re: Excel Dashboard using Access DB

    Hello,

    Perhaps you can guide me on the approach.

    What i m trying to achieve is pull the result(Metrics like Volume, FCR etc) using list boxes regions wise, State wise in excel result boxes. For example, Customer wants to know the volume metrics for East region in the month of June.
    I have the table prepared with different raw dataset of volume, FCR% etc(Calculation is pending).

    My first step would be to put the list boxes in excel by region, state variable which will populate region value, State value and later on selection the user can get the result on different metrics.

    Let me know if you can help me to put the query for pulling the result in excel also. I can summarize the data for calculation but need help in pulling in excel thru VBA

  6. #6
    Forum Contributor noboffinme's Avatar
    Join Date
    08-29-2013
    Location
    Melbourne, Australia
    MS-Off Ver
    Excel 2003/7/10/13/16/19
    Posts
    1,067

    Re: Excel Dashboard using Access DB

    Hi Biplab1985,
    This is how I would go about it, also the link below is for an article on Transferring Data from Access to Excel using VBA.
    http://stackoverflow.com/questions/1...heet-excel-vba
    Where is the Access database kept? On your PC/Laptop, on a Network Drive at work?
    I’ll assume it’s on your PC.
    1. Create a folder on your ‘C Drive’
    2. Create your Access database, import a small amount of data as a practice table & save the Database in this folder
    3. Save an Excel Workbook in the same folder
    4. Use the link above to import the data from Access to Excel (Import your practice table from Access to an Excel Worksheet)
    5. Create a Userform in Excel
    6. Link your fields from the Userform to the imported data
    Remember your naming conventions from the start to avoid confusion.
    Let me know how you go.

  7. #7
    Forum Contributor
    Join Date
    05-19-2015
    Location
    Karnataka, INdia
    MS-Off Ver
    MS Office 2010
    Posts
    277

    Re: Excel Dashboard using Access DB

    I have attached the Reporting Structure Workbook,( sheet 1 - Coverpage and sheet2 has the sample data) that have list boxes for selection and Metrics result below.

    User will select the parameter, can be one selection or multiple to calculate different metrics, click the Generate Metrics button and result will be displayed under diff metrics header.

    How to Generate the result by VBA sql from access DB.

    Thanks!
    Attached Files Attached Files

  8. #8
    Forum Contributor
    Join Date
    05-19-2015
    Location
    Karnataka, INdia
    MS-Off Ver
    MS Office 2010
    Posts
    277

    Re: Excel Dashboard using Access DB

    Any Input?

  9. #9
    Forum Contributor noboffinme's Avatar
    Join Date
    08-29-2013
    Location
    Melbourne, Australia
    MS-Off Ver
    Excel 2003/7/10/13/16/19
    Posts
    1,067

    Re: Excel Dashboard using Access DB

    Hi,

    Have a look at the code below, it will read an SQL Statement that is on an Excel Worksheet &
    then run the query on the Database.

    You will need to create Named Ranges in Excel for 1. The Connection String
    (named in the below code as 'ConStr') and 2. The beginning of the SQL Statement ('Stmt1')

    The code below will read the SQL from the Excel Workbook line by line until it finds a clear line

    It will then run that code against the database, get the results & then Paste them into Excel at
    the Cell defined in the code as 'A2'.

    Give it a try & confirm the Transfer is working.

    Please Login or Register  to view this content.

  10. #10
    Forum Contributor
    Join Date
    05-19-2015
    Location
    Karnataka, INdia
    MS-Off Ver
    MS Office 2010
    Posts
    277

    Re: Excel Dashboard using Access DB

    Thanks for your input. Could you please help me out below -

    "You will need to create Named Ranges in Excel for 1. The Connection String
    (named in the below code as 'ConStr') and 2. The beginning of the SQL Statement ('Stmt1'')"

    e.g - User wants to know the ALL different Metrics info for Region - Keith and Month -May 2015 or Multiple Selection Region - Keith, Mark and Month - May 2015,Jun 2015.

  11. #11
    Forum Contributor noboffinme's Avatar
    Join Date
    08-29-2013
    Location
    Melbourne, Australia
    MS-Off Ver
    Excel 2003/7/10/13/16/19
    Posts
    1,067

    Re: Excel Dashboard using Access DB

    Get one statement to work before you are concerned with the whole lot.

    Can you advise if you were able to connect to the Access Database & use that VBA to place the query results into Cell 'A1' of your Excel Workbook.

    Also, I've asked before, what is your skill level & experience with Access VBA & Excel VBA?

    I can help you more specifically if I know your experience level.

  12. #12
    Forum Contributor
    Join Date
    05-19-2015
    Location
    Karnataka, INdia
    MS-Off Ver
    MS Office 2010
    Posts
    277

    Re: Excel Dashboard using Access DB

    My Knowledge with Excel VBA is intermediate but Access VBA is Novice

    How to Prepare the Named range for 1. The Connection String
    (named in the below code as 'ConStr') and 2. The beginning of the SQL Statement ('Stmt1')
    Last edited by Biplab1985; 08-17-2016 at 08:27 AM.

  13. #13
    Forum Contributor
    Join Date
    05-19-2015
    Location
    Karnataka, INdia
    MS-Off Ver
    MS Office 2010
    Posts
    277

    Re: Excel Dashboard using Access DB

    Hi noboffinme,

    Could you please help me out on understanding the building of connection string and beginning of the SQL statement.

  14. #14
    Forum Contributor
    Join Date
    05-19-2015
    Location
    Karnataka, INdia
    MS-Off Ver
    MS Office 2010
    Posts
    277

    Re: Excel Dashboard using Access DB

    Need guidance.

  15. #15
    Forum Contributor noboffinme's Avatar
    Join Date
    08-29-2013
    Location
    Melbourne, Australia
    MS-Off Ver
    Excel 2003/7/10/13/16/19
    Posts
    1,067

    Re: Excel Dashboard using Access DB

    Try looking at some examples in these links;

    This may save you from using VBA to connect to your database;
    https://support.office.com/en-us/art...ad=US&fromAR=1

    This one for named ranges;
    http://www.contextures.com/xlNames01.html

    Don't use the code I sent for the moment as the first link should get you connected to your Access database.

    Then the next step is to connect the data, now in Excel, to your Userform.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Access Database - Refreshing Linked Dashboard
    By Xcelguy in forum Access Tables & Databases
    Replies: 1
    Last Post: 11-16-2013, 07:32 AM
  2. Replies: 5
    Last Post: 11-07-2013, 02:48 PM
  3. Excel or MS Access Dashboard
    By msalib888 in forum Excel General
    Replies: 0
    Last Post: 07-01-2013, 10:54 PM
  4. Refreshing Filtered Dashboard Worksheets from ACCESS
    By CMPNH in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-25-2012, 01:02 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1