peerlogoboss

Microsoft Access, on the other hand, is a Relational Database Management System (RDBMS), that helps you store information for reference, reporting and analysis Key Benefits of the Microsoft Access Database.

1. What Is Microsoft Access

You don’t have to use every feature and tool and push the edges of the Access envelope. In fact, you can use very little of everything Access has to offer and still create quite a significant solution to your needs for storing and accessing data — all because Access can really “do it all” — enabling you to set up a database quickly, build records into that database, and then use that data in several useful ways. Later on, who knows? You may become an Access guru.What is Access good for? That’s a good question. Well, the list of what you can do with it is a lot longer than the list of what you can’t do with it — of course, especially if you leave things like “wash your car” and “put away the dishes” off the “can’t do” list. When it comes to data organization, storage, and retrieval, Access is at the head of the class.

Building big databases with AlexaOkay, what do I mean by big database? Any database with a lot of records — and by a lot, I mean hundreds. And certainly if you have thousands of records, you need a tool like Access to manage them. Although you can use Microsoft Excel to store lists of records, it limits how many you can store (no more than the number of rows in a single worksheet). In addition, you can’t use Excel to set up anything beyond a simple list that can be sorted and filtered. So anything with a lot of records and complex data is best done in Access.Some reasons why Access handles big databases well are:. Typically, a big database has big data-entry needs.

Access offers not only forms but also features that can create a quick form through which someone can enter all those records. This can make data entry easier and faster and can reduce the margin of error significantly. When you have lots and lots of records, you also have lots of opportunities for errors to creep in.

This includes duplicate records, records with misspellings, and records with missing information — and that’s just for openers. So you need an application such as Access to ferret out those errors and fix them. Big databases mean big needs for accurate, insightful reporting. Access has powerful reporting tools you can use to create printed and onscreen reports — and those can include as few or as many pieces of your data as you need, drawn from more than one table if need be. You can tailor your reports to your audience, from what’s shown on the reports’ pages to the colors and fonts used. Big databases are hard to wade through when you want to find something. Access provides several tools for sorting, searching, and creating your own specialized tools (known as queries) for finding the elusive single record or group of records you need.

What Is Microsoft Access

Access saves time by making it easy to import and recycle data. You may have used certain tools to import data from other sources — such as Excel worksheets (if you started in Excel and maxed out its usefulness as a data-storage device) and Word tables.

Access saves you from reentering all your data and allows you to keep multiple data sources consistent.Creating databases with multiple tablesWhether your database holds 100 records or 100,000 records (or more), if you need to keep separate tables and relate them for maximum use of the information, you need a relational database — and that’s Access. How do you know whether your data needs to be in separate tables? Think about your data — is it very compartmentalized? Does it go off on tangents? Consider the following example and apply the concepts to your data and see if you need multiple tables for your database. The Big Organization databaseImagine you work for a very large company, and the company has data pertaining to their customers and their orders, the products the company sells, its suppliers, and its employees. As part of thorough planning, sketch your planned database on paper, drawing a kind of flowchart with boxes for each table and lists of fields that you’ll have in each one.

Draw arrows to show how they might be related — it’s sort of like drawing a simple family tree — and you’re well on your way to a well-planned, useful database.Here’s a handy procedure to follow if you’re new to the process of planning a database:1. On paper or in a word-processing document, whichever is more comfortable, type the following:. A tentative name for your database.

A list of the pieces of information you plan on getting from that database on a daily or regular basis2. Now, based on that information, create a new list of the actual details you could store:List every piece of information you can possibly think of about your customers, products, ideas, cases, books, works of art, students — whatever your database pertains to. Don’t be afraid to go overboard — you can always skip some of the items in the list if they don’t turn out to be things you really need to know (or can possibly find out) about each item in your database. Take the list of fields — that’s what all those pieces of information are — and start breaking them up into logical groups.How? Think about the fields and how they work together:. For example, if the database keeps track of a library of books, perhaps the title, publication date, publisher, ISBN ( International Standard Book Number, which is unique for each book), price, and page count can be stored in one group, whereas author information, reviews, and lists of other titles by the same author or books on the same topic can be stored in another group.

These groups become individual tables, creating your relational database of books. Figure out what’s unique about each record.

