Microsoft Access The Basics – The Primary Key

The Primary Key 

The primary key is a way of uniquely identifying a record.  When that record is going to be referred to in another table then there must be a way of making sure that it can only be one particular one. 

To make sure that the data in that field is not duplicated in other records then the field can be made into a primary key.  A table can only have one primary key; the key can be made up of several fields.  Where multiple fields are used, the individual fields can be duplicated but the combination must be unique. 

To set the primary key:

1. Select field(s) to be primary key.

2. Click on the Primary Key button on the Ribbon or right click on the field name and select Primary Key from the shortcut menu.

Microsoft Access The Basics – Starting Access

Starting Access 2007 

Access 2007 can be started by:

1. Click on the Windows Start button.

2. Select Programs, then Microsoft Office 2007.

3. Select the package Microsoft Office Access 2007

 Or  

By clicking on the icon



This brings up the opening screen



The different elements of the screen are:

1. The Office button – This brings up the main menu system.

2. Blank Database – To create a blank database.

3. Recent Databases – Lists databases that have been opened recently.

4. More… - allows you to choose the database to open from the file system.

5. Template categories –creates a new database based on a template.

Microsoft Access The Basics – Sorting a Table

Sorting a table 

Data is stored in the table in the primary key order as default (See Primary Key page 26).  If you want to view the data in some other order then it has to be sorted in that way.

To sort on one field 

1. Select a field that is to control the sort order

2. Click on the appropriate button to sort in Ascending or Descending order.

To sort on more than one field 

Sometimes the data has to be sorted on more than one field when there are matches on the main sort.

For instance if the customers are sorted by country, then as there are probably several customers in each country then you might want to sort the customers further either by customer name or by city.  In this case the Country would be the main sort, and then customer name or city would be the secondary sort.

Depending on the amount of data involved, you can sort the data by as many fields as you want.  (The maximum number allowed is the number of fields in the table minus one).

The sort on multiple fields is carried out in reverse order, with the first sort carried out on the secondary field first and then the primary field if there are to be two fields that the sort is carried out on.  If there are to be three sorts, then the first sort would be on the tertiary field first.

1. Select the secondary field
.
2. Click on the appropriate button to sort in Ascending or Descending order

3. Select the primary field.

4. Click on the appropriate button to sort in Ascending or Descending order.

Microsoft Access The Basics – Planning a Relational Database

Planning a relational database 

A database differs from a list of data in that a database is made of inter related tables.  The different tables are set up so that data is not duplicated. 

If there is one list, it will hold all the data of the customers and the products that have been ordered by them with the prices they were sold.  As customers order more than once, products are sold repeatedly and more than one item can be ordered on one order, this leads to duplicate data. 

To prevent duplication the data needs to be split up into different tables: Customers, Products, Orders and Order Details. 

When the data is stored in different tables – there needs to be some way of reconnecting the different tables.  The key field is a way of making the connection.   

For instance on the Customers table there is a Cust ID field, this uniquely identifies each customer so that on the Orders table it can be used to specify which customer has placed each order.  On the customers table each order has a distinct order number, so that the details can be linked to an order. 

1. On the table where it uniquely identifies the record the field that makes the connection is known as the primary key.

2. On the table where it makes the connection to, it is known as the foreign key (child).

Microsoft Access The Basics – Finding Specific Records

Finding Specific Records 

There are times when certain records are going to need to be found.  The data might need to be lookup, or selected for editing or deleting.

This can be done by using the Find feature.


1. Position cursor in the field where the search is to be carried out.

2. Call up the Find dialog box by on the find button or by using the keyboard shortcut of Ctrl+F.



3. Fill in the appropriate sections of the dialogue box, click on Find Next, and keep repeating as necessary.



The different sections of the dialog box are:

1. Specify what you want to search for.  Depending on the options selected on the match field, you can use wildcard characters.

2. The search can be carried out in the selected field or the whole table.

3. The match can be carried out on:

Any part of field - equivalent to wildcard characters at either side

Whole field  - No wild card characters

