jargon that means it’s a Database Management System or DBMS. So what is a database? A database is basically a collection of data or pieces of information. Whether you know it or not, you use databases all of the time. Some examples of commonly used databases might be:
• Address book
• Library catalogue
• Telephone directory
• Stock list
A database isn’t necessarily contained on a computer. A telephone directory is still a database even if it’s in the form of a huge book sitting next to your phone. However, for the purpose of these exercises, we will assume that the term database refers to an organised collection of information stored on a computer.
Databases are intended for storing and maintaining large amounts of information. The following are examples of the sort of information that can be kept in a database:
• Inventory control
• Payroll systems
• Personnel records
• Music collection catalogue
• Phone and address lists
In the exercises that will follow you will create a simple database while you learn the basic features of Access. Later on, you will create a more complex database as you learn more advanced features in addition to some of the principles of database theory. In these exercises it is assumed that you are familiar with Windows use and have experience in using other applications such as Word or Excel.
Starting Microsoft Access
Like most applications, Access can be
started in several ways, such as clicking
the Microsoft Access shortcut in the
Windows Start Menu as shown. The
location on the start menu will vary on
different computers.
1) Start Microsoft Access by
clicking its icon on the start
menu (or by any other
method you prefer).
When Microsoft Access begins, you will see a window similar to the one shown below.
On the top of the window you will see menus and toolbars similar to the ones found in most
applications. On the right, you will usually see the task pane which provides shortcuts to common tasks, such as opening or creating a database (the task pane is also a feature of recent versions of Word, Excel and other Microsoft Office applications).
Creating a new Database
You will notice that the first time you open Access the screen is rather empty and unfriendly. This is very different from other programs such as Word or Excel where you are presented with a new, ready to use document each time you start the program. This is because you can’t begin using a database without setting one up first, which takes a fair amount of effort. That is why you don’t get a blank, ready to use database when you begin the program. The first thing we will need to do is create a database to use.
The first step is to create and save a blank database file. In other programs, you usually don’t save a file until you’ve done something with it but in Access, saving the file is the first step. This is because in Access, many things are automatically saved as you work so Access needs to know where to save it all right at the start.
Creating a Database File
1) Make sure your task pane is showing. If it isn’t, go to the View menu and select Task Pane
(or press [Ctrl][F1]).
2) From the list of options in the task pane, click (you can also click the
icon on the toolbar).
3) From the new list of options which appears, click .
4) For the file location, select (or create) and appropriate folder in your student drive. Type
Student List for the file name and click Create.
The file will be saved with a .mdb extension (for Microsoft Database).
Note Creating a database should normally only be done after a lot of planning has taken place. In the following exercises it is assumed that the database you will create has already been well planned.Later on we will get in to some procedures for planning a database.
The Database Window
When you create a new database or an existing database, you will usually see the Database Window similar to the one shown below (any time it is hidden or covered by other windows you can press F11 to make it appear)
Databases are made up of various objects such as tables, queries and forms. The options down the left side will take you to the main sections of the database. The options at the top of the database window allow you open existing database objects modify database objects
or create new database objects .
Some older database management systems required you to create a separate file for each object in the database. In Access, all of these database objects are contained in the same database file. The first database object we will look at is tables.
Tables
Tables are the most important component of an Access database because tables are where all of your information is stored. Tables in an access database are similar in many ways to Excel tables. Most Access databases will consist of more than one related table, but the first database we will create will be a simple, single-table database. Each table is made up of columns referred to as Fields and rows referred to as records.
Example: A telephone book is made up of several related tables. There is the main table which
contains all of the phone listings. There are also related tables containing information such as area codes and post codes. The following is an example of how a telephone listing may appear in a phone book.
Each category of data (Name, Initials, Address etc) is a field.
Each individual item of information (such as the information for A L Smith) is a record.
Fields and Primary Keys
When you create a new table, one of your first tasks is to create the fields that will make up the table. One of the fields should be a primary key. A Primary key is a field which is unique to
each record. In the example above, Name is not unique for each record, since more than one record can contain the same name as shown. The same is true for Initials and Suburb. It is even possible that there may be more than one phone listing for people at the same Address. Therefore the only field which would be unique to each record is Phone, since each phone number would only have one listing in the table. This would make Phone a good choice for a primary key.
Creating a Table
1) Begin by making sure you are still in the Tables section of the database window.
2) Click the button to create a new table.
3) The New Table dialog will appear. Select Design View from the list of options and click
OK. A new blank table will now appear in the design view (You can also click the
option in the database window to create a new table).
The table design window will look similar to the one below.
The top section of the window is where you enter the names of the fields in your table along with the data type for each field. When you have a field name selected in the top section, the bottom section will display properties which allow you to customise the selected field.
Field Data Types
Each field in a table can be one of several available field types. When you enter a field name in the design view, the next column will allow you to choose a field type. The following field types are available to choose from. You will use several of these in later exercises.
Creating Fields
1) Make sure the first row in the Field Name column is selected.
2) Type Last Name and press [Tab] or [Enter]. This will move you to the Data Type column.
3) Leave the Data Type as Text and press [Tab] or [Enter] to move to the Description column.
4) In the Description column type Last name of the student and press [Tab] or [Enter] to move
on to the next line.
5) For the next field enter First Name as Field Name, Text as Data Type and First name of the
student as the Description.
6) For the next field enter Date of Birth as Field Name.
7) In the Data Type column we want to change it to Date/Time. An easy way
of selecting Data Types is to press the first letter of the one you want.
Press the letter D and Date/Time will become selected. Enter Birth date of
the student for the Description and move to the next line.
8) Complete the remainder of the fields so that they are the same as the ones
shown below.
Creating a Primary Key
At the moment the table has no primary key specified. None of the fields would contain information that would be unique to each student. For instance, the same last name could be shared by more than one student so last name could not be used to identify a specific student record. The same is true for each other field. In cases like this where there is no field unique to each record, a new field can be created and used as a primary key.
1) Click on the first record (Last Name).
2) From the Insert menu choose Rows to insert a new row.
You can also insert a new row by right-clicking on a row and choosing Insert Rows or by clicking the icon on the toolbar.
3) Enter Student No as the new Field Name.
4) Choose AutoNumber as the Data Type.
5) For the new field Description enter - Unique identification number.
An AutoNumber field is the only one where you don’t need to type anything in to a record. For each new record entered, Access will automatically place a new number in the AutoNumber field. It is useful as a primary key field as it ensures that each record will have a unique number.
6) Make sure the Student No field is still selected
7) From the Edit menu select Primary Key. A key symbol will appear next to that field.
You can also set a field as a primary key by right-clicking and selecting Primary Key or by clicking the icon on the toolbar. If you accidentally set the wrong field as a primary key, then simply select the intended field and choose the primary key option once again to set it as the new primary key.
Saving a Table
It is usually best to specify a primary key for a table before saving it as we have done. Otherwise, Access will want to create an extra field as a primary key. When a table is saved, it doesn’t become a new file. It becomes an object within the database file you have already created. In all other ways though, it is similar to saving a file.
1) From the File menu choose Save (you can also press [Ctrl][S] or click the icon).
2) For the table name type STUDENTS.
3) Click OK to finish saving the table.
Note You can call the table whatever you want but it is best to choose names that describe what sort of information the table will store. This helps when there are several tables in the database. Also, some developers like to name tables in uppercase letters and queries in lowercase letters or use prefixes in names to make it easy to distinguish between the two.
Entering a Record
While a table is open, you can quickly switch between the design view (used for modifying the design of the table) and the datasheet view (used for working with the actual data in the table).
1) Click the View icon on the toolbar (first icon on the far left). This is a quick way to
switch between views. You can also change views from the View menu.
2) Notice that the first column is for the Student Number field and it is filled with the text
(AutoNumber). We don’t need to enter anything in this field since a student number will be generated automatically (remember that’s what an AutoNumber field’s for) so press
[Tab] to move to the Last Name field.
3) Type Robbins as the Last Name. Notice that as soon as you enter information, a number
will appear in the Student Number column. Every record will be given a unique number. If
you delete a record, that number won’t be used again.
4) Press [Enter] after typing the name and the cursor will move to the next field.
5) Complete the rest of the fields as shown below.
6) When you reach the last field (Comments) press [Enter] again to move to the next line.
As soon as you move off that row, the record will be saved. Changes to table data are saved
automatically in Access. The only time you need to save any changes yourself is when you’ve
modified the structure of a database by adding or modifying the design of objects (such as tables). Changes to data are saved for you.
7) Click the View icon to return to the Table Design view.
Opening a Database
Opening a database in Access is similar to opening a file in another program. When access first starts, you can click the More... option from the task pane as shown to the right.
At other times you can open a database by selecting File, Open, pressing [Ctrl][O] or by
clicking the icon on the toolbar.
1) Use one of the above methods to bring up the Open dialog.
2) Open the Student List.mdb database.
Setting up a Query
Your Student List database should already be open with the Database Window showing.
1) Click the Queries button on the left side of the Database Window.
2) Double-click the option that says Create query in Design View.
The Show Table window will appear, allowing you to select the source table or query to be used. The Query design window will be visible behind that.
3) Make sure the STUDENTS table is selected as shown above and click Add (you can also
double-click the name of a table to add it).
4) Click Close to close the Show Table window and you will now be in the Query Design
window as shown on the following page.
The top section of the window shows a list of fields in the source table(s). The bottom half, referred to as the QBE Grid (Query by Example), is where you can select the fields and criteria essential to the query results.
Customising the Design Window
1) Move your mouse over the bottom-right corner of the window until your
mouse appears as a re-sizing arrow shape.
2) Drag your mouse downwards to extend the bottom border of the window.
Next, you could drag the border between the top and middle sections of the design window downward so that you can see more of the table fields listed.
3) Move your mouse over the line between the
top and bottom half until your mouse appears as
a re-sizing arrow.
4) Drag your mouse downward so that the top section of the window is larger.
Lastly, you can re-size the window in the top section which
lists the table fields.
5) Re-size the table field list so that you can see more of the fields
(perhaps all of them) without having to scroll)
Selecting Fields for Query Output
1) Make sure the first column in the QBE Grid is selected. The first row is for the fields
names and a drop-down arrow will appear next to selected box.
2) Click the arrow and a list of fields from the STUDENTS table will
appear.
3) Select Last Name from the list. The Query results will now show the Last Name field.
4) Click the Datasheet View icon to view the results of the current query.
You will see a list of all students (since we haven’t yet specified any criteria) with only the Last
Name field showing.
5) Click the Design View icon to return to the Query Design view.
Fields can also be dragged to the QBE Grid from the field list in the top section of the window.
6) Click on First Name in the Field List at the top and drag it down to the
column next to where you selected the Last Name field (you can also drag
it on to a column that already contains a field to insert it before that column).
7) Click the Datasheet View icon to view the results of the current query.
8) Click the Design View icon to return to the Query Design view.
You can select multiple fields from the Field List using the [Shift] and [Ctrl] keys the same way you can select multiple files in Windows Explorer.
9) Click on Date of Birth in the Field List.
10) Hold down [Shift] and click State in the Field list. All the fields in
between will be selected.
11) Hold down [Ctrl] and click Mark in the Field List. This field will be
added to the selection.
12) Drag any one of the selected fields to the next available column in
the bottom section and they will all be added to the QBE Grid.
13) Click the Datasheet View icon to view the results of the current query.
14) Click the Design View icon to return to the Query Design view.
The last and possibly most commonly used method of selecting fields is double-clicking. First we will clear the existing selection of fields so we can have a fresh start.
15) From the Edit menu select Clear Grid. This will clear the QBE Grid.
16) Double-click on Last Name in the Field List. It will appear in the first available column of
the QBE Grid.
17) Double-click to add the following fields.
First Name, Date of birth, Address, Suburb, Postcode, State, Phone, Gender, Mark.
These fields will be used in the queries that will follow.
Saving a Query
1) Click the Save icon on the toolbar.
2) For the query name enter test query as shown above and click OK.
Adding Criteria to a Select Query
In this exercise we will use criteria so that the query results will only show records for students
where the suburb is Dianella. Any records with a different suburb will not appear in the results. Your
test query from the last exercise should still be open. 1) Click in the Criteria row in the Suburb column.
2) Enter Dianella as shown to the right. It doesn’t matter if you use
uppercase letter for the criteria or not. It makes no difference.
3) Click the Datasheet View icon to view the results of the current
query. Your results will only show records matching the criteria. I.e. all records that have
“Dianella” in the Suburb field.
4) Click the Design View icon to return to the Query Design view.
You will notice that Access has placed quotation marks around your criteria. Access uses quotation marks to specify that it is text criteria. When dates are used as criteria the date will be surrounded by a # on each side as you will see later. Numbers are left alone. When entering criteria you don’t need to enter these symbols yourself but be careful when editing. If you delete quotation marks on one side and not the other, you will get an error.
5) Change the criteria from Dianella to Yokine.
6) Click the Datasheet View icon to view the results of query again. Now you will only see
students from the suburb of Yokine.
7) Click the Design View icon to return to the Query Design view.
8) Delete the criteria in the Suburb field and click to save the query.
Using “And” Criteria
“And” criteria work by using more than one criterion in different fields. This has the effect of
reducing the number of results. In the last page, you used criteria in the Suburb field to limit the
results to only six records. If you decided to also specify that you only wanted results for female
students you would reduce the results even further. I.e. you would only see results where the record is in a certain suburb and a certain gender.
1) In the Suburb field, enter Dianella for the criteria.
2) Scroll to the right until you can see the Gender field.
3) Enter Female for the criteria in the Gender field.
4) Click the Datasheet View icon to view the results of query. You will only see records
where the record has Dianella for the Suburb field and Female for the Gender field. The additional criterion has reduced the number of results from six records to two records.
Return to Design View when done.
Using “Or” Criteria
1) Delete any existing criterion in your query.
2) In the criteria row for the Suburb field enter Yokine or Bedford or Morley.
3) Click the Datasheet View icon to view the results of query. You will see records that
have any one of those suburbs in the Suburb field. Return to Design View when done.
You can also use or Criterion by placing each criterion on a different row. You will notice that under the Criteria row is an Or row which works an “or” operator. All of the ones below it are also rows used for Or criteria so you can have as many criteria as you like in a single field.
4) Delete the criteria in the Suburb field.
5) Enter the same suburbs one under the other as shown below.
6) Click the Datasheet View icon to view the results of query. You should get the same
results as before. Return to Design View when done.
Although the results were the same in this instance that is not always the case, particularly if there are criteria in other fields. Consider the following two examples.
In the first example, the results will show records for Male students in Yokine or Bedford. In the
second example, the results will show Male students in Yokine and all students from Bedford. This is because in the second example, the Gender field criteria only applies to the suburb on the same line. If you wanted both Suburbs to only show Male students you would need to modify the query as shown below.
Using an Upper or Lower Limit
Logical Operators can be used to set a logical limit on the range or results. If you want to specify a “Less than” criteria you can use the < symbol. To specify a “Greater than” criteria, use the > symbol.
7) Clear any existing criteria from your query.
8) Click in the Date of Birth criteria row.
9) For the criteria, enter <1/7/89. 10) Click the Datasheet View icon to view the results of query. You will see all students with birth dates before 1/7/89. Return to the Design view. 11) Delete the criteria in the Date of Birth field. 12) Scroll right to the Mark field and enter >70 for the criteria.
13) Click the Datasheet View icon to view the results of query. You will see all students
with a mark higher than 70. Return to the Design view.
14) Delete the criteria in the Mark field.
15) Scroll left to the Last Name field and enter
Mark <54 Marks less than but not including 54 Mark <=54 Marks less than and including 54 First Name >=M First names beginning with the letter M or later in the alphabet.
Using an Upper and Lower Limit
You can use range criteria to specify an upper and lower range so that records between those values will appear in the results.
1) Clear any existing criteria from your query.
2) In the Mark field, enter >=50 And <80 for the criteria and view the results of the query. 3) Your results will show all records for students with a mark that is 50 or more, but less than 80. 4) Clear the Mark field criteria. 5) In the Date of Birth field, enter >=1/3/89 and <=30/9/89 for the criteria and view the
results of the query.
6) Your results will show all students with birthdates between the dates specified (and
including those dates).
Creating a Datasheet Auto Form
1) Make sure your Student List database is open.
2) Select the Forms section from the Database Window.
3) Click the New button at the top of the Database Window.
4) When the New Form dialog appears, click on the list at the bottom as shown
below. A list of all your tables and queries appears since forms can be based
on either.
5) Select your STUDENTS table from the list (it’s easy to tell which one’s a table because we
named tables in uppercase and queries in lowercase).
6) From the list of options at the top of the dialog, select AutoForm: Datasheet.
7) Click OK to create the form.
The end result is a form that looks and acts the same as a table. It may not seem very useful to have a form that’s the same as a table but it can be very useful for subforms as you will see later on. Sometimes it is handy to have a list inside a form. Since a table can’t be placed inside a form, another form that looks like a table can be used instead.
8) Close the form.
9) When you are prompted to save the form, click Yes.
10) Enter STUDENTS: Datasheet as the form name and click OK.
Creating a Tabular Auto form
1) Click the New button at the top of the Database Window.
2) When the New Form dialog appears, select the options shown above (STUDENTS as the table and AutoForm: Tabular as the type of form).
3) Click OK to create the form.
4) Close the form.
5) When you are prompted to save the form, click Yes.
6) Enter STUDENTS: Tabular as the form name and click OK.
A tabular form can be used in the same was as a table, but it can be formatted and customised a lot more than a table. This form could be neatened up in Design View as you will see later.
Creating a Columnar Auto Form
This is the type of form that was created in the first section when you used a form for data entry.
1) Click the New button at the top of the Database Window.
2) When the New Form dialog appears, select the options shown above (STUDENTS as the table and AutoForm: Tabular as the type of form).
3) Click OK to create the form.
Unlike the other two forms, this one only shows one record at a time. Most people find this makes it easier to work with records.
4) Close the form.
5) When you are prompted to save the form, click Yes.
6) Enter STUDENTS: Columnar as the form name and click OK.
Using a Form Wizard
1) Click the New button at the top of the Database Window.
2) Choose Form Wizard from the list of options. Choose STUDENTS for the table.
3) Click OK to begin the wizard.
In the first step of the wizard, you are asked to specify which fields will be used in the form.
4) Click the button to select all of the fields for use in the form. All of the fields will now be listed on the right side.
5) Double-click on Student Number to move it back over to the left, since we won’t need to have that displayed in our form.
6) Click Next to move to the next step of the wizard.
7) Leave Columnar selected for the Form Layout and click Next.
8) Click on each of the different form styles to see the preview for each one.
9) Select a style you like and click Next.
The last step in the wizard asks you to specify a name for the form. When you click Finish, the form is automatically saved with that name.
10) Type Student Entry Form for the form title and click Finish.
11) Close the Form. There is no need to save it since it was saved at the end of the wizard.
Creating a Tabular Auto Report
1) Make sure you are in the forms section of the Database Window.
2) Click the New button at the top of the Database Window.
3) Select AutoReport: Tabular and make sure the STUDENTS table is selected as the source.
4) Click OK to create the report. The report will appear in Print Preview ready for printing.
5) Close the report. When prompted, save the report as Student Report: Tabular.
Creating a Columnar Auto Report
1) Click the New button at the top of the Database Window.
2) Select AutoReport: Tabular and make sure the STUDENTS table is selected as the source.
3) Click OK to create the report. The report will appear in Print Preview ready for printing.
4) Close the report. When prompted, save the report as Student Report: Columnar.
Using a Report Wizard
1) From the Database Window, click the option that says Create report by using wizard.
2) In the Tables/Queries list, make sure that table: STUDENTS is selected.
3) Click the button to select all of the fields for use in the form. All of the fields will now be listed on the right side.
4) Double-click on Student Number and Comment to move them back over to the left as shown above.
5) Click Next when ready.
6) The next step allows you to choose grouping levels for your report. Double click on Gender to select that as the grouping field. This means that all of the female students will be grouped
together in the report and all the male students will be grouped together.
7) Click Next to continue.
This step in the wizard allows you to choose how the records in the report will be sorted. There are also options for adding totals and subtotals to your report.
8) In the first sort box, select Last Name as shown above. You can also select additional fields for sorting in case there are any records with the same last name.
9) Click the Summary Options button.
You can use these options to add totals for any number fields. The only field available here is Mark.
10) Click the box to put a tick under the Avg option as shown above. This will add an average mark figure to the report.
11) Click OK to return to the wizard.
12) Click Next to move to the next step.
13) Click Align Left 2 for the report layout with Landscape selected as the orientation and click Next.
14) Select a report style and click Next.
15) Enter Students by Gender for the report name and click Finish.
Note The name of the report will also become the main heading on the report so choose carefully.
Report Design
Several things may appear in the Report Design window. Some may appear to begin with and others may need to be turned on before they will appear.
The design window itself has several sections as shown below.
Customising a Report in Report Design
1) With the Students by Gender report still open, click the View icon to go to Design View.
2) Click on the Text Box icon in the toolbox to the left of the screen.
3) Click in the Report Footer area to place the textbox as shown. A textbox will usually have a label next to it so make sure you don’t click too close to the left.
4) When you click, a textbox and a label will be created.
5) Click in the label (the one that says Text33:). Edit the label so that it says Total Students.
6) Edit the textbox (the one that says unbound) and change it so it says =count([Mark]).
This is a function that will count all of the records using the Mark field. The square brackets are used because they are necessary for any formula that refers to a field name.
7) If you are still editing the textbox, press [Enter] to exit edit mode. The whole box should be
selected. Move your mouse over the top-left corner of the box. Your mouse pointer will change
to a hand shape as shown.
8) Use this corner to drag the textbox towards the right edge of the report.
Normally dragging a textbox will move it’s related label as well. Using this corner will move only the textbox.
9) Click the view icon to preview the changes to the report and see your new total.
10) Close the report and save any changes.
Creating a Report from a Query
1) From the Database Window, click the option that says Create report by using wizard.
2) From the Tables/Queries list, select Query: parameter: students by suburb as shown.
3) Click the icon to select all of the fields in the query and then click Next.
4) Click Next again to skip the grouping options.
5) Choose to sort by Last name and First name as shown and click Next.
6) Leave the report layout as Tabular and click Next.
7) Select a report style and click Next.
8) For the name of the report, enter Students by Suburb and click Finish.
Because the report is based on a parameter query, the parameter criteria prompt will appear.
9) Enter Morley as the suburb and click OK to see the report.
10) Click the View icon to enter Design view.
The Report Header section contains a label with the text Students by Suburb. We will change this in to a textbox that shows the name of the suburb being displayed in the report results.
11) Click the Students by Suburb label to select it.
12) From the Format menu choose Change To and then Text Box
as shown to the right. When the label changes to a textbox,
its contents will change to Unbound, which just means that
we haven’t specified what will appear in the textbox yet.
13) In the textbox enter the following
="Student Listing for” & [Suburb]
This is a formula that will take the text inside the quotation
marks (including the space after the word for) and then the &
character will join it to the contents of the Suburb Field (since
we are using the query to only show the results for one suburb.
14) Re-size the textbox to make it wider.
15) Click the view icon to preview the report.
16) Enter Morley for the suburb again. The modified report header should now include the suburb as shown below.
17) Close the report and save the changes.
18) Double-click the report to test it again with a different suburb (such as Yokine or Bedford).
19) Close the report again when you are done.
Posts
