Создание и работа с базой данных

ЗапускMicrosoft Access выполняется командой Пуск \ Программы \ Microsoft Office \ Microsoft Access, либодвойным щелчком по его значку или значку файла базы данных, имеющего расширение .accdb. Баз данных может быть много, каждой из них соответствует свой файл.

При первом запуске приложения Access, а также при закрытии базы данных без завершения работы Access отображается представление Microsoft Office Backstage.

Для создания новой базы данных необходимо перейти на вкладку Создать. На данной вкладке доступны команды создания базы данных:

1. Новая база данных. Создать базу данных можно с нуля. Это хороший вариант, если к структуре базы данных предъявляются очень специфичные требования или имеются данные, которые необходимо поместить или встроить в базу данных.
2. Шаблон, устанавливаемый вместе с Access. Шаблон можно использовать, если нужно быстро начать новый проект. В Access по умолчанию установлено несколько Образцов шаблонов.
3. Шаблон с сайта Office.com. В дополнение к шаблонам, поставляемым с Access, много других шаблонов доступно на сайте Office.com. Для их использования даже не нужно открывать браузер.

Одновременно можно работать только с одной базой данных, хотя Access предоставляет возможность обмена информацией (импорта / экспорта) с другими базами данных. В процессе работы в Access могут быть открыты окна различных объектов базы данных.

Область навигации содержит перечень имеющихся в базе данных объектов: таблиц, запросов, форм, отчетов, страниц, макросов и модулей, списки которых высвечиваются после щелчка по соответствующей вкладке. Выбрав одну из вкладок и выделив один из соответствующих вкладке объектов, с помощью кнопки Конструкторконтекстного меню, можно перейти к просмотру и изменению выбранной таблицы, запроса, формы, отчета, страницы, макроса, модуля в режиме Конструктор, а по кнопке Открыть – к просмотру и изменению выбранной таблицы, запроса, формы в режиме Таблица. Переход на вкладку Создание позволяет выбрать один из способов создания соответствующего объекта. Создание может осуществляться с помощью соответствующего Мастераили самостоятельно. Использование Мастеров существенно упрощает создание базы данных, однако не вскрывает всех возможностей Access.

Создание базы данных включает несколько этапов:

Этап I. Определение структуры базы данных, когда устанавливается, какие таблицы она должна включать и характер связей между таблицами, из каких полей состоит каждая таблица, какое поле является первичным ключом, каковы типи размеркаждого поля. Размер одного и того же поля во всех записях одинаков. Поэтому он должен быть достаточным для размещения содержимого поля в любой записи. В процессе работы можно дополнять таблицу новыми полями. Созданную таблицу необходимо сохранить, дав ей имя, уникальное в пределах создаваемой базы.

Создать новую таблицу и определить ее структуру можно, выбрав на вкладке Создание один из вариантов:

Определение структуры новой таблицы при ее создании в режиме Конструктора происходит в окне, имеющем следующий вид:

Здесь для каждого поля вводится его название, тип данных хранимых в поле (выбирается из раскрывающегося списка), описание, которое будет выводиться в строке состояния при выборе этого поля в режиме Таблица, свойства поля.

Различают следующие типы данных:

· текстовый (алфавитно-цифровые данные до 255 символов);

· Поле MEMO –тексты до 63,999 символов;

· числовой:

§ Байт – целые числа от 0 до 255 (занимает 1 байт);

§ Целое – целые числа от -32 768 до +32 767 (занимает 2 байта);

§ Длинное целое – целые числа от -2 147 483 648 до +2 147 483 647 (занимает 4 байта);

§ Одинарное с плавающей точкой (4 байта) – числа с точностью до 6 знаков в диапазоне от -3,402823E38 до -1,401298E-45 для отрицательных значений, от 1,401298E-45 до 3,402823E38 для положительных значений и 0;

§ Двойноес плавающей точкой (8 байт) – числа с точностью до 10 знаков от 1,79769313486232E308 до -4,94065645841247E-324 для отрицательных значений, от 4,94065645841247E-324 до 1,79769313486232E308 для положительных значений и 0;

