CIS 146 | Microcomputer Applications

# More Access

Database design: The arrangement of data into tables and fields.

A table is a collection of data about a specific topic, such as products or suppliers. Using a separate table for each topic means that you store that data only once. This results in a more efficient database and fewer data-entry errors.

Data Types: Data type is a characteristic about a field which determines what kind of data it can hold. Pick the data type based on the type of information that will be stored in the field.

Access provides the following data types:

- Text - used to store text or words, or even numbers that will not be used in calculations (like phone numbers or social security numbers). A Text field can store up to 255 characters. The default size is 50 characters. Similar to a label in Excel.
- Memo - used to store large amounts of text - up to 65,536 characters. If you want to store formatted text or long documents, you should create an OLE Object field instead of a Memo field.
- Number - used to store numbers that will be used in calculations, except calculations that involve money or that require a high degree of accuracy. Similar to a value in Excel.
- Date/Time - used to store dates and/or times
- Currency - used to store dollar amounts. The currency field is accurate to 15 digits to the left of the decimal point and 4 digits to the right - more accurate than the number data type.
- AutoNumber - can generate three kinds of numbers: sequential numbers that increment by one, random numbers, and Replication ID (aka: GUIDs - globally unique identifiers) numbers. Good choice for use as a table's primary key.
- Yes/No - Boolean expression, either True or False, or can be either Yes or No.
- OLE Object - used for objects that were created in other applications that can be linked or embedded.
- Hyperlink - used for linking to a web address.
- Lookup Wizard - used to provide a list of choices from another table.

Considerations to determine which data type to use:

- What kind of values do you want to allow in the field? For example, you can't store text in a field with a Number data type.
- How much storage space do you want to use for values in the field?
- What types of operations do you want to perform on the values in the field?
- Do you want to use a field to group records in queries or reports?
- How do you want to sort values in a field? In a Text field, numbers sort as strings of characters (1, 10, 100, 2, 20, 200, and so on), not as numeric values. Use a Number or Currency field to sort numbers as numeric values. Also, many date formats will not sort properly if entered in a Text field. Use a Date/Time field to ensure proper sorting for dates.
- Will you need to store Microsoft Word or Microsoft Excel documents, pictures, sound, and other types of binary data created in other programs?

Primary Key: Unique identifier. A primary key is one or more fields whose value or values uniquely identifies each record in a table. We use the primary key to link tables together. A primary key cannot allow Null values and must always have a unique index. A primary key is used to relate a table to foreign keys in other tables.

Foreign Key: A field in one table whose values are required to match the primary key of another table. For example, an account number in an invoice table could be required to match the account number of an account from the accounts table.

Referential Integrity: A property that ensures that the value in a foreign key must match the primary key in another table. For example, by clicking the Enforce Referential Integrity check box when joining tables, you make certain that a user cannot enter an invoice for an account which does not exist in the accounts table.

Join: find records in two tables that have identical values in matching fields. A join is an association between a field in one table or query and a field of the same data type in another table or query. Joins tell the program how data is related. Records that don't match may be included or excluded, depending on the type of join.

Index: An ordered list which is usually based on one field. An index is a feature that speeds up searching and sorting in a table based on key values and can enforce uniqueness on the rows in a table. The primary key of a table is automatically indexed. Some fields can't be indexed because of their data type. We can also base an index on multiple fields. Multiple-field indexes enable you to distinguish between records in which the first field may have the same value.

Data Redundancy: Storing the same fact in more than one place. Avoid data redundancy as it can waste disk space and cause inconsistent data. This means that we would not store a customer name in two different tables. Instead, we would link to a second table by using a customer number.

Problems with data redundancy:

1. wasting disk space
2. difficulty in updating the database
3. possibility of inconsistent data

Detail Lines in Reports: The line that prints one line for each record in the underlying table or query.

Forms: A type of a database object that is primarily used to enter or display data in a database. You can also use a form as a switchboard that opens other forms and reports in the database, or as a custom dialog box that accepts user input and carries out an action based on the input.

