Introduction to Access

Microsoft Access is a relational database management system (RDBMS) and is widely used in industry for managing staff lists, stock inventory, and the like. It allows you to create a database with separate files called tables and then link these tables together. For example a video store might keep one table listing all videos they have in stock and another table listing all people (members) eligible to borrow a video. By linking the tables together you can keep track of who has borrowed which particular video. In a flat file database program it is not possible to have multiple tables. 

The Access database screen will be referred to a number of times and looks like that shown below:

Back Forward

Folio Piece 1 - Setting up Tables

Your first task is to set up a database that consists of two tables which will be linked. Let's assume that you have an extensive CD collection and you are constantly lending your CDs to your friends. In order to keep track of your CDs you create one table listing the names and phone numbers of your friends and another table listing all your CDs. When planning how to set up a database you first need to create a data dictionary. This consists of the field names, the data type, possibly the size of the field, and a description of the data you'll be storing in that field. For the first table the data dictionary will look like this:

 Start the Access program and click on the first option which is create a new database using a Blank Database.

You'll then be asked where you wish to save the file - select your folder on H drive - and the name of the database - call it CDCollection and click on create. Select New (Table) - Design View and enter the above fields. When you enter Data Type a further drop down menu appears. The MemID is to be Number/Long Integer and Phone is to be Number/Long Integer. Make the MemID field the key field by clicking on MemID and then clicking on the key on the toolbar. Your screen should appear like that below:

Back Forward

There should be no spaces in the field names. One of the fields must be designated the key field - in this case MemID - click on this field name and then click on the key icon on the toolbar. When you click on the Data Type you usually get a drop down menu which allows you more choices.

Now that you have set up the table format you are ready to enter data. Click on the datasheet icon and you'll see the message - You must first save the table - Save table now? - click on Yes and call the table MemberTable. Go ahead and enter data on 15 of your friends, giving them MemID numbers 1 to 15. When you have done this enter the following table. You will use this to save data about your CD collection. Again you'll need a key field that is different for each entry - CDID and make it type Number (Long Integer). Think about the data you'll need to enter about each CD (they may be music or games CDs).

When you have completed the data dictionary save it - give the table the name CDTable. Enter data on at least 10 CDs. For the purposes of a later exercise make sure you have at least three CDs by the same band or artist. At this stage your main database screen should look like:

Back Forward

Back Forward

This task simply requires you to print a screen printout of your two tables. To obtain a printout you must first create a Report. From the database screen select Reports, New, AutoReport: Tabular and select MemberTable. Click on the Print Preview icon to see the appearance of your report. Do not print a hard copy. Save your report as MemberReport. Repeat the process to obtain a screen printout of your CDs calling your report CDReport.

In order for you to know who has borrowed which CD you need to create a third table to link your two existing tables. We'll call this table LoanTable. The data dictionary for this table will be:

Make LoanNum the key field. You do not enter any data for this table.

After saving the design of this table you need to create a new Query. From the database screen select Queries, New, Design View and add all 3 tables by double-clicking on them. If you created the three tables correctly then links should appear automatically joining the tables (see below).

If this does not occur you'll have to return to the tables and check the MemID and CDID data types in the tables in which they appear are correct (all should be Number/Long Integer).

Back Forward

After closing the show table screen double click on the following field names - LoanNum, BorrowDate, CDID (from LoanTable), Title, MemID (from LoanTable), FirstName, Surname, and Phone. Save this design as LoansQuery and then open the table in datasheet view.

Add a borrow date and a CDID for one of your CDs and then tab across to MemID and add a members ID. Other details about the loan should appear automatically. Continue to add details on another 6 loans. Your screen should appear something like that shown below:

Back

You need to appreciate one of the strengths of Access - its ability to automatically find the title and artist of a CD given its ID number, and the name and phone number of the member given their ID number. Create a Report based on this Query and print a screen copy of your loans. Save your report as LoansReport. You need to go into design view of the Report and 'tidy it up' by renaming the headings and adjusting the format of the fields (the alignment etc).

Back

Folio Piece 4 - Modifying Tables

As your lending library becomes larger you decide to add the address of each of your friends to your MemberTable. Open this table in Design View and add the fields Street (text - 20), Suburb (text - 20) and Postcode (Number - integer).

