Access Tutorial 2 – Maintaining a Database
I. Designing a database:
A. Identify the fields needed to produce the required information.
B. Group related fields into tables.
C. Determine each table’s primary key (that uniquely identifies each record in the table) This may be a single field or a composite key of two or more fields, or an autonumber field the application can create.
D. Include a common field in related tables. The common field, a primary key in one table, is considered a foreign key in another table. IT IS NOT a primary key in the second table.
E. Avoid data redundancy. NORMALIZATION will be discussed later, but this is important.
F. Determine the properties of each field so the DBMS knows how to store, display and process the field’s data. The field properties determine the space that is held for fields.
II. Guidelines for designing tables:
A. Naming fields and objects:
1. can have up to 64 characters
2. can contain letters, numbers, spaces and special characters except for a period, exclamation mark, accent grave, and square brackets.
3. cannot start with a space
4. Table names must be unique within the database, but field names can be used in other tables in the same database
1. Capitalize the first letter of each word in the name
2. Avoid extremely long names
3. Use standard abbreviations
4. Do not use spaces in field names as they will take up more space in field titles
C. Field Data Types:
1. Text: 0 to 255 characters, default 50 characters
2. Memo: 1 to 64,0000 characters, determined by the entry
3. Number: 1 to 15 digits
4. Date/Time: 8 bytes
5. Currency: accurate to 15 digits on left side of decimal and 4 digits to the right
6. Autonumber: 9 digits
7. Yes/No: 1 character
8. OLE Object: 1 Gb maximum, depends on object size
9. Hyperlink: up to 64,000 characters total
10. Lookup Wizard: same size as the primary key field used to perform the lookup
D. Assigning field sizes:
1. Field size property defines the maximum storage size for text, number, and AutoNumber fields only.
2. Text – default of 50, but can be anywhere from 0 to 255
3. Memo: much larger
a) Byte: stores whole numbers from 0 to 255 in 1 byte
b) Integer: stores whole numbers from –32,768 to + 32,768 in 2 bytes
c) Long Integer: stores whole numbers from – 2,147,483,648 to + 2,147,483,648 in 4 bytes (2 billion, 147 million, 483 thousand, 648)
d) Single: positive and negative numbers to precisely seven decimal places – 4 bytes
e) Double: Stores positive and negative numbers to precisely 15 decimal places – 8 bytes
f) Decimal: stores positive and negative numbers to precisely 28 decimal places – 12 bytes
III. Creating a Table: P 42
A. Start a table in Design View.
1. Define fields by typing in a name,
2. Tab to Data Type (set this), and
3. Enter a description.
4. Make changes to field properties:
a) Use the F6 key to move to the property pane (or click there)
b) Change field size property to an appropriate size
c) Possibly change “Required” status
d) Possibly change “Default value”
e) Data type: If you are using numbers which will never be used for calculations, less space will be required if you choose a data type of TEXT.
B. Custom Date Formats:
See P 48 Set mm/dd/yyyy to get four-digit years on dates.
C. Specify the Primary Key.
1. Primary keys cannot be duplicated and every record must have one.
2. Records are shown in the order in which they are entered until the table is closed out and then re-opened. Then the records will be in order of Primary Key.
3. Select the primary key field and click the Primary Key button on the table design toolbar. (Key on toolbar)
D. Save the table structure
IV. Add Records to the table:
A. Add records in the Datasheet view. Click on the Datasheet view button on the toolbar (toggles between Datasheet View and Design View Button)
Backspace key Delete key double-click
1. Text – Left-aligned
2. Date/Time -- Right-aligned
3. Numbers -- Right-aligned
D. Once all the fields are filled, the TAB takes you to the next record and SAVES the previous record.
V. Modifying the structure of the Access Table:
1. If you delete a field from a table, you also delete all the data in that field.
2. Move a field in Design View by Drag and drop
3. Insert a new field between two existing fields Click column to right and Insert Column
4. Changing or adding fields does not get saved until you save the table structure.
B. Changing field properties: Data already entered must fit the new properties
C. Check boxes – yes/no Use spacebar to toggle between yes/no. Empty is “no.”
“Yes” is –1 and “no” is 0.
D. Copying records from another database:
1. If the fields are exactly the same (properties, also), then you can “cut and paste” from one database table to another. Open the other database, select and copy the records (See page 65 for instructions), close the database, keeping the data in the clipboard, then open the original database and table, and, after clicking in the new record selector, paste the data into the first empty row.
2. If the data is from a different database or spreadsheet, even one which you do not have and cannot, therefore, open, use the Import Table Wizard from the New Table dialog box. Change the Files of Type list box to display the non-Access database file, such as Excel.
VI. Updating a database.
A. Deleting Records: highlight the row by clicking the record selector and hit the delete key.
B. Changing Records:
1. Make the record the current record, position the insertion point in the field to make minor changes—use the Editing mode to insert or delete a few characters
2. Hit F2 to toggle between Editing Mode and Navigation Mode.
3. In Navigation Mode the entire cell is highlighted. Just type the new entry.
4. On page 70 there is a chart showing the difference effects of several keys depending on whether in Editing or Navigation Mode.