Agreed that Access is more than up to the job. A lot of normalization links there, but I suggest you first read up on how to begin understanding the requirements regardless of whether you build this or contract it out. You can know a lot about what your app has to have without knowing a thing about how to do it.
Maybe read this and research similar posts and write a treatise on what the primary concepts are before you begin. I know there are were books on how to approach such a project, but can't recall their titles from long ago. One of their major concepts was that conferring with the db sponsor/process manager or a team tasked with the development to produce an outline of the project. The main aspects are decided first, then sub aspects. Drive down into the details while recording (and parking) absolute and indefinite bells and whistles that are suggested as you go. The reason being, if you have a time line and begin without a clear deliverable, more nice-to-haves will arise, the project suffers from scope creep and pushes your deadline out. You run the risk of appearing less than competent at fulfilling the task. As has been said in this forum many times, pencil and paper will be a big help. In fact, that's where you should flesh out the requirements of each process. Large sheets of flip chart paper (one for each function) enables you to think about what information you want to go in, what you want out, and how to control both. As the link suggests, you examine the inputs and outputs of the current system, whatever that is, and identify the shortcomings.
After or during this exercise is when I'd begin studying db concepts. As June rightly pointed out, there is a fairly steep learning curve that will only be exacerbated by increasing the level of complexity (dummy "proofing"), and the nature of Access is that it will never be as secure as the worst designed executable program. It wasn't really designed to be a software development tool. Since you seem to be involved in financial work, there may be an increased need to learn about data integrity, data types (for decimal precision) and security (forget anything you research about security that involves .mdw as it has been deprecated). You must learn about how to properly split a db and secure it to a level that satisfies your requirements. Your development time will be in the order of months (3 - 5?) depending on how much time you get to devote to this in the normal course of your work, how fast you learn and how complex you make it. It can be more work raising the level of complexity later than doing it right the first time.
You should also learn about naming conventions, calculated fields, lookup fields, multi-value fields, reserved names, etc. (most/many of us here would NOT use mvf's). Here's some links for that stuff when you have your design needs figured out.
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
a LOT of info there if you examine the left nav bar at this site.
About calculated table fields - http://allenbrowne.com/casu-14.html
In the db creation process, it's my belief that you design queries before forms or reports and you base them on queries instead of tables as much as practical. I see little point in creating a form if the query you want to base it on either does not provide the correct data or needs to be updatable but is not due to design constraints. You'd probably find the Access wizards handy for forms and whatnot, but beware of how controls are named. I will use a wizard, but will always rename all the controls according to my convention lest I introduce ambiguity. Which reminds me. NameAutoCorrect is a db option that most of us would turn off. Then there's always requiring variable declaration if you're writing code, which BTW is much better than macros but maybe a bit longer to learn. Oh yeah, in a financial situation, it's probably more important than usual that you learn about why calculations should not be stored - about 99% of the time. Be thankful that there's tons of info on the Net!!
Last edited by Micron; 11-13-2017 at 06:03 PM.
Reason: added info
The more we hear silence, the more we begin to think about our value in this universe.
Paraphrase of Professor Brian Cox.