1.2 Microsoft Access

MS Access is a relational database system for workstations that run the Microsoft Windows operating system. MS Access is typically used by individuals for data they use personally, but in some situations a single MS Access database may be used by a group of people or small department.

MS Access databases are stored in a single file that has a file suffix of “.accdb” or “.mdb”. Databases created using MS Access 2007 and later have a file suffix “.accdb”, and databases created using MS Access 2003 or earlier have a file suffix “.mdb”. We will be using databases where the files have names ending in “.accdb”. You need to use MS Access 2007 or later to open these databases. We have used Access available in Microsoft 365.

Our first sample database is in a file named Library.accdb; this database is available from the website associated with this text.

To use this database, you must first download the file containing the database, and then either:

      • Navigate to its location in File Explorer, and open the database by double-clicking the file name.
Figure 1.4: Double-click the database file to open the sample database
      • Start Access and then browse to the folder holding the database, select it, and open it.

Select Open in Access:


Then browse to the location of the database file and open the file:


                   Figure 1.5: With Access started, browse and open the database

When you open this database, you see a list of objects (figure 1.6) in the database; you will see three tables: Book, Loan, Member:


                      Figure 1.6: The Home tab in MS Access shows you the table names

Double-click a table name and MS Access opens the table in Datasheet View; you can see the contents of Book in figure 1.7. The datasheet view for a table is easily obtained, but it is not a particularly user-friendly way to view and manage data in a table. We will learn other ways of handling data with MS Access Forms. The Book table has three fields (i.e., attributes): callNo, title, author. When we view a table we see data organized into rows and columns. The data in one row corresponds to one book; if there are 11 books, then we have a table of 11 rows.


                                        Figure 1.7: Datasheet View of a table

The Book table contains one row for each book in the library. We can verbalize the content of a row as:

      • The book identified by call number is titled and is authored by

Substituting actual values from rows we can make explicit statements such as:

      • The book identified by call number PC 14 V48 1965 is titled Medieval miscellany and is authored by Frederick Whitehead
      • The book identified by call number QA 76.76 A65P76 2011 is titled Programming Android and is authored by Zigurd R Mednieks

Knowing that books are identified by their call number and since the above statements use the conjunction ‘and’, the above verbalization can be expressed in an elementary form as:

      • The book identified by call number is titled
      • The book identified by call number is authored by

Each of these expressions is considered elementary because each states one fact about a specific book. We cannot make these statements any simpler.

Of course, we can now substitute values from the table and obtain:

      • The book identified by call number PC 14 V48 1965 is titled Medieval miscellany
      • The book identified by call number PC 14 V48 1965 is authored by Frederick Whitehead
      • The book identified by call number QA 76.76 A65P76 2011 is titled Programming Android
      • The book identified by call number QA 76.76 A65P76 2011 is authored by Zigurd R Mednieks

At this point, expressing verbalizations this way may seem trivial and unnecessary, but they do serve a purpose – they make it clear that the title and the author’s name serve only to describe a book, and that the call number identifies the book. An aim of a database designer is to understand data requirements in terms of these elementary forms.

We will have more to say about this in a later chapter.

Up to this point we have seen how to:

      • open an MS Access database;
      • recognize the database comprises a number of tables;
      • open a table to see it displayed as a collection of rows and columns;
      • verbalize the information in a table.

Next, we will examine the basic features of MS Access that allow us to change, insert, and delete data.



Recall that an elementary verbalization is one where the verbalization cannot be simplified in any further way. Simpler statements would result in a loss of information.

1) Rewrite the verbalization for the Employees table using elementary verbalizations.


2) Is the verbalization given for Circulation of Leading U.S. Magazines in elementary form?


3) What elementary verbalizations apply to the Loan table in the Library database?


4) What verbalizations apply to the Member table in the Library database?


5) View the data in the Loan table. Each row in the table corresponds to a member borrowing a book. Notice how the call number field contains values that appear in the Book table and how the id field contains values that appear in the Member table. All rows have a value for the date borrowed field. Why would some of the date returned fields appear to have no value at all?


The web site for these notes has a number of databases. Download the University database and examine its contents. This database contains information about departments and courses in a fictional university. Typically, a university is organized into faculties which comprise departments and those departments offer courses. For instance, many universities have a Faculty of Science which itself may contain departments such as Mathematics, Statistics, and Physics. Each of these departments will offer courses for students to take: Introduction to Calculus, Introduction to Statistics, Discrete Mathematics, etc.


Share This Book