§ Код репликации (GUID – глобальные уникальные идентификаторы) – используется при репликации базы данных (процесс создания двух или нескольких копий (реплик) одной базы данных Access) для создания уникальных идентификаторов, обеспечивающих синхронизацию реплик (16-байтовое поле);

§ Действительное – для сохранения десятичных значений в диапазоне от до ;

· Дата/Время –дата и время, относящиеся к годам с 100 по 9999 (8 байт);

· Денежныйс 4 знаками после запятой (занимает 8 байт);

· Счетчик– уникальное длинное целое, генерируемое Access при создании каждой новой записи;

· Логический –логические данные (1 бит);

· Поле объекта OLE– картинки, диаграммы и другие объекты из приложений Windows до 1 Гбайт;

· Гиперссылка– сохраняет до 64 000 знаков;

· Мастер подстановок –создает поле, позволяющее выбрать значение из другой таблицы или из списка значений, используя поле со списком. При выборе данного параметра в списке типов данных запускается мастер для автоматического определения этого поля. Например, если в таблице Предложения определить поле № Поставщика мастером подстановок, то в нем можно будет выводить названия поставщиков из таблицы Поставщики. Например, если в таблице Предложения определить поле № Товара мастером подстановок, то в нем можно будет выводить названия товаров из таблицы Товары.

Свойства полей зависят от выбранного типа данных и включают:

· размер поля – данная процедура используется для задания максимального числа символов, которые могут быть введены в текстовое поле, а также для указания диапазона и типа числовых данных, которые разрешается вводить в числовое поле;

· формат поля:

§ для Текстового и Мемо типов данных пользователь может задать свой формат;

§ для типов Числовой, Денежный, Счетчик существует стандартный набор форматов (основной 12345,678, денежный 1 234,56 р., евро 1 234,56€, фиксированный 1234,56, с разделителями разрядов 1 234,56, процентный 20%, экспоненциальный 1.05×10³);

§ для типа Дата/Время существует стандартный набор форматов (полный 15.04.94 05:30:10 РМ, длинный формат даты Среда, 15 апреля 1994, средний формат даты 15-апр-94, краткий формат даты 15.04.94, длинный формат времени 05:30:10 РМ, средний формат времени 5:30 РМ, краткий формат времени 17:30);

§ для логического типа Да/Нет, Истина/Ложь, Вкл/Выкл;

· число десятичных знаков;

· маска ввода, которая будет видна при вводе данных в это поле, например для даты (__.__.__);

· подпись поля – более описательное имя поля, которое будет выводится в формах и отчетах;

· значение, которое выводится в поле по умолчанию;

· условие на значение, например, <100 означает, что значение поля должно быть меньше 100;

· сообщение об ошибке выводится на экран, когда вводимое значение не удовлетворяет условию на значение;

· обязательное поле –определяет обязательность ввода данных;

· пустые строки– ввод пустой строки путем ввода пары прямых кавычек ("") означает «известно, что значение отсутствует»;

· индексированное поле определяет построение для поля индекса.

Чтобы определить выделенное поле в качестве первичного ключа необходимо щелкнуть по кнопке Ключевое поле вкладки Конструктор или использовать команду контекстного меню.

Сохранение новой таблицы выполняется нажатием на кнопку панели быстрого доступа, либо командой контекстного меню, вызываемого нажатием правой кнопки мыши на заголовке таблицы.

Перед модификацией таблиц следует учесть, что MS Access автоматически не вносит изменения таблиц в использующие их другие объекты базы данных; нельзя менять тип данных для поля, используемого для связи таблиц; прежде чем открыть таблицу в режиме Конструктора для внесения изменений, надо закрыть все ссылающиеся на нее открытые объекты базы данных.

Модификация может включать:

· Удаление базы данных, выполняемое в Проводнике Windows путем удаления файла базы данных с расширением .accdb;

· Создание резервной копии таблицы с другим именем включает выделение таблицы в Области навигации, выполнение команд Ctrl+C, Ctrl+V, ввод нового имени таблицы;

