• Home   /  
  • Archive by category "1"

Foreign Key Access 2007 Assignments

Working with the data in Microsoft Access tables is very important and is often difficult for new users to understand how to properly design tables to store data in the most efficient manner possible. This article has been submitted to discuss the use and creation of Primary Key and Foreign Key fields in Microsoft Access tables to help properly break down data into its core components and distribute those components efficiently between multiple tables.

Primary Keys and Foreign Keys in Microsoft Access

The use of Primary Keys and Foreign Keys in the table design of a Relational Database is very important. First, let’s discuss each of these concepts and what these terms really mean:

  • The term Primary Key (PK) in a Relational Database System refers to a field (or combination of fields) whose values uniquely identify each record in that table. In Access, it is common to define a Primary Key field in each table, usually as a field that is of AutoNumber data type, to serve as the PK. This AutoNumber field will automatically create a unique number for each record as it is created, so that those records can be identified from the other records in the table.
  • The term Foreign Key (FK) in a Relational Database System refers to a field that in a table that stores values from a Primary Key field in another table, to relate the two records to each other.

For example, assume that you have some contacts for which you want to store some information, and that information happens to include the phone numbers for each contact. You realize that some contacts have more phone numbers than others and you want to store the data in your database application as efficiently as possible. So, you decide to create an Access database application that has a Contacts table and a PhoneNumbers table to store this data. In your database application, you need a way to identify any number of phone number records that relate to each contact record. If the PK in the Contacts table is an AutoNumber type field, then the FK in the PhoneNumbers table should store the ContactID (AutoNumber) from the Contacts table and will need to be the type of Long Integer (Number). This means that a record in the PhoneNumbers table has the potential to contain the ContactID value of the contact record in the related Contacts table.

Note:
While you might be tempted to just create a couple of fields in the Contacts table to store the phone number information, that would not be the best (or Normalized) method for storing the phone data. Also consider that using the two table design, any contact record can have any number of phone number records associated with it. Whereas, if you kept everything in the Contacts table, you would have to create a separate field for each phone number that you want to store. However, each contact record may have no phone, one phone, or even several phone numbers associated with it, so many of the records in the Contacts table would have empty (wasted) space for the record. Using the two table method and relating the records between the tables, we can properly store any number of phone number records for each contact record, without wasting any space in either table.

Step-by-Step

So, to show how to create a working example of the database application example above, assume you have the following information:

Figure 1: Contact and Phone Number Data

Create three tables in your Microsoft Access database with the following design. Note that these tables could contain other fields too, but for this example, only contain the fields required to illustrate the points about using PKs and FKs for related data between tables:

tblContacts table
Field NameData Type, SizeField DescriptionDefault Value
ContactIDAutoNumberThe Contact ID (PK)
NameFirstText, 50The Contact’s First Name
NameLastText, 50The Contact’s Last Name
DateAddedDate/TimeThe date/time the record was added=Now()
tblPhoneTypes table
Field NameData Type, SizeField DescriptionDefault Value
PhoneTypeIDAutoNumberThe Phone Type ID (PK)
PhoneTypeText, 20The Phone Type name. The records of this table should contain the at least the following values: Primary, Home, Work, Cell, Fax, Other.
DateAddedDate/TimeThe date/time the record was added=Now()
tblPhoneNumbers table
Field NameData Type, SizeField DescriptionDefault Value
PhoneIDAutoNumberThe Phone ID (PK)
ContactIDNumber, Long IntegerThe Contact Person/Company (FK lookup to tblContacts)
PhoneTypeIDNumber, Long IntegerThe Phone Type – ie: Primary, Home, Work, Cell, Fax, etc. (FK lookup to tblPhoneTypes)
PhoneNumberText, 20The Phone Number
PhoneExtText, 10The Phone Extension
DateAddedDate/TimeThe date/time the record was added=Now()

Note:
Notice that each table has a date tracking field called DateAdded that is automatically assigned the current date and time (by the use of the Now() function in the Default Value property) when the record is created. It is generally recommended for good housekeeping to know when a record in a table has been created, and usually, the user of the application never directly interacts with the data stored in this field.

Now that you have created the tables, here are examples of actual records that your tables could contain. Note that each table has an AutoNumber field that is designated as the PK. When entering data for the record, an AutoNumber field automatically gets populated with the next value when a new record is created. Access ensures this value for each record is unique.

