Creating and filling databases in Microsoft Access. MS Access: What is a database? The difference between Access and Excel Office Access

The main purpose of this program is to create and work with databases that can be linked to both small projects and large businesses. With its help, you will be able to conveniently manage data, edit and store information.

Package application Microsoft Office– Access – used to work with databases

Naturally, before you begin, you will need to create or open an existing database.

Open the program and go to the main menu by clicking on the “File” command, and then select “Create”. When creating a new database, you will be presented with a choice empty page, which will have one table or a web database that allows you to use the program’s built-in tools for, for example, your publications on the Internet.

In addition, to make creating a new database as easy as possible, the user is provided with templates to choose from that allow him to create a database focused on a specific task. This, by the way, can help you quickly create the necessary table form without setting everything up manually.

Filling the database with information

Having created the database, you need to fill it with relevant information, the structure of which should be thought out in advance, because the functionality of the program allows you to format data in several forms:

Nowadays the most convenient and common type of information structuring is a table. In terms of their capabilities and appearance, tables in Access are not very different from those in Excel, which, in turn, greatly simplifies the transfer of data from one program to another.
The second way to enter information is through forms; they are somewhat similar to tables, however, they provide a more visual display of data.
To calculate and display information from your database, reports are provided that will allow you to analyze and calculate, for example, your income or the number of contractors with whom you work. They are very flexible and allow you to make any calculations, depending on the entered data.
Receiving and sorting new data in the program is carried out through queries. With their help, you can find specific data among several tables, as well as create or update data.

All of the above functions are located in the toolbar, in the “Creation” tab. There you can select which element you want to create, and then, in the “Designer” that opens, customize it for yourself.

Creating a database and importing information

When you create a new database, the only thing you will see is an empty table. You can fill it out manually or fill it out by copying the necessary information from the Internet. Please note that each piece of information you enter must be placed in a separate column, and each entry must have a personal line. By the way, columns can be renamed to better navigate their contents.

If all the information you need is in another program or source, the program allows you to configure the import of data.

All import settings are located in a separate tab in the control panel called “External Data”. Here, in the “Import and Links” area, the available formats are listed, including Excel, Access documents, text and XML files, Internet pages, Outlook folders, etc. Having selected the required format from which information will be transferred, you will need specify the path to the file location. If it is hosted on a server, the program will require you to enter the server address. As you import, you will encounter various settings that are designed to correctly transfer your data into Access. Follow the program's instructions.

Basic keys and table relationships

When creating a table, the program automatically assigns each record a unique key. By default, it has a column of names, which expands as new data is entered. This column is the primary key. In addition to these primary keys, the database may also contain fields related to information contained in another table.

For example, you have two tables containing related information. For example, they are called “Day” and “Plan”. By selecting the “Monday” field in the first table, you can link it to any field in the “Plan” table and when you hover over one of these fields, you will see information and related cells.

Such relationships will make your database easier to read and will certainly increase its usability and efficiency.

To create a relationship, go to the “Database Tools” tab and in the “Relationships” area, select the “Data Schema” button. In the window that appears, you will see all the databases being processed. Please note that databases must have special fields designated for foreign keys. In our example, if in the second table you want to display the day of the week or a number, leave an empty field, calling it “Day”. Also configure the field format as it should be the same for both tables.

Then, with two tables open, drag the field you want to link into the specially prepared field for foreign key. The “Edit Links” window will appear, in which you will see individually selected fields. To ensure data changes in both related fields and tables, check the box next to “Ensure data integrity.”

Creation and types of requests

A query is an action in a program that allows a user to edit or enter information into a database. In fact, requests are divided into 2 types:

Selective queries, thanks to which the program retrieves certain information and makes calculations on it.
Action requests that add information to the database or remove it.

By selecting “Query Wizard” in the “Creation” tab, the program will guide you through the process of creating a specific type of request. Follow the instructions.

Queries can greatly help you organize your data and always access specific information.