Start of field - wild card character at the end

Using the Match option you cannot get the option of “End of field”, but by using the whole field and then using an asterisk at the beginning of the search you will have the same effect.

The search facility on the navigation bar also lets you find an item, but it does not allow you to search just one field – it searches all the fields.

Microsoft Access The Basics - Opening an existing Database

Opening an existing Database 

If Access has just been opened or there is no database already open then you can open an existing database from the Getting Started page.



A database can be opened in one of two ways from this dialog box.

1. If the database has been viewed recently then the name will be listed in the “Open Recent Database” list.  Click on the requisite database.

2. If the database that you require has not been used recently then you need to click on the “More” folder.  This will take you to the Open dialog box.

If a database is already open then a database then the menu system has to be used. Click on the Office button, from the menu you have the same two options as above.

Navigating Access 

Once a database is opened, the Navigation Pane is used to access all the objects in the database.

The different functions of the navigation pane are:


1. Navigation Pane Menu allows you to choose how the database objects are shown in the Navigation Bar.

2. The Shutter Bar lets you show or hide the Navigation Bar.

3. The groups of objects.

4. The objects in the selected group.

The Navigation Pane Menu 

1. The navigation Pane Menu allows you to choose how the objects are shown in the Navigation pane.

The default category on creating a new database is “Tables and Related Views”.
If you are opening a database that was created in an earlier database the default type is Object Type.
This is the option nearest to the Database Window that was used in earlier versions of Access.

2. Filter by Group allows you to choose whether all the objects are shown or a particular group of them.

The group name will only appear if objects of that type actually exist in the database.


The Shutter Pane shows or collapses the navigation pane.  When the navigation pane is showing it takes up space that you might want to use to view your objects – hence the facility to collapse it to a side bar.

To take off the navigation pane completely has to be done via the Access Options.




The groups that are visible depend on the options chosen from the navigation pane menu. Using the default settings only the groups where objects exist are listed.

To show or hide the objects in a group all you need to do is click on the group name.

Navigation Pane Settings 

A search bar can be added onto the navigation pane, the order in which the objects are listed and how they are displayed can all be changed.  The display options will depend on how you detailed you want the list to be.

They can be changed by right clicking on the Navigation Pane Menu or by right clicking on the blank area at the bottom of the Navigation Pane



The Navigation Pane Options can be changed if none of the categories meet your requirements totally.  If necessary, a new category can be set up with your required groups.



1. Click on the Add Item.

2. Change the name of the added item to what you require.

3. Groups within the category can be added by clicking on Add Group.

4. Change the name as required.

Repeat 3 and 4 as many times as necessary to add the required number of groups.

Opening an existing Table 

If you need to see data that has already been entered, or to add to or to amend the data then the data must be accessed.  This can be done in several different ways, but the simplest method is via the table where it is stored.  (This is not necessarily the most user friendly).

To open a table 

1. go to the navigation pane.

2. Open the group with the table to be opened.

3. Double click on the table name.




Any open tables are shown with the tabs showing, and can be activated by clicking on the tab across the top.

You can open as many tables as you want to – the likelihood of reaching the limit is very slim.  The maximum number of tables that can be opened at any one time is 2048.  (Though that number does include the system tables that are hidden and not normally seen, explicitly used or changed by the user).

Navigating in a table 

To be able to add, edit or delete a record you must first be able to select the appropriate record.  This can be done using the navigation bar at the bottom of the window.





The selected record indicator is the orange box on the left of the row.

Adding a new record 

A new record can be added by clicking on the new button on the navigation bar or by clicking on the new button on the Home Ribbon.


This positions the cursor onto a blank record at the bottom of the table.  The new record is shown by the asterisk in the indicators column.  When new data is added the indicator changes to a pencil.  The pencil shows that there is some data that has not been saved and so could be lost





When the record has been completed, you must move off the record by either tabbing off the record or by clicking onto another record.  By moving off the record, the data is saved.  The pencil changes to an asterisk so that you are ready to add another record.

