Microsoft Access 2003 - Intro

1. Microsoft Access 2003 - Intro Class 2 Designing Databases and Tables

2. Naming Conventions • Clearly defines the type and purpose of each object • A consistent naming convention will • Bring order to your database • Aid in documentation • Make it easier to understand the database (ie: choosing an object using the report wizard, reading a report generated by the Database Documenter) • Common naming conventions (choose one and then be consistent) • Hungarian • Reddick (http://www.xoc.net/standards/) • Leszynski – Popular

3. Naming Conventions (cont’d) • Access Requirements • Object names • up to 64 characters long • Can include any combination of letters, numbers, spaces, and special characters, except a period (.), and exclamation point (!), and accent grave and brackets ([]) • Cannot begin with a space • Cannot include control characters (ASCII values 0 through 31) • Table, view, or stored procedure names cannot include a double quotation mark (“) • Table and Query names must be unique in the database. Naming any other objects with the same name is NOT recommended

4. Creating a Table in Design View In Database window, double-click the option to create a new table using Design View Type desired field names in appropriate column Choose data type for each field Type description for each field (optional) Mark primary key (right-click desired field) Set field properties, if desired Save table

5. Data Types

6. Field Properties • Field Properties • Field Size • Format • New Value (new in Access 2000) • Precision (new in Access 2000) • Scale • Decimal Place • Input Mask • Caption • Default Value • Required • Allow Zero Length • Indexed • Unicode Compression (New in Access 2000 – stores characters in two bytes mainly to accommodate language fonts)

7. Field Properties Field Size for Text data types determines the maximum number of characters you can type. For Number data types, the Field Size property can be used to determine how large the number can be and if you can have decimal places.

8. Field Properties (cont’d)

9. Indexes • Index • A separate hidden table that consists of pointers to records or a group of records • Automatically set on primary keys • Makes sorting and searching more efficient • Maximum of 32 per table • Normally index any field you plan to sort on or search by frequently • Can be added at any point in the development of the database to improve performance

10. Formatting Tables When in Doubt, Right-Click • Insert/Delete Fields or Records • Hide/Unhide, Freeze/Unfreeze • Right-click the field name or the record selector and choose the desired option from the pop-up menu • Change column width/row height • Position mouse pointer on line between field names or rows and drag or double-click

11. Formatting Tables (cont’d) • Move/Rearrange Fields • Click once on field name, then drag field name until a black line is visible and drag to new position • Formatting Fields • Font and style changes made to a range of data will affect all data in the ENTIRE table • Remember, tables are meant to store the RAW data – to make data pretty, put it in a report!

12. This work is licensed under a Creative Commons Attribution-ShareAlike 2.5 License.