For example, you can create a custom query based on certain parameters. If you want to see information on a specific date or day of the “Day” table for the entire period of time, you can set up a similar query. Select the “Query Builder” item, and in it the table you need. By default, the query will be selective; this becomes clear if you look at the toolbar with the “Selection” button highlighted there. In order for the program to search for exactly the date or day that you need, find the line “Selection condition” and enter the phrase [what day?] there. Remember, the request must be placed in square arms and end with a question mark or colon.

This is just one use case for queries. In fact, they can also be used to create new tables, select data based on criteria, etc.

Setting up and using forms

Thanks to the use of forms, the user can easily view information for each field and switch between existing records. When entering information for a long time, using forms simplifies working with data.

Open the “Creation” tab and find the “Form” item, clicking on which will display a standard form based on the data in your table. The information fields that appear are subject to all sorts of changes, including height, width, etc. Please note that if there are relationships in the table above, you will see them and can reconfigure them in the same window. At the bottom of the program you will see arrows that will allow you to sequentially open each column of your table or immediately move to the first and last. Now each of them is a separate record, the fields of which you can customize by clicking on the “Add fields” button. The information changed and entered in this way will be displayed in the table and in all tables attached to it. After setting up the form, you need to save it by pressing the key combination “Ctrl+S”.

Creating a report

The main purpose of reports is to provide the user with an overall summary of the table. You can create absolutely any report, depending on the data.

The program allows you to choose the type of report, providing several to choose from:

Report - an auto-report will be created using all the information provided in the table, however, the data will not be grouped.
A blank report is an unfilled form for which you can select data yourself from the required fields.
Report Wizard - will guide you through the process of creating a report and will group and format the data.

In an empty report, you can add, delete or edit fields, filling them with the necessary information, create special groups that will help separate certain data from the rest, and much more.

Above are all the basics that will help you cope and customize the Access program for yourself, however, its functionality is quite wide and provides for more fine-tuning of the functions discussed here.

Access databases on your computer help you store and track virtually any type of data, such as inventory, contacts, or business processes. Let's detailed description paths you can use to create an Access database, add data to it, and then learn the next steps toward setting up and using your new database.

Select a template

Access templates come with built-in tables, queries, forms, and reports ready to go. Available templates are the first thing you see when you launch Access, and you can download additional options from the Internet.

In Access tab File select team Create.

