1 / 39

Microsoft Access 2

Microsoft Access 2. Database Creation and Management. Creating a Order table.

jerod
Download Presentation

Microsoft Access 2

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Microsoft Access 2 Database Creation and Management

  2. Creating a Order table • Barbara wants to track information about each order placed by each restaurant customer. This information includes the order’s billing date and invoice amount. Barbara asks you to create a second table in the Restaurant database, named Order, in which to store the order data.

  3. Read from this to slide #17 Create a new table To create a new table, click the New button in the Database window. The New Table dialog box then opens. You can design your own table or use one of the Wizards to assist you in designing the table. Choose an option, then click OK.

  4. The database Design View window

  5. Access data (field) type Make certain the field type you select matches the data to be held in that field.

  6. Access data (field) type (con’t) Additional Access field types.

  7. Defining a field in Design View Enter a field name that describes the purpose of the field. Click the Data Type list arrow to see a list of types to select from. A field description is optional, but it is a good idea to supply one. Other field properties can be set in the Properties window.

  8. Choosing a primary key Click a row selector for the field you want to use as the primary key, and then click the Primary Key button on the toolbar. After clicking the Primary Key button, a key symbol appears in the row selector to indicate the key field.

  9. Add records to a table using Datasheet View • Once the table has been created, you can then add records to the table using Datasheet View. • As you enter records, they will be placed in the order in which you enter them. However, when you close the table and open it again, the records will be ordered according to the primary key order. • The navigation bar at the bottom of the Datasheet view will indicate how many records are in the table and what the current record is. • You can move through the fields in the table by pressing the Tab key. Once your cursor is moved off a record, that record is automatically saved on your disk.

  10. Datasheet View with 2 records added Two records have been inserted, and the insertion point is positioned for the third. The navigation bar shows the current record indicator is in the third of three records.

  11. Moving a field in Design View 2. Place the pointer over the row selector, and press and hold down the left mouse button. 1. Click the row selector for the field you want to move to select the entire row. 3. When the pointer changes to the move pointer, drag the field to its new location.

  12. Adding a new field in Design View 2. Select Insert Rows from the shortcut menu, and a new, blank row will be inserted before the row you selected, as shown below. 1. Right-click the row selector for the field you want to insert the new field in front of--StartDate in this figure. 3. Enter the information for the new field.

  13. Change field properties • You can make additional modifications to the structure of a table by changing the properties of the table's fields. • For example, if you have a field that represents a currency value but you do not want to display dollar signs, you could change the format to a standard format • In the Design View, you will find a list of all formats available to you. You can make a selection from the list of options • Each data type has a separate set of options available in the Field Properties portion of the Table Design Window • Field properties are changed in the Design View window.

  14. Changing field properties in Design View When you click a row selector for a field you want to modify, the properties window shows the current properties for that field. Clicking the list arrow for the Format property displays a list of formats that can be selected.

  15. Delete and change records • Once records have been added to a table, they can be deleted or modified: • In Datasheet view, click the row selector for the record(s) you want to delete and then click the Delete Record button on the toolbar • To change or modify a record: • Place your cursor on the record and then on the field you want to change • You can then edit the field's value as you wish by typing in new data • You can switch from navigation mode to editing mode depending on what you want to do at the time. • When you are editing a record, you are in editing mode, Otherwise you are in navigation mode • Editing mode is indicated by a pencil symbol in the left margin of the datasheet view

  16. An alternate method for deleting a record Records can also be deleted by right-clicking the row selector for the record you want to delete, and then clicking the Delete Record option on the shortcut menu.

  17. This is slide #17 Modify a table by deleting, moving, and adding fields • The structure of a table can be modified after it has been created. • To delete a field, enter Design View, right-click on the field, and then click Delete Rows on the drop down menu. • To move a field, (you also do this in Design View) click on the field you want to move, and while holding your mouse button down, move the field to the desired location. • To add a field in Design View, right-click the field where you want to insert the new field and then click Insert Rows on the dropdown menu.

  18. Creating a Order table • Use Design view (NOT Wizard) • Not allow design flexibility • Use wizard for the exam will cause problems… • Case sensitive • NUMBER vs. number • Exact match of each name • Use tap key to enter data • Especially, when you enter data in datasheet view.

  19. Creating the Order table

  20. OrderNum CustomerNum BillingDate PlacedBy InvoiceAmt 323 624 02/15/2001 Mike Smith $1,986.00 201 107 01/15/2001 Matt Davis $854.00 Adding Records to a Table • Enter data sequentially in Datasheet view • Do not jump from field to field • Use tap key to enter data

  21. Modifying a Table • From the Order table • Delete the PlacedBy field • Move the BillingDate field to the end of the table • Insert the Paid as a new field between CustomerNum and InvoiceAmt (position) fields • data type: Yes/No • default value: No (means “unpaid”) • Add following data to each filed: 211, 201, paid (mark the check box using space bar), $703.50, 01/15/2001

  22. Practice: Creating a DB • Barbara needs a database to track the coffee products offered by Valle Coffee. She asks you to create the database by completing the following: • In the initial Microsoft Access dialog box, click the blank Access database option button, and then click OK button. Click the Create button the new database. • YOUR LAST NAME DB file name • Display the Table window in Design view (if necessary), and then create a table using the table design shown in the next slide.

  23. Practice: Creating a Table • Specify ProductCode as the primary key, and then save the table as Product. • Add the product records shown in next slide table to the Product table. (Hint: You must type the decimal point when entering the Price field values.)

  24. ProductCode CoffeeCode Price Decaf BackOrdered 2316 JRUM 8.99 Yes 9754 HAZL 40.00 D Yes 9309 COCO 9.99 D No Practice: Modifyinga Table • Add a new field between the CoffeeCode and Price fields, using these properties; • Field Name: WeightCode • Data Type: Text • Description: foreign key • Field Size: 1 • Move the Decaf field so that it appears between the WeightCode and Price fields.

  25. Practice: Updating aTable • Enter these WeightCode values for the three records: A for ProductCode 2316, A for ProductCode 9309, and E for ProductCode 9754. •  Add a record to the Product datasheet with these field values: • ProductCode: 9729 • CoffeeCode: COLS • WeightCode: E • Decaf: D • Price: 39.75 • BackOrdered: Yes

  26. Primary Key (PK) • No “null” value can be allowed. • No two records can have the same primary key. • A PK can be a composite key • Midterm: ONLY single primary key; No composite • More than one field can be used as a PK (composite) • See the example on the web: “Composite PK”

  27. Foreign Key • A filed that connects one table logically with another table. • if B references A, then A must exist. • See exception example on the web: “PK as FK” • Midterm: • No Exception • ONLY single PK & FK

  28. Importing External Access Table and Excel Worksheet • Barbara also wants you to include the Product and Order Detail tables from the FineFood database in the Restaurant database. • Review design view of FineFood DB • And she wants you to include the Billing Address Excel worksheet as a Access table in the Restaurant database. • Before try to import the excel file, review it first • Use Excel column headings for Access table • PK: CustomerNum • Specify in the description area of Design View that CutomerNum is not only primary key of BillingAddress table but also a foreign key of Customer table.

  29. Access is a relational database • Access allows you to form relationships between the tables. • That’s why it’s called a relational database • The simplest way to create a relationship • Look for common fields (identical field names) between the tables. • Tables can be joined in three ways; one-to-one, one-to-many, and many-to-many. • Midterm:ONLY one-to-many relationship type; import three Excel files…

  30. An example of a one-to-many relationship The Employer table is related to the Position table via the common field EmployerID. The Employer table has one record for EmployerID value 10126. The Position table has two records.

  31. A one-to-one relationship • A one-to-one relationship exists when one table has one record associated with only one record of another table. • As we saw from PK as FK databse • Shipping Address table is an related table. • Primary table: customer table • Primary key is also foreign key

  32. Using referential integrity • Referential integrity allows you to maintain the integrity and consistency between related tables. • If you choose to enforce referential integrity, you can insure that you will not have records that have no matching record in the primary table. • The rules associated with referential integrity specify that when you update or delete a record (PK) in the primary table, a matching record (FK) in the related record must be updated or deleted.

  33. Use cascade update and cascade delete • In referential integrity, there are two options. • If you choose cascaded updates, making a change in a field that is common to two related tables will cause the update to be made in both tables. • If you delete a field that is common to two tables, the deletion will take place in both tables. • Enforcing Access Referential integrity Rules • Let’s tryReferential Integrity Example on the web for better understanding

  34. Selecting the tables for a relationship To define a relationship, open the Show Table dialog box by clicking the Relationship button on the toolbar. Select each table you want to be in the relationship and click the Add button. When all tables are added, click the Close button.

  35. Setting relationship options The Edit Relationships dialog box is where you can determine the type of relationship, and set referential integrity and cascade update/delete options.

  36. The Relationships window You can see the tables, fields, and relationship types for any relationship in the Relationship window. The lines indicate the common fields involved in each relationship. The Employer table has two one-to-many relationships--one with the Positions table, and one with the NAICS table. The symbols indicate the type of relationship.

  37. Creating Relationships • Download Restaurant 2 from the web • Create relationships using 5 table • In terms of creating a relationship between Customer and BillingAddress, start from the Customer table. • Enforce both cascade options • Primary key of the Order Detail table • Combination of OrderNum and ProductCode • Otherwise, a duplication of the quantity field in both the Order and Product tables.

  38. Practice: Creating Relationships 1 • Create a blank database (use any name you like) And then, import the three Excel Worksheets (Course, Instructor, and Membership) from the class web site into your Access database. • Define each imported table’s primary key using information below: • Course table: Class_Number • Instructor table: Employee_Number • Membership table: Member_Number

  39. Practice: Creating Relationships 2 • Establish relationship based on common fields. • And enforce referential integrity (apply both options) among three imported tables

More Related