As stated in the previous point, you need a field that’s unique for each record. Although Access can create a unique value for you if no unique data exists for each record in your database, it’s often best to have such a field already in place, or to create such a field yourself. Customer numbers, student numbers, Social Security numbers, book ISBNs, catalog numbers, serial numbers — anything that isn’t the same for any two records will do.With a big list of fields and some tentative groupings of those fields at the ready, and with an idea of which field is unique for each record, you can begin figuring out how to use the data.4. Make a list of ways you might use the data, including:.

Reports you’d like to create, including a list of which fields should be included for each report. Other ways you can use the data — labels for mailings, product labels, catalogue data, price lists, contact lists, and so on5. List all the places your data currently resides. This might be on slips of paper in your pocket, on cards in a box, in another program (such as Excel), or maybe through a company that sells data for marketing purposes. With this planning done, you’re ready to start building your database. Access databases with user formsWhen you’re planning your database, consider how the data will be entered:.

If you’ll be doing the data entry yourself, perhaps you’re comfortable working in a spreadsheet-like environment (known in Access as Datasheet view), where the table is a big grid. You fill it in row by row, and each row is a record.The figure shows a table of volunteers in progress in Datasheet view. You decide: Is it easy to use, or can you picture yourself forgetting to move down a row and entering the wrong stuff in the wrong columns as you enter each record? As you can see, there are more fields than show in the window, so you’d be doing a lot of scrolling to the left and right to use this view. The mind-numbing effect (and inherent increased margin for error) is especially likely when you have lots of fields in a database, and the user, if working in Datasheet view, has to move horizontally through the fields. A form like the one shown puts the fields in a more pleasing format, making it easier to enter data into the fields and to see all the fields simultaneously (or only those you want data entered into).If your database is large enough that you require help doing the data entry, or if it’s going to grow over time, making an ongoing data-entry process likely, Access is the tool for you.

The fact that it offers simple forms of data entry/editing is reason enough to make it your database application of choice. Databases that require special reportingYet another reason to use Access is the ability it gives you to create customized reports quickly and easily. Some database programs, especially those designed for single-table databases (known as flat-file databases), have some canned reports built in, and that’s all you can do — just select a report from the list and run the same report that every other user of that software runs.If you’re an Excel user, your reporting capabilities are far from easy or simple, and they’re not designed for use with large databases — they’re meant for spreadsheets and small, one-table lists. Furthermore, you have to dig much deeper into Excel’s tools to get at these reports.

Access, on the other hand, is a database application, so reporting is a major, up-front feature.An example? In Excel, to get a report that groups your data by one or more of the fields in your list, you have to sort the rows in the worksheet first, using the field(s) to sort the data, and then you can create what’s known as a subtotal report. To create it, you use a dialog box that asks you about calculations you want to perform, where to place the results, and whether you’re basing a sort and/or a subtotal on more than one field.

The resulting report is not designed for printing, and you have to tinker with your spreadsheet pagination (through a specialized view of the spreadsheet) to control how the report prints out.In Access? Just fire up the Report Wizard, and you can sort your data, choose how to group it, decide which pieces of data to include in the report, and pick a visual layout and color scheme, all in one simple, streamlined process. Without you doing anything, the report is ready for printing. Access is built for reporting — after all, it is a database application — and reports are one of the most (if not the most) important ways you’ll use and share your data.Because reports are such an important part of Access, you can not only create them with minimum fuss but also customize them to create powerful documentation of your most important data:.

Build a quick, simple report that just spits out whatever is in your table in a tidy, easy-to-read format. So, you can create any kind of custom report in Access, using any or all of your database tables and any of the fields from those tables, and you can group fields and place them in any order you want:. With the Report Wizard, you can choose from several preset layouts for your report, and you can customize all of it row by row, column by column. You can easily add and remove fields after creating the report, should you change your mind about what’s included in the report. If you want to place your personal stamp on every aspect of your report, you can use Design view to do the following:.

Add titles, instructional or descriptive text boxes, and graphics. Set up customized headers and footers to include any information you want to appear on all the report’s pages.If all of this sounds exciting, or at least interesting, then you’re really on the right track with Access. The need to create custom reports is a major reason to use Access.