Download
1 / 76
760 likes | 1.04k Vues
Tables. Any database must contain one or more tables, because that’s where the data is stored. For any table there are two views provided: Datasheet view Design view. Here is an example of a table in Datasheet View.
E N D
Any database must contain one or more tables, because that’s where the data is stored. • For any table there are two views provided: • Datasheet view • Design view
Here is an example of a table in Datasheet View. • Each record is displayed as a separate row, and the column headings are the field names.
You can navigate among the records and fields just as you would on an Excel worksheet, using the arrow keys, or Tab and Shift+Tab, or Enter, or clicking in a “cell” • You can also move among the records using the arrow buttons at the bottom of the window.
You can even change the field names by right-clicking on them, and other Excel-like operations are possible as well.
Filtering and sorting • Among the simple things you can do with data from a table displayed in Datasheet View is to apply a filter to display only those records satisfying certain criteria, or to sort the records according to the values of certain fields.
Much more sophisticated versions of these are available through the use of queries, which we discuss later. • To apply simple filters or sorting operations to a table, use the appropriate buttons on the database toolbar or select the appropriate items from the Records menu.
Designing a Table • To design a table and specify many important additional details, you need to use the Design View. • Here is our earlier table, shown in Design View:
In Design View, you can see (and specify) what the field names are for the table and what their datatypes are. • In addition, there are many other properties that can be associated with each field, including its maximum size, whether it is required, etc.
Write a short description of what kinds of stuff, i.e. the domain of values, that can go into this field. Select its data type from the drop down list. Type in the name of the field. Specify the length of the field and any other properties of that field.
What about the Table Wizard? • If your table closely matches one of the standard ones available in Access, you might consider using the Table Wizard. • You can always start with one of these pre-designed tables and modify it using Design View.
Field names • In Access field names can be up to 64 characters long, including spaces. • Make your field names non-cryptic so your users don’t have to guess what the name and the data mean.
The next thing you have to specify about that field is the datatype that it has, that is tell the system what type of data can go into that field.
Text Memo Number Date/Time Currency AutoNumber Yes/No OLE Object Hyperlink Lookup Wizard... Data types that Access recognizes
Text data type • Use a Text data type to store data such as names, addresses, and any numbers that do not require calculations, such as phone numbers, part numbers, or postal codes. • A Text field can store up to 255 characters, but the default field size is 50 characters.
Memo data type • Use the Memo data type if you need to store more than 255 characters. • A Memo field can store up to 65,535 characters. • Memo fields can't be indexed or sorted. • If you want to store formatted text or long documents, you should create an OLE field instead of a Memo field.
Number data type • Use a Number field to store numeric data to be used for mathematical calculations, except calculations that involve money or that require a high degree of accuracy.
The kind and size of numeric values that can be stored in a Number field is controlled by setting the FieldSize property. For example, the Byte field size will only store whole numbers (no decimal values) from 0 to 255 and occupies 1 byte of disk space.
Currency data type • Use a Currency field to prevent rounding off during calculations. • A Currency field is accurate to 15 digits to the left of the decimal point and 4 digits to the right. • A Currency field occupies 8 bytes of disk space.
AutoNumber data type • The AutoNumber data type is used to create fields that automatically enter a unique number when a record is added. • They are often used as primary keys. • Once a number is generated for a record, the number can't be deleted or changed.
An AutoNumber field can generate three kinds of numbers, but the most important kind is the kind that increments by one each time. • This is a good choice for use as a table's primary key if there is no other field with unique values. • It can be formatted to display as ‘C0001’, for example.
Date/Time data type • Storing dates and times in a Date/Time field ensures that dates and times will be sorted properly. • Also, changes made to the date or time formats that are specified by double-clicking Regional Settings in Windows Control Panel will be automatically reflected in Date/Time fields.
Pre-formatted date/time settings: • General Date (Default) • If the value is a date only, no time is displayed. • If the value is a time only, no date is displayed. This setting is a combination of the Short Date and Long Time settings. • Examples: 4/3/93, 05:34:00 PM, and 4/3/93 05:34:00 PM.
Long Date: Same as the Long Date setting in the Regional Settings Properties dialog box in Windows Control Panel.Example: Saturday, April 3, 1993. • Medium Date Example: 3-Apr-93.
Short Date • Same as the Short Date setting in the Regional Settings Properties dialog box in Windows Control Panel.Example: 4/3/93.
Time • Long Time--Same as the setting on the Time tab in the Regional Settings Properties dialog box in Windows Control Panel.Example: 5:34:23 PM. • Medium Time--Example: 5:34 PM. • Short Time (24 hour clock)--Example: 17:34.
Yes/No datatype • The Format property provides the Yes/No, True/False, and On/Off predefined formats. • Yes, True, and On are equivalent, as are No, False, and Off. • This datatype only takes up one bit of storage.
OLE Object data type • OLE Object fields are used to store data such as Microsoft Word or Microsoft Excel documents, pictures, sound, and other types of binary data created in other programs. • OLE objects can be linked to or embedded in a field in a Microsoft Access table.
When you embed an object, Access stores the object in your database file. • If you modify the object from your form or report, the object is changed in your database file. For this reason, an embedded object is always available.
When you link to an object, you can look at the object and make changes to it from your form or report, but the changes are stored in the original object file, not in your database file.
Hyperlink data type • You can store hyperlinks in fields in tables, just as you store phone numbers and fax numbers. • For example, our Client table could contain a field that would store hyperlinks to home pages for some of our clients.
Lookup Wizard • A lookup wizard creates a field that lets you choose a value from some other table or from a list of values by using a list box or combo box. • Clicking this option starts the Lookup Wizard, which creates a Lookup field. • After you complete the wizard, Microsoft Access sets the data type based on the values you select in the wizard.
Lookup Wizard… ‘data type’ • Using the Lookup Wizard, you can create a field that displays either of two kinds of lists to make data entry simpler: • A Lookup list that displays values looked up from an existing table or query • A value list that displays a fixed set of values that you enter when you create the field
Lookup list • The most common Lookup list displays values looked up from a related table:
Value list • A value list looks the same as a Lookup list, but consists of a fixed set of values you type in when you create it. • A value list should only be used for values that will not change very often and don't need to be stored in a table. • For example, a list for a Salutation field containing Mr., Mrs., or Ms. would be a good candidate for a value list.
Choosing a value from a value list will store that value in the record. It doesn't create an association to a related table. • For this reason, if you change any of the original values in the value list later, they will not be reflected in records added before this change was made.
Field Properties • Fields can have certain properties. • The most commonly used ones are • Format • Caption • Default Value • Indexed • Primary • Validation Rule • Validation Text
‘Default Value’ Property • You can use the DefaultValue property to specify a value that is automatically entered in a field when a new record is created. For example, in a ZIP table consisting of ZIP, City, and State, you can set the default value for the State field to, e.g. MA, if most of your clients are from that state. • When users add a record to the table, they can either accept this value or enter the name of a different city.
The DefaultValue property is applied only when you add a new record. • If you change the DefaultValue property, the change isn't automatically applied to existing records.
You can use the Indexed property to set an index. • An index speeds up queries on the indexed fields as well as for sorting and grouping operations. • For example, if you often search for specific employee names in a LastName field, you should probably create an index for this field to speed up the search for specific names.
The field (or combination of fields) can hold either unique or non-unique values. For example, you can create an index on an EmployeeID field in an Employees table in which each employee ID is unique (and which you might specify as the primary key) or you can create an index on a Name field in which some names may be duplicates.
You can create as many indexes as you need, but do they do take up space and require extra computing time to update as the database is modified. • Also, don’t index a field which can take only a few values, like Yes or No.