Select a desktop database template and enter a name in the field File name. Database (If you don't seetemplate that will work for you, useSearch for templates on the Internetfield.)

File name

Click the button Create.

Depending on the template you choose, you may need to complete any of the following steps before you begin.

If Access displays a dialog box Entrance with an empty list of users:

Click the button new user.

Fill the form User information.

Select the username you just entered and click entrance.

If Access displays the message Security Warning in the message bar, as well as a template from a trusted source, click Include content. If the database requires a login, log in.

Additional information: .

Creating a database from scratch

If none of the templates suits you, you can start with an empty database on your computer.

In Access, select the command Create, and then - item Empty desktop database.

Enter the database name in the field File name.

You can use the default location listed below the field File name, or select another by clicking the folder icon.

Click the button Create.

Adding tables

In a database, data is stored in several related tables. To create a table:

When you open the database for the first time, you will see a blank table in Datasheet view where you can add data. To add another table, go to the tab Create > Table. You can start typing data into a blank field (cell) or insert data from another source, such as an Excel workbook.

To rename a column (field), double-click its header and enter a new name.

To add more fields, start typing in the column Click to add.

To move a column, select it by clicking the header and drag it to the desired location. You can do this operation with multiple adjacent columns.

Copy and paste data

You can copy data from another program, such as Excel or Word, and paste it into an Access table. It's best if they are organized into columns. If the data is in text editor, such as Word, tab-separate the data columns or convert the data to a table before copying.

If the data needs to be changed, such as splitting full names into first and last names, do this in the source program.

Open the source and copy the data (CTRL+C).

Open the Access table you want to add data to in Datasheet view and paste the data (CTRL+V).

Double-click each column header and provide meaningful names.

Note: Depending on what data you insert in the first row of a column, Access assigns a data type to each field. Make sure that the data in the remaining rows is of the same type.

Import or link data

You can import data from other sources or you can link data from Access without moving the information from which it is stored. Linking may be a good option if you have multiple users updating data and want to make sure you can see latest version, or if you want to save disk space. You can whether you want to link to or import data for most formats. Learn more about importing or linking data from another Access database.

The process varies slightly depending on the data source, but always starts with the procedure below.

On External data Click the Data Format button you will be importing or linking to. If you don't see the format you need, click the button Additional.

Note: If you still don't have one, you may need to first export the data to a file format supported by Access (for example, text file with delimiters).

Follow the instructions in the article dialog box " External data ».

When linked, some formats are read-only. The following are external sources whose data can be imported or linked.

Microsoft Access is a functionally complete relational DBMS. It provides all the necessary tools for defining, processing, and managing data when working with large volumes of information.

DBMS Microsoft Access provides the opportunity to control the definition of the structure and description of your data, work with them and the organization of collective use of this information. DBMS Microsoft Access also significantly increases the capabilities and facilitates cataloging and maintaining large volumes of information stored in numerous tables. DBMS Microsoft Access includes three main types of functions: definition (setting the structure and description) of data, data processing and data management. MS Access is a set instrumental means for creating and operating information systems.

Using Access, you can perform the following operations.

1. Design basic IS objects - two-dimensional tables, with different types data, including OLE object fields. Before filling a table with data, you need to create its layout.

2. Establishment connections between tables, with support for data integrity, cascading field updates and cascading record deletions.

3. Entering, storing, viewing, sorting, modifying and retrieving data from tables using various means of information control, indexing tables and logic algebra (for filtering data).

4. Creation, modification and use derivatives IS objects (forms, queries and reports).

The MS Access processing object is database file, having an arbitrary name and extension .MDB. This file contains the main objects MS Access: tables, forms, queries, reports, macros and modules. Thus, an information system created in Access is a database file.MDB plus the tools of Access itself. The operation of such an IS is the processing of an .MDB document by the Access application.

Information in an Access database is presented in the form of separate tables. In this case, each table column corresponds to a data field, and each row corresponds to a data record.

A data record consists of several fields. The following rule applies: a data record is a group of interrelated fields considered as a single whole. For example, a data record may contain information about a specific customer or product. Each data record in a table contains the same number of fields. Each field contains the same type of information. For example, in each data record of a certain table, the CLIENT NAME field will be for the client's name, i.e. will contain the same type of information.

There are different types of data. The data type for a particular data field is selected depending on what information will be located in this field. The size of the data field is determined depending on the type selected for it. Descriptions of data types and their characteristics are given in Table 2.

Table 2 - Data types

Data type	Application	Size
Text	Text or a combination of text and numbers, such as an address, and numbers that do not require calculations, such as telephone numbers. item numbers or postal code.	Up to 255 characters. Only characters entered in the field are stored; positions not used in the text field are not stored. To control the maximum number of characters entered, you must define the FieldSize property.
MEMO field	Long text or numbers, such as comments or descriptions. MEMO fields cannot be indexed or sorted. For storing formatted text or long documents instead of the MEMO field. you should create an OLE object field.	Up to 65535 characters.
Number	Numerical data used for mathematical calculations, excluding calculations involving monetary transactions(for which the currency type is used). The type and size of values that can be contained in a number field. Can be changed in the FieldSize property. For example, in the field. occupying 1 byte on disk, only integers (without decimal places) from 0 to 255 can be entered.	1, 2, 4 or 8 bytes. 16 bytes only for replication codes.
Date/Time	Dates and times. Storing date and time values in a Date/Time field ensures correct sorting. All changes made to date and time formats in the Language and Standards Panel window Windows management, will be automatically reflected in fields of the Date/Time type.	8 bytes.
Currency	Currency values. The currency type is used to prevent rounding during calculations. Assumes up to 15 characters in the integer part of the number and 4 in the fractional part.	8 bytes.
Counter (AutoNumber)	Automatic insertion of consecutive (differing by 1) or random numbers when adding an entry. To create an incremental counter, leave all property settings at the bottom of the window the same (default), with the FieldSize property set to Long Integer, and the New Values property set to Increment. To create a random number counter, the New Values property must be set to Random.	4 bytes. For replication codes - 16 bytes.
Logical (Yes/No)	Fields. containing only one or two values, such as Yes/No, True/False. On off.	1 bit.
OLE Objects	Objects (for example, documents Microsoft Word. spreadsheets Microsoft Excel, pictures, sounds and other data) created in other programs that use the OLE protocol. Objects can be linked or embedded in a Microsoft Access table. To display an OLE object in a form or report, you must use the Attached Object Frame control.	Up to 1 gigabyte
Lookup Wizard	A field is created. allows you to select a value from another table or from a list of values using a combo box. When choosing this parameter in the list of data types, a Wizard is loaded to define them.	The size is the same as the size of the key field, which is also the Lookup Wizard, usually 4 bytes.
Hyperlink	The field in which hyperlinks are stored. A hyperlink can be either a UNC type (Universal Naming Convention - a standard file path format with the inclusion of a network server) or a URL (Uniform Resource Locator - the address of an object on the Internet or an internal network with the inclusion of an access protocol type). A hyperlink can consist of four parts: text that appears in a field or control; path to the file (in UNC path format) or page (URL); additional address - location inside a file or page; tooltip - text displayed as a tooltip.	The length of each part of the hyperlink is no more than 2048 characters.
Note: Numeric, currency, and Boolean data types, as well as Date/Time, provide standard display formats. To select formats for each data type, you must define the Format property. For all data except OLE objects, you can also create a custom display format. For more information, see the "Field Format Property" section below.

In addition to tables, Access DBMS works with the following objects:

Forms;

Requests;

Reports;

Macros;

Modules.

The table is base MS Access object. All other objects are derivatives and are created only on the basis of previously prepared tables.

A form is not a standalone Access object. It simply helps you enter, view, and modify information in a table or query. Queries and reports perform independent functions: select, group, present, and print information.

Each MS Access object has Name. The length of the name of any MS Access object (tables, forms, etc.) is no more than 64 arbitrary characters (except for the dot and some service characters). The name may include spaces and Russian letters. Each object is worked in a separate window, and two modes of operation are provided:

1) mode designer, - when a layout, object structure (for example, table structure) is created or changed;

