Is what I want even possible? *Warning - long post*

[Safety Guy]

This isn't an "how do I do something" post (yet), but an "is it possible" post. I don't want to invest a lot of time and effort into trying to create something if it isn't possible, so I haven't even started creating a database yet. My background with MS Access is as a user, not a creator, so I will have to educate myself before attempting to build it. So please be gentle...for now.

Background information:
My company has a process for getting incident reports. However, that process is not what you would call efficient. Or effective. Currently, Department "A" will email a daily report (a list created in Excel) of incidents that need an incident report done to around 85 - 90 recipients. Now, at most, only about 5 or 6 people of those 85+ will be responsible to make a report and those people change each day. Needless to say, the daily report has a tendency to be ignored. Often. Then there is a bunch of other emails and phone calls and begging and pleading. Possibly even bribery.

To streamline things and improve the odds of getting incident reports turned, I want to go to a database. So here is how I see the process going:
1) Department "A" fills out a form with basic information of the incident and submits it.
2) A notice email with a link or attachment to a separate form that contains the information from step #1 , is emailed to a person responsible to add incident description(s) and corrective actions taken.
3) That form would be logged automatically or added to the record started in step #1 .
4) Some incidents will also require a deeper investigation, and would want the report and investigation linked.
5) I would need the ability to pull the report data into Excel PowerPivot to slice and dice it for monthly/quarterly/annual reports.
6) I would also want the corrective actions to be reported on and against for completion.

Is what I want possible to build in Access? If not, is there something better (SharePoint is not an option at this time)?

Totally separate, but related, question. Can someone direct me to a source to learn database theory? Something that can teach me the difference between a well built, efficient, and stable database and a crappy one that might work but will break and require a lot of maintenance.


Thanks everyone!
Safety Guy

 

[mole999]

i think what you want is 100% achievable, one constraint may access to the backend database, can everybody who needs to see it on their online machines. Personally I would want your todo list fleshed out, and then hire someone who does that for a living so you have the support built in.

I would think you could have a web frontend for occasional users, so you don't need to install access at every location and that will require investment in an https (if its outside the organisation) web presence and also firewall checks etc. get your ideas gatered and then approach some established / recommended by other users

 

[Micron]

As to your first question - if you can conceive it, someone can, without a doubt, build it. However, the learning curve for building something like this that "sorta works" is not nearly as long as it is for a database that works reliably. IMHO, one of the major pit falls of Access is that there are built in things/methods that make it easier to get by without having to learn best design practices. Sooner or later, you find that some things you want to do seem unsolvable if not impossible because you used these things. If you're starting at square one in terms of relational database theory, vba, form, query and report design and in a multi-user network environment want to add email notices and tracking on top of that, expect to put hundreds of hours into this. Not kidding, and if this isn't going to be a full time task, how many months will go by before you're done? Here's my standard list of links which are by no means complete in scope, along with the suggestion to do a TON of additional research, such as Steve Bishop videos...

Normalization Parts I, II, III, IV, and V
http://rogersaccessblog.blogspot.com/2017/03/what-is-normalization-part-i.html
and/or
http://holowczak.com/database-normalization/

Entity-Relationship Diagramming: Part I, II, III and IV
http://rogersaccessblog.blogspot.ca/2009/01/entity-relationship-diagramming-part-i.html

How do I Create an Application in Microsoft Access?
http://rogersaccessblog.blogspot.ca/2009/05/how-do-i-create-application-in.html

Important for success:
One source about how to name things - http://access.mvps.org/access/general/gen0012.htm
What not to use in names - http://allenbrowne.com/AppIssueBadWord.html
About Auto Numbers
- http://www.utteraccess.com/wiki/Autonumbers
- http://access.mvps.org/access/general/gen0025.htm

The evils of lookup fields - http://access.mvps.org/access/lookupfields.htm
Table and PK design tips - http://www.fmsinc.com/free/newtips/primarykey.asp
About calculated table fields - http://allenbrowne.com/casu-14.html
About Multi Value Fields -http://www.mendipdatasystems.co.uk/multivalued-fields/4594468763

There is a process that will aid in achieving a successful outcome, and I once saw a book on this subject that made a lot of sense to me. It starts with meetings that decide, at a high level, what a db must support vs nice to have stuff, because what you don't want is people constantly moving the goal line while a db is under development. Wish I could remember the title...

Then, believe it or not, you start your design on paper.

 

[Safety Guy]

Thanks Mole and Micron. I really appreciate your thoughts. I totally agree that my wish list needs fleshed out, but without knowing if things were possible, I didn't want to get too deep into the rabbit hole. Given what you guys said, this is way above my skill level and way more than I can learn in a reasonable amount of time. I will see about "contracting" it out.

The process Micron described is pretty much my usual M.O. Actually, I take it a bit further and map out a process, then take it to the levels above me and give them a few options so they feel they had a say in things. You know...manage your managers.

Thanks for the link Micron. I have them bookmarked for later study.


Thanks again,
Safety Guy