Hi all! In this lesson we will look at 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
Goal of the work:
1. Develop the table structure of your own database using various methods.
2. Fill the tables with records
3. Establish connections between tables (1 - 1; 1 - ?; ? - ?)
Completing of the work.
First, we will create the table structure of our own database using various methods, then we will establish connections between them.
v Creating a table structure.
Opening MS Access, we see that the table can be created in three ways.
1. Create a table structure using the wizard. Here, standard table field names and data types are automatically provided. To create you need to do the following:
ь From the list of sample tables, select a table prototype that is similar to the table being created, and from the list of sample fields - table fields, using buttons > place them in the list of fields of the new table. Having generated a list of fields - <Далее>.
ь In the window asking you to specify a table name, specify the method for defining the key.
b If previously created tables already exist in the database, then at this step a request will appear to create connections between the new table and existing ones. In the dialog box that appears, select one of the possible connection types and OK.
b Select the mode of further work: change the structure of the table, enter data into the table, or enter data directly into the table using the form created by the wizard. After selecting the mode <Готово> .
2. Creating a table structure using the Designer.
ь Creation of field names and field types. In this mode, at the top of the window there is a table in which you enter: Field name, Data type, Description.
ь Create a key field. Select the required field. Team Edit - Key Field. A key will appear in the table opposite this field, which means that this field is key.
ь Filling out the table. From Design mode, go to table mode (View - Table mode) and fill it out.
3. Create a table by entering data. This method is simpler and more visual
b The window that appears contains a table created by default, which contains 10 columns and 20 rows. Enter data.
ь Create a key field. To define the Code as a key, you need to go to Design mode - menu command View - Constructor. Highlight Code field Edit - Key Field.
ь All changes to the table structure are carried out in Design mode. It is necessary to check whether the data types are selected correctly.
v Creating relationships between tables.
Linking tables. In this case, it is necessary to close all database tables. Select team Service - Data Schema. In the dialog box that appears Data Schema add tables that need to be linked: Relationships - Add Table. In the window Adding tables select the tables to link and click Add. Close a window. In the dialog window Data Schema The linked tables will appear. Select the connection field (key field) in the first table and, without releasing it, move it to the corresponding field of the other table. A window will appear on the screen Changing connections. Here you need to check the correctness of the names of the associated fields and establish data integrity. Relationships between tables are established only based on matching fields. They must have the same data type.
Data integrity condition is a set of rules used to maintain relationships between records in related tables. They make it impossible to accidentally delete or change data links.
Restrictions:
1. It is impossible to enter a value into a foreign key field of a linked table that is not contained in a key field of the main table. However, you can enter blank values to indicate that the records are not related.
2. You cannot delete records from the main table if there are related records in the subtable.
3. You cannot change the value of a key field in the main table if there are records associated with this record.
To overcome restrictions on deleting or modifying related records while maintaining data integrity, you must enable cascading update and cascading delete modes. When checked Cascade update of related fields Changing a value in a key field in the main table automatically updates the corresponding values in all related tables. When checked Cascade deletion of related records Deleting a record in the main table automatically deletes related records in the subtable. These checkboxes are checked in the dialog box Changing entries only after installing the option Ensuring data integrity.
Then press the button Create. After this window Data Schema will take a different form.
IN MS Access There are 4 types of connections:
Sh "one to one" - One record in one table corresponds to one record in another table. (In our case, this is the connection between the Types of services and Salary rates tables.)
Ш "one-to-many" - each record of the main (first) table can be associated with any number of records of the second table. But each record of the second table can be associated with at most one record of the first table. (We have tables connected in this way: Prices for salary, Cosmetics, Visitors and Performers with the table Demand for salon services.)
Ш "many-to-one" - the same as the previous connection. (We have a table connected with the table Demand for salon services with the tables Price for salary, Cosmetics, Visitors and Performers.)
Many-to-many - each record in the main table can be associated with any number of records in another table. And vice versa. This connection is two connections 1 - ? through the third table. To do this, the following actions are performed: a third (linking) table is created with fields whose descriptions coincide with the description of the key fields in each of the two linked tables; defining a key in the new table that contains all the key fields of the two tables being linked; defining a one-to-many relationship between each of two tables and the joining table. (In our case, this is the connection between the Visitors and Types of Services tables through the Auxiliary)
Changing an existing connection. Close all tables, then click Data Schema on the toolbar. If the tables whose relationships you want to change are not displayed in the dialog box Data schema, press the button Show table, select the desired table and click Close. Next, place the pointer on the communication line that you want to change and, double-clicking the mouse, make changes in the window that opens Changing connections.
Deleting a connection. Click Data schema, Use the mouse to highlight the connection line that needs to be deleted. Press key
Conclusion: we learned how to develop the table structure of our own database using various methods and establish relationships between these tables.
5) When you have finished entering data, press the button Close(in the upper right corner of the table window).
6) In response to the question Save table layout or structure changes<имя таблицы>? (Do you want to save changes to the design of table< table name >?) click Yes button(Yes).
7) In the window Preservation(Save As) in the field Table name(Table Name) enter the name of the new table and click the button OK(Fig. 5.2).
Figure 5.2 -Dialog window Preservation
Microsoft Access will display a message Key fields not specified(There is no primary key defined) and the question Create a key field now?(Do you want to create a primary key now?). Click the No button if one or more fields in the table can uniquely identify records in the table and serve as a primary key, or click Yes, and then Access will create an additional field that will make it a key field.
5 .2 Creating tables using the Table Wizard
To do this you need:
1) Double left click on the shortcut Create a table using the wizard(Create table by using wizard).
2) In the dialog box Creating tables(Table Wizard) (Fig. 5.3) select the purpose of the table: Business(Business) (for other purposes you can select Personal(Personal)).
3) In field Sample tables(Sample Tables) select the appropriate table - Distribution list(MailingList).
4)
Move required fields from the list Sample fields(Sample Fields) to list New table fields(Fields in my new table). To do this, use the arrow buttons: > - move one field, » - move all fields.
Figure 5.3 -Table Wizard Dialog Box
5. Repeat those described in paragraphs. 3-4 steps until a suitable list of fields for the new table is created.
6.
If you need to rename a field, you need to select it in the list New table fields(Fields in my new table) and click the button Rename field(Rename Field), then enter in the dialog box Renaming a field(Rename Field) new field name and click OK (Fig. 5.4). Press the button Further(Next).
Figure 5.4 -Dialog window Renaming a field
7.
In the next dialog box, in the field You must specify a name for the new table(What do you want to name your table?) enter the name of the new table “Alert distribution list” (Fig. 5.5).
Figure 5.5 -Second Table Wizard dialog box
8. Select a key determination method Microsoft Access automatically detects the key(Yes, set a primary key for me) (there is an alternative The user defines the key independently(No, I"ll set the primary key)). Then an automatic numbering field will be added to the table. Click the button Further(Next).
If you chose to define the key yourself, in the combo box You must select a field with data unique to each record(What field will hold data that is unique for each record?) you need to select the name of the field that will become the key one. Next, you need to determine the type of data that will be contained in the key field.
9. In the next window, the Table Wizard offers to establish connections between existing tables and the table being created (Fig. 5.6). It tries to create relationships between tables itself and provides information about the created relationships to the user in the form of a list. If you need to correct connections, you need to select the appropriate list line and click the button Connections(Relationship).
Figure 5.6 -Third Dialog Box of the Table Wizard
Figure 5.7 -Dialog window Connections
Figure 5.8 -Fourth window of the Table Wizard
10. In the window Connections(Relationships) you need to select the type of relationship with this table (Fig. 5.7).
11. In the Table Wizard dialog box (see Figure 5.6), click the button Further(Next).
12. In the last dialog box, the user needs to select what he will do with the table next (Change table structure) and click the button Ready( Finish) (Fig. 5.8).
5 .3 Creating a table using the Table Builder
In Design mode, tables are created by specifying field names, their types, and properties. To create a table in Design mode, you must:
1.
Double left click on the shortcut Creating a table using the designer(Create table in Design View) or click on the button Create(New) at the top of the database window, select from the list in the window New table(New Table) element Constructor(Design View) (Fig. 5.9).
Figure 5.9 -New table window in Design mode
2. In the Table Designer window, in the Field Name column, enter the names of the fields of the table being created.
3. In column Data type(Data Type) for each table field, select from the drop-down list the type of data that will be contained in this field.
4. In column Description(Description) You can enter a description of this field (optional).
5. At the bottom of the Table Designer window in the tabs Are common(General) and Substitution(Lookup) enter the properties of each field or leave the default property values.
6. After describing all the fields of the future table, click the button Close(in the upper right corner of the table window).
7. To the question Save table layout or structure changes<имя таблицы>? (Do you want to save changes to the design of table< table name >?), click Yes button(Yes).
9. In response to. message Key fields not specified(There is no primary key defined) and the question Create a key field now?(Do you want to create a primary key now?) Click the Yes button if a key field is required, or the No button if it is not required.
Print table
The easiest way to print a table is to use the button Seal(Print) on the toolbar Database(Database). To print a table in this way, you must:
1. Select the table in the database window.
2. Press the button Seal(Print) on the toolbar Database(Database).
This prints the entire table. To display the entire table, you can also right-click on the table icon in the database window and select the command from the context menu Seal(Print).
— Designing tables, the concept of a key field, filling and editing tables, data types
Before you fill a table with information, you need to design it. You can start designing a table after opening the database file. In the DB window, activate the Tables tab, click on the Create button, and select Constructor --OK from the list that appears. A window appears in which we must create the table structure - specify field names, data types, field sizes (see figure).
Data types.
- Text – a set of arbitrary alphanumeric characters. The size of such a field cannot exceed 255 characters. The default is 50 characters. The size can be set using the Field Properties table.
- The Memo field is long text intended for notes or descriptions. Used to create text with more than 255 characters.
- Numeric – numerical data used in mathematical calculations. Specific numeric type options and their length are specified in the Field Size property.
- Monetary – allows you to represent numbers in monetary format. When you select this data type in the Field Properties table, you set the desired format and the number of decimal places.
- Date/time – when selecting this data type, the desired format is assigned through the Field Format property.
- Boolean - Boolean data can take only one of two mutually exclusive values - 0 or 1, true or false.
- The Counter field is filled in automatically. The DBMS independently increases the value of the variable in this field, assigning a new number to each record.
Key field.
If a field is assigned a primary key, then Access prevents duplicate elements of this field.
The field to which the primary key is assigned is called the key field. The primary key is assigned in design mode: you need to right-click anywhere in the line with the specifications of the field that we want to assign as key → select Key Field from the context menu. The primary key field is marked with a special marker - an icon in the form of a key.
Records in tables are automatically reproduced sorted in ascending order by key field.
After you have finished creating the table structure, you should save it (m. File - p.Save - enter the table name - OK.
Editing a table project.
To switch to Table Design mode, you need to select the icon of the desired table in the Tables tab of the Database window, click on the Design button, the project of the desired table will open, in which you can:
- Insert a new field - to do this, right-click anywhere on the line with the specifications of the field before which we want to insert a new field, and select Add Lines from the context menu.
- Delete a field - to do this, right-click anywhere on the line with the specifications of the field that we want to delete, and select Delete Lines from the context menu.
- Enter new values for field specifications (field name, data type, etc.) - left-click in the cell whose contents we want to change, enter new values instead of old ones.
Entering data into a table.
To fill out a table, you need to open it - double-click on its icon in the database window. The new table consists of one empty record. When entering data, an empty record is moved to the end of the table. When you enter data, Access checks it against the specified data type, and if there is a discrepancy, it displays a warning message. When entering data, you move to the next cell using the Enter key, or TAB, or the arrow key. In the opposite direction - using the Shift+Tab keys or the arrow key. Using the key combination Ctrl -Home you can go to the first cell of the table, using Ctrl-End - to the last. Using Ctrl -" the contents from the top cell are copied to the current cell.
After filling out the table, you need to close it using the X button, which is located in the upper right corner of the table window. The entered data is automatically saved in the table.
Editing table entries.
- Selecting an entry – place the mouse pointer on the entry to be selected, execute the command from the menu bar Edit-p. Select entry.
- To cancel a selection, click anywhere in the table.
- Copying (moving) records in a table - select the record, copy (delete) the record to the clipboard using the command from the Edit menu bar - item Copy (Cut); Place the mouse pointer on the entry over which you want to copy a new entry, execute the menu bar command Edit – p.Add from clipboard.
- Deleting records - select the record, execute the menu bar command Edit - p.Delete record.
Sorting records in a table.
You need to open the table that needs to be sorted. Next, right-click in any cell of the field by which we sort the records and select the “Sort Ascending” or “Sort Descending” command.
I hope this article helped you resolve your issue. how to create a table in Access.
Creating database tables is the first step in developing a DBMS. Tables are designed to store database information. Creating a table consists of two stages: setting the table structure; entering records into a table.
To create a new table, select the "Tables" menu in the database window (in older versions of Access, the "Tables" tab is used for this purpose) and click the "Create" button. In the "New Table" window, you must select how to create the table.
If the DBMS developer does not have sufficient experience, it is recommended to select Design mode to create a table.
Create a table in design view
When you select design mode, the design window appears.
In the "Field name" field, enter the name of the table field, which is its identifier. It is recommended to form field names so that the name is short, does not contain special characters (commas, spaces, etc.) and reflects the meaning of the field. It is necessary to distinguish between the field identifier in the table structure and the field title in the output document. In the second case, the field title must fully reflect the meaning of the field and is usually set manually when formatting the output document.
In the "Data type" field, select one of the types shown in the expanded list in the figure.
The "Description" field contains comments about the table field being created. Its completion is optional.
At the bottom of the window, on the tab "Are common" field properties are set. If you click on one of the rows of the property table, a hint about the purpose of this property will appear on the right.
The "Substitution" tab is used to provide hints when filling in the foreign keys of a table. Keys are most often formal identifiers of records in database tables. Therefore, when populating foreign keys, the user may have difficulty determining which key in the base table corresponds to a record in the subtable. Setting the properties of the "Lookup" tab allows you to turn a simple field for a foreign key into a combo box containing a complete list of keys of the base table and fields corresponding to these keys - hints from the base table. After selecting a record in the list, only the key is placed in the table to be filled. Thus, the user does not need to remember the keys and their entry is performed by selecting from a list rather than entering from the keyboard. Subsequently, lookup fields are inherited by forms if a table with such fields is selected as the form data source.
The above figure describes the "Group" table, consisting of three fields with identifiers NG, KOL and PBALL. The NG field is text and contains the numbers of student groups. The main property of this field is its length, which does not exceed 6 characters. The KOL field contains the number of students in the group and is numeric (integer). The PBALL field contains the average score received by the students of the group upon admission to the educational institution; it is numeric, but unlike the KOL field, it is real, containing a decimal part.
An important step at the stage of developing a table structure is setting key fields. To specify a simple key consisting of one table field, just use the design mode to place the cursor in any position of this field and click the "Key field" button on the toolbar. In the figure this is the NG field. To specify a composite key consisting of several table fields, you need to select these fields by clicking on the buttons to the left of the corresponding rows while holding down the Ctrl key, and then clicking the "Key Field" button. A sign of installation is the appearance of a key picture on the buttons to the left of the corresponding constructor line.
The table name is prompted when the designer window is closed. After closing the designer, the icon and name of the created table appear in the database window. If you select the table icon and click on the "Open" button, the table will be opened for entering records.
Create a table by entering data
Creating a table by entering data does not include a description of the table structure. After selecting this mode (table mode), an empty table opens into which you can enter data.
Any field in this table can be changed at the user's discretion. Field names are specified directly in the headers. When you save this table, Access will analyze the data and automatically assign the appropriate data type to each field, i.e. will create the table structure. When you close Table View, Access will prompt you to create a key field. If you answer “Yes,” then another field of the “Counter” type will be added, which will be the key one. If you answer "No", then the key can be set later by going to design mode.
Creating a table using the Wizard
The Table Wizard automatically creates a table using one of the templates offered in the window "Creating Tables":
The wizard determines the table key and creates a relationship between the new table and existing ones. In this case, the key of the new table will be included in the table with which the connection is established. At the user's request, the wizard creates a form for entering data into the table.
Entering data into a table
Data can be entered into the table directly in its opening mode or through a specially created form. The second method is preferable because forms provide a more user-friendly interface and input control capabilities.
The data you enter must match the data type and format defined in the structure for each table field. If there is a mismatch, Access issues a warning and does not allow you to continue typing. You must either enter the data in the required format or cancel the entry.
Control questions
- What are database tables used for?
- How many steps does it take to create a table?
- How to create a new table while working in Access?
- What fields should you fill in when creating a new table in Design view?
- How is the field name formed? How is it different from the field header in the output document?
- What is the purpose of the Data Type and Description fields?
- What field properties can be set? What is their purpose?
- What is the Substitution tab used for?
- How are simple keys specified? Composite?
- What is special about creating a table by entering data?
- How to create a table using the Wizard?
- What methods allow you to enter data into a table?
