Microsoft Access Tutorial
|
|
|
|
| Articles Reviews Microsoft Access | |
| Written by Adi Bach | |
| Friday, 01 September 2006 | |
|
Page 10 of 22 Input Masks: Microsoft Access comes with input masks allowing you to controlling user's input such as dates, times, Social Security Number, Currency values, etc. Start Microsoft Access and choose to create a blank database called Ashburn Agency. In Design View, create a table with the following fields:
Switch to Datasheet View and save the table as tblEmployees. In the Employee # field, type dot and in the Salary field, type Suzy and press Enter. Microsoft Access has accepted your entries and is ready for another record, the only problem is that if in your company you have employee's numbers formatted and called dot and whatever and a table or form accepting a name in a field reserved for money, you might end up getting confused about employees and on pay day, somebody will not receive her wages. These are the things we will avoid from data entry. Switch to Design View. Click the Salary field. Set its Data Type to Currency and delete the 0 set as its Default Value. The data you left in the Datasheet View will be deleted when you save the table. In our records, we only want to know whether an employee is married or not. To get that result, the user will choose between a Yes or No. Set the Data Type of the MaritalStatus to Yes/No. One of the Data Types provided by Microsoft Access allows you to specify a field for a date or time values. Click the DateHired field. Change its data type to Date/Time. In the lower part of the Design View, change its Format to Short Date. Save the table. You can built an input the way you want data in that field to be formatted. Or, you can use the Input Mask wizard to assist you in building your mask. Click the Input Mask field. There appears an ellipsis/build button on its right, click that button. When the Input Mask Wizard comes up, choose Short Date. Click Next twice and click Finish. Most companies and institutions give telephone extensions in the form of a two, three or four-number format. Let's imagine that our company here uses an extension like 4266. This is a number made of four characters. So, specify the Extension's Data Type as Number. In the lower part of the design view, change its field size to Integer. To make sure that the user will not enter a value like 3,528, set its Format to General Number. Set the Decimal Places to 0 zero. Unfortunately, the user can still type a number like 218 or 54 for the extension, that would not abide to the rules of our company here. To make sure that the user can only type a four-character number, in the Input Mask, type 0000. As it happens, 0000 tells Microsoft Access that the user will not be allowed to type anything else than four digits, which means you can reset the Extension's Data Type to Text. The Input Mask is very useful when you want to control what your users type. Most institutions would accept just one letter for the middle initial name. If you use our table now and type something in the MI field, it would accept anything. If you want to limit the user to enter only one character, you can save space on your table and eventually on your form by allowing only one letter in that field. Click the MI field and set its field size to 1. Unfortunately, the field can still accept a number, and we know that nobody has a middle name as 3, 8, or 5. To limit the entry to one letter only, type L in the Input Mask. The other extra thing you can do is ask Microsoft Access to convert to upper case any letter entered in the MI field. To do that, just type > in the Format field. The Validation Rule field allows you to specify the standard that will recognize whether the user type a correct entry in an appropriate field. And you can write the write message to indicate the error to the user. Every bank has a set of rules on how its bank accounts are formatted, in the same way different companies use different formats for their employees numbers. Since we have already seen how you can set a field to use only numbers or letters, let's use a different formatting for our employees numbers. Let's say our company has decided that an employee's number will display like ED-4577-G. This number uses two letters separated by a dash, underscore, or a hyphen, followed by four number, then another separator and ending with one letter. To accomplish that, you can specify the properties of the employee number field like the following:
If you use that formula, the data entry personal will have to keep in mind how employee's numbers are formatted or formulated. On the other hand, there is a better way of formatting the employee number and at the same time helping the data entry employee by providing masks. For the employee field, delete the formula you entered in the Validation Rule and, in the Input Mask, type the following: LL-0000-L0;0;_ This formula assures that two letters and strictly two letters will be typed first, followed by a dash, followed by four digits, then a dash, and finally one letter and one digit. Save the table. To control telephone number entry (in the US and Canada), click the WorkPhone field. In the lower part of the Design View, click the Build button of the Input Mask. In the first page of the Input Mask Wizard, choose Phone Number and click Next twice. In the third page click the first radio button to allow the user to see parenthesis and the dash sign while he is typing the value. Then click Next and click Finish. Change the Data Type of the FavWebSite to Hyperlink. Validating Entries: While you are trying to control valid entries, you can decide what Microsoft Access will accept as an acceptable value. You can set ranges of acceptable values and direct the user to what is correct. As we are still in the Design View, we will now set the minimum wage in the company. Let's say the salary in the company starts at $6.45 and nobody should earn less. If you want to specify a date as a text, you enclose it between pound (#) signs. For example, let's say this company was created on 24 January 1984. There is no way anybody could have been hired before that date. Click the Salary field. In its Validation Rule field, type >=6.45 and press Enter. In the Validation Text, type The minimum wage in this company is $6.45. Click DateHired. In the Validation Rule, type >= #1/1/84#. In the Validation Text, type Come On, no one was hired here before January 1st, 1984. Setting Items For A Preset List: You can furthermore control the user's input by presetting some values so that he can only choose what is available, in the form of a combo box (or a list box). Click the Title field. For its Data Type, choose Lookup Wizard... When the first page of the Lookup Wizard comes up, choose the second radio button telling the wizard that you will type values for the field, then click Next. Under Col1, type Ms., then press Tab, type Mr., the third field will be Mrs., then Dr., Prof., Sir, Rev., Rabbi, Fong, Other. Click Next. Accept the column label as Title and click Finish. Click the Lookup tab in the lower part of the Design View and see the work that was done behind the scenes. To make sure that the user can only choose a preset value in the list, set the Limit To List field to Yes; on the other hand, if you want to allow the user to type new entries, you would set Limit To List to No (keep in mind that even if you restrict the user to choose only from the list, you yourself can eventually modify that list). Save the table, then close the Ashburn Agency database. Getting A Field's Data From Another Source: Using the wizard or working manually, you can make sure that there are fields that will receive their data from another table, once more this reduces redundancy of data. Open the Books3 database. Imagine that you work for a public library where books are categorized in a certain order. It is better to make sure that the new books registered follow the categories that have been preset, this reduces confusion. We will then make sure that the book categories originate from the book table. Open the tblBooks table in Design View. Click the Category field to highlight it. Press Tab to move to the Data Type area. Press L and press F6. When the first page of the Lookup Wizard comes up, accept the first radio button and click Next. You then have to choose which table or query holds the originating data. Right now we have only one table, so it is chosen by default; otherwise, you would choose tblBookCategories and click Next. You can include many fields for a particular column, except that only one will be seen when its combo box is not dropped. For this example, only the BookCategory is needed (Microsoft Access will include the other, even if you don't select it). So, double-click BookCategory and click Next. Agree to let Microsoft Access Hide The Primary Key and click Next. Accept the Category label for the column and click Finish. Accept to save the table. When the wizard finishes, it should take you to the lower part of the view because you clicked F6 before starting the wizard. Delete the Default Value of 0 (zero). If you want to see what Microsoft Access did behind the scenes to get the lookup values, click the Lookup property sheet. Save the table and switch to the Datasheet View to verify that the Category field has a combo box now and doesn't allow any value that is not coming from the tblBookCategories table. Specify the Category of MFC Programming as Computer - Programming. The Category of America A Narrative History is History. When you have finished, close the tblBooks table and close the Books3 database. Exercises: Improving the Silver National Bank database Open the Silver National Bank2 database. On the Database Windows, click the Tables button.Open the tblAccountTypes table and type Checking then Saving, and finally CD for the account types. Close the table. Open the tblAccounts table in Design View. Change the Input Mask of the AccountNumber field to read: 000-000-00. Using the Lookup Wizard, specify the AccountType as getting its data from the tblAccountTypes table (select only the AccountType field). Using the Lookup Wizard, specify that the titles will come from the list that you are going to provide. The titles are: Ms. Mr. Mrs. Dr. In the lower part of the Design View, in the Lookup tab, specify the Limit To List to Yes. Set the default value of the Country to USA. Switch the tblCustomers table to datasheet view. Fill it out as follows: DateJoined: 02/24/95; AccountType: Checking; Account #: 312-874-39; Title: Mrs.; First Name: Gertrude; MI: C; Last Name: Waters. Address: 4288 S. Patton St; City: Colesville; State: MD, ZIP Code: 20912. The next account is, DateJoined: Ctrl+'; Account #: 576-873-47; Account Type: Saving; Title: Dr.; First Name: Steve; Last Name: Yacobby; Address: 3901 Georgia Blvd; City: College Park; State: (press Ctrl + ', that will paste MD from the former State entry), ZIP Code: 20707. The concept of relational database insures data reliability on the foundation of data moving from one source to another. There are many goals behind this concept. Data in your resources needs to be as much accurate as it can be. Provided your database is made of various components, mainly tables, you should avoid any redundancy possible, in other words, data from one source should be unique and almost never duplicate. To accomplish these goals, you interrelate the various components of your database, namely tables (remember, and as you will see later, data in your database depends on, or is originating from, your tables). Relationships: Open the Music Collection 2 database. When creating various tables in your database, if your first goal is to get necessary data, the second would be to provide accurate data. To succeed in that, you build relationships between and among tables so that they will collaborate and provide necessary data to one another. Building tables relationships is one thing, controlling these relationships is another. This is where your efficiency in avoiding redundancy is revealed. For one table to provide data to another, each one of them should have unique data that the another table needs. When you are creating a Lookup field, you are telling one table that the value entered in this particular field will come from another table, and you specify the originating table. The originating table is the parent table, the target table is the child table. The reason you established Primary Keys in your tables is because these are the fields used to build relationships between tables. They are used to verify the uniqueness of data. Also, they avoid that data in relationship get mixed. You can build a reliable relationship only between data of the same kind. Click the Relationship button on the toolbar. The Show Table property sheet comes up. From here, you will specify what tables (or queries) will be used when building your relationship (s). Click the tblMusicCategories once and click the Add button. Double-click the tblMusicAlbums; finally include the tblMusicTracks. Close the Show Table property sheet. Arrange the layout of your relationship window so that the tblMusicAlbums table be in the middle of the tblMusicCategories table on its left and the tblMusicTracks table on its right.
The originating table uses its Primary Key and associates it to the field you choose in the target table. The target field is referred to as the Foreign Key. Drag the MusicCategoryID field from the tblMusicCategory table to the MusicCategoryID field in the tblMusicAlbums table. The Edit Relationship dialog comes up. This allows you to confirm creating a relationship. Click the Create button to create the relationship. Now you have a line relating these two tables. Drag the AlbumID field from the tblMusicAlbums table to the AlbumID in the tblMusicTracks. When manipulating data that is in a relationship, it is very important to make sure that data keeps its accuracy from one table or source to the other, to succeed in that purpose, you need to Enforce Referential Integrity so that when you change data in the parent table, data corresponding in the child table be changed/updated also; this is done through Cascade Update Related Fields. On the other hand, the Cascade Delete Related Records helps to delete data in the child field when the corresponding data has been deleted in the parent field. The relationship you establish between two tables creates a Subdatasheet which is a child table related to the parent table. Click the Enforce Referential Integrity check. Now, the database would like to know how you will handle data updating and deletion. Check all the three check boxes, and click the Create button. Now you have a 1 on the parent field and the infinity sign on the child field. The 1 (One side relationship) means that the originating field, the parent, supplies the data that changes the value in the target. The infinity symbol (¥) means that many fields in the target table are affected by the data coming from the parent table. Right click the relationship line between the tblMusicCategories table and the tblMusicAlbums table, then choose Edit Relationship... from the popup menu. Click all the three check boxes. Click the Join Type button. The Join Properties dialog allows you to specify the direction of the relationship. Click the second radio button that will allow the parent field in the tblMusicCategories to control the corresponding and related fields in the tblMusicAlbums. Click OK twice. Now you have not only the one-to-many sign, you also have an arrow reminding that data in the MusicCategory field of the tblMusicAlbum will come from the MusicCategory field of the tblMusicCategies table.
When you are finished with the Relationship Window, save and close it. If you are using Microsoft Access 97, skip this Subdatasheet section.
|
|
| Last Updated ( Friday, 02 January 2009 ) | |
| < Prev |
|---|