In datasheet view add the necessary data. You also decide to start charging a fee for the loan of your CDs and a time limit that they can be borrowed for. Open your CDTable in Design View and add a field called Rate (text - 2) to your CDTable . Switch to Datasheet View and classify each of your videos as rate A, B, or C. Then create another table call ChargesTable with the fields Rate (text 2) and Fee (Currency). Make  Rate the key field.

Add 3 records:

Rate     Fee

A         0.50
B         1.00
C         2.00

Now modify your LoansQuery query by adding the ChargeTable. To do this select Query/Show Table when in Query view. Rate should appear linked in each of the two tables it appears in. Add Fee (select from ChargesTable) to your list of fields shown in the query. The Design screen of your Query should look something like that shown below.

Back

Switch to Datasheet View and check that all the appropriate details on your loans appear. It should look something like:

Folio Piece 5 - Creating a Mail Merge

With your Access screen open in Database View and the MemberTable icon selected click on the drop-down arrow beside the Word mail merge option which appears on the menu bar and select 'Merge It with MSWord'. 

From the next set of options choose 'Create a new document and link database to it.' and enter the letter on the next page in the exact format shown. To draw the line you'll need the drawing tools - select View/Toolbars/Drawing. Database fields are inserted by clicking on the Insert Merge Field button. Note: the database fields that need to be inserted are in red.

Back

To obtain a list of your CDs open CDTable in datasheet view, copy to the clipboard and then paste into your letter. Print two screen copies of the letter with friends names merged. To do this click on the View Merged data button and File/Print current page.

Kims CD Hire
222 Torquay Rd
Grovedale VIC 3216

---

Monday, 12 February 2001

«FirstName» «Surname»
«Street»
«Suburb»   Vic   «Postcode»

Dear «FirstName»

As my CD lending service has expanded significantly I am writing to inform you that I will be introducing a small charge for the loan of my CDs. There will be three rates:

Rate A $0.50 for 1 week (7 days)
Rate B $1.00 for overnight
Rate C $2.00 for 1 week (7 days)

The following is a current list of the CDs available together with their rates:

Title	Artist	Rate
Gold	Abba	A
Their Finest Hour	The Angels	B
Beach Boys	20 Golden Greats	B
Pat Benatar	The Very Best Of	C
Black Sorrows	Chosen Ones Best Of	C
Greatest Hits	Cold Chisel	A
The Best of the Doors	The Doors	B

I trust that you will understand, and hope that I can continue to be of assistance.

Yours sincerely
Kim Wilde
 

Kim needs to print a set of mailing labels for her letters. To do this select Reports, New, Label Wizard, MemberTable, select Label C2180 (metric), 10 point Ariel. Add Firstname, space, Surname, Enter, Street, Enter, Suburb, 3 spaces, type VIC, 3 spaces, Postcode. Check the appearance of your labels and if OK then Print Preview a set. Save it as MemberLablesReport. It should appear:

So far you have used datasheet view for the entry of data (records). The use of a form is a better option for the entry of data for a number of reasons. More appropriate headings may be used and directions as regards the type of input required are two such reasons.

You'll create a form for the entry of new friends (members) to your lending library, a form for the entry of new CD titles, and a form for the loan of a CD. In database view click on Forms, New, Autoform: Columnar, and select MemberTable. Switch to Design view and add labels explaining what it required to be entered (see next page). If the toolbox is not present on the screen click View/Toolbars/Toolbox. Call your form MemberForm.

Create a form for the entry of new CD titles based on CDTable (called CDForm) and a form for the loan of CDs (based on LoansQuery) called LoansForm. Both forms are to have appropriate labels.

Folio Piece 8 - Masks and Data Validation Checks

Input Mask

A mask can be used for the input of data if the values to be entered in a field have exactly the same format. For example, phone numbers in Geelong all begin with 52, then another six digits. So a mask could be created to present like:

52000000

The user would then be required to type in the remaining six digits. Masks like this make entry errors less likely, assisting in maintaining the integrity of the database. An Input Mask makes use of special mask characters, some of which allow optional entry, others requiring a specific type of character entry.

 Input Masks

The Field Properties including Input Mask, Validation Rule and Validation Text for the Postcode field are shown below:

