Search results
Jul 24, 2018 · I have a table called Student that has a column called ClassDescription. I have a query that pulls the right data here: SELECT Description AS ClassDescription FROM Class AS CL INNER JOIN Student ON CL.StudentId = Student.Id
- Overview
- Creating a table
- Create a new table in a new database
- Create a new table in an existing database
- Importing or linking to create a table
- Set a table's properties
- Save a table
- Setting a primary key
- Determine which fields to use as a primary key
- Set or change the primary key
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.
A 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.
1.Click File > New, and then select Blank desktop database.
2.In the File Name box, type a file name for the new database.
3.To browse to a different location and 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.
Top of Page
1.Click File > Open, and click the database if it is listed under Recent. If not, select one of the browse options to locate the database.
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.
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 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.
Top of Page
In addition to setting properties fields, you can also 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 Table 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.
After 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:` / \ : ; * ? " ' < > | # { } % ~ &.
1.Select File > Save, or press CTRL+S.
2.If you are saving the table for the first time, type a name for the table, and then click OK.
Unless 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
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.
Top of Page
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 Table Design tab, in the Tools group, click Primary Key.
The information in this article explains how to create and run a make table query in Access. You use a make table query when you need to copy the data in a table, archive data, or perhaps save query results as a table.
Jul 1, 2024 · To define a Primary Key, make sure you're in the table Design View and select the field you want to use as the primary key. On the Table Tools Design tab on the ribbon, select Tools > Primary Key.
An Access table contains all the data in a database. Learn more about creating and modifying tables in Microsoft Access.
Access: Working with Tables. Watch on. Table basics. To open an existing table: Open your database, and locate the Navigation pane. In the Navigation pane, locate the table you want to open. Double-click the desired table. The table will open and appear as a tab in the Document Tabs bar. Understanding tables.
People also ask
What is a table in access?
How do I create a table in access?
What are rows and columns in access?
How do I open a table in access?
Jun 14, 2022 · Use the CREATE TABLE statement to define a new table and its fields and field constraints. If NOT NULL is specified for a field, new records are required to have valid data in that field. A CONSTRAINT clause establishes various restrictions on a field, and can be used to establish the primary key.
