4.6 Основные объекты ms Access

Таблицы

Таблицы в MS Access создают с помощью шаблонов таблиц и полей, в режиме таблицы путем ввода данных и в режиме конструктора.

Шаблоны таблиц и полей используются для быстрого создания таблицы. Шаблон таблицы – это пустая заготовка таблицы с заданной структурой, то есть, составом и характеристикой полей. После выбора шаблона пользователь при необходимости может изменить структуру в соответствии со своими требованиями. Можно добавить поля из области задач Шаблоны полей. Шаблон поля — это предопределенное поле, которое включает имя поля, тип данных, значение свойства поля Формат и другие свойства поля. Выбранные поля перетаскиваются на таблицу методом «drag and drop» (или двойным щелчком мыши). Далеепользователь заполняет таблицу конкретными данными с помощью клавиатуры.

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

Режим таблицыдает возможность проектировать и заполнять таблицу данными при ее наглядном отображении на экране. Этот режим активизируется по умолчанию при открытии новой БД или устанавливается при использовании элементаТаблицаво вкладкеСоздание интерфейсаЛента. MS Access автоматически создает первое полеКодс типомСчетчик. В режиме таблицы доступны многие возможности режима конструктора, например, добавление и удаление полей, установка типов полей.

Схема данных

Создать связь между таблицами можно двумя способами:

• с помощью окна Связи

• с помощью перетаскивания поля из области Список полей в таблицу.

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

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

Первый способсвязывания таблиц выполняется в окнеСвязи. Для этого на вкладкеРабота с базами данныхв группеОтображениевыбирается пунктСхема данных.

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

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

Чтобы создать отношение «один-к-одному», оба общих поля должны иметь уникальный индекс. Это означает, что свойства Индексированноеэтих полей должны иметь значения Да (Совпадения не допускаются).

Чтобы создать отношение «один-ко-многим», поле на одной стороне отношения (как правило, поле первичного ключа) должно иметь уникальный индекс. Поле на стороне «многие» не должно иметь уникального индекса. Это означает, что свойство Индексированоэтого поля должно иметь значениеНет,либоДа(Допускаются совпадения).

При втором способеиспользуется областьСписок полей, которая отображает поля, доступные в связанных таблицах, а также поля, доступные в других таблицах БД. При перетаскивании поля из «другой» (несвязанной) таблицы и выполнении инструкций мастера подстановок автоматически создается новое отношение «один ко многим» между таблицей в областиСписок полейи текущей таблицей.

Запросы

Запрос представляет собой обращение к данным для получения информации и выполнения действий с данными.

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

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

Имя таблицы. В каждом столбце в этой строке отражается имя источника данных для находящегося в нем поля.

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

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

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

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

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

Различают два типа запросов к БД: запросы на изменение и запросы на выборку.

Запросы на изменениявключают четыре типа:запрос на добавление, запрос на удаление, запрос на обновление и запрос на создание таблицы

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

Кроме этого, существует три основных типа запросов SQL:запрос на объединение, запрос к серверу и управляющий запрос.

Рассмотрим некоторые типы запросов.

Запрос на удалениепредназначен для удаления записей из одной таблицы или нескольких таблиц, связанных отношениями «один-к-одному» и «один-ко-многим». При проектировании запроса в бланк запроса помещаются только те поля, по которым будут указаны условия отбора на удаление.

Запрос на обновлениеиспользуют в том случае, если необходимо обновить значения полей на новые. Например, для всех работников увеличивается премия на 10%. При проектировании запроса в бланке запроса в строкеОбновление в поле Премия, значения которого требуется изменить, вводится выражение, обеспечивающее замену прежних значений на новые: [Премия]*1,1. Если премия изменяется только для сотрудников со стажем больше 15 лет, то дополнительно в строкуУсловие отборав поле Стаж вводится критерий для отбора нужных записей: > 15.

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

Итоговый запрос.Создается с целью выполнения расчетов в отобранных группах записей. К итоговым операциям относятся:

Sum – суммирование отобранных значений поля;

Avg – вычисление арифметического среднего отобранных значений поля;

Min –нахождение минимального значения среди отобранных значений поля;

Max – нахождение максимального значения среди отобранных значений поля;

Count –вычисление количества отобранных значений в поле;

StDev –расчет стандартного отклонения для отобранных значений поля;

Var –расчет дисперсии для отобранных значений поля;

First –отображение значения поля в первой отобранной записи;

Last –отображение значения поля в последней отобранной записи.

Параметрический запрос. Позволяет задавать разные условия отбора записей непосредственно при запуске запроса. При проектировании запроса в строкуУсловие отборадля заданного поля вводится приглашением на ввод параметра. Текст приглашения заключается в квадратные скобки. Например, [Введите фамилию сотрудника]. При появлении на экране этого приглашения пользователь должен набрать на клавиатуре и ввести конкретную фамилию. В результате запроса на экран выводятся сведения по указанному сотруднику.

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

Условия отбора

При создании запросов важно правильно сформулировать условия отбора записей из БД. В MS Access доступны следующие возможности:

• простой критерий выборки;

• точное несовпадение значений одного поля;

• неточное совпадение значений поля;

• выбор по диапазону значений;

• объединение критериев нескольких полей;

• условие отбора для результатов итоговых вычислений.

Простой критерий выборки. Записи выбираются по совпадающим значениям поля. Например, из поляГороднеобходимо выбрать значения Минск. Для этого в бланке запроса в строкеУсловие отборав графеГородвводится с клавиатуры значение «Минск».