Figure 2 – Sample Data for the Contacts Table

Figure 3 – Sample Data for the PhoneTypes Table

Figure 4 – Sample Data for the PhoneNumbers Table

In this case, the PhoneNumbers table has two FKs: ContactID and PhoneTypeID:

  1. The ContactID field in the PhoneNumbers table specifies which contact the phone number record relates to. For example, the value of 1 for the ContactID field specifies the record corresponding to “Crystal Long” in the Contacts table.
  2. The PhoneTypeID field in the PhoneNumbers table specifies which phone type the phone number record is designated. For example, the value of 3 for the PhoneTypeID field specifies the record corresponding to the “Work” record in the PhoneTypes table.

So, in this case, the Relationships diagram for these tables looks like the image shown in Figure 5 below. The PhoneNumbers table has one PK and two FKs, each relating to a PK for both the Contacts and PhoneTypes tables.

Figure 5 – The Relationships Diagram

So, as a result of this design, in this database, the tables have the following relationships:

  1. One record in the Contacts table may have many related records in the PhoneNumbers table. This means that there is a one-to-many relationship between Contacts and PhoneNumbers tables.
  2. One record in the PhoneTypes table may be used in many records in the PhoneNumbers table. This means that there is a one-to-many relationship between PhoneTypes and PhoneNumbers tables.

Submitted By: Crystal – February 1, 2011


Random Posts:

  1. Creating Screen Clippings (screenshots) using OneNote 2010
  2. Connecting Microsoft Outlook 2007 to GoDaddy email with SSL
  3. How to Enable the Developer Ribbon in Microsoft Excel 2010
  4. Showing Hidden and System Objects in the Navigation Pane
  5. Can’t Delete File in Windows Vista or Windows 7 – Problem Solved!
  6. Saving a Report as PDF with the Access 2010 GUI
  7. Using Text Box controls to create borders in Microsoft Publisher 2010
  8. Using the Graphics Manager to work with Images in Publisher 2007
  9. The new msOfficeUser.com site is up and running!
  10. Creating a Spreadsheet from Template in Microsoft Excel 2007
:Access, Access 2007, Access 2010, Application, Contacts, Crystal, Database, Field, Foreign Key, Microsoft, Office, Primary Key, Relationships, Table

When you use a database, you store your data in tables — subject-based lists that contain data arranged in records. For instance, you might create a Contacts table to store a list of names, e-mail addresses, and telephone numbers.

This article provides an overview of tables in Microsoft Access 2010, and introduces new or removed table features. This article also explains how to create a table, how to add fields to a table, how to set a table's primary key, and how to set field and table properties.

You should start designing a database by planning all of its tables and deciding how they are related to each other. Before you create tables, carefully consider your requirements and determine all the tables that you need. For an introduction to planning and designing a database, see the article Database design basics, and the links in the See Also section.

Note: Design view is not available for web tables. Instead, you can use design features that are now included in Datasheet view. For more information about designing a web database, see the article Build a database to share on the Web.

In this article

Overview

Table specifications

What's New

Create a new table

Set a table's primary key

Set a table's properties

Add a field to a table

Create a calculated field

Set a field's properties

Overview

A table is a database object that you use to store data about a particular subject, such as employees or products. A table consists of records and fields.

Each record contains data about one instance of the table subject, such as a particular employee. A record is also commonly called a row or an instance.

Each field contains data about one aspect of the table subject, such as first name or e-mail address. A field is also commonly called a column or an attribute.

A record consists of field values, such as Contoso, Ltd. or someone@example.com. A field value is also commonly called a fact.

1. A record

2. A field

3. A field value

A database can contain many tables, each storing information about a different subject. Each table can contain many fields of different types of data, such as text, numbers, dates, and hyperlinks.

External data

You can link to a variety of external data sources, such as other databases, text files, and Excel workbooks. When you link to external data, Access can use the link as if it were a table. Depending on the external data source and the way that you create the link, you can edit the data in the linked table, and can create relationships that involve the linked table. However, you cannot change the design of the external data by using the link.

Table and field properties

Tables and fields have properties that you can set to control their characteristics or behavior.

A table open in Design view.

1. Table properties

2. Field properties

