Access Basics- The Juicy Bits

Microsoft Access is a database – it is a relational database ie more than 1 table of records can be stored in a single file and those tables can be related together so that reports can be made from different tables.

A database is an organised collection of related data from which info can be extracted.

Some examples of what you can do with Access include: making alphabetical listings; formatting reports; mailing labels; and fill-in-the-blank forms.

Access Database Objects

There are different objects in Access that allow you to do different things with data:

  • Tables store data in rows (records) and columns (fields)
  • Queries select and combine tables (like a filter)
  • Forms help you to easily view, add and update data in tables
  • Reports analyse or print data in a specific layout for printing
  • Macros can help you to program events and add functionality
  • (Don’t worry about Pages or Modules yet)

You can find all these objects in the left pane. In Access, you can group different objects.

Access Tables

Tables are the structures that hold the data that you want to store in the database. A column is called a field and a row of data is called a record. Each row represents a complete set of fields that make up a single record. To move to the next field press tab or enter. To add a new record click the new record button (or start typing in the bottom most row. Press tab/enter at the end of the row to move automatically to a new record. Delete a record by selecting the record using the row selector (on the far left of the record) and pressing delete keyboard button. To change a check box setting, press space bar to change from checked to unchecked. For a drop down list, press F4 to view the list, press the down arrow to move to the required selection then press entre to select it. Press Ctrl + Z to undo the last action. You can sort and filter just like Excel by clicking any of the fields.

Tip: Pressing Ctrl + ‘ to copy the text from the field directly above the cell holding the cursor.

A table wizard can help to create your own tables. You make a table using the wizard from data in an excel spreadsheet. It can be easier to create data and fields in excel first, then move that data to Access.

Access saves changes to data being entered automatically. So you are only asked to save the database if you have changed the structure.

There are 2 views in Access, a datasheet view and a design view. The datasheet view is the standard view that allows you to create, read, update or delete data in tables (Back-end web developers call this ability to change data CRUD). The design view allows you to see the tables underlying structure and make changes. IE to accept data in a certain field as numbers instead of text.

Design View – modifying a table structure

The 1st column in the ‘field list’ pane lists all of the field names (column headings). You can change the data type:

  • Text – any text and numbers not requiring calculations
  • Memo – long combinations of text and numbers
  • Number – Numerical data for use in calculations
  • Date/Time – Stores dates or times for use in calculations
  • Currency – numerical data stored in monetary format
  • Autonumber – Automatically generated and incremented
  • Yes/No – Yes/No, true/false, on/off (Boolean)

For different data types, there are a number of formats to choose. The input mask is used for data to be entered in a pattern. EG To type a phone number and set the input mask to add brackets for the area code. Validation rules allow you to set limits to the data entered. EG if you want numbers to be 1000 or bigger. An expression can contain logical operators like and, or, not, between and like. ‘Required’ means that data must be entered. ‘Allow zero length’ means that Access will allow you to leave the field blank. You can add comments in the description section. In the bottom section you can change the selected field’s properties. An arrow-head next to a row indicates this is the primary key. Indexing a field speeds up sorting or searching the database.

Primary Key: A primary key field contains data that uniquely identifies each record. There can be more than 1 primary key. Primary keys are indexed.

Access Table Relationships

You can create a relationship between tables by joining them using their common fields. Joining tables allows you to bring together data from more than 1 table.

A table relationship can be 1 to 1, 1 to many or many to many. Use the Relationships button on the toolbar.

A deep look into table relationships is beyond the scope of this article. I’ve never used them in Access personally, but I have used them extensively in Web-based databases.

Access Forms

An Access form is used primarily for easier data entry than using a table.

A form allows you to view just 1 record at a time and is ideal for data entry or for looking up information. All the fields for 1 record can be viewed together on 1 screen. You can view data from different tables on 1 form. Forms are created from existing tables.

When you design forms to display information on the screen, you can choose where you want the information to appear. The form can include calculation. You can include sub-forms that display information from a related table/query.

Double click to open a form in form view. At the bottom there are navigation buttons to move to different records. Design view is used to see and edit the underlying structure of the form.

Access Form Wizard

To build a new form, select the ‘new’ button, use design view, or the form wizard. Click the list and select a table. Then click in the ‘available fields’ box to transfer them to the form. You can choose different styles to view the form. You can also change tables from here. Type a title for the form.

Once in the form you can add, edit and delete data. You can still add new records.

To change the structure of a form go into design view. The background is a grid. Here you can move and resize fields. You can format with different fonts, etc. The toolbox contains specific form design tools.

There are lots more you can do with Access forms but outside of the scope of this article.

Access Queries

Queries are questions you ask the database in order to extract information. They can also manipulate data in order to perform many tasks.

In the left bar, you can run an existing query by double clicking it.

There are 2 types of query. ‘SELECT’ and ‘ACTION’. Select queries retrieve records that match specific conditions without changing the data. Action queries perform tasks that result in the changing of data. You can use design view or wizard to create queries.

Using the wizard to create a new query, you can find and select tables and other queries and add them to the query. Type a name for the query and then you can open it view the results.

In design view, you can add fields using the ‘show table’ button. This brings up a window to select fields from other tables. Select the field and press add.

In the design screen there are 2 panes. The upper pane shows the ‘field list’ of the tables used (primary key in bold). The lower pane (field box) shows where the query is created. In the ‘criteria’ section you can design the query to only select certain fields if they match criteria (eg find only ‘Ashford’ in the list of towns table).

You can perform other operations such as show, hide and sort. You can also retrieve data from multiple tables by joining them. These are outside the scope of this article.

Access Reports

While tables and forms can be printed, reports are normally used with you want to print from the database as they can be formatted. Records can be grouped and sorted, while statistics such as totals can be produced. In preview, Access shows the report as it will appear when printed. Design view is used to see the underlying structure.

Click the reports button to view a report, click new to create a new one. Choose Report wizard, select a table or query and click ok. Select the fields to transfer to the report. Select another table/query if required.

The next step allows you to make grouping decisions, such as by totalling the sales for each category.

The next step allows you to make sorting, summary decisions and calculations.

The next step allows you to adjust the layout and orientation (to fit all on 1 page).

Note: A columnar report displays fields down the page, a tabular report – across the page.

Now you can preview the report and print it out (to a printer or to pdf). Print preview shows the page margins.

In Layout view, you can configure the overall formatting and layout of the report. The Layout view shows WHERE fields will appear on the report.

In Design view you can fine-tune the fields. Design view shows items per section. You can modify a report in design view, change properties, add pics, create headers and footers and adjust text boxes but this is out of the scope of this article.