Текст книги "Искусство создания базы данных в Microsoft Office Access 2007"

В Access существуют следующие типы запросов на выборку:

• однотабличный запрос. Выбор подмножества данных из одной таблицы (выбор полей и выбор записей, удовлетворяющих заданным условиям);

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

• запрос с параметром. Выбор записей по заданному значению некоторого поля (параметра). Это значение запрашивается с экрана у пользователя. Параметров может быть насколько;

• итоговый запрос. В таком запросе выполняются статистические расчеты;

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

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

• запрос на выборку повторяющихся записей. Отбор записей-дубликатов в таблице;

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

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

В Access возможны такие типы запросов на изменение:

• запрос на создание таблицы. Создает новую базовую таблицу, в которую копируются записи существующей таблицы;

• запрос на обновление. Разновидность диалогового окна Поиск и замена с более широкими возможностями (несколько условий отбора и изменение записей сразу в одной, двух и более таблицах);

• запрос на добавление. Позволяет добавить набор записей из одной или нескольких таблиц в одну или несколько результирующих таблиц;

• запрос на удаление. Удаление набора записей целиком из одной или нескольких таблиц.

Существуют три инструмента создания запроса: Мастер запросов, Конструктор запросов, программирование на SQL. Любой запрос, построенный с помощью Мастера запросов, может быть доработан в Конструкторе запросов и с помощью SQL. Возможен переход из Конструктора запросов в окно SQL и наоборот.

Мастер запросов позволяет быстро построить определенные типы запросов:

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

• перекрестный запрос;

• запрос на выборку повторяющихся записей;

• запрос на выборку записей без подчиненных.

Чтобы создать запрос при помощи Мастера запросов нужно поступить так.

1. Открыть базу данных.

2. Перейти на вкладку Создание и в ней на группу команд Другие.

3. Щелкнуть команду Мастер запросов. Откроется диалоговое окно Новый запрос.

4. Выбрать тип запроса и следовать указаниям мастера.

5. Задать имя запроса, под которым следует его сохранить.

Универсальным средством создания и модификации запросов в Access является Конструктор запросов. Конструктор запросов позволяет создавать все типы запросов. Запросы в нем формулируются на языке QBE – языке запросов по образцу.

☞ Примечание. QBE (Quere By Example) – непроцедурный язык манипулирования данными, с помощью которого пользователь указывает, какие данные необходимо отобразить на экране, не уточняя процедуру их формирования. Был разработан отделением IBM Research в 1977 году и задуман как средство, облегчающее работу неспециалистов. В настоящее время реализован практически во всех реляционных СУБД. В Access всякий QBE-запрос автоматически преобразуется в предложения структурированного языка запросов SQL (Structured Query Language). Язык SQL является также непроцедурным языком манипулирования данными, но уровнем ниже, чем QBE. Первый стандарт языка SQL вышел в 1987 году. Автором SQL считается математик Э. Кодд – сотрудник фирмы IBM.

Для вызова Конструктора запросов следует осуществить следующие шаги.

1. Открыть базу данных.

2. Найти вкладку Создание и в ней группу команд Другие.

3. Щелкнуть команду Конструктор запросов. Откроется бланк конструктора и диалоговое окно Добавление таблицы.

4. Указать в окне Добавление таблицы имена таблиц, из которых необходимо извлечь данные. Заметим, что в данном окне доступны как базовые таблицы, так и запросы. Это позволяет решить проблему вложенных запросов.

5. Закрыть окно Добавление таблицы. На ленте инструментов откроется вкладка Конструктор с четырьмя группами команд (табл. 2.1). Используя эти команды, приступить к заполнению бланка конструктора. Сформированный запрос сохранить.

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

1. Щелкнуть кнопку Microsoft Office.

2. Выбрать команду Сохранить.

3. Ввести имя запроса, если запрос сохраняется впервые. Щелкнуть кнопку Ok.

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

Таблица 2.1

Всякий созданный запрос, когда он сохранен под некоторым именем, появляется в области переходов в группе Запросы. Дальше с запросом можно работать. Эта работа может сводиться к выполнению таких действий:

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

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

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

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

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

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

Запрос как всякий объект базы данных обладает свойствами, которые влияют на результаты его выполнения. Эти свойства выводятся в Окне свойств, которое отображается в режиме Конструктор запросов при нажатии команды Страница свойств. Поскольку запрос можно рассматривать как виртуальную таблицу, то свойства запроса во многом схожи со свойствами таблицы. Наиболее важные свойства запроса представлены в табл. 2.2.

Таблица 2.2

☞ Ограничение. Свойства Уникальные записи и Уникальные значения связаны таким образом, что только для одного из них может быть установлено значение Да. Например, если для свойства Уникальные значения задается значение Да, то Access автоматически устанавливает значение Нет для свойства Уникальные записи. Однако значение Нет можно задать для обоих этих свойств одновременно.

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

Таблица 2.3

Бланк (окно) конструктора разделяется на две области:

• область схемы. Расположена в верхней части бланка. Отражает источник записей запроса – базовые таблицы и запросы, из которых осуществляется выбор полей, а также связи между таблицами. Каждая таблица представляется отдельным окном, с указанием всех доступных полей. Межтабличные связи отражаются в полном соответствии со схемой данных, определенной при создании базы данных;