Точное несовпадение значений одного поля. Из базы выбираются все записи, кроме тех, для которых задано условие. Например, необходимо выбрать все записи с полемГород, кроме тех, которые в этом поле имеют значениеМинск. Для этого в строкеУсловия отборав графеГородвводится выражениеNot «Минск» или <> «Минск». Логический операторNot исключает записи со значениемМинск, оператор сравнения <> означает «не равно».

Неточное совпадение значений поля. Такое условие можно задавать, если не известны значения полей. Для выборки используется оператор сравненияLike (подобный). Рядом с оператором записывается образец, содержащий или точное значение, например, Like«Петров»,или включающий символы шаблонов, например,Like «Пет*».

Access допускает следующие символы шаблонов:

? - любой один знак;

* - ноль или более знаков;

# - любая одна цифра;

[список знаков] - любой один знак в списке знаков;

[!список знаков] - любой один знак, не входящий в список.

Кроме списка знаков в квадратные скобки может заключаться диапазон символов, например, [Б-Р]. Условие [б-рБ-Р] позволяет выбрать как заглавные, так и прописные буквы.

При условии Like «[БР]*»выбираются все фамилии, которые начинаются на Б или Р.

Выбор по диапазону значений.Для задания диапазона значений используются операторы:

> (больше),

>= (не менее, больше или равно),

< (меньше),

<=(не более, меньше или равно) (например, >= 10).

Between ... аnd ... (служит для проверки принадлежности диапазону, верхняя и нижняя граница которого соединена логическим оператором AND (например, between 1990 and 1995).

Операторы можно употреблять с текстовыми и цифровыми полями, а также с полями дат.

Объединение критериев одного поля.Если на одно поле налагается более одного условий, то условные выражения могут быть соединены с помощью операторовOr(ИЛИ) иAnd (И).

Объединение критериев нескольких полей. В запросе может быть несколько условий отбора. В этом случае имеют место два варианта выборки записей:

запись выбирается только при выполнении всех условий, что соответствует логической операции И. Запрос называетсяИ-запросом;

запись выбирается при выполнении хотя бы одного условия, что соответствует логической операции ИЛИ. Запрос называетсяИЛИ-запросом.

При построении ИЛИ-запросакаждое условие, входящее в критерий, должно располагаться на отдельной строке. При построенииИ-запросакаждое условие, входящее в критерий, должно располагаться в одной строке.

В итоговых запросахсуществуют два типа критериев отбора записей.

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

Формы

Приложение MS Access создает формы с помощью нескольких средств: форма, разделенная форма, несколько элементов, мастер форм, пустая форма, конструктор форм.

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

Если MS Access обнаруживает одну таблицу, связанную отношением «один-ко-многим» с таблицей или запросом, который использовался для создания формы, MS Access добавляет таблицу данных в форму, основанную на связанной таблице или запросе. Например, если создается простая форма, основанная на таблице «Сотрудники», и между таблицами «Сотрудники» и «Заработная плата» определено отношение «один-ко-многим», то в таблице данных будут отображаться все записи таблицы «Заработная плата», относящиеся к текущей записи сотрудника. Если таблица данных в форме не нужна, ее можно удалить. Если существует несколько таблиц, связанных отношением «один-ко-многим» с таблицей, которая использовалась для создания формы, то данные таблицы в форму не добавляются.

Разделенная форма – позволяет одновременно отображать данные в двух представлениях  – в режиме формы и в режиме таблицы.

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

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

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

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

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

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

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

Инструментом конструирования формы являются элементы управления.Наиболее часто используемый элемент управления – поле. К другим элементам управления относятся: надписи, флажки, элементы управления подчиненных форм и отчетов и другие. Элемент управления «поле» может быть присоединенным, свободным и вычисляемым.

• Присоединенный элемент управления  – элемент управления, источником данных которого служит поле таблицы или запроса. Присоединенный элемент управления формируется посредством окна Список полей и служит для отображения значений полей источника данных. Это наилучший способ создания присоединенного элемента управления по двум причинам:

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

• присоединенный элемент управления наследует значения свойств полей источника данных, например, Формат, Число десятичных, Маска ввода.

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

• Вычисляемый элементы управления  – элемент управления, источником данных которого является выражение, а не поле.

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

Для изменения форм используются режимы макета и конструктора.

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

Режим конструктора  Режим конструктора позволяет более подробно просмотреть структуру формы. Можно просматривать разделы колонтитулов и данных формы. В этом режиме форма не выполняется, поэтому при внесении изменений невозможно просматривать базовые данные. Однако в режиме конструктора удобнее выполнять другие работы:

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

• изменять источник элемента управления «Поле» непосредственно в поле без использования окна свойств.

• изменять размеры разделов формы, таких как «Заголовок формы» или «Область данных».

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

Отчеты

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

Приложение MS Access создает отчеты следующими средствами: отчет, мастер отчетов, пустой отчет, конструктор отчетов.

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

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

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

Приложение MS Access имеет средство Мастер наклеек, которое помогает создавать наклейки большинства стандартных размеров. Источником записей для наклеек служит таблица или запрос.

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

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

Конструктор отчетов. Структура отчета, как и структура формы, имеет несколько разделов: Заголовок отчета, Верхний колонтитул, Заголовок группы, Область данных, Примечание группы, Нижний колонтитул, Примечание отчета.

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

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

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

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

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

• Нижний колонтитул.   Располагается внизу каждой страницы. Используется для нумерации страниц и для печати постраничной информации.

• Примечание отчета. Примечание отчета можно использовать для печати итогов и другой сводной информации по всему отчету. Печатается один раз в конце отчета.

В проект отчета можно вносить изменения в режимах макета и конструктора.

Просматривать отчет можно различными способами:

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

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

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

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