· Удаление таблицы, выделенной в Области навигациикомандой контекстного меню Удалить;

· Переименование полей таблицы выполняется в режиме Конструктора редактированием имени поля; в режиме Таблицы – двойным щелчком по имени столбца и вводом нового имени;

· Вставка полей выполняется в режиме Конструктора путем установки курсора в строку следующего за вставляемым поля и выполнения команды раскрывающегося меню Вставить строки с последующим заполнением появившейся чистой строки атрибутами нового поля;

· Копирование полей выполняется в режиме Конструктора и предполагает выделение строки копируемого поля (щелчком по области маркировки строки), копирование строки в буфер обмена командой контекстного меню Копировать, перемещение курсора в строку, перед которой будет вставляться поле, выполнение команды раскрывающегося меню Вставить строки,перевод курсора в чистую строку и выполнение команды контекстного меню Вставить;

· Удаление полей в режиме Конструктора сводится к их выделению и нажатию клавиши Delete;

· Перемещение полей в режиме Конструктора сводится к выделению поля и перетаскиванию его в новое место, которое будет выделено жирной межстрочной линией;

· Изменение типа данных поля и его свойств выполняется в режиме Конструктора путем прямого редактирования, но с учетом ограничений на изменение типов;

· Расщепление таблицы на две предполагает создание резервной копии таблицы с новым именем, удаление из обеих таблиц лишних полей, редактирование связей таблиц, как это будет рассмотрено ниже;

· Объединение таблицвыполняется путем сохранения результата запроса, включающего поля обеих таблиц, в виде таблицы;

· Изменение ключевого полятаблицы включает выделение строки первичного ключа, щелчок по кнопке Ключевое поле, выделение строки поля, которое должно стать первичным ключом, щелчок по кнопке Ключевое поле;

· Для обеспечения оптимальной производительности периодически после переопределения объектов базы данных выполняется команда Работа с базами данных / Сервис / Сжать и восстановить базу данных.

Связи между таблицами для дальнейшего использования в запросах, формах и отчетах устанавливаются после нажатия на вкладке Работа с базами данных кнопки . Чтобы установить связь между двумя таблицами, например Поставщики и Предложения, необходимо в окне Схема данных щелкнуть по полю № Поставщика таблицы Поставщики, перетащить и бросить его на поле № Поставщика(такое поле называют внешним ключом) таблицы Предложения с последующей отметкой в открывающемся диалоговом окне Изменение Связей флажка Обеспечение целостности данных и нажатием кнопки Создать. При этом в окне Схема данных между таблицами будет проведена линия с символом 1 со стороны таблицы Поставщики и символом ¥ со стороны таблицы Предложения. Для удаления связи надо щелкнуть по ее линии и нажать клавишу Delete. Для изменения существующей связи надо дважды щелкнуть по ее линии, чтобы открыть диалоговое окно Изменение Связей. Для сохранения установленных связей надо закрыть окно Схема данных и подтвердить сохранение. Для рассматриваемой базы данных схема данных выглядит следующим образом:

Этап II. Ввод данных в таблицу выполняется в режиме Таблица, в который можно перейти двойным щелчком по имени закрытой таблицы в Области навигации или выделив таблицу и нажав кнопку Открыть контекстного меню. Данные таблиц Поставщики, Товары и Предложения представлены в следующих окнах:

Работа с таблицей в режиме Таблица предусматривает:

· Перемещение по таблице путем переноса курсора или с помощью линеек прокрутки или поля номера записи в нижней левой части окна таблицы ;

· Изменение высоты строк или ширины столбцов путем перетаскивания мышью границ строк или столбцов в областях их маркировки;

· Изменение порядка расположения столбцов путем выделения столбца (щелчок в области маркировки) и перетаскивания его области маркировки в нужное место;

· Выводна экран только необходимыхстолбцов достигается выделением ненужных столбцов и выполнением команды раскрывающегося меню Скрыть поля с последующим восстановлением по команде раскрывающегося меню Отобразить поля (столбец можно убрать, перетащив его правую границу влево до исчезновения столбца);