• область сетки. Расположена в нижней части бланка. Содержит сетку, аналогичную электронной таблице, в которой задается описание запроса.

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

В области сетки можно задать следующие особенности запроса:

• отображаемые поля;

• имя таблицы, в которой находится поле;

• выражения, для вычисляемых полей;

• порядок сортировки записей в запросе;

• условия отбора записей;

• условия группировки записей, включая статистические функции;

• новые значения для запросов на обновление или добавление;

• имена полей-получателей в запросах на добавление.

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

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

Таблица 2.4

1. Вызвать Конструктор запросов.

2. Выбрать в окне Добавление таблицы запросы и базовые таблицы, из которых необходимо извлечь данные.

3. Закрыть окно Добавление таблицы. На ленте инструментов откроются вкладка Конструктор и бланк конструктора.

4. Установить в области схемы для каждой межтабличной связи параметры объединения (режим соединения) таблиц. Напомним, по умолчанию записи таблиц соединяются при равных значениях полей связи.

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

6. В строке Сортировка указать порядок сортировки. Допускается сортировка по нескольким полям. Если сортировка выполняется по двум и более полям, то порядок перечисление полей в сетке (слева направо) задает, какие из них будут использоваться в качестве внутренних и какие в качестве внешних.

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

8. В строке Условие отбора задать фильтр, по которому следует отобрать записи в результат запроса.

9. Запрос сохранить.

☞ Примечания:

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

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

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

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

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

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

Запись условия во многом зависит от типа поля, к которому оно относится. Например, текстовые литералы заключаются в прямые кавычки «"», а даты – в символы «#». Однако это делать необязательно, Access автоматически ставит кавычки для текстов и символы # для дат, если они не указаны. Правила составления выражений описаны в прил.1. Там же даны краткие сведения об идентификаторах, функциях, операторах и константах Access. Примеры задания простых условий для полей с типами данных Текстовый, Поле MEMO приведены в табл. 2.5. Примеры описания условий для полей типа Числовой, Денежный, Счетчик представлены в табл. 2.6. Условия для полей типа Дата/время показаны в табл. 2.7.

Таблица 2.5

Если поле имеет тип данных Логический, то указание для него в строке Условие отбора значения Да (или Нет), приведет к тому, что в результат будут включены все записи, у которых установлен (снят) соответствующий флажок. Для типа данных Поле подстановок задание условий отбора зависит от типа подстановки. Если столбец подстановок представляет собой фиксированный набор значений, то в этом случае Поле подстановок имеет тип данных Текстовый, поэтому к нему можно применять те же условия, что и к другим текстовым полям (табл. 2.5). Если столбец подстановок – список значений из другой таблицы или запроса, то Поле подстановок наследует тип значений списка. С учетом именно этого типа и задаются условия отбора.

Таблица 2.6

Таблица 2.7

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

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

1. Создать запрос на выборку. Открыть запрос в Конструкторе запросов.

2. В строке Условие отбора поля, для которого нужно применить параметр, ввести текст сообщения для диалогового окна. Этот текст должен быть заключен в квадратные скобки. Например,

[ВведитеДатуПоставкиТовара]

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

3. Повторить шаг 2 для каждого параметра, который должен использоваться в запросе.

Таким образом, можно задавать не только отдельное значение параметра, но и диапазон. Например, диапазон дат в строке Условие отбора можно указать так:

Between [ВведитеНачальнуюДату] And [ВведитеКонечнуюДату]

Здесь мы имеем по существу два параметра для одного поля.

При вводе значений в диалоговое окно Введите значение параметра следует учитывать тип поля, для которого определен параметр. Для текстового типа используемый регистр не играет значение. При создании запроса с параметром можно применять подстановочные знаки. Например, если требуется выбрать студентов или сотрудников по первой букве фамилии, для поля Фамилия можно указать условие отбора в виде:

Like [ВведитеПервуюБуквуФамилии] & ^*

Текст «ВведитеПервуюБуквуФамилии» будет отображаться в диалоговом окне Введите значение параметра. Знак «&» указывает Access, что нужно сцепить введенную букву со звездочкой. Напомним, что звездочка является подстановочным знаком, заменяющим любое число символов (букв или цифр). Вместо «&» можно использовать знак «+».

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

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

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

В Access существуют следующие способы добавления в запрос статистических функций:

• создать строку итогов в Режиме таблицы;

• создать итоговый запрос;

• создать перекрестный запрос.

Чтобы создать строку итогов в запросе нужно выполнить действия.

1. Открыть запрос в Режиме таблицы.

2. На вкладке Главная в группе Записи щелкнуть команду Итоги.

Таблица 2.8

^*) Отсутствует в Мастере запросов.

3. В таблице, являющейся результатом выполнения запроса, появится строка Итог.

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

Скрыть строку итогов можно щелчком по команде Итоги на вкладке Главная в группе Записи.

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

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

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

1. Открыть запрос.

2. На вкладке Конструктор в группе Показать или скрыть выбрать команду Итоги. В бланке конструктора появится строка Групповая операция, а для каждого поля будет установлено состояние Группировка.

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

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

5. Запрос сохранить.

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

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

☞ Примечание. Итоговый запрос можно создать также с помощью Мастера запросов, если в окне Создание простых запросов выбрать переключатель Итоговый. После этого станет активной кнопка Итоги. Надо не забывать, что в Мастере запросов доступны не все статистические функции (табл. 2.8).