From the current view Datasheet view, change the field Size property for the StaffID field to 7
1642Import data from the Departments comma-delimited text file to anew table. Use the first row as field names. Do not change any fieldinformation. Use the DeptID field as the primary key. Accept thenew table Departments. Do not save the import.1/1In the External Data Ribbon Tab in the Import & Link Ribbon Group,you clicked the New Data Source button. In the New Data Source menuin the From File menu, you clicked the Text File menu item. Inside theGet External Data - Text File dialog, you clicked the Browse... button.You clicked Departments. Inside the File Open dialog, you clicked theOpen button. Inside the Get External Data - Text File dialog, you clickedthe OK button. Inside the Import Text Wizard dialog, you clicked theNext > button, checked the First Row Contains Field Names check box,clicked the Next > button, clicked the Next > button, selected theChoose my own primary key. Radio Button, clicked the Next > button,and clicked the Finish button. Inside the Get External Data - ExcelSpreadsheet dialog, you clicked the Close button.43Limit the query results to records where the insurance Premium isgreater than 250. Run the query to view the results.1/1You clicked the Premium field's criteria row, changed the Premiumfield's criteria to >250. In the Design Ribbon Tab in the Results RibbonGroup, you clicked the Run button.44Modify the table relationships so that when a record is deleted in theDepartments table, the related records in the Staff table will also bedeleted. Show
1From the current view, add a newDate/Timefield to the far right sideof the table. Name the field:StartDateOn the Table Tools Fields tab, in the Add & Delete group, click theDate/Time button. Type StartDate and press Enter.2Add a new calculated field namedEmployeeCostin the first emptycolumn to the right of theProviderNamefield. The new field shouldcalculate the value in thePremiumfieldmultiplied by .25. Run thequery to view the results.Click the first empty cell in the Field rowin the query grid to the right of the ProviderName column. On theQuery Tools Design tab, in the Query Setup group, click the Builderbutton to open the Expression Builder. Double-click the Premiumfield and type *.25 to finish the expression. Click OK. Click in thenew field name for the field. On the Query Tools Design tab, in theResults group, click the Run button.3Add a new record to the table with the staff ID10-9999and advance tothe second field.On Click the New (blank) record button at thebottom of the table. Type 10-9999 in the StudentID field and pressTab4Add a new record to this form.On the Home tab in the Recordslibrary click New.5Add a new table in Design view.On the Create tab in the Tablesgroup, click the Table Design button.6Add criteria to this query to return only the records where the value itheSubscriptionTypefield isSelforFamilyand the value inthePremiumfield is<200. Run the query to view the results.TypeSelf in the Criteria row in the Subscription Type column. TypeFamily in the or row in the Subscription Type column. Type <200 inthe Criteria row in the Premium column. Type <200 in the or rowPremium column. On the Query Tools Design tab, in the Resultsgroup, click the Run button.7Add criteria to this query to return only the records where the value ithePremiumfieldis greater than 200 and less than 250. Run thequery to view the results.Type >200 and <250 in the Criteria row inthe Premium column. On the Query Tools Design tab, in the Resultgroup, click the Run button. When you create an Access database, you store your data in tables—subject-based lists that contain rows and columns. For instance, you can create a Contacts table to store a list of names, addresses, and telephone numbers, or a Products table to store information about products. This article explains how to create a table, add fields to a table, set a table's primary key, and how to set field and table properties. Before you create tables and add fields, make sure you understand the background concepts. For more information, see Introduction to tables. In this article Creating a tableA simple database, such as a contact list, might use only a single table. Many databases, however, use several tables. When you create a new database, you create a new file on your computer that acts as a container for all of the objects in your database, including your tables. You can create a table by creating a new database, by inserting a table into an existing database, or by importing or linking to a table from another data source — such as a Microsoft Excel workbook, a Microsoft Word document, a text file, or another database. When you create a new, blank database, a new, empty table is automatically inserted for you. You can then enter data in the table to start defining your fields. Top of Page Create a new table in a new database
Top of Page Create a new table in an existing database
Top of Page Importing or linking to create a tableYou can create a table by importing or linking to data that is stored elsewhere. You can import or link to data in an Excel worksheet, a SharePoint list, an XML file, another Access database, a Microsoft Outlook folder, and more. When you import data, you create a copy of the data in a new table in the current database. Subsequent changes to the source data will have no effect on the imported data, and changes to the imported data do not affect the source data. After you connect to a data source and import its data, you can then use the imported data without connecting to the source. You can change the design of an imported table. When you link to data, you create a linked table in the current database that represents a live link to the existing information that is stored elsewhere. When you change data in a linked table, you are changing it in the source. Whenever data changes in the source, that change is shown in the linked table. You must be able to connect to the data source whenever you use a linked table. You cannot change the design of a linked table. Note: You cannot edit data in an Excel worksheet by using a linked table. As a workaround, import the source data into an Access database, and then link to the database from Excel. Top of Page Create a new table by importing or linking to external data
Top of Page Use a SharePoint site to create a tableYou can create a table in your database that imports from or links to a SharePoint list. You can also create a new SharePoint list by using a predefined template. The predefined templates in Access include Contacts, Tasks, Issues, and Events.
Top of Page Use a Web service to create a tableYou can create a table in your database that connects to data at a Web site that provides a Web service interface. Web service tables are read-only.
Top of Page Set a table's propertiesIn addition to setting properties fields, you can also set properties that apply to an entire table or to entire records.
Top of Page Save a tableAfter you create or modify a table, you should save its design. When you save a table for the first time, give it a name that describes the data that it contains. You can use up to 64 alphanumeric characters, including spaces. For example, you might name a table Customers, Parts Inventory, or Products. Access gives you lots of flexibility when it comes to naming your tables; however, there are some restrictions to be aware of. A table name can be up to 64 characters long, can include any combination of letters, numbers, spaces, and special characters except a period (.), exclamation point (!), square brackets ([]), leading space, leading equal sign (=), or nonprintable character
such as a carriage return. The name also cannot contain any of the following characters:` / \ : ; * ? " ' < > | # Tip: You should decide on a naming convention for the objects in your database, and use it consistently.
Top of Page Setting a primary keyUnless you have a specific reason not to, you should specify a primary key for a table. Access automatically creates an index for the primary key, which can help improve database performance. Access also makes sure that every record has a value in the primary key field, and that the value is always unique. Unique values are crucial, because otherwise there is no way to reliably distinguish a particular row from other rows. 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. Top of Page Determine which fields to use as a primary keySometimes, you might already have data that you want to use as a primary key. For example, you may have existing ID numbers for your employees. If you create a table to track employee information, you might decide to use the existing employee ID as the primary key for the table. Or, perhaps employee ID is only unique in combination with department ID, requiring that you use both fields together as the primary key. A good candidate for the primary key has the following characteristics:
If no suitable data exists to use as a primary key, you can create a new field to use as a primary key. When you create a new field to use as a primary key, set the field's data type to AutoNumber to help make sure that it meets the three characteristics in the preceding list. Top of Page Set or change the primary key
Top of Page Remove the primary key
When you save a new table without setting a primary key, Access prompts you to create a new field for the primary key. If you click Yes, Access creates an ID field that uses the AutoNumber data type to provide a unique value for each record. If your table already includes an AutoNumber field, Access uses it as the primary key. If you click No, Access does not add a field, and no primary key is set. Top of Page Adding fieldsTo store a new piece of data about something for which you already have an Access table, consider adding a field to the table. For example, suppose you have a table that stores the last name, first name, email address, telephone number, and mailing address of each of your customers. If you want to start tracking each customer’s preferred means of communication, you add a field to store that data. You store each piece of data that you want to track in a field. For example, in a contacts table you create fields for Last Name, First Name, Telephone Number, and Address. In a products table you create fields for Product Name, Product ID, and Price. Every field has certain essential characteristics, such as a name that uniquely identifies the field within a table, a data type that defines the nature of the data, the operations that can be performed on the data, and how much storage space to set aside for each value. Before you create fields, try to separate data into its smallest useful parts. It is much easier to combine data later than it is to pull it apart. For example, instead of a Full Name field, consider creating separate fields for Last Name and First Name. Then, you can easily search or sort by First Name, Last Name, or both. If you plan to report, sort, search, or calculate on an item of data, put that item in a field by itself. After you create a field, you can also set field properties to control its appearance and behavior. For example, the Format property defines how the data appears in a datasheet or form that contains that field. Top of Page Add a field by entering dataWhen you create a new table or open an existing table in Datasheet view, you can add a field to the table by entering data in the Add New Field column of the datasheet (1). Access automatically assigns a data type for the field, based on the value that you enter. If no other data type is implied by your input, Access sets the data type to Text but you can change the data type.
To enter data in the Add New Field column:
Top of Page Add a field by using a field templateSometimes it is easier to choose from a predefined list of fields that fit your needs than to manually create a field. You can use the More Fields list to choose from a list of field templates. A field template is a predefined set of characteristics and properties that describes a field. The field template definition includes a field name, a data type, a setting for the field's Format property, and other field properties.
Top of Page Setting field propertiesAfter you create a field, you can set field properties to control its appearance and behavior. For example, by setting field properties, you can:
You can set some of the available field properties while you work in Datasheet view. To have access to and set the complete list of field properties; however, you must use Design view. The properties that you can set, depend on the field’s data type. Top of Page Set field properties in Datasheet viewYou can rename a field, change its data type, change its Format property, and change some of a field's other properties while you work in Datasheet view.
Top of Page Rename a fieldWhen you add a field by entering data in Datasheet view, Access automatically assigns a generic name to the field. Access assigns the name Field1 to the first new field, Field2 to the second new field, and so on. By default, a field's name is used as its label wherever the field is displayed, such as a column heading on a datasheet. Renaming fields so that they have more descriptive names helps make them easier to use when you view or edit records.
Top of Page Change a field's data typeWhen you create a field by entering data in Datasheet view, Access examines that data to determine the appropriate data type for the field. For example, if you enter 1/1/2017, Access recognizes that data as a date and sets the data type for the field to Date/Time. If Access can't definitively determine the data type, the data type is set to Text by default (Short Text if you're using Access 2016). The data type of the field determines which other field properties you can set. For example, you can set only the Append Only property for a field that has the Hyperlink data type or the Memo data type (Long Text if you're using Access 2016). There may be cases where you want to manually change a field's data type. For example, suppose you have room numbers that resemble dates, such as 10/2017. If you enter 10/2017 into a new field in Datasheet view, the automatic data type detection feature selects the Date/Time data type for the field. Because room numbers are labels, and not dates, they should use the Text data type. Use the following procedure to change a field's data type.
Top of Page Change a field's formatIn addition to determining the data type of a new field, Access may also set the Format property for the field, depending on what you enter. For example, if you enter 10:50 a.m., Access sets the data type to Date/Time and the Format property to Medium Time. To manually change a field's Format property, do the following:
Top of Page Set other field properties
Top of Page Set field properties in Design viewYou can set any field property while you work with a table in Design view. In Design view, you set a field's data type in the table design grid, and you set other properties in the Field Properties pane.
Top of Page Change a field's data typeYou can change various aspects of a field after you create it. Warning: You can also change these aspects of a field that you did not just create. However, if data already exists in the field some actions will not be available, or may cause data loss.
Top of Page Set other field propertiesNote: Not all formats are available for all data types. Set the data type first, and then, if needed, set the format.
Top of Page Move a fieldTo move a field, drag it to the position that you want. To select multiple contiguous fields to move, click the first field, hold down the SHIFT key, and then click the last field. You can then drag the selected group of fields to a new position. Dragging a field changes its position on the Datasheet, but does not change the order of the fields in the table design. If you programmatically access the fields, the original order applies. For example, if you drag a field to a new position on the Datasheet, and then create a form from the table by using the Form button, the field will be in its original position. Top of Page See AlsoAdd a calculated field to a table Add a field to a form or report How do you set the default value property for the classification field to FR?From the current view (Design view), set the Default Value property for the Classification field to Fr. In the Access Table, click the Default Value property box, type Fr, press Enter.
Does changing the column width in a Datasheet change the structure of a table?Changing the column width in a datasheet changes the structure of a table. Landscape orientation means the printout is across the length (height) of the page. To print a wide database table, a table whose contents do not fit on the screen, you will need portrait orientation.
What field most likely uses the AutoNumber data type?The AutoNumber data type automatically generates a unique number for each record that's added to a table, and is commonly used for primary key fields.
|