Access can save businesses time and effort in terms of employee preparation of spreadsheet data for reporting and analysis, increasing productivity while eliminating human error in data entry.
MS Access can manage existing data sets and import or export from Word processing or spreadsheet files, as well as supporting the ODBC standard to work with SQL databases.
Creating a Database
Microsoft Access is a database program used for the storage, organization and analysis of information. Users are able to create multiple tables which can be linked together into relational databases using rows and columns – commonly referred to as relational tables – in addition to tables themselves (known as relations databases). Furthermore, MS Access supports forms, queries and reports in addition to tables themselves. When building databases with MS Access you should first plan out its structure such as tables fields relationships before using its blank database option or one of the provided templates to create your databases themselves using these tables (known as relational databases).
To create a blank database, open Access and click File >> New on the ribbon. Type in your database’s name in File Name box before clicking Create or Download; or create a desktop shortcut so you can double-click to launch Access when it comes time to create or access it later.
Once you’ve created a database, its left side displays all available objects – tables, queries and forms. You can navigate between these objects by using the Navigation Pane.
Tables are the cornerstones of any database, enabling you to store information in an organized fashion. When creating a new table, the initial field appears as ID by default – however you can change this using the Field Name property in Design View of Property Sheet. After changing this setting you can rename and choose its data type; such as Date for date-related records, or Yes/No for fields which accept two states only such as true or false (zero or one).
Import data from an external data file by clicking Get External Data in the Database Tools section of the ribbon and selecting Import. Depending on its data type, you can either import it as an entire new table or append it to an existing one in your database.
Creating Tables
Tables are at the core of every database system, providing an effective organization and manipulation of data. Microsoft Access makes creating tables easy by entering field names and choosing field data types in table design view; you can even use templates to quickly setup your structure before beginning to import or enter data.
To create a table, select the “Table” button from the “Tables” group on the “Create” tab of the Ribbon and choose either Table Design or Table View before giving your table a name.
Your table is now ready to be populated with records. To do this, either enter the data in the record navigation bar, or navigate your way around it using up and down arrow keys or scrollbar.
When adding a field, you must select the kind of information to store in that field. Your decision should reflect both what data your table requires to operate effectively, as well as calculations you intend on performing with that data. Selecting an appropriate data type for a field is critical as this determines how you can manipulate its contents and display formats available to you.
Your table requires a primary key. This unique identifier allows you to quickly find specific records. For instance, when creating a course table you might choose the departmentCode and courseNo fields as primary keys; failing which Microsoft Access could reject the database by saying duplicate values exist for these fields.
Once you’ve selected field data types and assigned your primary key, it is time to populate your table. Start by adding one record; when clicking it, choose which field to enter information into and type into that field before clicking “Enter Record.” Using the Field Properties feature at the top of your table design window you can also set formats or other properties of individual fields.
Creating Queries
Microsoft Access allows users to search databases and perform various actions on that data, including sorting, filtering, calculation and summarization for further use – these operations are known as queries.
Microsoft Access offers an end-user tool to build queries called the query design view, which works by accessing and using SQL to construct your queries. If you know SQL, this view allows you to build queries freely without using Access’s built-in templates. Alternatively, an SQL view provides another method for developing free-form queries in freeform mode.
Queries are one of the main draws of Microsoft Access for users, offering flexibility, utility and integration features that enable fast information retrieval compared to manually entering large amounts of data into tables.
Access provides various kinds of queries, including select, action, totals and crosstab. Select queries let you show only records that meet specific criteria; action queries allow users to change or delete records in tables by performing actions when run; totals queries total up data in a table for reports while crosstab queries allow you to group or sort it.
Make Table is a query used to create new tables either within the current database or another database depending on which option is selected from within the Make Table dialog box. To create one using Make Table mode, click Create > Queeries button group > Querie Design button then Query Design button on Create tab of Queeries button group and follow directions therein.
Add tables from which you wish to extract data in the top section of the query design view, then fields you wish to display in your query results into the QBE grid (query by example grid) at the bottom section. Furthermore, specify any criteria or sorting options needed.
Once your query is exactly as desired, simply name it by clicking in the text box and typing its title. Save your query using Access’ File tab > Save icon option; your query will then be stored under Queries under All Access Objects Pane and can be opened whenever required to view results.
Creating Reports
Microsoft Access allows users to organize and summarize data for printing or online viewing. A report includes information pulled from tables or queries as well as stored with its design (such as labels, headers or graphics). Tables or queries providing this data source for reports are known as record sources, while its output can either be detail or summary reports – detail reports display all selected records while summary reports summarize these via totals, counts or percentages instead.
To create a report, navigate to the table or query you want to base it off of in the Navigation Pane and click Report button on Ribbon’s Button Bar. Alternatively, if you prefer an easier method for report creation, open a blank report in Layout view by selecting it and clicking Blank Report button instead of Report Wizard button on Ribbon Button Bar.
When creating a report, you have the ability to add text boxes, pictures, and graphics that illustrate what the report is reporting rather than simply showing numbers and text. Furthermore, you can add charts as part of a report to present your data in an engaging manner.
Once your report is ready, it can be printed or viewed in Print Preview mode for review and modification of its layout and dimensions.
Layout controls how a report appears on each page; for instance, you can choose whether it will be displayed portrait or landscape orientation and define page margin sizes as needed.
Your report allows you to easily adjust its fields by simply clicking and dragging. Text boxes may also be changed in width by dragging their edges or by opening their properties sheet and entering values for their Width field in their properties sheet. Furthermore, row-based reports offer you the chance to resize columns by simply dragging their thick line separating columns.
