Appendix C: Database Management with Access – Introduction to Database Management Systems

Appendix C

Database Management with Access

C.1 BASIC TABLE DESIGN

When we start the MS-Access software, the screen shown in Fig. C.1 appears. Using this screen, we can create a new database table, or open an existing one.

Fig. C.1  MS-Access: The first screen

Let us begin with a blank database, as creating it would teach us many simple concepts. Therefore, we shall select the radio button Blank Access Database, and click on OK. MS-Access would prompt us for the desired name and location of the database that we want to create. The result is shown in Fig. C.2.

Fig. C.2 Creating a new database

We can change the database name and location (directory/folder), if needed. For now, we shall keep it to the default. That is, we will click on the Create button. This presents us with three choices, namely, creating a new database table in the design view, or by using a wizard, or by entering data, as shown in Fig. C.3.

Fig. C.3 Options for creating a new table

Let us begin with the default option of creating a table in the design view. In order to do this, MS-Access shows us the screen shown in Fig. C.4.

Fig. C.4 Creating a new table in the design view

Now, we can insert column names, data type and optionally, the description for each of the columns. The description is just for our information and has no impact on the database design or contents. We will create a table to store data about people and their credit cards, as shown in Fig. C.5.

Fig. C.5Column names and types

There are four columns in the Card Holder Master (CMF) table. They allow us to store the cardholder's account number, name, credit limit, and card type. Associated with each column is a data type. A data type identifies the type of data that the column can store (text, numbers, dates, and so on). As we have mentioned earlier, for each column, we can add more description (which we have not done), and also more properties. Depending on the requirements, we can choose an appropriate type.

Now that our table is ready in its structural form, let us save it. For that, we can click on the floppy disk icon in the toolbar, or use the shortcut of CTRL-S. When we do that, MS-Access prompts us for the table name, as shown in Fig. C.6.

Fig. C.6 Saving the table definition

Imagine that we change the table name to CMF, and press the OK button. In response, MS-Access tells us that a primary key for this table is not defined, and whether we want to define one. This is shown in Fig. C.7.

We will choose No, as we would use another technique to define the primary key for our table. Why? This is because if we select Yes, MS-Access would add a new column on its own, with its data type as AutoNumber (which means that it is increased by one automatically for each row). This would be designated as the primary key of the table. We do not want this to happen. Instead, we want our CMF_Act_No column to be the primary key. How can we do so?

Fig. C.7 Primary key prompt

For designating a column as the primary key of the table, the simplest option is to right click on the column, and choose the Primary Key option, as shown in Fig. C.8. The other mechanisms for performing this action are the Edit-Primary Key menu option or the CTRL-K shortcut.

Fig. C.8 Making a column the primary key

Now, the CMF_Act_No column is the primary key of our CMF table. MS-Access shows this fact by adding a small key symbol in the leftmost column of CMF_Act_No, as shown in Fig. C.9.

Fig. C.9 CMF_Act_No is the primary key

C.2 INSERTING DATA IN A TABLE

Now that we have a table definition made, let us insert some data (rows) to it. The easiest way to do this is to use the menu option View-Datasheet View. This option shows the columns vertically, and allows us to enter the actual values to be stored in the various columns. Let us add some data values, as shown in Fig. C.10.

As we can see, creating a table and adding data to it is child's play! Now, let us try a trick. We know that the CMF_Act_No column is the primary key of our table. It means that we cannot have two or more cardholders with the same account number. If we attempt to do so, it would violate the principle of the uniqueness of the primary key. Let us try inserting another employee row with CMF_Act_No as a duplicate. The result is shown in Fig. C.11.

Fig. C.10 Adding rows to the table

Fig. C.11 Attempt to add a duplicate value to a primary key column

Fig. C.13 Relationships – Part 1

We select both the tables, and press the Add button. In response, MS-Access shows both tables, with all their columns, as depicted in Fig. C.14.

Fig. C.14 Relationships – Part 2

We can now create the required relationship between the two tables, based on the Brand code column. For this, take the cursor on the Brand code column in the Employee table, and then drag it onto the Brand code column of the Brand table. MS-Access notices that we wish to establish a relationship between the CMF and the Brand tables based on this column, and shows us the screen as shown in Fig. C.15.

Fig. C.15 Relationships – Part 3