In an Access database, table properties are attributes of a table that affect the appearance or behavior of the table as a whole. Table properties are set in the table's property sheet, in Design view. For example, you can set a table's Default View property to specify how the table is displayed by default.

A field property applies to a particular field in a table and defines one of the field's characteristics or an aspect of the field's behavior. You can set some field properties in Datasheet view. You can also set any field property in Design view by using the Field Properties pane.

Data types

Every field has a data type. A field's data type indicates the kind of data that the field stores, such as large amounts of text or attached files.

A data type is a field property, but it differs from other field properties as follows:

  • You set a field's data type in the table design grid, not in the Field Properties pane.

  • A field's data type determines what other properties the field has.

  • You must set a field's data type when you create the field.

    Note: You can create a new field in Access by entering data in a new column in Datasheet view. When you create a field by entering data in Datasheet view, 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. If needed, you can change the data type by using the Ribbon, part of the new Microsoft Office Fluent user interface.

Examples of automatic data type detection

The following table shows how automatic data type detection works in Datasheet view.

If you enter:

Office Access 2007 creates a field with a data type of:

John

Text

http://www.contoso.com

You can use any valid Internet protocol prefix. For example, http://, https://, and mailto: are valid prefixes.

Hyperlink

1

Number, Long Integer

50,000

Number, Long Integer

50,000.99

Number, Double

50000.389

Number, Double

12/67

The date and time formats recognized are those of your user locale.

Date/Time

December 31, 2006

Date/Time

10:50:23

Date/Time

10:50 am

Date/Time

17:50

Date/Time

$12.50

The currency symbol recognized is that of your user locale.

Currency

21.75

Number, Double

123.00%

Number, Double

3.46E+03

Number, Double

Table relationships

Although each table stores data about a different subject, tables in a database usually store data about subjects that are related to each other. For example, a database might contain:

  • A customers table that lists your company’s customers and their addresses.

  • A products table that lists the products that you sell, including prices and pictures for each item.

  • An orders table that tracks customer orders.

Because you store data about different subjects in separate tables, you need some way to tie the data together so that you can easily combine related data from those separate tables. To connect the data stored in different tables, you create relationships. A relationship is a logical connection between two tables that specifies fields that the tables have in common.

Keys

Fields that are part of a table relationship are called keys. A key usually consists of one field, but may consist of more than one field. There are two kinds of keys:

  • Primary key    A table can have only one primary key. A primary key consists of one or more fields that uniquely identify each record that you store in the table. Often, there is a unique identification number, such as an ID number, a serial number, or a code, that serves as a primary key. For example, you might have a Customers table where each customer has a unique customer ID number. The customer ID field is the primary key of the Customers table. When a primary key contains more than one field, it is usually composed of pre-existing fields that, taken together, provide unique values. For example, you might use a combination of last name, first name, and birth date as the primary key for a table about people.

  • Foreign key    A table can also have one or more foreign keys. A foreign key contains values that correspond to values in the primary key of another table. For example, you might have an Orders table in which each order has a customer ID number that corresponds to a record in a Customers table. The customer ID field is a foreign key of the Orders table.

The correspondence of values between key fields forms the basis of a table relationship. You use a table relationship to combine data from related tables. For example, suppose that you have a Customers table and an Orders table. In your Customers table, each record is identified by the primary key field, ID.

To associate each order with a customer, you add a foreign key field to the Orders table that corresponds to the ID field of the Customers table, and then create a relationship between the two keys. When you add a record to the Orders table, you use a value for customer ID that comes from the Customers table. Whenever you want to view any information about an order's customer, you use the relationship to identify which data from the Customers table corresponds to which records in the Orders table.

A table relationship, shown in the Relationships window.

1. A primary key, identified by the key icon next to the field name.

2. A foreign key — note the absence of the key icon.

Benefits of using relationships

Keeping data separated in related tables produces the following benefits:

  • Consistency    Because each item of data is recorded only once, in one table, there is less opportunity for ambiguity or inconsistency. For example, you store a customer's name only once, in a table about customers, rather than storing it repeatedly (and potentially inconsistently) in a table that contains order data.

  • Efficiency    Recording data in only one place means you use less disk space. Moreover, smaller tables tend to provide data more quickly than larger tables. Finally, if you don't use separate tables for separate subjects, you will introduce null values (the absence of data) and redundancy into your tables, both of which can waste space and impede performance.

  • Comprehensibility    The design of a database is easier to understand if the subjects are properly separated into tables.

