Access Practice
Access Practice
Access Practice
This exercise is for you to practice using MS Access in the lab, and to provide you with help as to how to work with access. In many regards, it is similar to yourfor-grade project. The goal with this project is to learn how to create and use an MS Access database, not to rush through the exercise. Take your time and follow the instructions carefully it will help you later while working on the actual project. PART I THE FILE STRUCTURES Our database shall be to keep track of salespersons in a company. The SALESPERSON table shall be made up of the following fields: Field Salesperson ID Last Name First Name SSN CommRate Data Type Auto number Text Text Text Number Width 12 char. 10 char. 11 char Single, 3 decimals 4 char. Other Info Primary Key
Office
Text
State CompanyCar
Text Yes/No
2 char.
Input mask as SSN Commission rate is entered as a percentage, thus 6.5% is entered as .065 Upper case (> in Format). Validate as SAV or BRU or ATL or GRE or CHA Upper case (> in Format); Default value is GA
There should be a table of customers who are Customers of the salespersons, the CUSTOMER table. It should be made up of the following fields: Field Customer ID Customer Name Customer City Employee # (this is the foreign key into the Customer Table) Data Type Auto number Text Text Long Integer Width 15 char. 12 char. 5 digits Other Primary Key
PART II STEP BY STEP, CREATING THE SALESPERSON TABLE 1. First open up MS Access. Click in theCreate a New Database boxs use aBlank Database radio button. Then click OK. 2. Youll be asked to save the new database. Place your diskette in the A drive. Click in the Save in box, and click on3 inch floppy A . Down below, click in theFile name box, and call thisEmployee.mdb . Remember this database will have two tables. Click Create. 3. The blank database will be presented. TheTables tab will already be selected for you. Note that you cant select anything but New on the right. Click on it.
Prof. Katz
Page 1
3/25/2010
PART IV CREATE THE RELATIONSHIP 1. Exit your table after youve entered all the data. 2. Click on the Tools drop-down menu from the menu bar. Click on the Relationships command. 3. The Show Table dialog box is displayed: Click on each table and then click on Add Click on the common field in the Salesperson table, and drag it to the same common field in the Customer table. When you do, the Relationships dialog box will be displayed. Confirm the fields displayed and their tables. Then click in the Enforce Referential Integrity check box. Click on the Create button. The relationship has been created. 4. X out ofRelationships. If asked to save it, click "Yes."
Prof. Katz
Page 2
3/25/2010
PART VI - CREATING QUERIES (simple) 1. Click theQueries tab. Click New. 2. Click on Design View and click "OK" 3. From the "Show Table" dialog box, click on the Salesperson Table and click "Add". The table will be added to the view table area above the grid in your query design view. Now click on "Close". 4. In the Query grid, first column, click on the drop-down arrow on the Field row. Choose the Salesperson ID field. Now do the same for each column's field, selecting an Salesperson Table field to display. Don't select all the fields - the idea here is to select only the fields you want. 5. When you've selected your fields, save the query by clicking on the diskette icon and giving your query the name Salesperson Query. Now run the query by clicking on the red exclamation point icon in the toolbar at the top of the screen. Your data will be displayed. 6. Once you've done that, click on the Design View icon in the top left of the toolbar. Experiment by changing your query, and running it each time, in the following ways: Click on the Sort row in a column other than the Salesperson ID, and select Ascending. Run the query and view the results. Now change back to Design View and select Descending for the same field and run it. View the results. Try this with several fields. Click on the Show button (in Design View) for a field (should be checked - so "uncheck" it) and run the query. That field should not be present. Check the field back again. If you haven't chosen the State field to be in your query, click on the next empty field's drop-down arrow and choose it. Now you're going to select, or filter out, records. Click in the Criteria row for the State field and key in =GA. (obviously, you will have to have some employees in GA, and you should have some in other states too) Tab out of the field. Now run the query. Try the same selection with Sex being M and then F. You should have some records of each sex so that this filter works, displaying only the male records (and then, the female records) You can do the same thing with the Company Car Yes / No field, using it to filter only those Salespersons who drive a company car.
Prof. Katz
Page 3
3/25/2010
PART VII JOINED TABLE QUERY When you create this kind of query, using the same steps (1 through 3 above) you did before, this time add both tables from the Show Table box. The relationship you have previously created will also be in the query. Now you can follow the same steps above, selecting fields from both tables, without even displaying (or using) the Salesperson ID in the query as a field. The connection between the common Salesperson IDs will allow data such as the employee's name and his customers city to be displayed.
Prof. Katz
Page 4
3/25/2010