2) operational mode - when information is viewed, changed, and selected in the IS task window.

In addition, the database file includes another document that has own window: Data schema. In this window you can create, view, change and tear communications between tables. These connections help you control data and create queries and reports.

Form, strictly speaking, it is an optional element of the IS (in principle, you can do without it), but it allows you to simplify the operations of entering and viewing data.

Request is a derived table that includes data from other tables and performs various operations on them. In particular, the request may contain calculated fields, i.e. fields whose values are functions values of other fields (possibly from different tables). In addition, queries allow you to group operations, i.e. operations on a group of records united by some common characteristic (for example, you can sum the quantity for records with the same code). Finally, queries allow you to make selections from tables based on some condition. In such cases, the algebra of logic is used.

Report - this is actually the same request, but formatted so that it can be printed on paper and presented to management (with beautiful headings, subtotals, etc.).

In essence, tables and forms are used to maintain the contents of the database, and queries and reports perform the main function of the information system - retrieving, transforming and presenting information.

Creation derivatives objects - requests, forms and reports - are maintained approximately according to the same scheme. Here are some features:

For derived objects, you must specify the name(s) of the table (query) on the basis of which this object is created;

When using the Wizard, you must check the “Element Panel” checkbox on the toolbar, and a set of icons will appear on the screen (Fig. 14.5), with which you design a form (report, query).