Tip: Plan your tables with relationships in mind. You can use the Lookup Wizard to create a foreign key field if the table that contains the corresponding primary key already exists. The Lookup Wizard creates the relationship for you.

Top of Page

Table specifications

In Access 2010, a table has the following practical limits:

Attribute

Maximum

Number of characters in a table name

64

Number of characters in a field name

64

Number of fields in a table

255

Number of open tables

2048; this limit includes tables opened internally by Access

Table size

2 gigabytes, minus the space needed for the system objects

Number of characters in a Text field

255

Number of characters in a Memo field

65,535 when entering data through the user interface;
2 gigabytes of character storage when entering data programmatically

Size of an OLE Object field

1 gigabyte

Number of indexes in a table

32

Number of fields in an index

10

Number of characters in a validation message

255

Number of characters in a validation rule

2,048

Number of characters in a table or field description

255

Number of characters in a record (excluding Memo and OLE Object fields) when the UnicodeCompression property of the fields is set to Yes

4,000

Number of characters in a field property setting

255

Top of Page

What's New

Differences from Access 2007

Creating a table in Access 2010 is a very similar process to creating a table in Access 2007. However, in Access 2010 there are a few new features that pertain to tables:

  • Data macros    You can now attach macros to events in tables, which enables you to perform actions whenever a record is modified, inserted, or deleted. These macros can be used to validate data or perform a calculation, for example.

  • Calculated data type    This new data type lets you create a field that is based on a calculation of other fields in the same table. For example, you might create a Line Total field that contains the product of a Quantity field and a Unit Price field. Then, if you update the Quantity or Unit Price field, the Line Total is updated automatically.

  • Web service connectivity    In addition to linking to external data sources such as Excel workbooks and SharePoint lists, you can now connect to data at Web sites that provide a Web service interface. For example, you might link to data at an online merchant's Web site and then create your own application for viewing products or initiating orders.

  • Table and field templates are replaced by models    Access 2007 introduced table templates and field templates. Table templates were empty tables that you could use to create new tables, built to be compatible with these built-in Windows SharePoint Services lists:

    • Contacts

    • Tasks

    • Issues

    • Events

    • Assets

      In Access 2010, you use models when you want to add pre-built parts to your database. Models can include tables, and can also include other kinds of database objects, such as queries and forms.

      Note: This topic does not explain models. For more information, see the See Also section.

Differences from Access 2003

  • Models have replaced the Table Wizard    In versions of Access earlier than 2007, you used the Table Wizard to create a table quickly by answering a few questions. In Access 2007, the Table Wizard was replaced by table and field templates. In Access 2010, models replace table and field templates.

    Note: This topic does not explain models. For more information, see the See Also section.

  • Datasheet creation     You can now create and modify tables and fields while working in Datasheet view.

Top of Page

Create a new table

Create a new table if you have a new source of data that doesn't belong in any of your existing 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 Office Excel workbook, a Microsoft Office Word document, a text file, a Web service, 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.

What do you want to do?

Create a new table in a new database

Create a new table in an existing database

Create a new table in a new database

  1. On the File tab, click New, and then click Blank Database.

  2. In the File Name box, type a file name for the new database.

  3. To browse to a different location to save the database, click the folder icon.

  4. Click Create.

    The new database opens, and a new table named Table1 is created and opens in Datasheet view.

Create a new table in an existing database

  1. Click the File tab, point to Open, and then do one of the following:

    • If the database that you want is listed under Recent Databases, click the database in that list.

      - or -

    • If the database is not listed under Recent Databases, under Open Document, click the option that you want.

  2. In the Open dialog box, select the database that you want to open, and then click Open.

  3. On the Create tab, in the Tables group, click Table.

    A new table is inserted in the database and the table opens in Datasheet view.

Top of Page

Import or link to create a table

You 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 Windows SharePoint Services list, an XML file, another Access database, a Microsoft Office 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. For more information about linking to Access from Excel, search Excel Help, or see the links in the See Also section.

Create a new table by importing or linking to external data

  1. On the External Data tab, in the Import & Link group, click one of the available data sources.

  2. Follow the instructions in the dialog boxes that appear at each step.

    Access creates the new table and displays it in the Navigation Pane.

