Microsoft Access makes it possible to efficiently organize data by creating tables and linking them. Users can design forms for easy data input as well as run queries to quickly locate specific pieces of information.
Microsoft Access supports multi-user database solutions by supporting a “split” approach: with one file for data tables stored on a network folder and another with forms, reports, queries, code, and macros distributed to individual users.
Tables
Microsoft Access tables are at the core of its data management, serving as the basis of forms, queries and reports you create. An underlying database handles complex relationships among records as well as record locking. Tables themselves function like spreadsheets in that you can quickly change their appearance – for instance resizing columns and rows or hiding information you don’t wish to see.
Step one of creating a table involves selecting which fields will hold your data in it. You should typically consider which information needs to be stored; for instance, you might require customer info tables, products tables with product and price descriptions and orders tables which keep track of transactions between you and customers.
To insert a field into a table, first click the row where you wish to add it and select the New Field option in the Table Design group on the Ribbon. When asked for information regarding your new field’s name, data type, and description you simply type your text directly in.
Each field has a set of properties that determine its appearance in a table and how data within that table can be edited or changed. These properties are usually located on tabs within the Field Properties section of a table’s design grid – you will learn more about these in later lessons in this course.
A table must include a primary key that uniquely identifies each record within it. This typically comprises one or more pre-existing fields that, when taken together, yield unique values for every record; for instance, in a customers table this could mean having an ID field which uniquely identifies customers.
Access automatically saves changes made when using the Ribbon to create tables, when selecting another record or closing the table. However, you can manually save by accessing File > Save command on File menu and manually deleting records from table.
Forms
Forms provide users with data more efficiently from tables by making it more user-friendly than working directly with tables themselves. They allow you to build forms from one table, multiple tables or queries; you could even make use of controls from another form as their control source! In addition, forms allow easier navigation through your database by acting like switchboards for other users by creating forms to act as switchboards specialized forms for different sections.
To build a form, first launch a database and then select the table or query you wish to base your form upon. Next, click on More Forms button in ribbon and choose form type that best meets your needs (Single Item, Multi-Item, Datasheet Split or Modal Dialog Forms are options available to you), follow wizard’s prompts until completion and view in Design View at any time for tweaking if necessary.
When creating a form, it’s also necessary to determine what fields to include. By default, creating one field for every column in a table would create too many fields in your final database if all aren’t needed. You also have the option of including a Primary Key field which serves as an unique identifier of every record in your database.
Select the Check Boxes that correspond to each field name in your table to define which columns will appear in your form. When you check a box for one of your fields, Access automatically adds it; when unchecked it disappears from your form.
In the final step of creating a form, selecting which controls to include and how they will be organized is of crucial importance. Label each control in your form with text that can be changed by size or color of font as well as selecting background color options as well as whether or not displaying border control borders.
Your form can include text boxes for entering comments or date boxes to enter dates into a calendar. Furthermore, custom toolbars allow you to show/hide specific buttons. Furthermore, forms can be made read-only to prevent other users from making changes while you’re viewing it, as well as its Visible property changed so it remains hidden when not needed.
Queries
Queries provide a powerful means of extracting, displaying and manipulating data in an Access database. Unlike tables which store actual physical data, queries are written using structured query language (SQL) code which is saved as a small text file; this saves space as only SQL code needs to be stored compared with actual physical data. A simple query may utilize fields from multiple tables by creating relationships using the SELECT command; more complicated ones can use more advanced features like JOINS, GROUP BY and HAVING commands; Microsoft provides an intuitive graphical user interface which makes writing complex SQL easier while simplifying writing complex SQL code easier compared with writing complex SQL code by making complex SQL easier to write and understand.
Your data tables appear in the top section of a query design view, while its query displays their fields in the QBE grid in the bottom grid section. With field properties on this view you can add and modify criteria used to filter or sort results of your query results.
Once your query is ready to run, click the “Run” button in the Query Tools contextual tab on the Ribbon. If the results do not satisfy, use the “Edit Result” button instead to return to query design view.
Clicking the “Edit Result” button allows you to change your query’s property settings, such as selecting whether or not to display all rows, changing Top Values property value limits, or customizing format of query results.
When making changes to your query, it is always advisable to create a backup copy of your database before running action queries that physically change it. Running such action queries may result in the loss of information; Microsoft warns about this risk by displaying a warning before running them; in case your external data source becomes unavailable then working with objects or tables using this data forces MS Access back online so it can reconnect and reopen your database.
Macros
Access provides more than tables, forms and queries – the “External Data” menu includes several tools for importing external data sources such as Word documents, Excel files, Web browsers or any other external source – making SQL powerful without the need to learn structured Query Language (SQL).
Microsoft Access contains many features designed to allow users to customize the database to meet their individual needs, from tables and reports, forms, and queries that match user specifications to creating ready-to-use templates that save users time when building databases.
Access provides users with an easy way to create and execute macros. Macros perform many different functions and can be saved as standalone macros that can be seen on the Navigation pane or embedded directly into forms or reports. Macro creation and execution is as straightforward as selecting an action from the macro design window’s drop-down list and entering its arguments; actions represent commands the macro will perform while arguments contain any additional settings necessary for evaluation or functioning.
After selecting an action, the macro can be dragged and dropped onto any form or report control, creating a macro button on that control that runs when clicked by the user. Alternatively, macros can be saved as standalone programs and added as icons with play buttons in either Quick Access toolbars or Ribbons where users can select them at any later date and run them when required.
Note that while macros can be stored as individual objects, for easier management and organization it is recommended that they are combined into a single macro object for easier management and organization. This will enable users to assign names for each macro that distinguishes one from another; otherwise it will appear as “untitled macro” in Navigation Pane and Macro Gallery.