Creating any objects is possible in two ways: using the wizard and in design mode.

MS Access has powerful, convenient and flexible tools visual designing objects with the help of Wizards, and this allows the user, with a minimum of preliminary preparation, to quickly create a full-fledged IS - at the level of tables, forms, query selections and reports.

The masters themselves perform the necessary actions. When calling the Master, the corresponding sequence opens dialog boxes. Having received answers to the questions asked, the Master performs all the necessary actions. Let's say you want to generate a report. The Report Wizard will ask you what the report should look like and what data it should contain. After receiving the answers, the report will be completely ready. Using other Wizards, you can create database tables, queries and forms.

Working with Wizards is simple, reliable and efficient, and allows you to use the full richness of Microsoft Access.

The Builder is an Access tool that makes it easier to complete a specific task. The Expression Builder is extremely useful - it allows you to quickly create a complex expression. Using the Query Builder, you can easily create queries that are used to obtain data samples for a form or report. In addition to those listed, Access has other constructors. This is a macro constructor with the help of which various macros are generated, as well as constructors for menus, fields, colors, codes and others.

1 Launch Microsoft Access:

When Access starts, a window appears on the screen (Figure 1.1), in which you can:

Or create a new database, i.e. a file with the extension .MDB;

Or open an existing database, i.e. a file with the .MDB extension.

Creating a database. After selecting the [File-Create...] command, a standard filer appears on the screen (Figure 1.2), in which you need to specify the name and address created database file. For example, for information system with the name Telephone Directory.MDB.

After creating the file, Access opens an empty database window, and in this window all operations are carried out - creating and manipulating database objects. This window contains 7 objects (Tables, Queries, Forms, Report, Pages, Macros, Modules) and groups.

Opening a database. After selecting the [File-Open...] command, a standard filer appears on the screen, in which you must specify the name and address existing database file. Then the application window opens database window, providing access to already created database objects and the ability to create new objects.

MS Access - multi-window application, however only one database can be open at any time. It is her window that is main document window in the Access application (Figure 1.3), and closing it means closing the corresponding .MDB file. However, this window generates many subsidiaries windows (table, query, form, etc.). You can save an object (for example, a table layout), the window of which is on the screen, and assign it a name in the same way as you do with files: with the [File-Save] or [File-Save As...] command.

Working with tables

So, in the database window there is a list of existing objects DB - tables, queries, etc. You can work with the window of any object (child window) either in operational mode (for example, enter or view data in a table), or in designer(for example, change the table layout).

Before using any object (for example, filling a table with data), you must create this object (for example, create a table layout).

If the Table tab is selected, the tab window displays a list existing tables of this database.

To open an existing table, you need to highlight her name in this list and click the “Open” button.

Figure 1.3 shows the MS Access application window along with the Intercity database window. There are no objects in this file yet.

To be included in the database new tables, you need to click the “Create” button.

In order to fix layout existing table, select its name in the list and click the “Design” button.

The same operations are performed with all other MS Access objects.

Any object (table, query, form, report) can be created either manually (design mode) or using the Wizard.

When you call the Wizard, the corresponding sequence of dialog boxes opens. Having received answers to the questions asked, the Master performs all the necessary actions. For example, when generating a report, the Report Wizard will ask you what the report should look like and what data it should contain. After receiving the answers, the report will be completely ready. Using other Wizards, you can create database tables, queries and forms.

Create tables and queries manually(in design mode) ;

- create forms and reports using Masters.

Let's consider the sequence of steps when included in the database new tables. First you need to determine the subject area for which the database will be created. As a subject area, we define intercity telephone conversations, calculation of the amount of payment for long-distance telephone calls for individual subscribers. First of all, the fields that the table will contain are determined. Subscribers" database "Intercity". The required fields are shown in Table 3, with the “Subscriber Number” field selected as the key one. The key represents a field in the corresponding table. It uniquely characterizes each record contained within the table.

