Access Introduction Training Course

Tables That Hold Everything

Access Basics

Tables are the foundation of any Access database. They store all the data in a structured format, similar to a spreadsheet. Each table consists of:

Fields: Columns in the table that define the type of data stored. For example, a Customers table might have fields like CustomerID, Name, Email, and PhoneNumber.

Records: Rows in the table, where each row represents a single entry. For example, each row in the Customers table represents a different customer.

Examining Queries

Queries are used to search, filter, and manipulate data in tables. They allow you to extract specific information based on criteria you define. There are several types of queries:

Select Queries: Retrieve data from one or more tables and display the results. For example, a query might show all customers who live in a specific city.

Action Queries: Perform actions on the data, such as updating or deleting records. For example, an update query might change the email domain for all customers from @oldmail.com to @newmail.com.

Find, Sort and Calculate

Working with Fields and Records

Open Design View: In the Navigation Pane, right-click the table you want to modify and select Design View.

Add Fields: To add a new field, click on an empty row in the Field Name column and type the name of the new field. Choose the appropriate Data Type from the dropdown menu.

Modify Fields: To change a field's properties, select the field and modify its properties in the Field Properties pane at the bottom. For example, you can set a field to Required or specify a Default Value.

Delete Fields: To delete a field, right-click the row selector (the gray box to the left of the field name) and select Delete Rows.

Adding and Deleting Records

Adding Records: Open the table in Datasheet View. Click on the empty row at the bottom of the table and start typing to add a new record. Press Tab to move to the next field.

Deleting Records: Select the record you want to delete by clicking the row selector. Right-click and choose Delete Record, then confirm the deletion.

Using Calculations in Queries

Add Calculated Fields: In Design View, create a new field in the query design grid. Use an expression to calculate values. For example, to calculate total sales, you might use [Quantity] * [Price].

Forms, Reports and Beyond

Creating and Using Forms

AutoForm is a quick way to create a form based on a table or query.

Select the Table or Query: In the Navigation Pane, click the table or query you want to use for the form.

Use AutoForm: Go to the Create tab on the Ribbon and click Form. Access will automatically create a form that includes all the fields from the selected table or query.

Adjust the Form: The form opens in Layout View, where you can make adjustments like resizing text boxes or changing the layout.

Creating Reports

Using the Report Tool: For a quick report, select the table or query in the Navigation Pane, go to the Create tab, and click Report. Access will generate a basic report with all the fields from the selected table or query.

Blank Report: For more control, go to the Create tab and click Blank Report. You can then add fields and controls manually.