IDENTIFY DATABASE ELEMENTS Elements of databases A database


IDENTIFY DATABASE ELEMENTS Elements of databases A database stores information in an organized way, and makes it easy to get information in and out. Tables store data within the database. Forms make it easy to put data into tables. Queries pull out specific data. Reports put data in an easily-read format.

WORKING WITH TABLES In this section, you'll learn how to: Modify tables Create new tables Specify data types Specify field properties Edit records Find records Sort and filter records Create table relationships

CREATE NEW TABLES in Design view. (Open the FlowerStore database.) Click Create Table in Design view.

A blank table will open in Design View

In the first box in the Field Name column, type: Order ID then press the TAB key. It should look like this

On the Toolbar, click the icon .

When the Save As window appears, type: Orders in the Table Name box.

Click the OK button. When the alert window appears, click the NO button. TIP: You'll assign a Primary Key later.

SPECIFY DATA TYPES ASSIGN THE AUTONUMBER DATA TYPE In the Data Type column beside the Order ID field, click the drop-down arrow. When the list appears, click Integer.

Under Field Properties, beside the AutoValue field, click the drop-down arrow. When the list appears, click Yes.

In the Description column, type: This is the Generic Order ID Number then press TAB. TIP: Filling in a Description is optional, but it helps you to remember what sort of information is supposed to be stored in a field.

ASSIGN THE DATE/TIME DATA TYPE 1.In the Field Name column, in the second row, type: Order Date then press TAB. 2.In the Data Type column, click the drop-down arrow. When the menu appears, сlick Date/Time, then press TAB. 3. In the Description column, type: Date the order was placed then press TAB.

ASSIGN A PRIMARY KEY 1.Right-click the Order ID field. 2.When the menu appears, click Primary Key The field should now show a key beside it:

3.On the Menu Bar, click File, then Save.

SPECIFY FIELD PROPERTIES 1.Click in the Order Date field. 2.In the Field Properties section of the window, click the button next to the Format examplebox.

3.In the menu of formats that appears, click 31.12.99. Then click the button OK. On the Toolbar, click the Save icon. On the Menu Bar, click File, then Close to return to the database window.

EDIT RECORDS Open the Customers table. Click inside the Customer Name field for record number 1, between the w and s in Andrews Floral. Insert an apostrophe.

Press TAB eight times to move to the Phone Number field. Type: 2125554569 It should replace the previous text. Click in any other record. TIP: Remember-changed data in a record is saved when you move off that record.

DELETE RECORDS Right-click the button for HomeVase's record. In the menu that appears, click Delete Rows. When the alert window appears, click the Yes button. TIP: Once a record has been deleted from the database, it cannot be recovered.

FIND RECORDS NAVIGATE RECORDS In the Customers table window, click the |button. The cursor should be in the last record in the database. Click the button. The cursor should be in the previous record. Click the | button. The cursor should be in the first record. Click the button. The cursor should be in the second record.

SEARCH FOR RECORDS Click the Company Name column header. On the Toolbar, click the icon. When the Record Search window appears, type: Love in the Search for Text box. In the Position list, click the drop-down arrow, then anywhere in the field. The table should look like this:


Click the Search button. The Customers table should now look with Love Me True highlighted: In the Find and Replace window, click the Close button.

SORT AND FILTER RECORDS SORT IN DESCENDING ORDER Click the Company Name column header. On the Toolbar, click the Sort Descending icon. The Customers table should now look with the Company Names arranged in descending alphabetical order.

SORT IN ASCENDING ORDER Click the Customer ID column header to highlight the column. On the Toolbar, click the Sort Ascending icon. The Customers table should now look with the Customer ID numbers arranged in ascending order:

FILTER RECORDS Click in the State field for Pete's Moss. Notice that the State is Virginia. On the Toolbar, click the AutoFilter icon. The Customers table should now look showing all customers whose State is Virginia. On the Toolbar, click the RemoveFilter icon. The Customers table should show its original contents. On the Menu Bar, click File, then Close to return to the database window.

When should you filter? Filtering records works like a query, bringing up specific information from a table. But unlike a query, your filtering doesn't remain part of the database. When you perform a query and save it, the query remains with the database, even after you close it. Filtering does not. Since you can't save filtering like a query, filter records when you need quick results. Think of filtering as a temporary, quick, "on the spot" query.

What are "relationships?" Relationships are links that associate a field in one table with a field in another. An example is a school database with two tables: Students Classes

CREATE TABLE RELATIONSHIPS What are "relationships?“ Relationships are links that associate a field in one table with a field in another. An example is a school database with two tables: Students and Classes The Students table holds students' names and addresses The Math Class table holds information about the students in math class

CREATE TABLE RELATIONSHIPS Both tables have a Student Name field. By linking the Student Name fields, you make sure that John in the Students table is the same John listed in the Math Class table

EXAMINE CURRENT TABLE RELATIONSHIPS On the Menu Bar, click Tools, then Relationships.

When the Add Tables window appears, double-click Customers.

The Customers table should appear in the Relationships work area. In the Add Tables window, double-click Employees. The Employees table should appear in the Relationships work area: Double-click Order Details. Double-click Orders. Double-click Products. In the Add Tables window, click the Close button.

ADD RELATIONSHIPS

Place the cursor on the Title Bar at the top of the Employees table. Click and drag the Employees table to place it under the Customers table. Click and drag the Orders table to place it between the Customers table and Order Details table. Click the Order ID field in the Order Details table, then drag it and rest it on top of the Order IDfield in the Orders table.

Release the mouse button. The relationships window should now look like this: The Order ID field in the Orders table is ed to the Order ID field in the Order Details table. The same Order ID number in both tables refers to the same order. On the Menu Bar, click File, then Close to return to the FlowerStore database window. When the alert window appears, click the Yes button. On the Menu Bar, click File, then Exit.


PRACTICE: WORKING WITH TABLES Start Base. Open the Library database in the Practice Base Files folder. Create a new table in Design View. Add the following fields in the new table (data type in parentheses): Author ID (Autonumber) Author Name (Text) Good Author? (Yes/No)

Set the Author ID field as the key field. Save the table as Authors, then close it. Open the Books table and view it in Design View. Change the name of the Author field to Author ID. Set its data type to Integer. Change the data type of the Date Purchased field to Date/Time, and its format to 12/31/99. Save the Books table, then close it. TIP: An alert window will appear saying there was an error, due to changing the Date Purchased data type. Click the YES button.

http://inpics.net/tutorials/base2/tables45.html

22454-working_with_tables.ppt
- Количество слайдов: 40