Data is saved automatically in Microsoft Access by moving off the record – it differs from the other Microsoft packages, you do not have to click on the save button.  (But you can save the record by clicking on the Save Button or by Shift-Enter to save the record without moving off the record).



Changes to the current field can be abandoned by using the Esc key once.  So the contact name of Fred Bloggs would be removed.  Changes to the current record can be abandoned by using ESC twice.  This would remove the Customer id, company name and contact name.

If a record has been saved, then as long as nothing else has been done then the record can be removed by using the UNDO facility, either by clicking on the undo button on the Quick Access Toolbar, or by using the shortcut key combination of Ctrl-Z.  A warning will be given that you are about to delete a record.

In the above example, the BOOMI record has been saved and the BLOGG record has been abandoned.  Even though BOOMI is showing at the bottom of the table, once the table has been closed and then reopened the record will then rearrange into the correct order.

Microsoft Access The Basics – What is Access

What is Access? 

Microsoft Access is a relational database.  All data that is related is stored in one place. Different items are called objects, and the objects fall into four key categories.   

The different categories are: 

Tables 

Tables are where the data is stored and is the backbone of a database. The tables are related in some way, such as lists of customers, the orders they have placed and the items they have requested on each order can be stored in one place. 

Queries 

Queries are where the tables can be analysed, whether by a criteria, by categorising the data or over time.  Queries can be based on a single table, multiple related tables or another query. 

Forms 

Forms are for screen presentation of the data.  They are designed to make it easier to view the data for presentation purposes or to make it easier to input or amend data by the user. 

They can be based on a table, multiple tables or a query. 

There are design considerations that have to be taken depending on how the form is going to be used. 

Reports 

Reports are for static presentation of the data, whether on paper or on screen. 

As the data is not going to be manipulated, the design considerations are only going to be in relation to the presentation of the data. 

Microsoft Access The Basics – Column Views

Column Views 

At times, you do not want to view the table in a particular way.

Sometimes there will be columns that you do not want to see as they are not relevant at that point in time for what you need.

At other times as you scroll across to view other columns you still want to be able to see key columns.

Hiding /unhiding columns 

Columns can be hidden by

1. Highlight column(s).

2. Right click on column heading of column to be hidden.

3. Select Hide columns.



Columns can be unhidden by
Highlight column(s) on either side of the hidden column(s)

1. Right click on column heading.

2. Select Unhide Columns.

Freezing Columns 

At times as you scroll across the screen, you want to be able to always see certain columns rather than have them disappear to the left so that you can identify the records.

In this example, we can see the city country and telephone numbers but we can’t tell who the customers are





Columns can be frozen by:

1. Highlight columns to be frozen.

2. Right click on column heading

3. Select Freeze Columns.

