Microsoft Access

Cows Go µ

Mods, feel free to move this if I'm in the wrong place.

I'm setting up data analysis in the insurance firm I'm currently working in. Previously we would only be tracking the number of renewals/policies lost etc which could be calculated easily.

Now management want to know more in depth information and I have been doing it all manually and I feel like there is a much more efficient way of doing it.

At the moment there are 2 things we are tracking. Calls and renewals. So every day I run a report and get the data from the day before. I go through it and fix the data. For instance, I may know that A was off so B was using her phone because B's wasn't working so I change the data to reflect it. Or that A set up a policy on behalf of B so B should be getting the credit. And there are loads of those per day.

So I have 2 spreadsheets per day. One has columns labelled Name, Time, Call in/out, direct/through switch, length and another labelled Name, insurer, premium, renewal/lost etc. I had been manually going in and filtering to the info I need for each stat, and typing out the number of entries. Mostly because most of the info can be written in multiple format (renewal could be renewal, confirm renewal/direct renewal and names can have first name surname or initials or other) so doing a countif type thing is very awkward.

It's fine for daily reports but when it comes to weekly or monthly, it's not the best method. Plus if I decide after a few weeks that there is a stat I should have been tracking I have to manually re-do everything - for instance I had been getting the total number of calls per person but now they want the the number of incoming versus outgoing per person so I have to go back over a few months data to get it.

I'm wondering is there a way either in excel or access to amalgamate and manipulate all of the data? I'm thinking it will probably be access but I haven't used that in years and even then someone else had done most of the set up so I was just fixing the data. I had tried to use formulas in excel because I could link to multiple spreadsheets but they were getting very long and unwieldy but they might be the way to go

Sorry for the long post

Dravokivich

What happens when you aren't in? This is an awful lot of manual work to compile daily. If they want you to be able to performance manage on it, along with additional information or outputs, you should have them seek out a system to allow it. Are their other telephony teams in your workplace and do they need to track things like this?

Anyhow, to give you some room for an answer to steer you on it. You need a column for date. It's not clear in your OP if you have one. Once you have a date set for each entry, you can use the text formula within excel to get the month. Pivot tables filtered by Month will give you results for the period.

Example of text formula using for month. Lets just say the date is in A2.
=text(A2,"MMMM")
will give the full name. I tend to use it as:
=text(A2,"YYYY-MM")
To give the year and month, in an manner that can be sorted easier in pivot tables.

Cows Go µ

When I'm not in, it doesn't get done! I just have to do all the reports when I get in.

What used to happen was we only would check at the end of the month how many renewals were done, new policies set up or policies lost. The actual breakdown of what was actually done was never looked at. It was very general and not particularly accurate. Management now want everything analysed. I'm recording what is done by three separate teams then doing reports on individuals at the end of the month in case someone is either under performing or even that they are overdoing it.

I have a spreadsheet that has a column per day and I've been using that to add up the weekly reports and the monthly reports. Because I'm doing it manually, there could be a lot of human error.

Today I started working on excel to see if I could do some formulas. I've now formula to auto calculate some of the phone data. It's long and complex but it seems to work. It means I just have to go through and fix the data in the morning and then copy and paste the formula for the new day.

I'm hoping I can then work out a formula for adding everything up weekly and monthly.

I'm not sure if it's the most efficient way of doing it but it will definitely be faster than what I was doing

Jayd0g

Cows Go µ wrote: »

When I'm not in, it doesn't get done! I just have to do all the reports when I get in.

What used to happen was we only would check at the end of the month how many renewals were done, new policies set up or policies lost. The actual breakdown of what was actually done was never looked at. It was very general and not particularly accurate. Management now want everything analysed. I'm recording what is done by three separate teams then doing reports on individuals at the end of the month in case someone is either under performing or even that they are overdoing it.

I have a spreadsheet that has a column per day and I've been using that to add up the weekly reports and the monthly reports. Because I'm doing it manually, there could be a lot of human error.

Today I started working on excel to see if I could do some formulas. I've now formula to auto calculate some of the phone data. It's long and complex but it seems to work. It means I just have to go through and fix the data in the morning and then copy and paste the formula for the new day.

I'm hoping I can then work out a formula for adding everything up weekly and monthly.

I'm not sure if it's the most efficient way of doing it but it will definitely be faster than what I was doing

The poster above is steering you on the right track. Long term, if you need to do even further analysis then automation will be needed.

In the short term, pivot tables will change your life.

You need to have the data in a little more structured format first, which is where having the correct date beside each entry/row will help. If you're not comfortable using a formula to generate day,week,month or year for each entry from the date then do it manually.

I don't intend to be insulting by calling out the basics, I've just learnt not to make assumptions when it comes to people using Excel.

So it should look like:

Entry ID# | year | month | week number | Day | team name | employee name

or similar. Make sure to label each column by putting the name of the column in the first row of the spreadsheet. This should now allow you to slice and cut the data to show it in different ways using pivot tables.

For using pivot tables I suggest you watch a youtube video on "introduction to pivot tables" or similar. I'm on mobile or I would google it for you.

For your existing sheet with columns per individual week, for ease of use I suggest you use the transpose function to switch it to being based on rows. Personally I find this easier to pivot for the insights needed.

There are some really good training providers running excel training courses, the Intermediate Excel skills course covers a lot of what you're talking about doing. Investing now may save you a lot of stress and worry.

beauf

Do an access course and use forms to enter the data then it will be entered the same way every time.

Excel should not be used as a database. Access is a database. If you want to create reports like pivots in Excel fine. But don't store or collect the data there.

How many users will you have?

Cows Go µ

I figured I might need to use Access and a course would probably be a good idea. My knowledge of it is pretty non-existent now and it wasn't great to begin with.

There are roughly 20 people in the department split into 4 teams but we are mostly looking at the stats for just 2 of them. The data is coming from 3 places. For the counter it is a spreadsheet that is completed by anyone who goes to the counter and I type that into a spreadsheet manually. There are only about 20 odd entries per day so only takes 5 minutes.

The data is laid out:
Handler|Time In|Time Out|Time|Client|Reason

There are the policies, there are less than a hundred of those per day. There are 2 reports run to get that data - 1 that has most of the data then a 2nd for when we do stuff for other departments. The 2nd may be empty most days but still needs to be included. There is a 3rd report but it's literally only one stat. They are laid out as follows:

Client|Policy Type|Handler|Action|Premium|

Lastly it's the phones of which we get a few hundred per day. Again there are 2 reports. The main one that I only recently got access to which are the phone reports. They have never previously been used so we are still working out the kinks but they seem to be accurate at least. They have:

Time|Handler|Duration|Type

Then the second report is completed by our switch - it's the calls that we weren't available to take so the switch sends an email to call back:

Date|Time|Handler|Returned|Time Taken|

The callbacks report is the only one that is all in one spreadsheet. The others all have one per day.

I have set it up so that I have a daily report for each that now automatically pick up the information. Well, as automatically as I can make it. I've a spreadsheet each for Counter, Calls and Policies and I just copy the formula onto the next column and change the date and it gets the figures. Lots of countifs, sumifs and averageifs. I'm getting a lot better at doing those. But that's only for the daily reports and I still don't think it's the most efficient way of doing it.

Luckily all of the spreadsheets are formatted in the exact same way and labelled in the same way so hopefully that makes it easier to amalgamate them in future. All of the columns are also labelled - except there aren't any Ids

I haven't really looked into pivot tables before, I'll do some research on them and Access. Does anyone have any good online guides to get me started on understanding them?