A well-designed database not only helps ensure data integrity, but is easier to maintain and update. An Access database is not a file in the same sense as a Microsoft Word document or a Microsoft PowerPoint slide deck. Instead, it's a collection of objects — tables, forms, reports, queries, and so on — that must work together to function properly. Show
Users enter data primarily through controls. What you do with a given control depends on the data type set for the underlying table field, any properties set for that field, and properties set for the control. Finally, consider using additional database techniques such as validation, default values, lists and lookups, and cascading updates. For more information on updating data from a user point-of-view, see Ways to add, edit, and delete records. In this article
How database design affects data entryThe information that you keep in a database is stored in tables, which contain data about a particular subject, such as assets or contacts. Each record in a table contains information about one item, such as a particular contact. A record is made up of fields, such as name, address, and telephone number. A record is commonly called a row, and a field is commonly called a column. These objects must adhere to a set of design principles or the database will either work poorly or fail altogether. In turn, those design principles affect how you enter data. Consider the following:
For more information, see Database design basics and Create a table and add fields. Top of Page Set a default value for a field or controlIf a large number of records share the same value for a given field, such as a city or country/region, you can save time by setting a default value for the control bound to that field or the field itself. When you open the form or table to create a new record, your default value appears in that control or field. In a Table
In a Form
Top of Page Using validation rules to limit dataYou can validate data in Access desktop databases as you enter it by using validation rules. Validation rules can be set in either table design or table datasheet view. There are three types of validation rules in Access:
For more information, see Restrict data input by using validation rules. Top of Page Working with value lists and Lookup fieldsThere are two types of list data in Access:
By default, Access displays list data in a combo box control, although you can specify a list box control. A combo box opens to present the list, and then closes once you make a selection. A list box, by contrast, remains open at all times. To edit lists, you can also run the Edit List Items command or you can edit the data directly in the Row Source property of the source table. When you update the data in a lookup field, you update the source table. If the Row Source Type property of your list box or combo box is set to Value List, you can edit the list of values while the form is open in Form view — avoiding the need of switching to Design view or Layout view, opening the property sheet, and editing the Row Source property for the control each time you need to make a change to the list. To edit the list of values, the Allow Value List Edits property for the list box or combo box must be set to Yes. For more information, see Create a list of choices by using a list box or combo box. Prevent the editing of the value list in Form view
Specify a different form for editing the value listBy default, Access provides a built-in form for the purpose of editing the value list. If you have another form that you would prefer to use for this purpose, you can enter the name of the form in the List Items Edit Form property, as follows:
Examine a lookup field in a form
Examine a lookup field in a table
Top of Page Deleting the data from a values list or Lookup fieldThe items in a value list reside in the same table as the other values in a record. By contrast, the data in a lookup field resides in one or more other tables. To remove data from a value list, open the table and edit the items. Removing data from a lookup list requires additional steps, and those steps vary depending on whether the query for the lookup lists takes its data from a table or another query. If the query for the lookup list is based on a table, you identify that table and the field that contains the data that appear in the list. You then open the source table and edit the data in that field. If the query for the lookup list is based on another query, you must open that second query, find the source table and field from which the second query takes its data, and change the values in that table. Remove data from a value list
Remove data from a lookup field
Top of Page How data types affect the way you enter dataWhen you design a database table, you select a data type for each field in that table, a process that helps ensure more accurate data entry. View data typesDo one of the following: Use Datasheet View
Use Design View
How data types affect data entryThe data type that you set for each table field provides the first level of control over what type of data is permitted into a field. In some cases, such as a Long Text field, you can enter any data that you want. In other cases, such as an AutoNumber field, the data type setting for the field prevents you from entering any information at all. The following table lists the data types that Access provides, and describes how they affect data entry. For more information, see Data types for Access desktop databases and Modify or change the data type setting for a field.
Top of Page How table field properties affect the way you enter dataIn addition to the design principles that control the structure of a database and the data types that control what you can enter in a given field, several field properties can also affect how you enter data into an Access database. View properties for a table fieldAccess provides two ways to view the properties for a table field. In Datasheet view
In Design View
How properties impact data entryThe following table lists the properties that have the greatest impact on data entry and explains how they affect data entry.
Top of Page Use cascading updates to change primary and foreign key valuesAt times, you may need to update a primary key value. If you use that primary key as a foreign key, you can automatically update your changes through all child instances of the foreign key. As a reminder, a primary key is a value that uniquely identifies each row (record) in a database table. A foreign key is a column that matches the primary key. Typically, foreign keys reside in other tables, and they enable you to create a relationship (a link) between the data in the tables. For example, suppose you use a product ID number as a primary key. One ID number uniquely identifies one product. You also use that ID number as a foreign key in a table of order data. That way, you can find all the orders that involve each product, because any time someone places an order for that product, the ID becomes part of the order. Sometimes, those ID numbers (or other types of primary keys) change. When they do, you can alter your primary key value and have that change automatically cascade through all related child records. You enable this behavior by turning on referential integrity and cascading updates between the two tables. Remember these important rules:
For more information about creating relationships. see Guide to table relationships and Create, edit or delete a relationship. The following procedures go hand-in-hand and explain how to first create a relationship and then enable cascading updates for that relationship. Create the relationship
Enable cascading updates in primary keys
Top of Page Why ID numbers sometimes seem to skip a numberWhen you create a field that is set to the AutoNumber data type, Access automatically generates a value for that field in every new record. The value is incremented by default, so that every new record gets the next available sequential number. The purpose of the AutoNumber data type is to supply a value that is suitable for use as a primary key. For more information, see Add, set, change, or remove the primary key. When you delete a row from a table that contains a field that is set to the AutoNumber data type, the value in the AutoNumber field for that row is not always automatically reused. For that reason, the number that Access generates might not be the number you expect to see, and gaps might appear in the sequence of ID numbers — this is by design. You should rely only on the uniqueness of the values in an AutoNumber field, and not their sequence. Top of Page Bulk updating data by using queriesAppend, Update, and Delete queries are powerful ways to add, change, or remove records in bulk. Furthermore, bulk updates are easier and more efficient to perform when you use good principles of database design. Using an append, update, or delete query can be a time-saver because you can also reuse the query. Important Back up your data before you use these queries. Having a backup on hand can help you quickly fix any mistakes you might inadvertently make. Append queries Use to add many records to one or more tables. One of the most frequent uses of an append query is to add a group of records from one or more tables in a source database to one or more tables in a destination database. For example, suppose that you acquire some new customers and a database containing a table of information about those customers. To avoid entering that new data manually, you can append it to the appropriate table or tables in your database. For more information, see Add records to a table by using an append query. Update queries Use to add, change, or delete part (but not all) of many existing records. You can think of update queries as a powerful form of the Find and Replace dialog box. You enter a select criterion (the rough equivalent of a search string) and an update criterion (the rough equivalent of a replacement string). Unlike the dialog box, update queries can accept multiple criteria, allow you to update a large number of records in one pass, and allow you to change records in more than one table. For more information, see Create and run an update query. Delete queries When you want to either quickly delete a lot of data or delete a set of data on a regular basis, a delete is useful because the queries make it possible to specify criteria to quickly find and delete the data. For more information, see Ways to add, edit, and delete records. Top of Page When adding date criteria to the query design view the dates you enter must be delimited by?Table row of the query design grid. When adding date criteria to the Query Design view, the dates you enter must be delimited by: Pound signs (#).
What should you use to write criteria into a query?Apply criteria to a query. Open your query in Design view.. In the query design grid, click the Criteria row of the field where you want to add the criterion.. Add the criteria and press ENTER. ... . Click Run to see the results in Datasheet view.. When you design a query what view should you be in?In most cases, you will only need to use two main views: Datasheet view and Design view. Datasheet view lets you view your query results in the form of a table. Design view, featured here, allows you to view and modify the design of your query.
What determines the type of criterion you enter along with the way you enter the criterion quizlet?The type of criterion you enter, along with the way you enter the criterion, depends on the data type of the field. A calculated field is a field that gets its value from a calculation instead of a value that is entered into the field.
|