Let us now play an interesting trick. Let us go back to the create relationships screen, and check the Referential integrity checkbox on that screen. In other words, we are suggesting to MS-Access that we not only wish to relate the two tables but also want to have a referential integrity between the two tables. The result of this attempt is shown in Fig. C.16. The failure is caused because we have three possible brand type defined in the CMF table, but none of them exists in the Brand table! Therefore, we must first create these brands in the Brand table, and then attempt to create a referential integrity relationship between the two tables.

Fig. C.16 Relationships – Part 4

We now create the three brands in the Brand table, as illustrated in Fig. C.17.

Fig. C.17 Relationships – Part 5

Now if we go back and try to create a relationship between the CMF and Brand tables based on the Brand code column, it will work. Let us assume that we have done so, and then try another trick. Let us try a row in the CMF table that has the Brand code value as T.

Remember that no such brand code exists in the Brand table. Because we have established a referential integrity between the CMF and the Brand tables based on the Brand code column, any code that is present in the CMF table must be present in the Brand table.

Our attempt of inserting a row in the CMF table with Brand code as T, therefore, must and does fail. The result is shown in Fig. C.18.

Fig. C.18 Relationships – Part 6

We must add the Brand code to the Brand table and then add a row with this Brand code to the CMF table. That attempt would certainly succeed.

C.4 MISCELLANEOUS FEATURES

C.4.1 Filtering Records

We can filter records based on many conditions, so that we get only those ones that match our selection criteria. For this, use the Records-Filter menu. We shall discuss the Advanced Filter option within this menu. When we select that option, the screen as shown in Fig. C.19 is shown, where we can enter our filter criteria.

Fig. C.19 Applying filter

The filter that we have applied is to show only the details of cardholders whose credit limit is less than 10,000. When we click the Apply Filter button on the screen, we see only the records that satisfy the filtering criterion, as shown in Fig. C.20.

Fig. C.20 Results of the filter

We can cancel the effects of the filter by using the Records-Remove Filter/Sort option.

C.4.2 Sorting Records

We can sort records in a table, that is, reorder them, based on any condition that we want. For instance, let us sort the records in the descending order of credit limits. For this, select the Credit limit column first, and then select the menu options Records-Sort-Sort Ascending. The employee details would be sorted in such a way that the cardholder with the highest credit limit would appear first, and the one with the lowest credit limit would be shown last, as depicted in Fig. C.21.

Fig. C.21 Results of the sort

C.5 CREATING REPORTS

We can create reports based on our databases/tables, so that the data can be displayed in a professional manner, and printed, if desired. For this, choose the menu option Insert-Report in the main MS-Access window. There are many ways to do this. Regardless of the method chosen, we get a screen as shown in Fig. C.22.

Fig. C.22 Report – Part 1

As shown, we select the Report Wizard, and press OK. Ensure that the Employee table is selected in the list box shown in the bottom portion of the screen. Now, MS-Access prompts us to select the columns that we would like to see in the report, as shown in Fig. C.23. We will select all and press the Next button.

Fig. C.23 Report – Part 2

Now, MS-Access asks us if we want to perform any grouping based on any columns, as shown in Fig. C.24.

Fig. C.24 Report – Part 3

When we click Finish, MS-Access shows us the report as depicted in Fig. C.25.

Fig. C.25 Report – Part 4

There are many options and formatting mechanisms in reports. For example, we can easily click a report of all the cardholders per brand, sorted in the descending order of credit limits. This can be achieved with just 5-6 clicks. The resulting report is shown in Fig. C.26.

Fig. C.26 Report – Part 5

C.6 FORMS

Forms allow us to enter data in ways other than the default. Figures C.27 and C.28 show a couple of them.

Fig. C.27 Forms – Some options

Fig. C.28 Forms – Some more options

C.7 QUERIES

We can write SQL queries to bring data in the manner we want. We also have the option of building the SQL queries in a graphical form, without needing to write them. We can use the drag and drop as well as the other GUI features of MS-Access. Behind the scenes, MS-Access builds the corresponding queries for us.

Figures C.29, C.30, and C.31 show the process of building queries graphically without needing to explicitly code any SQL statements.

Fig. C.29 Queries – Step 1

Fig. C.30 Queries – Step 2

Fig. C.31 Queries – Step 3

Figure C.32 shows the corresponding output of the query we have created.

Fig. C.32 Query output

Figure C.33 illustrates the same query in SQL format. Note that MS-Access has created it for us automatically, based on our above operations.

Fig. C.33 Query – SQL view