· Фиксацию столбцов для их постоянного присутствия на экране путем выделения столбцов и выполнения команды раскрывающегося меню Закрепить поля,отмена производится командой Отменить закрепление всех полей;

· Выбор шрифтов на вкладке Главная;

· Сохранение таблицыкнопкой ;

· Выделение и изменение данных выполняется так же, как в MS Word или в MS Excel;

· Копирование и вставку данных через Буфер обмена;

· Удаление строк достигается их выделением в области маркировки и клавишей Delete;

· Сортировку строк в порядке возрастания или убывания данных в столбце, где установлен курсор, выполняемую с помощью кнопок По возрастаниюили По убыванию группы Сортировка и фильтр на вкладке Главная;

· Сортировку строк по нескольким полямосуществляемую нажатием на кнопку Фильтр группы Сортировка и фильтр вкладки Главная с указанием имен полей, по которым производится фильтрация с учетом дополнительных условий, налагаемых на значения этих полей;

· Предварительный просмотр таблицыперед печатью выполнением команды Файл/ Печать / Предварительный просмотр.

· Печать таблицы с помощью команды Файл/ Печать / Печать.

Для ввода данных в таблицу можно также использовать формы, которые упрощают как ввод, так и вывод отдельных блоков данных. Формы позволяют выводить на экран не все поля блока данных, благодаря чему существует возможность блокирования доступа к конфиденциальной информации.

Создать форму можно одним из ниже перечисленных способов:

· Создание формы с помощью Автоформы. После выбора источника записей Автоформа создает форму, которая использует все поля источника записей и все поля из подчиненных источников записей.

· Создание формы с помощью мастера. Мастер задает подробные вопросы об источниках записей, полях, макете, требуемых форматах и создает форму на основании полученных ответов.

· Создание формы без помощи мастера(в режиме Конструктора).

С помощью автоформможно создавать формы, в которых выводятся все поля и записи базовой таблицы или запроса. Если выбранный источник записей имеет связанные таблицы или запросы, то в форме также будут присутствовать все поля и записи этих источников записей. Для создания автоформы нужно перейти на вкладку Созданиеи нажать на кнопку .

Если выбранный источник записей имеет связанные таблицы, автоформа добавит элемент управления «Подчиненная форма», выводящий связанные таблицы в виде таблиц и подтаблиц.

Процесс создания формы с помощью мастера и создания формы без помощи мастера (в режиме конструктора) подробно описан в соответствующих разделах справки по Microsoft Access.

Этап III. Поискинформации в таблицах осуществляется с помощью кнопки вкладки Главная. Опция Замена, которую можно вызвать либо щелчком по соответствующей вкладке в диалоговом окне Поиск и замена, либо командой Главная / Найти / Заменить, действует аналогично Поиску, с тем отличием, что она не просто находит необходимые данные, но и меняет их на информацию, введенную в соответствующее поле.

Использование Фильтров позволяет из всего множества записей в таблице выбрать лишь те, которые удовлетворяют заданным пользователем параметрам. Более подробную информацию по работе с фильтрами можно получить в соответствующих разделах справки Access.

Поиск информации в базе данных может осуществляться также на основании условий отбора, содержащихся в запросе. Примеры составления условий отбора см. в справочной системе Access. Эти условия применяются к содержимому определенных полей таблиц. В ряде случаев Access предлагает автоматическое создание запросов и пользователю не приходится самостоятельно их создавать.

Без использования мастеров создаются запросы: запрос на выборку; запрос с параметрами; перекрестный запрос; запросы на изменение (создание таблицы, удаление, добавление и обновление записей); запрос на автоподстановку значений.

Мастера используются для создания запросов: простой запрос на выборку, перекрестный запрос, запрос на поиск повторяющихся записей, запрос на поиск записей без подчиненных записей в других таблицах.

· Для создания запроса, являющегося основой формы или отчета, попытайтесь использовать мастер форм, мастер отчетов. Мастера создают формы и отчеты для объектов, основанных на нескольких таблицах.

