Before you start populating a table with information, you should design it. Creating tables in Access can happen in several ways: using a wizard, by entering data, or using the designer. Each method is launched by a separate shortcut. They appear immediately after creating a new file for the database.
If the icons are not visible, open the “Options” window through the “Tools” menu. In it, select the “View” command and check the box next to “New object labels”.
This method is suitable for those users who find it difficult to immediately determine the structure of the future object:
A simple database, such as a contact list, may only have one table. However, many databases use multiple tables. When you create a database, a file is created on your computer that is used as a container for all of its objects, including tables.
There are several ways to create a table: you can create a new database, insert a table into an existing database, or import a table from another data source, such as a Microsoft Office Excel workbook, a Microsoft Office Word document, a text file, or another database, or link a table to it source. When you create a new database, a new empty table is automatically inserted into it. You can then enter data into it to begin defining fields.
Click File > Create and select Empty desktop database.
In field File enter the file name for the new database.
Click the button Create.
A new database will open in which a new table named "Table1" will be created and opened in table view.
Click File > Open and select the database if it is listed in the section Latest. Otherwise, select one of the database search options.
In the dialog box Opening a Database File find the database you want to open and click the button Open.
on the tab Creation in Group Tables click the button Table.
A new table will be inserted into the database and will open in Datasheet view.
To create a table, you can import data from another file (such as an Excel worksheet, SharePoint list, XML file, another Access database, Microsoft Outlook folder, etc.) or link the table to it.
When you import data, a copy of it is created in a new table in the current database. Subsequent changes made to the original data will not affect the imported data, and vice versa. After you connect to the source and import the data, you can use the imported data without connecting to the source. You can make changes to the structure of the imported table.
When data binding occurs, a linked table is created in the current database that provides a dynamic connection to data stored elsewhere. Changes to data in a linked table are reflected in the source, and changes in the source are reflected in the linked table. To work with a linked table, you must be connected to a data source. You cannot change the structure of a linked table.
Note: You cannot change data in an Excel worksheet using a linked table. As a workaround, you can import the source data into an Access database and then create a link to the database from Excel.
To create a database table, you can either import a SharePoint list or create a link to it. Alternatively, you can create a new SharePoint list based on a pre-made template. Templates available in Access include Contacts, Tasks, Questions, and Events.
Open menu File and select a team Open.
In the dialog box Opening a Database File select the database where you want to create the table and click the button Open.
On the tab Creation in Group Tables click the button SharePoint Lists.
Do one of the following:
Create a SharePoint list from a template
Select an item Contacts, Tasks, Questions or Events.
In the dialog box Creating a new list
Specify a name for the new list And Description.
Create a custom list
Select an item Another.
In the dialog box Creating a new list Enter the URL of the SharePoint site where you want to create the list.
Enter a name and description for the new list in the fields Specify a name for the new list And Description.
To open the linked table after it has been created, select the checkbox Open list after export is complete(it is installed by default).
Importing data from an existing list
Select item.
In the dialog box External data Enter the URL of the SharePoint site that contains the data you want to import.
Select an item Import source data into a new table in the current database and press the button Further.
Select the check boxes of all SharePoint lists that you want to import.
Linking to a list
Select an item Existing SharePoint list.
In the dialog box External Data - SharePoint Site Enter the URL of the SharePoint site that contains the list you want to link to.
Select an item Create a linked table to communicate with a data source and press the button Further.
Select the check boxes of all SharePoint lists that you want to create links to.
You can create a table in a database that is connected to data on a website with a web service interface. Web service tables are read-only.
On the tab External data in Group Import and connections click the button Additionally and select a team Data Services.
If the desired connection is already established, proceed to step 5. Otherwise, continue to the next step.
Click the button Establish a new connection.
Select the required connection file and click the button OK.
In the dialog box Create a Web Service Data Link Expand the desired connection.
Select the table you want to link to. Its fields will appear on the right side of the dialog box.
You can enter the name of the related table in the field Provide a link name. Access will display this linked table name in the Navigation Pane.
Click the button OK. A related table will be created.
In addition to setting field properties, you can set properties that apply to the entire table or to all records.
After you create or modify a table, you should preserve its structure. The first time you save a table, you must give it a name that describes the data it contains. You can use up to 64 characters (letters or numbers), including spaces. For example, you might name the table "Customers", "Parts List", or "Products".
Access gives you flexibility when naming tables, but there are some limitations. The name can be up to 64 characters long and include any combination of letters, numbers, spaces, and special characters, excluding periods (.), exclamation points (!), square brackets (), leading space, leading equal sign (=), or non-printing characters. such as carriage return. In addition, the name must not contain the following characters: ` / \ : ; * ? " "< > | #
Advice: Agree on how you will name objects in the database, and follow these rules.
If you are saving the table for the first time, enter its name and click OK.
You should set a primary key for a table unless you have a good reason not to. Access automatically creates an index on the primary key, which improves database performance. Additionally, Access ensures that every record has a value in the primary key field, and that value is always unique. This is extremely important because otherwise you cannot reliably distinguish one string from another.
When you create a table in Datasheet view, Access automatically creates a primary key named ID and assigns it a data type of Counter.
You can change or delete a primary key, or set a primary key for a table that does not already have one, in the Designer.
Sometimes data that can be used as a primary key already exists. For example, employees may already have identification numbers. If you are creating a table to track employee data, you can use their IDs as the primary key. Sometimes employee IDs are only unique in combination with department codes; in this case, you must use a combination of these fields as the primary key. The primary key must have the following characteristics:
The value of a given field or combination of fields must be unique for each record.
The field or combination of fields must not be empty (they must always have a value).
The values should not change.
If there is no data suitable for the role of a primary key, you can create a new field for this purpose. When creating a field to use as a primary key, select the field type as Counter to ensure it meets the three characteristics listed above.
If you try to save a new table without a primary key, Access will prompt you to create a field for it. If you press the button Yes, Access will create an ID field with a Counter data type that stores a unique value for each record. If the table already has a field of type Counter, it will be used as the primary key. If you press the button No, Access will not add the new field and will not set the primary key.
To save a new piece of data for an existing Access table, it is recommended to add a field to it. For example, suppose you have a table with fields that indicate the last name, first name, email address, phone number, and mailing address of each customer. If you want to start tracking the preferred method of communication for each customer, a field is added to store this data.
All data elements that need to be tracked are stored in separate fields. For example, in the contacts table you can create the fields “First Name”, “Last Name”, “Telephone” and “Address”, and in the product table you can create the fields “Product Name”, “Product Code” and “Price”.
Each field has some basic characteristics, such as a name that uniquely identifies it within the table, a data type that indicates the nature of the data, operations that can be performed on the data, and the amount of space allocated for each value.
Before you create fields, break down your data into the smallest useful elements. Then it will be much easier for you to combine the data than to separate it. For example, instead of the "Full Name" field, it is better to create separate "First Name" and "Last Name" fields. This will make it easier for you to search and sort data by first name, last name, or a combination of both. If you are going to create a report, sort, search, or calculate on a data item, make it a separate field.
You can set properties for a field that determine its appearance and behavior. For example, property Format specifies the display of data in a table or form that includes a field.
To add a field to the table when you create a new table or open an existing table in Datasheet view, enter data in the table column Add a field(1) . Depending on the value entered, the data type for the field is assigned automatically. If the value you enter does not have a specific data type, Access selects the text type, but you can change it if necessary.
Entering data into a column Add a field:
To create or open a table in Datasheet view, in the navigation pane, right-click the table you want, and then Table mode.
Enter the name of the field to be created in the column Add a field.
Use a descriptive name to help identify the field.
Enter information in the new field.
Sometimes it's easier to select a field from a ready-made list than to create it manually. To select a field from a list of templates, use the list Other fields. A field template is a specified set of characteristics and properties that describe it. Field template definition includes field name, data type, property value Format and a number of other properties.
You can set properties for a field that determine its appearance and behavior.
For example, using field properties you can:
change the type of data in a field;
prevent incorrect data from being entered into the field;
set the field to a default value;
speed up searching and sorting by field.
Some field properties can be set in Datasheet view, but you must use the designer to access and set all properties.
The properties that can be configured depend on the data type of the field.
In table mode you can rename a field, change its type, property Format and some other properties.
In the navigation pane, right-click the table you want to open.
In the context menu, select Table mode.
When you add a field by entering data in Datasheet view, Access automatically gives it a generic name. The first new field is named "Field1", the second is named "Field2", etc. By default, the field name is used as its label wherever the field is displayed (for example, in a table column header). If you give fields descriptive names, you can more easily view and edit records.
Right-click the title of the field you want to rename (for example, "Field1").
In the context menu, select Rename field.
Enter a new name in the field header.
When you create a field by entering data in Datasheet view, Access analyzes the data to determine the appropriate data type for the field. For example, if you enter the value 01.01.2017 , Access will recognize it as a date and assign the field a datetime type. If Access cannot unambiguously determine the data type, the field is assigned the text type by default. (Short text if using Access 2016).
The field's data type determines what other properties you can set for it. For example, property Add only Can only be set for a field with a data type of Hyperlink or MEMO Field (or Long Text in Access 2016).
There are situations when you need to change the data type of a field manually. Imagine that you need to enter room numbers that resemble dates (for example, 10/2017). If you enter a value 10.2017 into a new field in Datasheet view, the automatic data type detection feature will select the data type for the field as Date and Time. Because room numbers are labels and not dates, their data type must be set to Text. To change the data type of a field, follow these steps:
Warning: These parameters can also be changed for fields that were created earlier. However, if there is already data in the field, some actions may not be available or may cause data corruption.
Note: Not all formats are available for all data types. First set the data type and then customize the format if necessary.
To move a field, drag it to the desired location. To select multiple adjacent fields to move, click the first field, and while holding down the SHIFT key, click the last field. You can then drag the selected group of fields to a new location.
When you drag a field, its position in the table changes, but the order of the fields in the constructor does not change. When accessing fields programmatically, the original order is used. For example, if you drag a field to a new position in Datasheet view, and then use the Form button to create a form from that table, the field will be positioned in its original position.
Hi all! In this lesson we will walk through the step-by-step creation of tables in an Access database. All you have to do is read and repeat. Go!
Goal: learn to create database tables, set their structure, select field types and manage their properties; master techniques for filling tables with specific content.
The head of a small enterprise that assembles personal computers from ready-made components ordered the development of a database based on two tables of components. One table contains data that can be displayed to customers when agreeing on a product specification - it shows the retail prices of components. The second table is intended for analyzing the results of the enterprise's activities - it contains wholesale prices for components and brief information about suppliers (customers of the enterprise do not have access to the data in this table).
1. Launch the program Microsoft Access(Start - Programs - Microsoft Office - Microsoft Access).
2. In the window Microsoft Access turn on the switch New database.
Creating an Access database - Step-by-step creation of tables in an Access database
3. In the sidebar, select the location where the database will be stored, give the file a name: Accessories. Make sure the File Type is selected Microsoft Access Databases and click on the button Create. As a result, a new database window should appear on the screen. The first table creation mode will be started automatically:
4. Switch from table mode to design mode by selecting it in the menu:
When the program asks for a table name, enter the name: Accessories and click Ok. A designer window will open in which we will create the table structure:
Creating an Access Table - Step by step creating tables in an Access database
5. For the first table, enter the following fields:
Please note that in this case the field Price specified not by the monetary type, but numerical. Data related to the monetary type have a dimension expressed in rubles (if the work is performed with a version of Microsoft Access localized in Russia). But it is not customary to express the cost of computer components in this unit of measurement. To compare prices from different suppliers, “conventional units” are usually used. In such cases it is convenient to use the field numerical type, so as not to reconfigure the entire DBMS.
Table "Components" - Step-by-step creation of tables in an Access database
6. Click on the field Price. At the bottom of the form, set the property Number of decimal places, equal to 2.
7. To connect with the future supplier table, you need to set a key field. Since not a single field here clearly claims to be “unique,” we use a combination of fields Component And Model. Select both fields at the top of the form (while holding down the SHIFT key). Right-click to open the context menu and select Key field.
A “key” will then appear next to these fields.
8. Close the Designer window (the usual closing is through the “cross” in the upper right corner of the Designer window).
Answer the request to save the changed table structure with consent.
9. Repeat steps 4-8 to create a table Suppliers, which includes the following fields:
To do this you need to do the following: menu Create - Table.
Select mode Designer.
Please note that the phone number field is text, despite the fact that phone numbers are usually written in numbers. This is due to the fact that they do not have numerical content. Phone numbers are not compared by size, they are not subtracted from one number to another, etc. This is a typical text field.
The key field can be omitted– it is not required for the current task.
10. In the left side panel of the database window, double-click to open the created tables one by one and fill them with experimental content (10 records each).
11. When finished, close the tables and exit the program.
This lesson showed step-by-step creation of tables in an Access database using a simple example. If you were interested, I recommend watching the video tutorial below.
To be continued…
LESSON 1: Step-by-step creating tables in an Access database
LESSON 2: Creating inter-table relationships in the Access database
LESSON 3: Creating a query to select data in an Access database
LESSON 4: Creating queries with a parameter in the Access database
LESSON 5: Creating a cross-query in an Access database
LESSON 6: Creating change requests in an Access database
LESSON 7: Creating and filling out forms in the Access database
In the modern world, we need tools that would allow us to store, organize and process large amounts of information that are difficult to work with in Excel or Word.
Such repositories are used to develop information websites, online stores and accounting add-ons. The main tools that implement this approach are MS SQL and MySQL.
The product from Microsoft Office is a simplified version in terms of functionality and is more understandable for inexperienced users. Let's take a step-by-step look at creating a database in Access 2007.
Microsoft Access 2007 is a database management system (DBMS) that implements a full-fledged graphical user interface, the principle of creating entities and relationships between them, as well as the structural query language SQL. The only disadvantage of this DBMS is the inability to work on an industrial scale. It is not designed to store huge amounts of data. Therefore, MS Access 2007 is used for small projects and for personal, non-commercial purposes.
But before showing step-by-step how to create a database, you need to familiarize yourself with the basic concepts of database theory. 
Without basic knowledge about the controls and objects used when creating and configuring a database, it is impossible to successfully understand the principle and features of setting up a subject area. Therefore, now I will try to explain in simple language the essence of all the important elements. So, let's begin:
Now that we have a general idea of what we will be working with, we can begin creating the database.
For clarity of the whole theory, we will create a training database “Students-Exams”, which will contain 2 tables: “Students” and “Exams”. The main key will be the “Record Number” field, because this parameter is unique for each student. The remaining fields are intended for more complete information about students.
So do the following:
That's it, now all that remains is to create, fill and link tables. Continue to the next point.
After successfully creating the database, an empty table will appear on the screen. To form its structure and fill it out, do the following:
Advice! To fine-tune the data format, go to the “Table Mode” tab on the ribbon and pay attention to the “Formatting and Data Type” block. There you can customize the format of the displayed data.
Before you start linking two entities, by analogy with the previous paragraph, you need to create and fill out the “Exams” table. It has the following attributes: “Record number”, “Exam1”, “Exam2”, “Exam3”.
To execute queries we need to link our tables. In other words, this is a kind of dependency that is implemented using key fields. To do this you need:
The constructor should automatically create the relationship, depending on the context. If this does not happen, then:
What should we do if we need students who study only in Moscow? Yes, there are only 6 people in our database, but what if there are 6000 of them? Without additional tools it will be difficult to find out.
It is in this situation that SQL queries come to our aid, helping to extract only the necessary information.
SQL syntax implements the CRUD principle (abbreviated from the English create, read, update, delete - “create, read, update, delete”). Those. with queries you can implement all these functions.
In this case, the “read” principle comes into play. For example, we need to find all students who study in Kharkov. To do this you need:
What should we do if we are interested in students from Kharkov who have more than 1000 scholarships? Then our query will look like this:
SELECT * FROM Students WHERE Address = “Kharkov” AND Scholarship > 1000;
and the resulting table will look like this:
In addition to adding a table using the built-in constructor, sometimes you may need to perform this operation using an SQL query. In most cases, this is needed during laboratory or course work as part of a university course, because in real life there is no need for this. Unless, of course, you are engaged in professional application development. So, to create a request you need:
CREATE TABLE Teachers
(Teacher Code INT PRIMARY KEY,
Last name CHAR(20),
Name CHAR(15),
Middle name CHAR (15),
Gender CHAR (1),
Date of birth DATE,
main_subject CHAR(200));
where "CREATE TABLE" means creating the "Teachers" table, and "CHAR", "DATE" and "INT" are the data types for the corresponding values. 
Attention! Each request must have a “;” at the end. Without it, running the script will result in an error.
Everything is much simpler here. Go to the Create a Request field again and enter the following commands:
With a huge number of fields in the table, filling the database becomes difficult. You may accidentally omit a value, enter an incorrect one, or enter a different type. In this situation, forms come to the rescue, with the help of which you can quickly fill out entities, and the likelihood of making a mistake is minimized. This will require the following steps:
We have already covered all the basic functions of MS Access 2007. There is one last important component left – report generation.
A report is a special MS Access function that allows you to format and prepare data from a database for printing. This is mainly used for creating delivery notes, accounting reports and other office documentation.
If you have never encountered such a function, it is recommended to use the built-in “Report Wizard”. To do this, do the following:
Let's consider the sequence of actions when we are faced with the task of creating tables in MS Access.
Let us remind you that all Access database objects are created and saved in one file, which is created by clicking on the icon in the Access start window New database(Blank Database). Moreover, in the section New database(Blank Database) sets the name of the database and selects the folder in which to save the database file. As a result, a new empty database window opens.
Let's start creating the PRODUCT table by defining its structure in table designer mode. On the ribbon tab Creation(Create) in group Tables(Tables) execute the command Table builder(Table Design).
In the designer window Table 1(Table1) (Fig. 3.15) in accordance with those given in table. 3.1, the design parameters of the structure will define all fields of the PRODUCT table.
For each field of the PRODUCT table, we define Field name(Field Name) Data type(Data Type) and on the tab Are common(General) set the properties of the fields:
COMMENT
If some table records do not have a price or VAT value entered, and no numeric data has been provided for the default value, the result will not appear in the calculated field. This is because if there is no value in the field, it gets the value Null, with which calculations cannot be performed.
The condition on the value that is entered into the field property is an expression. It can be generated using the expression builder (Fig. 3.16). The builder is called in the table designer window (see Fig. 3.15) when you click the button located to the right of the row Condition on value(Validation Rule) into which the expression should be entered. The builder can also be called with the command Builder(Builder) on the designer panel in the group Service(Tools).
COMMENT
There are special characters to enter the “greater than equal” and “less than equal” operators. Avoid spaces when entering an expression. The builder will enter the necessary spaces himself.
After entering the expression into the builder window and clicking the button OK Access will parse the expression and display it in the string Condition on value(Validation Rule).
Now let's define the primary key of the table. Select the ITEM_CODE field by clicking on the marking area to the left of the field name and click the button Key field(Primary Key) on the ribbon tab Constructor(Design) in group Service(Tools) (see Fig. 3.14). A sign that a key has been installed is the image of the key to the left of the field name. Let's define the properties of the key field in accordance with table. 3.1.
COMMENT
For inventory numbers and other numbers or codes that often act as keys and are not used in mathematical calculations, it is advisable to select a text data type instead of a numeric one.
Let's save the created table structure and assign a name to the new table - PRODUCT. To do this, run the command Save(Save) in the table context menu, on Quick Access Toolbars(Customize Quick Access Toolbar) or on the tab File(File). In the window Preservation(Save As) enter the table name (Fig. 3.17).
