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

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

Если не открывается существующая база данных, при запуске Access открывается диалоговое окно, в котором предлагается открыть существующую базу данных или создать новую. Если необходимая база данных отсутствует в меню Открыть, следует нажать кнопку Дополнительно и выбрать нужный файл в диалоговом окне Открытие файла базы данных. В этом случае в диалоговом окне обычно по умолчанию отображается содержимое папки Мои документы. Найдя в списке необходимую базу данных, нужно выделить файл и нажать кнопку Открыть или дважды щелкнуть по элементу списка. В главном окне Microsoft Access появится окно выбранной базы данных.

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

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

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

Для создания проекта MicrosoftAccessи его подключения к существующей базе данныхMicrosoftSQLServerнужно щелкнуть по командеПроект с имеющимися данными.

Для создания базы данных MicrosoftSQLServerи подключения к ней базы данныхMicrosoftAccessнужно щелкнуть по командеПроект с новыми данными.

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

Для создания базы данных по предлагаемым шаблонам в области задач Создание файлав спискеШаблоныможно найти конкретный шаблон, введя его название в строке поиска в сети, найти шаблоны, имеющиеся на компьютере (нажавНа моем компьютере) или выбрать пунктШаблоны на узле Office Onlineдля обзора всех шаблонов, после выбора нужного нажать кнопкуЗагрузить.

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

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

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

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

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

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

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

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

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

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

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

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

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

• Мемо - тексты до 65 536 символов,

• числовой:

  • Байт- целые числа от 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%, экспоненциальный );

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

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

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

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

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

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

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

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

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

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

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

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

Сохранение новой таблицы выполняется по команде Файл/ Сохранить как.

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

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

• Создание резервной копии таблицы с другим именемвключает выделение таблицы в окне базы данных, выполнение командПравка/ Копировать, Правка/ Вставить, ввод нового имени таблицы;

• Удаление выделенной в окне базы данных таблицы командойПравка/ Удалить;

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

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

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

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

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

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

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

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

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

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

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

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

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

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

• Изменение высоты строк или ширины столбцовпутем перетаскивания мышью границ строк или столбцов в областях их маркировки, или командФормат/ Высота строки, Формат/ Ширина столбца;

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

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

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

• Вывод/Удаление сетки,разделяющей столбцы и строки, выбор режима оформления таблицы – по командеФормат/ Режим таблицы;

• Выбор шрифтовпо командеФормат/ Шрифт;

• Сохранение таблицы по командеФайл/ Сохранить;

• Добавление новой записи по командеВставка/ Новая запись, а после вводаЗаписи/ Сохранить запись;

• Выделение и изменение данныхвыполняется как обычно вWordилиExcel;

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

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

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

• Сортировку строк по нескольким полям по командеЗаписи/ Фильтр/ Изменить фильтр или кнопкойИзменить фильтр панели инструментов с указанием имен полей, по которым производится фильтрация с учетом дополнительных условий, налагаемых на значения этих полей;

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

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

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

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

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

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

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

Спомощьюавтоформ можно создавать формы, в которых выводятся все поля и записи базовой таблицы или запроса. Если выбранный источник записей имеет связанные таблицы или запросы, то в форме также будут присутствовать все поля и записи этих источников записей. Для создания автоформы нужно перейти на вкладкуФормыв окне базы данных,нажать кнопкуСоздатьна панели инструментов окна базы данных и выбрать в диалоговом запросеНовая формаодин из следующих мастеров:Автоформа: в столбец(каждое поле располагается на отдельной строке; подпись находится слева от поля).;Автоформа: ленточная(поля, образующие одну запись, расположены в одной строке; их подписи выводятся один раз в верхней части формы).;Автоформа: табличная(поля записей расположены в формате таблицы, где каждой записи соответствует одна строка, а каждому полю - один столбец; имена полей служат заголовками столбцов);Автоформа: сводная таблица (форма открывается в режиме сводной таблицы, имеется возможность добавлять поля путем их перетаскивания из списка полей в различные области макета);Автоформа: сводная диаграмма (форма открывается в режиме сводной диаграммы, имеется возможность добавлять поля путем их перетаскивания из списка полей в различные области макета).

В этом же окне выбирается таблица или запрос, для которой создается форма.

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

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

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

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

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

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

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

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

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

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

• Для создания запросов на основе обычного фильтра, фильтра по выделенному фрагменту или поля «Фильтр для», сохраните фильтр как запрос.

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

Создание запросов для выделенной в окне базы данных или открытой таблицы выполняется с помощью кнопки Новый объект/ Запроспанели инструментов или кнопкеСоздатьна вкладке Запросы базы данных. Создать запрос можно с помощью Мастера, либо самостоятельно (в режиме Конструктора).

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

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

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

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

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

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

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

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

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

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

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

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

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

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