· Чтобы упростить создание запросов, которые будут выполняться независимо или использоваться как базовые для нескольких форм или отчетов, пользуйтесь мастерами запросов. Мастера запросов автоматически выполняют основные действия в зависимости от ответов пользователя на поставленные вопросы. Если было создано несколько запросов, мастера можно также использовать для быстрого создания структуры запроса. Для настройки запроса переключитесь в режим Конструктора.

· Для создания запросов на основе фильтра, сохраните фильтр как запрос (последовательно выполнив команды Главная / Сортировка и фильтр / Дополнительно / Расширенный фильтр и Главная / Сортировка и фильтр / Дополнительно / Сохранить как запрос).

Если ни один из перечисленных методов не удовлетворяет требованиям, запрос можно создать самостоятельно в режиме Конструктора.

Создание запросов для выделенной в Области навигации или открытой таблицы выполняется с помощью кнопок группы Запросы, расположенной на вкладке Создание.

Например, окно запроса на выборку дешевых товаров с ценой менее той, что будет введена при ответе на диалоговый запрос

с выводом наименования поставщика, товара и цены в режиме Конструктора имеет следующий вид:

Результаты работы такого запроса при верхней границе цены, равной 70, представлены следующей таблицей:

Этап IV.Формирование отчетов для печати в виде полноценных документов с использованием средств форматирования и дополнительной обработки данных. Существуют следующие варианты:

· Создание отчета с помощью Автоотчета. Автоотчет создает отчет, который использует все поля источника записей и применяет последний использованный автоформат;

· Создание отчета с помощью Мастера. Мастер задает подробные вопросы об источниках записей, полях, макете, требуемых форматах и создает отчет на основании полученных ответов;

· Создание отчета без помощи мастера – самостоятельное создание отчета в режиме Конструктора.

Создание отчетов для выделенной в окне базы данных или открытой таблицы или запроса выполняется с помощью кнопок группы Отчеты, расположенной на вкладке Создание.

Простой отчет для приведенного выше запроса выглядит следующим образом.

В MS Access 2010 присутствует возможность расчета итогов и анализа данных с помощью сводных таблиц и сводных диаграмм. В более ранних версиях MS Access данной возможности не было, приходилось экспортировать данные в MS Excel и проводить анализ там.

В режиме сводной таблицы имеется возможность просматривать исходные данные или сводные данные, упорядочивая поля в областях фильтра, строк, столбцов и данных. Пользователи могут выбирать и организовывать данные с различными уровнями детализации и задавать макет, перетаскивая поля и элементы, которые выбираются в раскрывающихся списках полей.

Сводная диаграмма – это средство графического анализа данных из объекта в таблице или форме.

В режиме сводной диаграммы можно визуально представлять данные, выбрав тип диаграммы и упорядочивая поля в областях фильтра, рядов, категорий и данных. Пользователи могут просматривать данные с различными уровнями детализации и задавать макет, перетаскивая поля и элементы, которые выбираются в раскрывающихся списках полей.

Для создания сводных таблиц и диаграмм следует перейти на вкладку Создание, в группе Формы нажать на кнопку Другие формы и выбрать команду Сводная таблица или Сводная диаграмма.

Вопросы для самоконтроля

1. Что такое база данных?

2. Как создать базу данных?

3. Как создать таблицу?

4. Как изменить названия полей, типы полей таблицы и порядок полей?

5. Какие типы данных используются в Access и для чего они применяются?

6. Для чего применяется тип данных Счетчик?

7. Что такое ключевое поле?

8. Как создать другую таблицу, используя в ней столбец подстановок.

9. Для чего применяется схема данных?

10. Как изменить связь между таблицами?

11. Как отфильтровать данные по условию?

12. Как отсортировать таблицу по убыванию?

13. Как сохранить отсортированную таблицу?

14. Как создать запрос?

15. Как создать запрос, который выводит все записи, содержащие в заданном поле букву, введенную с клавиатуры?

16. Как создать запрос, который суммирует все значения по одному полю.

17. Как создать запрос на удаление?

18. Как создать форму?

19. Как создать отчет?