In this lesson, we'll look at how make a table in access in design mode, we will learn what a field is, how to choose a field type, how to save a table and enter data.
You can create a table in Access in several ways, for example, you can enter data into the table (although almost no one does that !!) or use the table creation wizard.
We discussed the advantages and disadvantages of using the table creation wizard in the previous lesson:. The best and convenient way creating tables is a constructor.
To create tables in design mode, in an open database, select the Tables object and double-click Create table in design mode or on the Design button.
A window will open in front of you in which you will need to create a table structure. In this window, write
- the names of the fields, and the fields, as you remember, are the columns in the table;
- select field type (field type is selected from the drop-down list)
- field description is optional.
In this video tutorial, we'll take a closer look at how make a table in access in design mode, how to choose the field type, how to save the table structure in Access:
That's it, the table structure has been created, now you can enter data into the table. There are two ways to enter data into a table:
- table mode;
- form.
We will look at how to work with the form in the next lesson, but in this one we will simply double-click on the table name and fill it with data.
In creating a table, it is very important to know not only how to create a key field, but most importantly, to choose the key field correctly. The video lesson that we studied in the lesson will help us with this issue:
I really hope that now you will not have problems creating tables in Access, and if you have any questions, write them in the comments, we will figure it out. Thanks for the comments and questions.
The Access DBMS is focused on working with objects, which include database tables, forms, queries, reports, macros, and modules.
Access constructs interactively objects such as forms, queries, reports.
Many wizards Access 7.0 helps the user to do work without resorting to design, develop forms, queries, reports, analyze database tables, and even completely create one of the many sample databases.
Microsoft package Access can be used to search and process all kinds of data and to prepare reporting documents. It is a linked database management system that allows you to group data on various topics into tables and then create relationships between the tables. This makes it possible to combine coherent information, while avoiding unnecessary duplication of data, which leads to saving computer resources, increasing the speed and accuracy of information processing.
tables– are created by the user of data for their storage by one object of the data model of the subject area.
Requests required to select the desired data from one or more related tables. The request can be generated using:
Requests by Sample (QBE),
Query Structuring Language (SQL).
You can also use queries to update, delete, or add data to a table, or create new tables based on existing ones.
Forms are intended for entering, viewing, correcting the interconnected database data on the screen in a convenient form that can correspond to the document familiar to the user. Forms can also be used to create control panels in the user's application.
Reports designed to generate an output document intended for printing.
Macros contain a description of the actions to be performed in response to some event. Each action is implemented by a macro. A macro allows you to combine disparate data processing operations in an application. Macros automate the execution of a specific database operation without programming.
Modules contain programs in the Visual Basic language that can be developed by the user to implement non-standard procedures when creating applications.
New object added to MS Access 2000 Data access pages , intended for viewing and working via the Internet or an intranet with data stored in an Access database or MS SQL Server database. Data access pages are a special type of Web pages and may also include data from other sources such as MS Excel.
The Access dialog box contains:
title bar;
menu bar;
Toolbars;
status bar;
Working field.
Database creation. To create a new database from the menu, select the command File/New. Then select tab Are common and activate the label "Database". In the dialog box that opens, you must specify the name of the database to be created. To open an existing database, select the command File/Open. When the database is opened, the database window will appear on the screen, from which you can access all the tables, queries, forms, reports, macros and modules and data access pages contained in it.
Working with a database in Access begins with creating tables.
Creating a database table consists of two steps:
1) the structure is determined: the composition of the fields, their names, the order in which they are placed in the table, the data type, size, key fields and other properties of the fields;
2) table records are created and filled with data.
To create a table, select the tab in the database window tables and press the button Create. Then choose how to create a table: table mode, Design view or Table Wizard.
Constructor mode.
To define a field, the field name, data type, description (short comment, as well as general properties) are specified.
Field name. Each field in the table must have a unique name.
Data type. The data type is determined by the values that are supposed to be entered in the field, and the operations that will be performed on these values.
Access allows the following basic data types:
Text;
Memo (large text);
Numeric (specific variants of the numeric type and their length are set in the "Field size" property);
Monetary;
Date Time;
Counter (used to determine the table's unique system key);
Logical;
An OLE object field.
The list of field types is called up by pressing the list button when selecting the data type of each field.
General field properties are set for each field and depend on the selected data type.
Field properties:
· Field size specifies the maximum size of data stored in the field;
· The format of the field specifies the format for displaying the data on the screen or printing;
· Number of decimal places sets the number of decimal places for numeric and currency data types;
· The field caption specifies the text that is displayed in the headings of tables, forms, and reports;
· The condition on the value sets the restrictions on the input values and allows you to control the input;
· Error message specifies the text of the message displayed on the screen when the value condition is violated.
Primary key definition. To create a relationship between tables, you must define a primary key for each table. To do this, the fields that make up the key are selected and the "Key field" button is pressed. An index is automatically built for the key field, which is always unique and does not allow empty fields in records.
After defining the structure of the table, it must be saved with an empty name. After the table is saved, accessible mode table, which allows you to proceed to the second stage of creating a table - creating records. Switching to the table mode is carried out by pressing the "Table View" button.
Entering data into a table.
Direct data entry into the table is carried out in the table mode. To switch to this mode from the database window, select a table and press the button Open. In the table that opens, you can enter new records by filling in the field values.
When filling in tables, for the links between which the integrity parameters are not defined, it is up to the user to ensure the correctness of the information entered. With one-to-many-valued relationships between tables and entering records into the subordinate tables, it is necessary to monitor the presence of records with the entered values of key fields in the main table. When changing (deleting) key fields in the records of the main table, it is necessary to change (delete) the records associated with them in the subordinate tables.
After you enter a value in a field cell and you try to navigate to another cell, Access checks to see if the entered data is valid for that field. If you enter a value that doesn't match the data type of the field, Access attempts to convert it to the correct data type.
Data schema in Access.
Creating a data schema allows you to simplify the design of multi-table forms, queries, reports, as well as ensure the integrity of related data is maintained when tables are adjusted.
The data schema clearly displays tables and relationships between them, and also ensures the use of relationships in data processing and the integrity of the database.
Table relationships . The data schema defines the structure of the database. It defines and remembers relationships between tables. The data schema is a graphic representation of the database. The database data schema is displayed graphically in its own window, where tables are represented by lists of fields, and links are lines between fields of different tables. The data scheme is focused on working with tables that meet the requirements of normalization, between which 1:M and 1:1 relationships are established to ensure the integrity of the database. Therefore, the data schema is built in accordance with the information-logical model.
When building a data schema, Access automatically determines the type of relationship between tables based on the selected relationship field. If the field you want to link to is a unique key in both the master table and the subtable, Access establishes a one-to-one link. If the relationship field is a unique key in the master table, but is a non-key or a composite key in the child table, Access establishes a one-to-many relationship from the master table to the child table.
Links are associations. When you select a non-key field as a link field in the main table, Access reports that the relationship type cannot be determined. In this case, it is only possible to establish a connection between tables - joins. Relationship - A join provides a union of records from two tables that have the same values in the relationship field. Moreover, each record from one table is combined with each record from another table, provided that the values in the link field are equal. The result of combining records, which is determined by choosing one of three ways, can be:
Merge only those records in which the related fields of both tables match;
Merging those records in which the related fields of both tables are the same, as well as merging all records from the first table (for which there are no related ones in the second) with an empty record in the second table;
Merge those records in which the related fields of both tables are the same, as well as merge all records from the second table for which there are no related ones in the first, with an empty record in the first table.
The creation of a data schema begins in the database window by executing the command Service/Data Schema or pressing the corresponding button. After that, you can select the tables to include in the data schema and begin to define relationships between them. To establish a link between a pair of tables in the data schema, you need to select a unique key field in the main table, by which the link is established, and drag the mouse cursor to the corresponding field of the subordinate table.
Ways to create tables in Access
Today in the lesson we will look at ways to create tables in the MS Access database, learn how to create a table in Wizard mode and by entering values, analyze the pros and cons of these ways to create tables in Access.
In a relational database, all data must be stored in a table. Therefore, the most important thing when working with databases is the ability to create tables. Consider how you can create tables in MS Access.
At MS Access there are three how to create tables:
1) in design mode;
2) in master mode;
3) and by entering data.
And before using any method, you need to determine the structure of the table, i.e. determine which fields (columns) will be included in our table. Let's start with this, and then we will consider all the ways and find the best one!
Let's create a grading table in our database with the grades of students in our group in different subjects. Let our table consist of columns: Last Name, First Name, Mathematics, Informatics, Physics, etc.
Here is the structure of the future table:
In general, a lot can be redone, but why? After all, you can manually create a table structure, and do nothing! We will see how to create a table manually in design mode a little later, but now let's deal with Creating a table by entering data. So,
- select the Table object again;
- select Create table by entering data.
A table is opened, consisting of 10 fields, whose names are Field1, Field2, etc. This table is already designed for data entry. Let's enter a few lines (records) and see the pros and cons of this method.
Cons of this method:
- Why only ten fields (columns), and if more fields are needed, how to add?
- Why are the field names Pole1, Pole2, etc.? How to change the names of the columns of the future table?
- How to change the table name?
In general, as you can see, this method is also not very good, and after analyzing it, we come to the conclusion that it is better to create a table in Design mode manually.
How to create a table in Design view, we will consider in the next lesson.
If you still do not understand why the Designer, then I recommend that you read the article again first and ask questions in the comments.
Practical work:
- Develop a table structure from your version.
- Create a table in Wizard mode: select the appropriate table, add new fields to the table, save the table. Analyze the pros and cons of this way of creating a table.
- Create a table by entering values, save the table. Analyze the pros and cons of this method of creating an MS Access table.
-
-
Create a table
A simple database such as a contact list may have only one table. However, many databases use multiple tables. When you create a database on your computer, a file is created 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 book Microsoft office excel, Microsoft document Office Word, text file or another database, or link the table to that source. When you create a new database, a new empty table is automatically inserted into it. You can then enter data into it to start defining the fields.
Creating a table in a new database
Click File > Create and select the item Empty desktop database.
In field File enter a file name for the new database.
Click the button Create.
Will open new base data in which a new table named "Table1" will be created and opened in Datasheet view.
Create a table in an existing database
Click File > Open and select the database if it is listed under 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.
tab Creation in Group tables press the button Table.
A new table will be inserted into the database and will open in Datasheet view.
Using import or linking to create a table
To create a table, you can import data from another file (for example, an Excel sheet, a SharePoint list, an XML file, another Access database, a folder Microsoft Outlook etc.) or link a table to it.
When you import data, a copy is created in a new table in the current database. Subsequent changes to the original data will not affect the imported data, and vice versa. After connecting to the source and importing the data, you can use the imported data without connecting to the source. You can make changes to the structure of an imported table.
When you link to data, a linked table is created in the current database to provide a dynamic connection to data stored elsewhere. Changes to data in a related table are reflected in the source, and changes in the source are reflected in the related 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 the data Excel sheet using a linked table. As a workaround, you can import the source data into an Access database and then link to the database from Excel.
Create a table by importing or linking to external data
Create a table using a SharePoint site
To create a database table, you can use either import a SharePoint list or create a link to it. Alternatively, you can create a new SharePoint list based on ready template. Templates available in Access include Contacts, Tasks, Questions, and Events.
Select an item Contacts, Tasks, Questions or Events.
In the dialog box Create a new list
Specify a name for the new list And Description.
Open the menu File and select command 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 press the button SharePoint Lists.
Do one of the following:
Create a SharePoint list from a template
Create a custom list
Select an item Another.
In the dialog box Create 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 check box Open list after export is complete(it is installed by default).
Importing data from an existing list
Select an 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.
Check the boxes for all the SharePoint lists you want to import.
Link to 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 link to a data source and press the button Further.
Select the checkboxes for all the SharePoint lists you want to link to.
Create a table using a web service
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 links press the button Additionally and select command Data Services.
If desired connection already installed, go to step 5. Otherwise, go to the next step.
Click the button Set up a new connection.
Select desired file connection and press 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 be displayed on the right side of the dialog box.
You can enter the name of the linked table in the field Specify a link name. Access will display this associated table name in the Navigation Pane.
Click the button OK. The linked table will be created.
Setting table properties
In addition to setting field properties, you can set properties that apply to the entire table or to all records.
Saving a table
After creating or modifying a table, save 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 in naming tables, but there are some limitations. The name can be up to 64 characters long, any combination of letters, numbers, spaces, and special characters except periods (.), exclamation marks (!), square brackets (), leading space, leading equal sign (=), or nonprinting characters, such as carriage return. In addition, the name must not contain the following characters: ` / \ : ; *? " "< > | #
Advice: Agree on how you will name the objects in the database, and follow these rules.
If you are saving the table for the first time, enter its name and click the button OK.
Primary key setup
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. In addition, Access ensures that each 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 doesn't already have one, in the Designer.
Determining the fields used as the primary key
Sometimes there is already data that can be used as a primary key. 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 when combined with department codes; in this case, a combination of these fields must be used 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.
A field or combination of fields must not be empty (they must always have a value).
The values must not change.
If there is no data suitable for the role of the primary key, you can create a new field for this. When creating a field to use as a primary key, select the type "Counter" for it - this will ensure that it meets the three characteristics indicated above.
Setting or changing the primary key
Removing a Primary Key
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 creates a "Code" field with a data type of "Counter" that stores a unique value for each record. If the table already has a field with type "Counter", it will be used as the primary key. If you press the button No, Access will not add the new field or set the primary key.
Adding fields
To store a new piece of data for an existing Access table, we recommend that you add a field to it. For example, suppose there is a table with fields that contain last name, first name, address Email, phone number and postal address of each customer. If you want to start keeping track of the preferred communication method 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 a contacts table, you can create the fields "First name", "Last name", "Phone" and "Address", and in the table of goods - the fields "Product name", "Product code" and "Price".
Each field has some basic characteristics, such as a name that uniquely identifies it within a 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 to each value.
Before creating fields, divide the data into the smallest useful elements. Then it will be much easier for you to combine the data than to split it. For example, instead of the "Full Name" field, it's 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, put it in 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.
Adding a field by entering data
To add a field to a table when you create a new table or open an existing table in Datasheet view, enter data in a table column Add field(1) . Depending on what value is entered, the data type for the field is assigned automatically. If the value you enter does not have a specific data type, Access chooses the text type, but you can change it if necessary.
Entering data into a column Add field:
To create or open a table in Datasheet view, right-click the desired table in the Navigation Pane, and then Table view.
Enter the name of the field to be created in the column Add field.
Use a descriptive name to help identify the field.
Enter data in the new field.
Adding a field using a template
Sometimes it's easier to select a field from a pre-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 given 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.
Setting field 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 the field;
prevent incorrect data entry in the field;
set the field to a default value;
speed up searching and sorting by field.
Some field properties can be configured in Datasheet view, but all properties must be accessed and configured using the designer.
Custom properties depend on the data type of the field.
Set field properties in Datasheet view
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 context menu select item Table view.
Renaming a field
When you add a field by entering data in Datasheet view, Access automatically assigns it a generic name. The first new field is named "Field1", the second is named "Field2", and so on. By default, the field name is used as its label wherever the field appears (for example, in a table column header). If you give the fields descriptive names, it will be easier for you to view and edit records.
Enter a new name in the field header.
Right-click the title of the field you want to rename (for example, "Field1").
In the context menu, select Rename field.
Changing the data type of a field
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 datetime type to the field. If Access cannot unambiguously determine the data type, the field is assigned the text type by default. (Short text if using Access 2016).
The data type of a field determines what other properties you can set for it. For example, property Addition only can only be set for a field with the data type "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 to a new field in datasheet view, function automatic detection data type will select the data type "Date and time" for the field. Because room numbers are labels, not dates, they must be set to the data type 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 the field already contains data, some actions may not be available or may result in data corruption.
Setting other field properties
Note: Not all formats are available for all data types. First set the data type and then adjust the format if necessary.
Moving the field
To move a field, drag it to Right place. 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 field group to a new location.
When you drag a field, its position in the table changes, but the order of the fields in the designer does not change. At programmatic access the original order is used for the fields. 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.
In the modern world, tools are needed that would allow storing, organizing and processing large amounts of information that are difficult to work with in Excel or Word.
Such repositories are used to develop information sites, 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 walk through creating a database in Access 2007 step by step.
Description of MS Access
Microsoft Access 2007 is a database management system (DBMS) that implements a complete GUI user, 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 personal non-commercial purposes.
But before showing step by step how to create a database, you need to familiarize yourself with basic concepts from database theory. 
Definitions of basic concepts
Without basic knowledge about the controls and objects used to create and configure the database, it is impossible to successfully understand the principle and features of customizing the subject area. Therefore, now I will try to explain in simple terms the essence of all important elements. So, let's begin:
- A subject area is a set of created tables in a database that are linked to each other using primary and secondary keys.
- An entity is a separate database table.
- Attribute - the heading of a separate column in the table.
- A tuple is a string that takes the value of all attributes.
- The primary key is a unique value (id) that is assigned to each tuple.
- The secondary key of table "B" is the unique value of table "A" used in table "B".
- SQL query is a special expression that performs a specific action with the database: adding, editing, deleting fields, creating selections.
Now that we have a general idea of what we will be working with, we can start creating a database.
Database creation
For clarity of the whole theory, let's 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 rest of the fields are for more complete information about students.
So, do the following:
Everything, now it remains only to create, fill in and link tables. Move on to the next item.
Creating and populating tables
After successfully creating the database, an empty table will appear on the screen. To form its structure and fill it, do the following:
Advice! For fine tuning data format, go to the "Table View" tab on the ribbon and pay attention to the "Formatting and data type" block. There you can customize the format of the displayed data.
Creating and editing data schemas
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. For this you need:
The constructor should automatically create the relationship, depending on the context. If this did not happen, then:
Executing queries
What to 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, which help to remove only the necessary information.
Request types
The SQL syntax implements the CRUD principle (abbreviated from the English create, read, update, delete - “create, read, update, delete”). Those. With requests, you can implement all of these features.
per sample
In this case, the “read” principle comes into play. For example, we need to find all students who study in Kharkiv. For this you need:
But what if we are interested in students from Kharkiv who have more than 1000 scholarships? Then our request will look like this:
SELECT * FROM Students WHERE Address = Kharkiv AND Scholarship > 1000;
and the resulting table will look like this:
To create an entity
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 necessary during the performance of laboratory or term papers as part of a university course, because in real life there is no need for this. Unless, of course, you are a professional app developer. So, to create a request, you need:
- Go to the "Create" tab.
- Click the "Query Builder" button in the "Others" block.
- In the new window, click on the SQL button, then enter the command in the text field:
CREATE TABLE Teachers
(TeacherCode INT PRIMARY KEY,
Surname CHAR(20),
Name CHAR (15),
Middle name CHAR (15),
Gender CHAR (1),
date of birth DATE,
main_subject CHAR(200));
where "CREATE TABLE" means to create the "Teachers" table, and "CHAR", "DATE" and "INT" are the data types for the corresponding values. 
Attention! At the end of each request, there must be a ";" character. Without it, script execution will result in an error.
To add, delete, edit
Everything is much simpler here. Again go to the field to create a request and enter the following commands:
Form creation
With a huge number of fields in the table, it becomes difficult to fill the database. You can accidentally omit a value, enter the wrong value, or enter a different type. In this situation, forms come to the rescue, with the help of which you can quickly fill in entities, and the likelihood of making a mistake is minimized. This will require the following steps:
We have already considered all the basic functions of MS Access 2007. The last important component remains - report generation.
Report generation
A report is a special function of MS Access that allows you to format and prepare data from a database for printing. This is mainly used to create invoices, 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:
- Go to the "Create" tab.
- Click the "Report Wizard" button in the "Reports" block.
- Select the table of interest and the fields you want to print.
- Add the required level of grouping.
- Select the sort type for each field.