(Frozen Columns can be unfrozen by selecting the Unfreeze All Columns command




By freezing customer id and customer name columns as you scroll across to see other data you will still see the two frozen columns.

Microsoft Access The Basics – Creating-Tables

There are many ways of creating tables; it depends on the fields required in the table and the complexity of the database to be set up as to how you create the tables.

If the database that is required is similar to a template then whole database can be generated by using a template that already exists.

We will look at three ways of creating a table:

1. A blank table.

2. A table based on a table.

3. A table in design view where you have total control as to how the data is displayed and entered into the table.

A Blank Table 

When a blank database is set up a table is automatically created: Otherwise to create a blank table

1. Click on the Create Tab of the Ribbon.

2. Click on the Table icon.

The table can be amended to contain the data required by:

Clicking on the column headings and changing them as necessary.  If you are not happy with the first field name of ID then it can be changed as necessary.

Other field names can be changed by Clicking on the Add New Field column heading.  As fields are added that heading just keeps moving along onto the next blank column.



The data can be added underneath the field names and as the data is added the fields will automatically get their data-types set from the information typed into the table.

The ID field is set up to be AutoNumber as default and so cannot be typed into as the number is generated automatically when information is added elsewhere on the record

If the data needs to be changed then the Data Type for the field needs to be changed.  The data types for any of the fields can also be changed if necessary.

Changing the data type 

1. Select the field to be changed.

2. Click on Data Type (on the Datasheet ribbon selected automatically).

3. Select the Data Type required.



When a table has been created in this way when you close it the first time it must be saved, otherwise all the changes and data is lost. 

Saving the table 


1. Right Click on the table name.

2. Select Save or Close.



If Close has been chosen you will be asked whether you wish to save – answer yes!

3. Enter name of table

Creating a table based on a template 

If the table that you need to create follows a standard pattern, then you do not need to start from scratch and build up the entire table.  You can use a template and then use the table as it is or amend it as necessary.

To create a table from a template click on the Table Templates button and then select the template required.  This creates a table with the appropriate fields already set up.  At this point, more fields can be added or the existing fields amended.

The table will need to be saved otherwise all the data and setup will be lost.

Create a table in design view  

When a table is created in design view, you have total control over the table.  When you have gone through the design phase of planning the database this is probably how most people set up the database.

Before creating a table in this way not only do you need to know what fields are needed in the table, but also what sort of data they will hold, what rules the data has to follow and how the tables are going to be inter-connected.

Once a table has been set up, whether by design view, blank table or a template, it can then be changed by using the design view of the table.  There are factors that have to be taken into consideration when amendments are being made.

The main considerations are:


Data types.

Relationships.

Validation rules.

The way of making the inter-connections between the tables is known as lookups.  If Lookups are to be set up then the relationships cannot be set up prior to the lookups.

The purists amongst database designers do not think they should be done at this point of a database development.

To create a table in design view – click on the button on the create ribbon – this brings up the design view.



1. List the fields in this column.

2. Choose the data type for each field.

3. Add a description of the field – this will appear on the status bar whenever the cursor is in this field where data can be entered. (Optional)

4. Set the properties of the selected field.

The field name can be up to 64 characters long.  Spaces are allowed (though not advised if you are going to be using the more advanced features of Access.)  The field name cannot start with a space and capital letters are allowed.

The data type controls what sort of data needs to be typed in.  It is not dependent on the field name.

For instance, the field name might be telephone number but the data type needs to be text.   When typing in the data there would be a space, it would start with a Zero which numerically has no value and you might even want to put in an extension number.

Microsoft Access The Basics – Creating a New Database

Creating a New Database 

A new Access file is an empty database that you create or it may be based on an existing template where certain elements of the database are created automatically.

When you create a new database, it has to be saved.  Whereas when you create a new document in Word or a new spreadsheet in Excel, you can set up the document or spreadsheet and then decide when to save.

The Process to create a database is

• Click on the Office button.

• Select New.

(Using the Office button is not necessary if you are creating a new database on first entering Access).

A New Blank Database 

To create a blank database click on




This will then allow you to name and save the database into a chosen location

1. Click on the button and choose the location of the database.

2. Enter the file name for the database.

3. Click on the create button.

A New Database From a Template 

1. When a new database is going to be based on a template, you need access to where the template is.  The database template can be on your machine or it can be a Microsoft template that you need to connect to on-line.

2. Once a template category has been selected, it updates the list of templates available. Select the template required.

3. A database with the same name as the template will be created and saved in the default location unless you choose the location and give the database a name.

File names can be upto 255 characters (including the file extension) the name cannot start with a space but can have spaces within the name. Ccertain characters should not be used in file names as they can produce unexpected results.

These are invalid characters for file names    ?, ",/,\,<,>,|,:

When a new database is created any database that was already open will automatically be closed.  If anything needed to be saved a prompt would have come up asking you to save if you wanted to.

Microsoft Access can only have one database open at a time.  The same is true when you open an existing database.  If you want more than one database open at the same time then you need to have Microsoft Access running once for each database.

Microsoft Access The Basics – Filtering Data

Filtering data 

Sometimes finding the data, where you have to go to each record one by one, is not enough. The data can be filtered instead – this means that you only see the data that meets the specified criteria.

This can be done in several ways depending on whether you already have a record with the appropriate criteria selected or not.

Filter 

1. Position cursor in field to be filtered.

2. Click on the filter button on the ribbon or the drop down button on the field name.




3. Make selections required.

Click on select all to remove the selection of everything and then select however many filters required.

Filter by Selection 

1. Select a field containing the data to be used in the filter.

2. If the criteria needs to be the same then click on the selection button in the Sort and Filter section of the Home Ribbon.

3. If the criteria should be a
n exception or partial match, then click on the drop down arrow at the end of the selection button and make the choice required.




There are other more advanced ways of filtering – filter by form and advanced filter.  Filter by form is an alternative way of selecting items to the normal filter and the advanced filter is very similar to queries (that we will cover later).

Removing a filter 

Once the filtered data is no longer required, you will need to go back to viewing all of the data.  This can be done via the ribbon or the navigation bar at the bottom of the window.



A filter has been applied.  This is shown by the orange background on the Toggle Filter button.  To remove the filter click on the button.



As there is no background colour on the Toggle Filter button, you can tell that no filter has been applied.  If you wish to re-apply the last filter then click on the Toggle Filter.

On the navigation bar it the button displays filtered or no filter – again it is a toggle button so it will either remove or apply the last filter.

Microsoft Access The Basics – Editing and Deleting Records

Editing and Deleting Records 

To make changes to an existing record:

1. Select record required.

2. Position cursor on the field to be changed.

3. Go into Edit Mode by either clicking at the appropriate place or by pressing F2 key.

4. Make changes to current field.

5. Repeat steps 2 – 4 as required.

6. Save the record.

A single ESC will remove the changes to the current field, and a double ESC will lose all the changes to the current record.

Deleting Records 

At times records will need to be removed from a table.  May be because they are old records that are no longer active, (should they be archived instead?), they were input incorrectly, or were duplicated (with a unique key).



1. Select record(s) to be deleted by clicking on the record selector.  (If multiple records are required then you need to drag on the record selector to highlight the necessary records).

2. Click on the Del button on the keyboard or the delete button on the records section of the home ribbon.

3. Click yes on the are you sure dialog box.

Microsoft Access – Defining Relationships

Defining Relationships 

The relationships define how the tables are interconnected.  If Lookups are created then the relationships are set up automatically but all the settings are not input automatically.

To view or change the relationships you need to be able to see the current relations.

They can be displayed by clicking on the Relationship button on the Database Tools button.



This opens up the relationship window that shows the tables that are already included in any relationships.

If this is the first time that you have gone into  the relationship window, the only relationships it would display are those that have been set up via the Lookup Wizard

Even if the lookup has been created and then cancelled by changing the property Display Control back to text the relationship remains.

To create a relation ship between two tables the two tables must be in the relationship window.  To add more tables to the window you need to click on the Show Table button.  This will allow you to add however many tables you need

Microsoft Access Reports – The Report in Design View

Report Sort Order 

On the Design Ribbon Clck on the Group and Sort Button in the Grouping and Total section .  This displays the bar at the bottom where  you can set the grouping and sorting setttings by clicking on the dropdown arrows and then selecting the options required.

Microsoft Access Macros – Attaching a macro to-field properties

Attaching a macro to field properties 

Macros can be added to any objects whether they are on forms or reports.  They can be added to the event properties of the object.

1. Add or select the control

2. Show properties

3. Select the Events categories

4. Select the event required.

5. From the drop down select the macro

The macros do not have to be added to command buttons they can be added to other controls as well. 

Microsoft Access Macros – Attaching a Macro to a Command Button

Attaching a Macro to a Command Button. 

A macro can be attached to a command button that has been added to a form.


1. Open the form in design view

2. Click on command, in the control group on the design ribbon.

3. Position the control on the form.



4. Select miscellaneous on the categories section and then run macro.

5. On the next screen select the macro required whether it is a standalone macro or a grouped macro.

6. Then decide whether to display text on the button or a picture.  If the text option is selected then the text can be changed as required.

7. Finally give the macro a name.

This creates an embedded macro (in earlier versions it would create VBA code).