How to create a dashboard with MS Access? (1 Viewer)

[cocoralie]

Hi,

I would like to get pro month and marketing campaign the values of about 12 KPIs. This dashboard will be used by different countries/marks.
My KPIS do not have the same entries. For example :
- the customer satisfaction is calculated on the number of customers "satisfied", "unsatisfied", "acceptable",
- the percentage of corrected and on-time reports is based on the number of reports "total", "late", "with failures", "late and with failures",
- the availability of the is based on the number of hours... etc.

Do you have an idea of the way to proceed?

I created the following tables :
-Tbcampaign (ID_Campaign, Campaign name)
-TbKPI (ID_KPI, KPI Name)
-TbRegion (ID_Region, Region Name)
-TbTime (ID_Time, Month, Year)
-TbFact (ID_KPI, ID_Campaign, ID_Region, ID_Time, Value)

... but how can I proceed with the KPIs?
Thanks a lot in advance!
Co.

 

[Moniker]

Does anyone know what KPIs are and how countries/marks are related?

 

[cocoralie]

Does anyone know what KPIs are and how countries/marks are related?

Hi Moniker,

Sorry, KPI = Key Performance Indicators. For example, Customer satisfaction with the user help desk (%age), average delay for responding a complaint, etc.

In this case, countries = markts.

Thanks!

 

[Moniker]

Are you just trying to calculate a number to give to management on a regular basis?

 

[cocoralie]

Are you just trying to calculate a number to give to management on a regular basis?

Yes, several numbers, to be given each month.

 

[Moniker]

This is your original description of your tables:

-Tbcampaign (ID_Campaign, Campaign name)
-TbKPI (ID_KPI, KPI Name)
-TbRegion (ID_Region, Region Name)
-TbTime (ID_Time, Month, Year)
-TbFact (ID_KPI, ID_Campaign, ID_Region, ID_Time, Value)

Clearly, you're going to have to use TbFact as it's the only one with all the keys, but there's not enough information about what a "fact" is in this situation. You haven't really provided much info here, but in general terms, regardless of how different each market may be, you're going to have to make up some metrics with different variables that all equate to each other and add up to the same thing.

What is it you've already tried? Where are you stuck? I can come up with examples here, but not without more information. I'm not understanding how you plan on coming up with performance indicators when there's not an apples-to-apples comparison. I may be taller and thinner than the majority of people, but that doesn't mean squat if you're talking about hair length. (My GF wins that one, but she's short. )

What you need to do here is get even-ish numbers for each attribute and then it's simple math from there. The problem is that, without the same attributes associated with each location, you're going to (intentionally or not) tick some people off. Your definition of timeliness in Canada may or may not measure up with what is considered a failure in India, and so on. What can you find in common with the various regions?

 

[cocoralie]

Hi Moniker,

Thanks again (and in advance!). It will be ok to define the same criteria for the same regions, so let's assume that they are all the same.
My problem is to get the indicators.
Here are some of them :
1. Customer satisfaction with the user help desk (employees will give, pro campaign and pro month, the number of customer "satisfied", "unsatisfied" and "without opinion"
2. The average time for complaints to be dealt with (in days)
3. The difference between planned budget and effective budget
etc.

My problem is that the different indicators depend on different type of values : how can I organise that in my tables?
Thanks a lot in advance!!
Co.

 

[Moniker]

How are they different values? What am I not getting?

As best as I can tell from the description, you have a percentage, a number, and a number.

1) Satisfied, Not Satisfied, No Opinion -- a percentage of each out of the total.
2) Number of days
3) Difference between budget and actual dollars

Store each of these, take an average, and then scale it.

I don't think I'm getting this right though. I'm not sure what the problem is as the solution seems self-evident.

 

[cocoralie]

Sorry, I am just starting with Acces...
So, my problem is : in which tables do the employees enter their data?

For some KPI, there will be 3 felds (example 1 : number of satisfied, not satisfied, no opinion), and the data for the different KPI could be a number of people, a number of hours, etc.

And then, where and how do I calculate the KPIs? With requests?
Thanks!

 

[Moniker]

There are not three fields. There are three values that are stored in one field. For example, you store the field "CustomerResponse" like this:

No Opinion = 0
Not Satisfied = 1
Satisfied = 2

Then, you end up with one field that looks like this:

CustomerResponse
1
2
2
0
1
2
0

That is attached to a table that has a "ticket number" or whatever you refer to them as. The ultimate goal is to get to this:

TicketID   CustomerResponse
1          1
2          2
3          2
4          0
5          1
6          2
7          0

You then use the same ticket ID to associate budgets, times, etc.

This is a part of normalization. You do not want to store the words "Satisfied" and "Not Satisfied" multiple times as that's a waste of space. You want to store values that represent them. It's like a multiple choice quiz. You select A, B, C, or D, not the entire answer.

 

[cocoralie]

Thanks!
And what if the employees directly enter the final results, each month?
For example :
Customer satisfaction => x "satisfied", y "unsatisfied", z "no opinon"

Thanks in advance,
Co.

There are not three fields. There are three values that are stored in one field. For example, you store the field "CustomerResponse" like this:

No Opinion = 0
Not Satisfied = 1
Satisfied = 2

Then, you end up with one field that looks like this:

CustomerResponse
1
2
2
0
1
2
0

That is attached to a table that has a "ticket number" or whatever you refer to them as. The ultimate goal is to get to this:

TicketID   CustomerResponse
1          1
2          2
3          2
4          0
5          1
6          2
7          0

You then use the same ticket ID to associate budgets, times, etc.

This is a part of normalization. You do not want to store the words "Satisfied" and "Not Satisfied" multiple times as that's a waste of space. You want to store values that represent them. It's like a multiple choice quiz. You select A, B, C, or D, not the entire answer.

 

[njethwa]

Does MS Access have charting abilities?

One way to do might be pull access data using odbc in Excel and build dashboards in excel

alternatively you could you some tool like infocaptor

any other ideas for SOHO?

I am pulling quickbook information into Access and wanted to build custom reports and dashboards

 

[johanelmander457]

Hi

You can find this info by using search box in the top of website with some keywords related before posting questions.

 

[gemma-the-husky]

what do you want - a form, a report, or to populate a excel spreadsheet

either way - you need to do this logically. buld up queries that are suitable to generate each KPI. Test them to make sure they are correct. Then include them in a suitable place on your form/report etc.

you are unlikely to get a query to do this. This needs some VBA.

 

[NigelShaw]

Hi

Isn't this just a version type of Act! the sales lead, customer service program used by sales companies, teams and executives? You could get a trial copy and see how the data is displayed I guess.

Cheers

N

 

[GaryPanic]

You can build a simple Dashboard quite easliy

Firstly will it mainly be charts (simple pie charts are easy - complicated charts Access isn't too clever at)

make your qry with your recortd set

I have as an example number of quotes by contract
so contract A may have 20 quotes
Contract B 15
contract C 10
and a little pie chart as one of the elements of the dash board

then i have another take up rate (excatly the same )
you could have on income by contract

the main point is get your qry right and if you charts/graphs are not too comlipicated it should be quite easy..

 

[johanelmander457]

Hi

I read some opinions in this topic. I do not agree above ideal. We can find out some articles at about.com by using Google search.