Approvals System (1 Viewer)

[StuartG]

Hi all,
I have been tasked with trying to come up with an inhouse approvals system but I am not sure whether Access is the ideal solution?

The management team would like me to develop something that can be audited, time stamped and dated, but also has the ability to leave comments if they refuse it.

due to the way that the approvals system currently works the form would need to be sent several times, whereby the "approver" does their part and then sends back to the end user.

I am not sure whether creating a front end with Users would be a solution, to enable the ability of better auditing.

Can access support something like this or is it too advanced, with a lot of coding etc?

Some people suggested Infopath but I wouldnt have a clue how this starts or works?

Any guidance is greatly appreciated.

Stuart

 

[plog]

I have been tasked with trying to come up with an inhouse approvals system

Honestly, this is a question only you can answer. I absolutely guarantee you this has been developed thousands of times in Excel. I absolutely guarantee you every business process has been developed thousands of times in Excel.

Now, the moral of that is not that Excel is the right tool, but it is a tool that solution providers have available and are familiar with. I know at least 10 people who frequent this forum who could build that system for you in Access---because they know Access.

So, the question can only really be answered by you. Access can solve your issue, but it being the right tool has to do with your skill level. You've admited little knowledge of Infopath, so I would suggest against Infopath. What do you have knowledge of? Have you undertaken anything similar?

 

[StuartG]

Hi Plog,

Thanks for the reply!.

my knowledge of advanced features in access is lacking, but learn quickly with the right support and advice.

I recently built a few other databases for the business but nothing on the scale of what is being requested this time as I think a lot of it will be VB code which i do not understand.

I have tried to look at templates but there are very few available online as I think alot will be bespoke to the companys requirement.

I would not like to say that I couldn't do it, but it would take time and support.

The current approvals process involes sending an excel document via email that is viewed and then "approved" and then another email is sent to the next approver, and so on until all approvals are in.

Stuart

 

[plog]

My overall advice in building this new system is focus on purpose and results. Two bullet points to that:

1: Disregard how the current system operates. While people are used to A, B and C occuring in that order. I've often found that sometimes you can accomplish the same result with just A & D. Yes, D is new and they no longer do B & C, but they still get to the same spot and in less steps. Don't limit yourself to what is happening now.

2: Get requirements. Don't just open up access and start coding. I advise finding out what the system is to produce (reports, emails, etc) and then work backwards from there. In my experiences the old systems have a lot of legacy code/data/processes that support functions no longer used.

 

[Lightwave]

Here are some musings

I have seen a number of systems like this that send stuff. Be aware that you are probably not going to be sending anything anywhere in the design. You simply have a case file that gets allocated to different individuals. I would suggest that that information is held in some kind of child table which might have a similar structure as below

PKID
Person
Notes
Received Date
Passed Date

Everytime a case was with an individual there would be a received date and not passed date to close the case with an individual fill out the passed date.

A simple query on uncomplete passed dates would give you who the case is with.

Base forms on the queries of different users and you have current position for any case and cases will move between users as people fill out the person and passed date.

A basic structure like this gives you a tremendous amount of flexibility to design how you want. EG here is a link to how you can get earliest or latest child records from a parent - this could be used in conjunction with the date field to get lists of cases for individual colleagues.

Manipulating child records using a date field

At the basic level most systems are just a matter of taking a big list and turning it into a small list. So getting to know SQL and using it in imaginative ways is important.

The nice thing about the above structure is that you can see all the cases and management will be able to see audit history of each case as well as the position of any case at the moment without any duplication of data. (normalization)

PS there is not a better desktop front end UI integrated design environment than MS Access - also the report writer is second to none.

 

[The_Doc_Man]

Here is my take on your questions:

Can access support something like this

Absolutely.

or is it too advanced,

Only you can answer that question.

with a lot of coding etc?

Certainly a lot of coding.

Get requirements. Don't just open up access and start coding. I advise finding out what the system is to produce (reports, emails, etc) and then work backwards from there.

