Generating reports to manage service level agreements, is it possible to do?

[blackrosepetals91]

Hello all,

I'm relatively knew to the world of databases - I know how to use them, but I'm now in a position where I need to start creating them for some projects at work.

One of the things I'm working on is to take data that we currently try and monitor in a spreadsheet and put the information into a database. However, within the spreadsheet we would use a lot of formulas to try and work out if service level agreements had been met.

To give context and put the whole process in relatively simple terms, it goes like this.



The company schedules meetings across the UK to different companies. Some companies may have a meeting every month, others every 2 months, and some every 3 months. The dates are scheduled in advanced. Lets say a meeting was scheduled for 03/06/2019, the staff attending have until the Friday of that week to make the meeting. Any working day thereafter, they are late.

Is it possible, to monitor this kind of SLA within Access? If it's doable then great...although I have no idea how ! But at this stage, I just want to know what I might be limited to doing.

Many thanks in advance !!!!

[Micron]

The most difficult portion, I think, is that Access doesn't have functions such as Weekday, so calculating a date based on a date + [days until Friday] would require more effort. If the date is an actual date then it's easier. You should be able to create a working, stable db if you do some up front research and thorough planning. The worst thing would be to design using your Excel brain. How are you with knowledge about basic relational database design principles?

[rpeare]

I agree with Micron, the hardest part here is calculating working days.

For instance, if a person is 'due' on 5/27/2019, said person would not be late until AFTER 5/31/2019 (the friday of the same week as the due date). To go along with this you'll also need a 'holiday' schedule. Let's say christmas and boxing day fall on a thursday/friday and the due date for someone is 12/26, is their due date changed to 12/24? or is it extended 12/29? or even to 1/2/2020? I've done this sort of thing before for timekeeping databases to automatically add holiday time so it's not out of the range of possibility but you'll definitely need some VBA I think.

[blackrosepetals91]

Hi Micron,

Thanks for your advice. I get what you mean about approaching this from more of an Excel mindset. With regards to "relational database design principles", do you mean the way in which the tables interconnect with one another as a result of defining their relationships to one another?

[blackrosepetals91]

Hi Rpeare,

Thanks for this. When you did databases for time keeping did you still use Access or did you use something else? I've been tasked with trying to make some of processes here a bit more automatic as at the moment, even with the spreadsheets they are quite time consuming. I'm not really familiar with VBA other than hearing about it. I can certainly try and self learn how to use / implement this, or would it's use be quite extensive and out of the range of any newbie?

[rpeare]

I did my timekeeping in MS access and had a problem where christmas is concerned because we have a 'floating' holiday take on either side of christmas with some specific rules outlined in the table below. On top of that a lot of our holidays are dependent on where in the month you are, for instance memorial day is the 4th monday of may so I had a table defining how to calculate all these holidays (including easter which was a pain in the butt) so I could calculate all the holiday dates at the beginning of any given year then discount them from any other day counts. You'd have to do something similar with your database. Define the rules first, then go about trying to figure out how to set your warnings, if you botch the rules it's going to be extremely hard to backtrack it.

In your case, as a novice it might be easier for you to actually have a table with every date of the year in it and programmatically fill in whether it's a workday or not, then use that 'datematrix' table to figure out when a person is late. I do not know enough about your rules to say for sure. In my previous post you can see there's a lot of 'what about situation x'.

Christmas Actual	Christmas Observed	Floating Observed
Monday	Monday	Tuesday
Tuesday	Tuesday	Monday
Wednesday	Wednesday	Tuesday
Thursday	Thursday	Friday
Friday	Friday	Thursday
Saturday	Friday	Monday
Sunday	Friday	Monday

[Micron]

With regards to "relational database design principles", do you mean the way in which the tables interconnect with one another as a result of defining their relationships to one another?

I would say relate to one another rather than interconnect because you don't have to actually create connections( if by that you mean defined relationships in the Access relationships window) to hava a db that can still properly function.

I agree that you will need a "non working days" type of table in order to define what they are, but I wouldn't bother trying to calculate them when it comes to holidays. Just plunk in the dates (including the year) and keep going year by year - not a table for each year. To calculate Easter you'd need a lunar calendar AFAIK. Not worth the trouble.

[blackrosepetals91]

Thanks both for your replies ! I might have to rethink using Access and see if there is something else I can use to create the database, and I think for the amount of monitoring we would need to use it for, Access might not be quite up to it unfortunately.

[rpeare]

I think Access can do what you're after, but you may need to do a deep dive into your process and how to program for it. You may spend more time up front getting it right but less time in the long run if you have your rules set up correctly.