When you create a new table in design view Access automatically assigns the name?
|
Microsoft Access Tutorial Show Overview MS Access is frequently used by small companies to create simple database solutions. Using Access, you don�t have to know too much about databases and will still be able to easily construct simple, aesthetically pleasing database solutions. This file has hyperlinks to some reference material. The bulk of the material that follows was copied and modified from the following websites: http://www.fgcu.edu/support/office2000/access and http://office.microsoft.com/en-us/access-help/CH010372755.aspx Part 0: Databases A database is a collection of related information. The database is the container for the data and associated objects.� Objects include tables, queries, forms, reports, macros, and modules.� To create a new database in Access 2010, start MS Access 2010, and select New->Blank Database. Fill out the filename for your database (yourlastname_labX.accdb for this class) and, if needed, �change the directory where the database is saved. Part 1: Tables In Access, the �table� holds the actual data, so we will begin your review with basic table operations. Below are a few useful definitions.
Introduction to Tables Tables are grids that store information in a database similar to the way an Excel worksheet stores information in a workbook. Access provides several ways to create a table. The default one is by using Datasheet View (see below), but we will use the Design View.
Create table in Design view will allow you to create the fields of the table. This is the most common way of creating a table and is explained in detail below. Create a Table in Design View Design view allows you to build a table from scratch and set or change every available property for each field. You can also open existing tables in Design view and add, remove, or change fields. See below for more details: Design View will allow you to define the fields in the table before adding any data to the datasheet. The window is divided into two parts: a top pane for entering the field name, data type, and an option description of the field, and a bottom pane for specifying field properties.
Field Properties Properties for each field are set from the bottom pane of the Design View window.
Indexes Creating indexes allows Access to query and sort records faster. To set an indexed field, select a field that is commonly searched and change the Indexed property to Yes (Duplicates OK) if multiple entries of the same data value are allowed or Yes (No Duplicates) to prevent duplicates. Validation Rule Validation Rules specify requirements for the data entered in the worksheet. Examples of field validation rules include: �<> 0 �to not allow zero values, �>= 0 to only allow numbers that are greater or equal to 0 ="A" or = "B" to only allow value A or value B ??? �to allow only all data strings three characters in length, etc. You can click the expression builder ("...") button at the end of the Validation Rule box to write the validation rule. Validation Text A customized message can be displayed to the user when data that violates the rule setting is entered, so the user knows how to correct the errors. Input Masks An input mask controls the value of a record and sets it in a specific format. They are similar to the Format property, but instead display the format on the datasheet before the data is entered. For example, a telephone number field can formatted with an input mask to accept ten digits that are automatically formatted as "(555) 123-4567". The blank field would look like (___) ___-____. An an input mask to a field by following these steps:
Primary Key Every record in a table must have a primary key that differentiates it from every other record in the table. In some cases, it is only necessary to designate an existing field as the primary key if you are certain that every record in the table will have a different value for that particular field. A social security number is an example of a record whose values will only appear once in a database table. Designate the primary key field by right-clicking on the record (or records) and selecting Primary Key from the shortcut menu or select Edit|Primary Key from the menu bar. The primary key field will be noted with a key image to the left. To remove a primary key, repeat one of these steps. If none of the existing fields in the table will produce unique values for every record, a separate field must be added. Access will prompt you to create this type of field at the beginning of the table the first time you save the table and a primary key field has not been assigned. The field is named "ID" and the data type is "autonumber". Since this extra field serves no purpose to you as the user, the autonumber type automatically updates whenever a record is added so there is no extra work on your part. Adding Records Add new records to the table in datasheet view by typing in the record beside the asterisk (*) that marks the new record. You can also click the new record button at the bottom of the datasheet to skip to the last empty record. Editing Records To edit records, simply place the cursor in the record that is to be edited and make the necessary changes. Use the arrow keys to move through the record grid. The previous, next, first, and last record buttons at the bottom of the datasheet are helpful in maneuvering through the datasheet. Deleting Records Delete a record on a datasheet by placing the cursor in any field of the record row and select Edit|Delete Record from the menu bar or click the Delete Record button on the datasheet toolbar. Adding and Deleting Columns Although it is best to add new fields (displayed as columns in the datasheet) in design view because more options are available, they can also be quickly added in datasheet view. Highlight the column that the new column should appear to the left of by clicking its label at the top of the datasheet and select Insert|Column from the menu bar. Entire columns can be deleted by placing the cursor in the column and selecting Edit|Delete Column from the menu bar. ����������� Part 2:� Forms Forms allow you to control the look and feel of the screen for the input of data and the reports generated. Form Views
Create a Form
To preview the form:
Adding Records Using A Form Input data into the
table by filling out the fields of the form. Press the Tab key to move from field to field and create a new record by clicking Tab after the last field of the last record. A new record can also be created at any time by clicking the New Record button Editing Forms The follow points may be helpful when modifying forms in Design View.
Part 3: Queries Queries select records from one or more tables in a database so they can be viewed, analyzed, and sorted on a common datasheet. To design a query using the Query Design Button:
Query Criteria To specify search criteria:
Note: Logical OR vs. Logical AND.�� If you put search criteria for different fields all on the same horizontal line, then the criteria is interpreted as joined by logical �AND�.�� If you place criteria on a line below the line of another criteria, then the two criteria are interpreted as joined by a logical �OR�.
Part 4: Reports Reports are a means to view and analyze large amounts of data from underlying tables or queries. You can use the Report Wizard or create a custom report that meets your specific needs. Report Views
To change report views:
Create a Report;
To create a blank report:
When you create a table Access automatically creates a field called?When you create a new table in Datasheet view, Access automatically creates a primary key for you and assigns it a field name of ID and the AutoNumber data type. In Design view, you can change or remove the primary key, or set the primary key for a table that doesn't already have one.
When designing a table in design view the field with the key is called the?Good table design requires that every table have at least one field that acts as a unique key. We call this the primary key field. In Access, you designate it by right-clicking the field in Design View and selecting “Primary Key.”
Is the default name for a new table in an Access database?Table1 is the default name of the first table in a new desktop database.
|