Query: A question we ask in a way that a database can answer. Often, we will search a table for a specific record or group of records - For example, searching for all clients that live in Alabama.

Computed Field: a field whose value is based on another field - For example, a tax field may be computed by multiplying the tax rate times the invoice amount.

Update Query: A query which automatically changes the value of specified fields in a table at once.

Form view - one record appears on the screen at a time which allows more fields to be viewed.

Wildcards: Used in queries to allow any character.

- The Asterisk (*) is a wildcard allowing any character from that point forward, or from that point back to the beginning. For example, searching for WIL* will return Wilsonville, Williams, Willington, etc. Or, searching for *TOWN will return Williamstown, Georgetown, etc.
- The Question Mark (?) is a wildcard allowing any character in that exact position. For example, searching for WILLING?ON will return both Willington and Willingson.

Computed Field or Calculated Field: A field, defined in a query, that displays the result of an expression rather than displaying stored data. The value is recalculated each time a value in the expression changes. A computed field is a field whose value is based on a calculation of one or more fields. To create a computed field, type the computed field name followed by a colon, then create the formula by enclosing field names in brackets.

For example, `OnHandValue:[OnHand]*[cost]` will create a computed field named OnHandValue and the values of this computed field will be the [OnHand] field multiplied by the [Cost] field.

Zoom: Provides a larger data input area. Although not a test question, the F9 key is the Zoom key.

Grouping: Select GroupBy as entry in the total row in the field column to be used.

Searching: Running a query to find all clients in a particular area.

Validation Rules: User must follow these rules when entering data. For example, create a validation rule that would require all hire dates to be on or after July 4, 1999 if that date was the date that the company was founded. Access will not allow a user to enter a date before that date if this validation rule is in effect. Validation rules help to reduce inaccurate data input, but it will not eliminate it.

Required: Will only allow user to leave a record after entering information in a field.

Default Value: Set the default value attribute of a field to a common value to avoid having to type the same information over and over. For example, if I was entering a lot of new accounts that were located in Birmingham, Alabama, I would set the default value property of CITY to Birmingham and I'd set the default value property of STATE to Alabama. When I entered data, I could tab through the fields since the information would already be there by default. I would only have to type a field's value for STATE if it was different than the default value of Birmingham, in this example.

Comparison Operators: Used to specify conditions to limit which records are included in the result set of a query or filter.

- Greater Than symbol >
- Less Than symbol <
- Greater Than or Equal To symbol >= (combine > with =)
- Less Than or Equal To symbol <= (combine < with =)

For example, the following criterion selects records for which the value for the Order Amount field is greater than 30,000: `Order Amount > 30000`

Logical Operators: You can enter additional criteria for the same field or different fields. When you type expressions in more than one Criteria cell, Microsoft Access combines them using either the AND or the OR operator. Access determines if you are wanting to use the AND or OR operator depending upon where the expression is placed.

- If the expressions are placed on the SAME ROW of the design grid, Access uses the AND operator, which means only the records that meet the criteria in all the cells will be returned.
- If the expressions are placed on the DIFFERENT ROWS of the design grid, Access uses the OR operator, which means records that meet criteria in any of the cells will be returned.

For example, Let's assume we're about to create an account list for a new salesperson that is taking over the three state territory of AL, GA, and FL. How do we create a query to return a list of all accounts with a branch in at least one of the following states: AL, GA, and FL? Would you use AND or OR?

ANSWER: You would use OR because you want all companies listed, even if they're only located in one state. If you use AND, only those companies that had branches in all three states would be listed.

Update query: Automatically changes the value of specified fields. BE CAREFUL with the UPDATE QUERY! It will update (or change) all your data!

One to many relationship: A relationship between two tables where one record in the first table can match many records in the second table. For example, a customer table can be joined with an invoice table using a One-To-Many relationship. One customer can have many invoices.

Portrait Orientation: Taller than it is wide.

Landscape: Wider than it is tall.