If you possess a version of Microsoft Office that incorporates Access (Office Professional 2016 is the latest version), yet you have never used it, you are neglecting an effective tool for organizing and analysing business data.
Working with simple lists does not require you to use Access database— you can do it with Excel. Microsoft Excel is a spreadsheet which offers fundamental tools for such tasks as sorting, filtering, and computing values. If you are working with complex data, Microsoft access database is the software to use. Even though you could store complex data in list form in Excel, doing so normally brings about a considerable measure of data duplication and the danger of data-entry errors. Furthermore, storing data in list form requires you to have special Excel tools like pivot tables, so you can analyse and view the data in a useful approach. That is not the situation with Access.
Here is a real-world scenario associating complex data that is a decent possibility for being stored and managed in Access Database rather than in Excel: For instance, your business requires to manage records as to which workers have been designated company assets such as mobile phones, PCs, tablets, or video projectors. Every worker may have any number of these assets in their ownership, and you have to store a description and an ID number for every electronic gadget a worker has (you need not restrain your data to electronic gadgets, obviously).
If you were to make this record keeping scheme as a list in Excel, you would follow either of the two methods below:
Method #1: Apportion one row in a worksheet for every worker and dedicate pairs of columns across that row to contain the description and ID for every asset the worker has been designated. If for example one individual had 15 gadgets you’d make 30 such columns. If one individual had just a single piece, then you’d require only two columns. Because the quantity of data stored for every worker would differ, searching for a specific item in the worksheet wouldn’t be simple. It likewise would not be anything but difficult to view the data if somebody had several equipment, as you would need to set up more columns that would fit well in the Excel window.
Method #2: Designate one row in the worksheet to each electronic gadget. This setup is just as lumbering although for various reasons. In this case, you would need to restate every worker’s name and worker number for every gadget in their ownership. That implies you would be monotonously entering the worker data, and you would end up with a ton of duplicate data stored in the Excel document. What’s more, the spreadsheet would be cumbersome to work with, it is quite possible that after some time you or an associate would enter some worker data mistakenly. You might end up with what looks like two different workers, for example, simply because somebody entered a record for “Bill Smith” and another person made a second record for the same individual as “Billy Smith.”
For such situations, Access Database is an endlessly better tool than Excel. In Access, you would make one table (a list) of workers, with each record containing the worker’s name and ID number. In another table (another list), you would enter the description and ID number for every gadget, in addition to the ID number for whichever worker is in control of that thing. The only duplicate data in this arrangement is the worker ID number, which serves to connect the two records. This is what is called a relational database, and Access Database makes such a database simple to make.
To view Part-2 of this series, (by Ben Beitler ), Click here >>
Kind regards,
Ben Beitler – “Your Microsoft Access Database Expert!
Tags: access database design, access database relationships, Access database tutorial, access form designs, how to build an access database, Microsoft Access 2016, microsoft access database, microsoft access queries, MS Access, ms access forms, ms access queries