Tip: You can also import or link to a SharePoint list by using a command on the Create tab.

Top of Page

Use a SharePoint site to create a table

You 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 2010 include Contacts, Tasks, Issues, and Events.

  1. On the Create tab, in the Tables group, click SharePoint Lists.

  2. Do one of the following:

    • Create a SharePoint list that is based on a template

      1. Click either Contacts, Tasks, Issues, or Events.

      2. In the Create New List dialog box, type the URL for the SharePoint site where you want to create the list.

      3. Enter a name for the new list and its description in the Specify a name for the new list and Description boxes.

      4. To open the linked table after it is created, select the Open the list when finished check box (selected by default).

    • Create a new custom list

      1. Click Custom.

      2. In the Create New List dialog box, type the URL for the SharePoint site where you want to create the list.

      3. Enter a name for the new list and its description in the Specify a name for the new list and Description boxes.

      4. To open the linked table after it is created, select the Open the list when finished check box (selected by default).

    • Import the data from an existing list

      1. Click Existing SharePoint List.

      2. In the Get External Data dialog box, type the URL for the SharePoint site that contains the data that you want to import.

      3. Click Import the source data into a new table in the current database, and then click Next.

      4. Select the check box next to each SharePoint list that you want to import.

    • Link to an existing list

      1. Click Existing SharePoint List.

      2. In the Get External Data - SharePoint Site dialog box, type the URL for the SharePoint site that contains the list to which you want to link.

      3. Click Link to the data source by creating a linked table, and then click Next.

      4. Select the check box next to each SharePoint list to which you want to link.

Top of Page

Use a Web service to create a table

You can create a table in your database that connects to data at a Web site that provides a Web service interface.

Note: Web service tables are read-only.

  1. On the External Data tab, in the Import & Link group, click More and then click Data Services.

  2. If the connection you want to use has already been installed, skip to step 5. Otherwise, continue with the next step.

  3. Click Install new connection.

  4. Select the connection file that you want to use, and then click OK.

  5. In the Create Link to Web Service Data dialog box, expand the connection that you want to use.

  6. Select the table that you want to link to.

    Access displays the fields on the right side of the dialog box.

  7. Optionally, type a name for the linked table in the Specify link name box. Access will use this name for the linked table in the Navigation Pane.

  8. Click OK.

    Access creates the linked table.

Top of Page

Set a table's primary key

You should specify a primary key for a table to gain the following benefits:

  • Access automatically creates an index for the primary key, which can help improve database performance.

  • Access makes sure that every record has a value in the primary key field.

  • Access makes sure that each value in the key field is unique. Unique values are crucial, because otherwise there is no way to reliably distinguish a particular record from other records.

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.

Determine which fields to use as a primary key

Sometimes, 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:

  • Each record has a unique value for the field or combination of fields.

  • The field or combination of fields is never empty or null — there is always a value.

  • The values do not change.

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.

Set or change the primary key

  1. Select the table whose primary key you want to set or change.

  2. On the Home tab, in the Views group, click View, and then click Design View.

  3. In the table design grid, select the field or fields that you want to use as the primary key.

    To select one field, click the row selector for the field that you want.

    To select more than one field, hold down CTRL, and then click the row selector for each field.

  4. On the Design tab, in the Tools group, click Primary Key.

    A key indicator appears to the left of the field or fields that you specify as the primary key.

Remove the primary key

  1. Select the table whose primary key you want to remove.

  2. On the Home tab, in the Views group, click View, and then click Design View.

  3. Click the row selector for the current primary key. If the primary key consists of multiple fields, hold down CTRL, and then click the row selector for each field.

  4. On the Design tab, in the Tools group, click Primary Key.

    The key indicator is removed from the field or fields that you previously specified as the primary key.

Note: 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

Set a table's properties