Table 3 – Fields of the “Subscriber” table

An ideal primary key is information such as customer number, account number, etc. Using such a key, it is easy to identify the corresponding record. Fields containing the customer name are not suitable for the primary key. It is always possible for several people to have the same name.

After determining the required fields, you can begin to create the actual table in MS Access.

To do this, you need to open the table designer window (Fig. 2.1) by selecting the Table tab and the line “Creating a table in design mode” or clicking the “Create” button.

In the upper part of the window that opens there is a created or modified layout table, which is simply a list of fields indicating the field name, data type and description. Each table field is one line at the top of this window (Figure 2.1).

Each table field should be assigned a unique name, determine the type of data that will be entered into it, and describe the contents of the field, i.e. enter text comments indicating the purpose of this field. The field name can have up to 64 characters. Description is an optional parameter and is used to further explain the field being used.

In the Field Name column (Figure 2.2) type an arbitrary Name. In the next column you must indicate data type for this field. The data type can be selected from the drop-down list that appears in this column (data types are described in Table 3). In addition, as soon as the cursor is in the Data Type column, a message appears at the bottom of the window. properties form(characteristics) of this field.

The properties form (lower part of the window) is a list of properties (on the left is the name of the property, on the right is meaning of this property) with a hint window for each property.

The list of properties changes depending on the data type that is currently displayed in the Data Type column. Clicking the mouse on the field values in the properties form, you can change this value (within the limits allowed for this data type). The field properties are listed in Table 4.

Table 4 – Field properties displayed in the table designer properties sheet

Property	Purpose
Field size	Sets the maximum number of characters to enter in this field
New values	Defines how counter values change when new records are added
Field Format	Specifies the format for displaying the values of this field
Number of decimal places	Determines the number of decimal places used when displaying numbers
Input mask	Specifies an input mask to make it easier to enter data into a field
Signature	Defines the text that appears as the field label
Default value	Allows you to specify a value that is automatically entered into the field when created new entry
Condition on value	Defines the requirements for data entered into the field
Error message	Allows you to specify the text of the message displayed on the screen if the entered data violates the condition defined in the property
Obligatory field	Indicates whether the field requires a value to be entered
Blank lines	Determines whether blank lines are allowed in this field
Indexed field	Defines an index created on a single field
Unicode Compression	Sets compression for fields that use Unicode encoding to store data (MEMO fields, hyperlinks, and text fields)

Most values are accepted by the system by default; some values can be selected from the drop-down list.

For text and number fields you must specify size fields, and for text this is the permissible length of the value (for example, 20 or 40 characters), and for a number - the representation format in the machine (byte, integer - two bytes, long integer, etc.). The maximum length of the text field is 255 characters. If you need to enter text that is longer than this value, you can use the data type Memo.

For the Date/Time field, you must specify a format so that the system knows how to process the entered data. For example, if you select Short Date Format, the system will expect you to enter exactly dates(in the Russian version - DD.MM.YYYY), and if you select “Short time format”, you will have to enter HH:MM (hours and minutes) in this field.

In the input mask property line, it is possible to create a mask according to which data will be entered. For example, for the field phone number you can set a mask 00-00-00 , according to which phone numbers will be entered.

As the value of the “Condition on value” property, you can specify verification rule, i.e. logical expression, which must evaluate to TRUE when entering data in this field. For example, if for the conversation date field you write:

The system recognizes as an error the entry into this field of any value equal to or greater than the current date.

In the following property, you can record an arbitrary error message that will be issued by the system, for example: “This date value is not valid” or “the date is invalid.” In the future, if the date is entered incorrectly, this message will appear on the screen.

The Required Field property can be set to Yes (blank values are not allowed) or No (blank values are allowed).

For primary table key (the “Subscriber Number” field) in the “Indexed Field” property, you need to select: “Yes, matches are not allowed,” and then click on the “Define Key” button in the toolbar (with the key image). This defines a key field, or simple index.