Plog has touched the central issue. Your question is one we cannot answer in general because only YOU are in a position to determine requirements and appearance issues. Only you know what resources will or won't be useful. Only you know the level of competence of your end users. (And you have expressed doubt with regard to the end users, which you need to resolve up front since it will totally impact the design.)

For projects in the feasibility/design stage, I usually trot out my old, tired advice - the Old Programmer's Rules. Here they are:

#1 - If you can't do it on paper then you can't do it in Access.

You need to have a "project design" document that will be your "bible" of how things are supposed to work and what they are supposed to do, at least at a functional level. No, the design document doesn't have to actually contain instructions - but it must identify steps in a process in great detail, including what to do when an approval occurs AND what to do if disapproval occurs. At EVERY STEP in the process. You need a "road map" of where this project is going. And trust me, if you don't have a roadmap, you will NEVER know when you are getting to your destination.

Without that document, you will never be able to answer you boss's question: How far along are you on that project? With the design document, you will have ideas of levels of difficulty for each part and you will know which parts are finished vs. those that are not. You can thus give the boss a percentage completion.

#2 - Access will not tell you anything you didn't tell it first.

Remember, Access is NOT the subject matter expert regarding your business. YOU are. All that Access is good for is building tables, queries, forms, reports, macros, modules, and relationships. The rest is on you.

In particular, your roadmap document (see rule 1) should list desired outputs. But part of your job is to assure that you have provided needed inputs to produce those outputs. If you need to see XYZ in the output, you EITHER need XYZ as input OR you need X, Y, and Z as inputs plus the formula or procedure that converts X, Y, and Z to XYZ. For EVERY output case, you have to work backwards through your project to verify that you have data sources you need to support the outputs.

If you are in the early stages of this project, you need to do an extensive data-gathering phase first. Because without it, your roadmap will have spots of terra incognita - spots where you will have NO idea where you are or how you got there or what you can do to find your way out of the tangle. Whenever that "where am I" tangle occurs, you can bet dollars to donuts that your fact-finding missed a few facts. So take your time to ask all the "what if" questions now.

 

[JHB]

If you going to be successful with your application, remember to involve future users in the development phase of the system, therefore you need to talk to each user until you get a good insight into how they would like the system to look like.
Because if you do not have users on your side they will do anything to sabotage your system.
And yes - you must be prepared that what you think is smart is not necessarily what the users think is smart, so you properly have to redo some of it.

 

[Pat Hartman]

The nice thing about using a database application such as Access to handle this is that you don't have to keep sending the file everywhere. Everyone comes to the application to see what needs to be reviewed and then to mark the review complete. If timing is such that people won't have a need to come to the app every day, then the app can send emails to inform people that something needs their attention.

EVERY action can be logged. You can log every email that gets sent - when, and to whom. You can tell by when things are approved/disapproved how long it took people to respond. The more you know about the auditing requirements, the better your design will satisfy them so they will be some of your most important subject matter experts.

You are correct about this being a bespoke application. I would be surprised if you found any examples. But as Doc says - if you can't do it on paper, you can't do it in Access so start with paper. I actually like to use sticky notes on a big whiteboard. The whiteboard allows me to draw connecting lines. The sticky notes allow me to move stuff around. You can just use a plain wall if you have some arrow sticky notes to use to connect things.

 

[CJ_London]

one question not asked or answered is - what is your infrastructure? is everyone in the same location? do they all have access to a common server?. Do all users have access installed? Are all users using Windows?

Most users have email, most will have excel (which may be why your existing system is designed this way), many may not have access (but you can install runtime which is free, but perhaps only with the agreement of IT).

So yes, access can do it and probably much better than excel - but only if you have the required infrastructure.

Alternatively you can use access as a control hub. An authorisation request is initialised in some way, access automatically sends out an email (or perhaps a text message) and monitors for a response, once a response is received, perhaps a second authorisation is sent out, etc.

Years ago I developed an excel solution which automatically forwarded the email to the second authoriser once the first one had done the necessary. Problem was it had to be an xlm and worked fine until IT decided that all emails with macro enabled files would be treated as malicious and deleted. So then it had to be zipped.... Lesson, understand in what direction your companies IT development policy is going.