You can set properties that apply to an entire table or to entire records.

  1. Select the table whose properties you want to set.

  2. On the Home tab, in the Views group, click View, and then click Design View.

  3. On the Design tab, in the Show/Hide group, click Property Sheet.

    The table property sheet is shown.

  4. On the property sheet, click the General tab.

  5. Click the box to the left of the property that you want to set, and then enter a setting for the property.

    Available table properties

    Use this table property

    To

    Display Views On SharePoint Site

    Specify whether views that are based on the table can be displayed on a SharePoint site.

    Note: The effects of this setting depend on the setting of the Display All Views On SharePoint Site database property.

    For more information, see the See Also section.

    Subdatasheet Expanded

    Expand all subdatasheets when you open the table.

    Subdatasheet Height

    Do one of the following:

    • If you want the subdatasheet window to expand to display all rows, leave this property set at 0".

    • If you want to control the height of the subdatasheet, enter the desired height in inches.

    Orientation

    Set the view orientation, according to whether your language is read left-to-right, or right-to-left.

    Description

    Provide a description of the table. This description will appear in tooltips for the table.

    Default View

    Set Datasheet, PivotTable, or PivotChart as the default view when you open the table.

    Validation Rule

    Enter an expression that must be true whenever you add or change a record.

    Validation Text

    Enter a message that is displayed when a record violates the expression in the Validation Rule property.

    Filter

    Define criteria to display only matching rows in Datasheet view.

    Order By

    Select one or more fields to specify the default sort order of rows in Datasheet view.

    Subdatasheet Name

    Specify whether a subdatasheet should appear in Datasheet view, and if so, which table or query should supply the rows in the subdatasheet.

    Link Child Fields

    List the fields in the table or query that are used for the subdatasheet that match the Link Master Fields property that is specified for the table.

    Link Master Fields

    List the fields in the table that match the Link Child Fields property that is specified for the table.

    Filter On Load

    Automatically apply the filter criteria in the Filter property (by setting to Yes) when the table is opened in Datasheet view.

    Order By On Load

    Automatically apply the sort criteria in the Order By property (by setting to Yes) when the table is opened in Datasheet view.

    Tip: To provide more space to enter or edit a setting in the property box, press SHIFT+F2 to display the Zoom box. If you are setting the Validation Rule property to an expression and would like help in building it, click next to the ValidationRule property box to display the Expression Builder.

  6. To save your changes, press CTRL+S.

Top of Page

Add a field to a table

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.

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. For more information about designing a database and creating fields, see the links in the See Also section.

After you create a field, you can also set field properties to control its appearance and behavior.

Add a field by entering data

When 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. Enter data in the Add New Field column.

  1. Create or open a table in Datasheet view.

    For more information about creating a table, see the section, Create a new table.

  2. In the Add New Field column, enter the name of the field that you want to create.

    Use a descriptive name so that the field will be easier to identify.

  3. Enter data in the new field.

Top of Page

Create a calculated field

You create a calculated field in Design view.

  1. Open the table in which you want to create a calculated field, in Design view.

  2. In the first empty row if the table design grid, under Field Name, type a name for the calculated field.

  3. Under Data Type, choose Calculated.

    The Expression Builder opens.

  4. Enter the calculation that you want to perform in the Expression Builder. For example, if your table has Field1 and Field2, and you want to create a calculated field (Field3) equal to the value of Field1 divided by the value of Field2, you would enter [Field1]/[Field2] in the Expression Builder.

    For more information about expressions, see the article Introduction to expressions.

  5. When you are finished entering the calculation, click OK.

    Tip: It is a good idea to switch to Datasheet view to verify that the calculated field works the way that you want.

Top of Page

Set a field's properties

After you create a field, you can set field properties to control its appearance and behavior.

For example, by setting field properties, you can:

  • Control the appearance of data in a field

  • Help prevent incorrect data entry in a field

  • Specify default values for a field

  • Help speed up searching and sorting on a field

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.

Set field properties in Datasheet view

You 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.

Open a table in Datasheet view

  1. In the Navigation Pane, right-click the table that you want to open.

  2. On the shortcut menu, click Datasheet view.

Rename a field

When 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.

  1. Right-click the heading of the field that you want to rename (for example, Field1).

  2. On the shortcut menu, click Rename Column.

  3. Enter the new name in the field heading.

    Field names can consist of up to 64 characters (letters or numbers), including spaces.

Change a field's data type

When 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/2006, 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.

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.

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/2001. If you enter 10/2001 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.

  1. Click the Datasheet tab.

  2. In the Data Type list, in the Data Type & Formatting group, select the data type that you want.

    Available data types

    Data type

    Use to store

    Size

    Text

One thought on “Foreign Key Access 2007 Assignments

Leave a comment

L'indirizzo email non verrà pubblicato. I campi obbligatori sono contrassegnati *