Having completed creating the table layout, you must save the created table and exit the table designer window. If you close the designer window without saving the layout, the system will remind you that you either need to give the table a name or cancel your work. Let's give the created table the name “Subscriber”.

If it is needed change(modify) the layout existing object, you need to select this object in the list of the corresponding tab of the database window and click the button Constructor. A window with a layout of the object will appear on the screen (the same as when creating it). You can correct the layout and save it under the same or a different name.

After creating the layout, data is entered into the table. By double-clicking on the table name, it will open in table mode and you can begin entering data (Figure 2.3). If the first field in the table is a key field of the Counter type, then the contents of this column are automatically increased by 1 when you go to input new line. This ensures that the column values are unique.

Access 2010 is a database creation and management program. To understand Access, you must first understand databases.

In this article, you will learn about databases and how they are used. You will learn the differences between managing data in Access and Microsoft Excel.

What is a database?

A database is a collection of data that is stored in a computer system. Databases allow their users to quickly and easily enter, access and analyze their data. They are such a useful tool that you see them all the time. Have you ever waited while a doctor's receptionist entered your personal information into a computer or watched a store employee use a computer to see if an item was in stock? Then you saw the database in action.

The easiest way to understand what a database is is to think of it as a collection of lists. Think about one of the databases mentioned above: a patient database in a doctor's office. What lists are contained in such a database? Well, for starters, there's a list of patient names. Then there is a list of past appointments, a list with medical history for each patient, a list contact information etc.

This applies to all databases - from the simplest to the most complex. For example, if you want to bake cookies, you can keep a database containing recipes you know how to make and friends you give those recipes to. This is one of the simplest databases. It contains two lists: a list of your friends and a list of cookie baking recipes.

However, if you were a professional baker, you would have many more lists to keep track of: a list of clients, a list of products sold, a list of prices, a list of orders... the list goes on. The more lists you add, the more complex the database will be.

Lists in Access are a little more complex than those you write on paper. Access stores its lists of data in tables, allowing you to store even more detailed information. In the table below, the list of people in the amateur baker database has been expanded to include other relevant information about friends.

If you're familiar with other programs in the Microsoft Office suite, this may remind you of Excel, which allows you to organize data in a similar way. In fact, you can create a similar table in Excel.

Why use a database?

If a database is essentially a collection of lists stored in tables, and you can create tables in Excel, why do you need a real database? While Excel is great at storing and organizing numbers, Access is much more efficient at handling non-numeric data such as names and descriptions. Non-numeric data plays a significant role in almost any database, and it is important to be able to sort and analyze it.

However, what databases really do, over and above any other way of storing data, is connectivity. We call a database like the ones you'll work with in Access a relational database. A relational database can understand how lists and the objects within them are related to each other. To explore this idea, let's go back to a simple database with two lists: your friends' names and cookie recipes you know how to make. You decide to create a third list to keep track of the batches of cookies you make and who they are for. Since you're just making them, you know the recipe, and you're only passing them along to your friends, this new list will get all of its information from the lists you've made previously.

See how the third list uses words that appeared in the first two lists? The database is able to understand that Ivan Ivanovich and Sour Cream Cookies in the list are the same things as Ivan Ivanovich and Sour Cream Cookies in the first two lists. This relationship seems obvious, and the person will immediately understand it. However, an Excel workbook cannot.

Difference between Access and Excel

Excel would treat all of these things as separate and unrelated pieces of information. In Excel, you will need to enter every single information about a person or type of cookie every time you mention it because this database will not be relative like an Access database. Simply put, relational databases can recognize what a person can do: if the same words appear in multiple lists, they refer to the same thing.

The fact that relational databases can process information in this way allows you to enter, search, and analyze data in more than one table at a time. All of these things would be difficult to do in Excel, but in Access, even complex tasks can be simplified and made quite user-friendly.