Default Value

This value is automatically placed in a field when a new record is created. Use this property when you know that a certain value appears most often in this field. For example, a table containing client information might have a field named State. If most of your clients tend to be from Victoria you could have the Default Value set to Vic.

Validation Rule and Validation Text

The Validation Rule is another property aimed at reducing the possibility of error when data is entered. The rule restricts what can be entered. If the rule is broken the Validation Text is displayed in a message box then the user is required to enter again. The rules are expressions describing the values that are acceptable. The following are some simple examples:

Return to MemberTable (Design View) and add the Input Mask 0000 for the PostCode field. This forces the user to enter 4 digits for the postcode. For the Validation Rule enter >=3000 and <=3999. For the Validation Text enter You must enter a postcode between 3000 and 3999 inclusive. Try this out.

Now enter Input Masks, Validation Rules and Validation Text for all appropriate data entry including MemID, PhoneNumber, CDID and Rate .

Folio Piece 9 - Using Formulas in a Query

Extend your ChargeTable so that for each of the Rates (A, B, C) as well as a set fee ($0.50, $1.00 and $2.00) there is a borrowing period of 7 days, 1 day, and 7 days respectively for each of the rates. Do this by adding the field Period (DataType Number/Integer) to ChargeTable and in Datasheet View add 7, 1, 7 beside Rates A, B, C.

Given that we entered BorrowDate when a CD was borrowed and the Charges table tells us the Period of the loan, we can calculate the return date for the CD. Return to your LoansQuery (Design View) and add the (calculated) field ReturnDate:[BorrowDate]+[Period]. The return date for all the CDs should appear.

Folio Piece 10 - Macros and Command Buttons

Return to your CDCollection database. Before you can complete this task you must have created a form for the entry of members (based on MemberTable), a form for the entry of CDs (based on CDTable), a form for the entry of loans (based on LoansQuery) a report based on MemberTable, a report based on CDTable and a report based on LoansQuery.

Your task is then to create a new form that is to function as a main menu for Kims CD Hire. This form is to have a series of command buttons on it. Each of these buttons will have a macro behind it so that clicking on the button will result in the macro being run.

Your form should look something like that shown on the next page:

From the database screen select Form, New/Design but do not select a Table or Query. In Design view add a command button, select the appropriate macro eg. for the Open Member Form select Form Operation and Open MemberForm. Give the button the text Open Member Form and name the button OpenMemberForm. Continue to add all six command buttons and a text heading eg. Kims CD Hire.

Name your form MainMenuForm.

You can also add buttons to each of your forms that will return you to this MainMenuForm and a button on MainMenuForm that will Quit Access.

Your uncle Mick asks you to computerise his current database of clients and jobs that he has for his lawn mowing and garden service. By coincidence his list of clients is identical to the list of friends that you used in the last database.

With your CDCollection database open in database view and your MemberTable highlighted copy this table to the clipboard. Now create a new (blank) database called MicksMowing and paste this table into it. Change the name of this table to ClientTable and in design view adjust the field names eg Friends surname becomes Clients surname.

Create a second table called JobsTable with the following design (note: JobType is the key field)

Add the following data to your JobsTable:

Create a third table to link your ClientTable and JobsTable. Call this table LinkTable. 

Now create a New Query / Design View and add all three tables. Links should appear connecting your tables. 

In Datasheet view allocate 4 jobs. Create forms for the addition of new clients, new jobs and for booking jobs. Create appropriate reports and then a main menu (or switchboard).

Folio Piece 12 - Adding Images to your Database

Your final task is to add CD covers to the first database you created - Kim's CD Hire. You'll need to download about 10 CD covers from the Internet - save them in a folder as either as a bmp, a tiff or a jpeg. If you can't download covers that match the CD titles you used in your first database, don't worry, you simply alter the titles in your CD table to match the covers. Return to Kim's CD Hire and open CDTable in Design view. Add another field called Cover  of data type OLE object. Close after saving, and then open your CDForm in Design view. Select View/Field list and drag Cover onto your form. Make the box about 2cm square. Right click, select Properties and for size mode select Zoom. Switch to Form view and as you navigate through your CDs, click on the Cover Field  then Insert from the menu bar Object/Create from File and Browse to find the appropriate image.