1.2: Microsoft Access
- Page ID
- 15490
\( \newcommand{\vecs}[1]{\overset { \scriptstyle \rightharpoonup} {\mathbf{#1}} } \)
\( \newcommand{\vecd}[1]{\overset{-\!-\!\rightharpoonup}{\vphantom{a}\smash {#1}}} \)
\( \newcommand{\id}{\mathrm{id}}\) \( \newcommand{\Span}{\mathrm{span}}\)
( \newcommand{\kernel}{\mathrm{null}\,}\) \( \newcommand{\range}{\mathrm{range}\,}\)
\( \newcommand{\RealPart}{\mathrm{Re}}\) \( \newcommand{\ImaginaryPart}{\mathrm{Im}}\)
\( \newcommand{\Argument}{\mathrm{Arg}}\) \( \newcommand{\norm}[1]{\| #1 \|}\)
\( \newcommand{\inner}[2]{\langle #1, #2 \rangle}\)
\( \newcommand{\Span}{\mathrm{span}}\)
\( \newcommand{\id}{\mathrm{id}}\)
\( \newcommand{\Span}{\mathrm{span}}\)
\( \newcommand{\kernel}{\mathrm{null}\,}\)
\( \newcommand{\range}{\mathrm{range}\,}\)
\( \newcommand{\RealPart}{\mathrm{Re}}\)
\( \newcommand{\ImaginaryPart}{\mathrm{Im}}\)
\( \newcommand{\Argument}{\mathrm{Arg}}\)
\( \newcommand{\norm}[1]{\| #1 \|}\)
\( \newcommand{\inner}[2]{\langle #1, #2 \rangle}\)
\( \newcommand{\Span}{\mathrm{span}}\) \( \newcommand{\AA}{\unicode[.8,0]{x212B}}\)
\( \newcommand{\vectorA}[1]{\vec{#1}} % arrow\)
\( \newcommand{\vectorAt}[1]{\vec{\text{#1}}} % arrow\)
\( \newcommand{\vectorB}[1]{\overset { \scriptstyle \rightharpoonup} {\mathbf{#1}} } \)
\( \newcommand{\vectorC}[1]{\textbf{#1}} \)
\( \newcommand{\vectorD}[1]{\overrightarrow{#1}} \)
\( \newcommand{\vectorDt}[1]{\overrightarrow{\text{#1}}} \)
\( \newcommand{\vectE}[1]{\overset{-\!-\!\rightharpoonup}{\vphantom{a}\smash{\mathbf {#1}}}} \)
\( \newcommand{\vecs}[1]{\overset { \scriptstyle \rightharpoonup} {\mathbf{#1}} } \)
\( \newcommand{\vecd}[1]{\overset{-\!-\!\rightharpoonup}{\vphantom{a}\smash {#1}}} \)
\(\newcommand{\avec}{\mathbf a}\) \(\newcommand{\bvec}{\mathbf b}\) \(\newcommand{\cvec}{\mathbf c}\) \(\newcommand{\dvec}{\mathbf d}\) \(\newcommand{\dtil}{\widetilde{\mathbf d}}\) \(\newcommand{\evec}{\mathbf e}\) \(\newcommand{\fvec}{\mathbf f}\) \(\newcommand{\nvec}{\mathbf n}\) \(\newcommand{\pvec}{\mathbf p}\) \(\newcommand{\qvec}{\mathbf q}\) \(\newcommand{\svec}{\mathbf s}\) \(\newcommand{\tvec}{\mathbf t}\) \(\newcommand{\uvec}{\mathbf u}\) \(\newcommand{\vvec}{\mathbf v}\) \(\newcommand{\wvec}{\mathbf w}\) \(\newcommand{\xvec}{\mathbf x}\) \(\newcommand{\yvec}{\mathbf y}\) \(\newcommand{\zvec}{\mathbf z}\) \(\newcommand{\rvec}{\mathbf r}\) \(\newcommand{\mvec}{\mathbf m}\) \(\newcommand{\zerovec}{\mathbf 0}\) \(\newcommand{\onevec}{\mathbf 1}\) \(\newcommand{\real}{\mathbb R}\) \(\newcommand{\twovec}[2]{\left[\begin{array}{r}#1 \\ #2 \end{array}\right]}\) \(\newcommand{\ctwovec}[2]{\left[\begin{array}{c}#1 \\ #2 \end{array}\right]}\) \(\newcommand{\threevec}[3]{\left[\begin{array}{r}#1 \\ #2 \\ #3 \end{array}\right]}\) \(\newcommand{\cthreevec}[3]{\left[\begin{array}{c}#1 \\ #2 \\ #3 \end{array}\right]}\) \(\newcommand{\fourvec}[4]{\left[\begin{array}{r}#1 \\ #2 \\ #3 \\ #4 \end{array}\right]}\) \(\newcommand{\cfourvec}[4]{\left[\begin{array}{c}#1 \\ #2 \\ #3 \\ #4 \end{array}\right]}\) \(\newcommand{\fivevec}[5]{\left[\begin{array}{r}#1 \\ #2 \\ #3 \\ #4 \\ #5 \\ \end{array}\right]}\) \(\newcommand{\cfivevec}[5]{\left[\begin{array}{c}#1 \\ #2 \\ #3 \\ #4 \\ #5 \\ \end{array}\right]}\) \(\newcommand{\mattwo}[4]{\left[\begin{array}{rr}#1 \amp #2 \\ #3 \amp #4 \\ \end{array}\right]}\) \(\newcommand{\laspan}[1]{\text{Span}\{#1\}}\) \(\newcommand{\bcal}{\cal B}\) \(\newcommand{\ccal}{\cal C}\) \(\newcommand{\scal}{\cal S}\) \(\newcommand{\wcal}{\cal W}\) \(\newcommand{\ecal}{\cal E}\) \(\newcommand{\coords}[2]{\left\{#1\right\}_{#2}}\) \(\newcommand{\gray}[1]{\color{gray}{#1}}\) \(\newcommand{\lgray}[1]{\color{lightgray}{#1}}\) \(\newcommand{\rank}{\operatorname{rank}}\) \(\newcommand{\row}{\text{Row}}\) \(\newcommand{\col}{\text{Col}}\) \(\renewcommand{\row}{\text{Row}}\) \(\newcommand{\nul}{\text{Nul}}\) \(\newcommand{\var}{\text{Var}}\) \(\newcommand{\corr}{\text{corr}}\) \(\newcommand{\len}[1]{\left|#1\right|}\) \(\newcommand{\bbar}{\overline{\bvec}}\) \(\newcommand{\bhat}{\widehat{\bvec}}\) \(\newcommand{\bperp}{\bvec^\perp}\) \(\newcommand{\xhat}{\widehat{\xvec}}\) \(\newcommand{\vhat}{\widehat{\vvec}}\) \(\newcommand{\uhat}{\widehat{\uvec}}\) \(\newcommand{\what}{\widehat{\wvec}}\) \(\newcommand{\Sighat}{\widehat{\Sigma}}\) \(\newcommand{\lt}{<}\) \(\newcommand{\gt}{>}\) \(\newcommand{\amp}{&}\) \(\definecolor{fillinmathshade}{gray}{0.9}\)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.
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 open the database by double-clicking the file name:
When you open this database you see a list of objects (Figure \(\PageIndex{2}\)) in the database; you will see three tables: Book, Loan, Member:
Double-click a table name and MS Access opens the table in Datasheet View; you can see the contents of Book in Figure \(\PageIndex{3}\). The datasheet view for a table is easily obtained, but it’s 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.
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’ll 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.
Exercises (Set 1)
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.
Exercise \(\PageIndex{1}\)
Rewrite the verbalization for the Employees table using elementary verbalizations.
Exercise \(\PageIndex{2}\)
Is the verbalization given for Circulation of Leading U.S. Magazines in elementary form?
Exercise \(\PageIndex{3}\)
What verbalizations apply to the Loan table in the Library database?
Exercise \(\PageIndex{4}\)
What verbalizations apply to the Member table in the Library database?
Exercise \(\PageIndex{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 data borrowed field. Why would some of the date returned fields appear to have no value at all?
Exercise \(\PageIndex{6}\)
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.
Modifying Rows
With the Book table open in MS Access and with the cursor positioned in a row, try modifying the data recorded for that book. If you position the mouse cursor you can change the value recorded for the book’s call number, title or author. Try doing this - remember you can always download this database again if you wish to get back to what you started with. As you begin modifying a value (e.g. adding an ‘s’ to make the last name Matthews) an editing symbol appears to the left of the row:
If you recognize that you are making a mistake you can undo your editing action by pressing the Escape key.
To make your change permanent you must move the cursor to another row for the update to be completed – when you do this, you will note the editing symbol disappears.
In some situations you will find MS Access provides a formal Undo capability. Consider the following figure that shows an Undo icon in the upper left corner that appeared after changed Matthew to Matthews and moving the cursor to the next row:
Adding New Rows
Try adding a new book to the Book table. You can add a new book by first clicking on the New Record button shown near the bottom of the window:
To complete your action you must type values for callNo, title, and author. As a first example use a call number that does not appear for any other book. As we will soon see the Book table is designed in such a way that each book must have a different call number. Your addition will be successful if your book is given a call number that no other book has. When you add a new row you must move the cursor out of the row for the addition to be completed.
As a second example try to add a new book, but this time, use a call number that already appears in the table. In this case MS Access will reject your new record. Try this and you will see a response similar to:
The important part of this message for us is the part that refers to duplicate values or duplicate data. When we try to add a row with the same call number as some other row MS Access refers to the duplicated call number value. Note that you can press the Escape key to remove the new row from the table display. Soon we discuss table design where you will see that the call number field is designed to be the primary key of the Book table.
Adding a row to a table is also referred to as inserting, or appending a row.
Deleting Rows
You can remove a book from the table by highlighting a row (click in the cell just to the left of a call number) and then press the Delete key on the keyboard:
When you press the Delete key MS Access will respond in one of two ways depending on whether or not there is an existing reference to the row you are trying to delete:
When you view the Loan table you are able to see the books that library members have taken out and whether or not a book has been returned. Rows in the Loan table have references to rows in the Book table and to rows in the Member table. The default action in MS Access is that a deletion is disallowed if there is some row in a table that has a reference to it. So we cannot delete a book if there is a Loan row referencing it.
We have briefly shown how to modify, add and delete data in tables. Next we introduce the design perspective for tables.
Table Design View
So far we have been opening tables in Datasheet View where we can view and change data in rows of a table. When in Datasheet View we can switch from datasheet view to Design View by clicking on the design icon located near the upper left hand corner (see Figure \(\PageIndex{10}\)). When the Design View icon is clicked, the display changes: the icon becomes a Datasheet View icon and the display changes to reveal design information (see Figure \(\PageIndex{11}\)).
When you click the Design View icon you will see the display change as shown in Figure \(\PageIndex{11}\) – you will see the field names listed along with their datatype, and according to the field where the cursor is located you see other properties for that field. Datatypes vary somewhat from one database system to another, but of course there are many similarities too. Properties are other characteristics that you can define for a field such as the maximum length of values stored for the field.
Generally we want data in a database to be reasonable and correct. We can use datatypes and properties to achieve certain types of correctness. Consider the following integrity rules as rules we would like to enforce:
- Call numbers, titles, and authors are alphanumeric. Any text you can type on the keyboard is acceptable.
- Each call number must be unique (there can be no duplicates)
- Each book must have a title
- A value for call number must be no more than 50 characters long
- A value for title must be no more than 255 characters long
- A value for author must be no more than 255 characters long
- The author field can be left out (it can be null).
Now we discuss how these integrity rules are obtained in Table Design View.
In Figure \(\PageIndex{11}\) the cursor is located on the callNo field; some properties of callNo are circled and discussed below:
- Beside the callNo field you can see the key icon. This means the callNo field is the primary key. A primary key is a unique identifier – every row in the table must have a unique value in that field. Every table should have a PK specified and there can be only one PK for a table. When a field is defined as the PK then a value must be provided in each and every row.
- The callNo field has a datatype of Text and a field size of 50. Any value you can type on the keyboard is acceptable but the overall length, number of characters, is restricted to at most 50.
- The callNo field is indexed and in this case no duplicates are allowed. The index constructed by MS Access is similar in purpose to the index at the back of any book: the index allows MS Access to quickly locate a specified row. However, this index is different from that at the back of a book because it allows only one entry per indexed value (No Duplicates is specified for the Indexed property). Each call number is unique.
As you move the cursor up and down you should note the following for this sample table:
For title:
- The title field has a datatype of Text with a field size of 255. A text field can comprise any combination of letters, digits, and punctuation. Any value entered by a user cannot exceed 255 characters in length.
- A value is required. When entering data for some book, the user cannot omit the title.
- There is no index on title.
For author:
- The author field has a datatype of Text with a field size of 255. A text field can comprise any combination of letters, digits, and punctuation. Any value entered by a user cannot exceed 255 characters in length.
- A value is not required. When entering data for some student, the user can omit the author.
- There is no index on author.
Now, open the Member table and then the Loan table in design view. Examine the properties of each field. For reference see Figure \(\PageIndex{12}\).
Later we will examine datatypes and properties in much more detail.
Exercises (Set 2)
Exercise \(\PageIndex{1}\)
Use Design View to add fields to the Member table as indicated:
gender: Text field of length 6 to accommodate the values male and female. Make this a required field that is not indexed.
birthDate: a Date/Time field; required; not indexed.
Switch back to Datasheet View (You must reply yes to the system prompt to save your changes). You should notice there are no values for gender nor birthDate.
Exercise \(\PageIndex{2}\)
Now enter values you deem appropriate in the gender and birthdate fields for each member. Close the table and reopen it. You will see the values you entered are still there.
Exercise \(\PageIndex{3}\)
When new members join the library information about them must be entered into the Member table. Each member is given an id value automatically. Add new members to the library and note how MS Access will not let you enter id values; instead, MS Access generates those values for you – id values are generated sequentially. Close the table and then reopen the table to confirm your additions worked.
Exercise \(\PageIndex{4}\)
Typically a library assesses a fine the user must pay if they keep a book out past the due date. As well the library needs to track the amount, if any, the member has paid. In this exercise we add two fields to the Loan table so we can keep track of fines that are assessed and the amount the member has paid.
- Open the Loan table in design view and add two new fields named fineAssessed and finePaid. These fields must have a datatype of Currency.
- Save the Loan table and then view the rows of the table. There are no amounts for these fields.
- Choose some row(s) in the Loan table and enter values for the fineAssessed and finePaid fields. Note the values you enter will appear as dollars and cents.
Exercise \(\PageIndex{5}\)
In exercise 3 you added a new member and in exercise 4 you added fields to the Loan table. Consider that the person you added now borrows a book and so a row must be entered into the Loan table. Enter such a row.
Exercise \(\PageIndex{6}\)
After successfully entering data for exercises 3, 4, and 5 you are aware of a member and a book for which there are references in the Loan table.
- View the Member table and try to delete that member, and then view the Book table and try to delete that book. These deletion attempts are unsuccessful because of the references to the Loan table.
- Now open the Loan table and find the loan record you entered in exercise 5. If you delete this row you will find that you are able to delete the member (provided you did not enter more loans for this person). These actions mirror the way in which data would typically be deleted from a database: if you want to delete a row you must first delete (or modify appropriately) any rows that reference it.