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

7.1. Типы запросов

При создании базы данных стремятся свести все данные, необходимые для дальнейшего использования в таблицы, таким образом, чтобы избежать избыточности данных и достичь логики их объединения в таблицах. В рассматриваемых примерах были созданы таблицы, которые не содержат избыточных данных. Вместе с тем, следует отметить, что конечному пользователю не требуется видеть всю информацию, которая находится в таблицах. Наоборот, пользователь заинтересован получать сведения из базы данных, не вникая, в каких таблицах они находятся. Для этой цели в Access 2010 включён самостоятельный объект – Запросы. Запросы создаются с помощью Мастера запросов, Конструктора запросов и языка запросов SQL (Structured Query Language – структурный язык запросов). Каждое из перечисленных средств имеет определённую специфику, о которой будет изложено ниже. К основным типам запросов относятся:

- Запрос на выборку (Select query). Эти запросы позволяют извлекать информацию из таблиц, проводить вычисления с показателями, создавать перекрёстные ссылки. В запросах на выборку, изменять данные в таблицах нельзя.

- Запрос на изменение (Action query). Запросы такого типа дают возможность корректировать информацию, которая содержится в таблицах. Запросы на изменение делятся на четыре категории.

• Запрос на создание таблицы (Make-table) – позволяет создать новую таблицу на основе данных, содержащихся в одной или нескольких таблицах.
• Запрос на удаление (Delete) – удаляет все записи из одной или нескольких таблиц на основе критериев, задаваемых пользователем.
• Запрос на присоединение (Append) – добавляет целые записи или только указанные поля в таблице.
• Запрос на обновление (Update) – изменяет данные в существующих таблицах на основании информации в окне Конструктора.

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

Для создания запросов к БД Access 2010 необходимо на вкладке «Создание» выбрать режим «Мастер запросов» или «Конструктор запросов», используя пиктограммы, которые показаны на рисунке 66 (режим SQL-запрос совмещён с режимом «Конструктор запросов»).

Рис. 66. Пиктограммы для выбора режима создания запросов

7.2. Создание простого запроса на выборку в режиме Конструктор

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

1. Кликнуть по пиктограмме «Конструктор запросов». В результате открывается пустое окно Конструктор запросов и диалоговое окно «Добавление таблицы» (Рис. 67).

Рис. 67. Диалоговое окно для добавления необходимых таблиц на поле запросов

2. Выберите название необходимой таблицы и нажмите на кнопку , если требуется несколько таблиц для выборки данных, то повторите эту операцию (для выполнения поставленной задачи, понадобятся таблицы «Личные сведения», «Надбавки за стаж» и «Номенклатура должностей»). По окончанию переноса таблиц нажмите на кнопку . Обратите внимание, на поле  отобразятся все интересующие нас таблицы, как показано на рисунке 68. В нижней части окна находится бланк запроса, служащий для определения параметров запроса.
3. Заполните бланк запроса, для этого в бланке запроса необходимо указать наименование поля таблицы, из которой это поле выбирают. Подведите указатель мыши к необходимому названию поля в таблице, и дважды щёлкните левой кнопкой мыши. Можно выбрать имя необходимого поля из списка, раскрыв его с помощью . На рисунке 68 показан раскрывающийся список доступных полей из всех таблиц. Как видите, в списке находятся имя таблицы и имя поля, после того, как будет выбрана необходимая строка, в бланке запроса появится в первой строке имя поля, а во второй – имя таблицы. На бланке запроса в строке под наименованием «Вывод на экран» установлен знак , в отдельных случаях можно снять эту пометку, тогда данные из этого поля будут обрабатываться в запросе, но на экране мы их не увидим.

Рис. 68. Пример заполнения бланка запроса

4. Установите корректную связь между таблицами без ключевого поля. Следует напомнить, что таблица «Номенклатура должностей» имеет связь с файлом в Excel, по этой причине ключевое поле в таблице не создаётся. Связь таблицы «Личные сведения» с таблицей «Номенклатура должностей» не показывает отношение «Один ко многим». Чтобы запрос работал корректно – измените связь, выделите линию связи между таблицами, щёлкните правой кнопкой мыши по этой линии. В контекстно-всплывающем меню выберите строку с командой . Поставьте пометку в строке  диалогового окна «Параметры объединения» (Рис. 69), нажмите на кнопку .

Рис. 69. Диалоговое окно «Параметры объединения»

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

5. Присвойте имя запросу, нажмите в правом верхнем углу , в появившемся сообщении (Рис. 70), нажмите на кнопку , затем в диалоговом окне присвойте имя запросу (например, Оклады и надбавки).

Рис. 70. Предложение системы по сохранению запроса

В области объектов базы данных в разделе «Запросы» появится новая запись , щёлкните по ней дважды левой кнопкой мыши, после чего будет сформирован ответ системы (Рис. 71) с выборкой данных из трёх таблиц. Как видите, результаты запросов отображаются в виде таблицы с заданными наименованиями полей. Особенностью такой таблицы является то, что ни одно значение данного исправить невозможно.

Рис. 71. Результаты выполненного запроса на выборку

Таблица с результатами по сформированному запросу на выборку данных содержит записи всех сотрудников организации (Рис. 71). Для пользователя неудобно просматривать данные, если они никак не упорядочены. Это следует иметь в виду при формировании запроса. Рекомендуем в бланке запроса (Рис. 68) использовать строку с наименованием «Сортировка». Например, можно выстроить список сотрудников по надбавкам за стаж от минимальной величины до максимального значения. Для этого в столбце с полем «Надбавка» раскрыть список символом , а затем выбрать строку с командой .  Пользователь, в свою очередь, запустив запрос, может провести упорядочивание данных внутри таблицы с результатами запросов или воспользоваться фильтрацией данных.

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

1. Раскрыть в поле «Фамилии» список символом .
2. Выбрать строку , и раскрыть её.
3. Выбрать строку с наименованием , нажать на кнопку .
4. В диалоговое окно «Настраиваемый фильтр» ввести «ова» (Рис. 72).

Рис. 72. Пример использования текстового фильтра в таблице запроса

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

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

1. Как отображаются результаты запроса?
2. Какие основные типы запросов создают в базе данных Access2010?
3. Чем отличаются запросы на выборку от запросов на изменение?
4. Какие средства предложены в Access2010 для создания запросов?
5. В каком порядке следует работать с Конструктором запросов?
6. Какие дополнительные возможности получает пользователь при просмотре запроса на выборку?

 

7.3. Создание параметрических запросов

Под параметрическим запросом следует понимать – отбор значений данных из таблиц по заданному параметру. Фактически, вводится условие на поиск информации в однотипных строках таблиц. Например, канцелярия организации должна пригласить на совещание сотрудников определённых должностей и обязательно всех заведующих отделами, для этого необходимо задавать параметры отбора данных. Для формирования запроса, необходимо продумать наименования таблиц, в которых находятся, интересующие нас сведения, а затем запустить Конструктор запросов.           В запросе должны быть отражены фамилии сотрудников, место их работы, телефон и фотография. Следовательно, таблицами для отбора данных будут: «Личные сведения» и «Номенклатура должностей», а результатом выполнения запроса будет таблица, например, «Телефоны и должность». На рисунке 73 представлено поле для формирования запроса, рассмотрим по шагам, как этот запрос был сформирован.

Рис. 73. Пример создания параметрического запроса

1. Поместим таблицы на поле запроса.
2. На бланк запроса перенесём поля из таблиц (Фамилия, Телефон, Фото, Наим_отдела, Должность).
3. В строке «Условия отбора» запишем в квадратных скобках текст сообщения [Введите должность], которое будет появляться перед пользователем.
4. В строке «Или» запишем параметр в двойных кавычках «Заведующий отделом».
5. В строке «Сортировка» раскроем список и выберем «по возрастанию».
6. Сохраним запрос под именем «Телефоны и должность», а затем запустим этот запрос на исполнение.

Система выдаст диалоговое окно с вопросом (Рис. 74), в которое введите, например – Менеджер, и нажмите на кнопку .

Рис. 74. Предложение системы для ввода параметра

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

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

После выполнения запроса откройте этот запрос в режиме Конструктор, и вы увидите, что в строке с наименованием «Или» бланка запроса ничего нет. Не удивляйтесь, система самостоятельно формирует выражение и записывает его в строку «Условие отбора». Попробуйте мышкой расширить в бланке запроса столбец  с наименованием «Должность». В строке появилось логическое выражение, которое показано на рисунке 76.

Рис. 76. Логическое выражение Or (Или) для заданных параметров отбора данных

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

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

1. Что подразумевают под словом «Параметры» при создании запроса?
2. Как задать параметр в виде текстового фрагмента?
3. Можно ли задавать несколько параметров в одном запросе для различных полей?
4. Как объединить несколько параметров для одного столбца в бланке запроса?

7.4. Формирование запроса по неполному значению поля

Пользователь, обращаясь к базе данных, может предполагать лишь приблизительное написание текстовых значений, например, фамилии, или предполагать диапазон численных данных, которые его интересуют. В этом случае создают запрос на выборку по неполному значению поля. Предположим, что руководитель организации хочет получить сведения о сотрудниках организации, которые поступили на работу между 2012 и 2007 годами, а их фамилии содержат сочетание букв – «ва». В этом случае целесообразно составить поисковое предписание, использующее стандартные функции “Like” – Подобно и “Between” – Между. Для решения поставленной задачи следует выполнить следующие действия:

1. Создать запрос в режиме Конструктора, используя в качестве источника таблицу «Личные сведения».
2. Поместить в бланк запроса поля «Фамилия», «Должность», «Год поступления», «Наим_отдела» (можете использовать и другие поля).
3. В строку «Условия отбора» для поля «Фамилия» ввести условие: Like“*ва*”. Звёздочка слева и справа заменяет неизвестный фрагмент текста (можно поставить звёздочку только с одной стороны).
4. В строку «Условия отбора» для поля «Год поступления» ввести условие: Between20012 2007, как показано на рисунке 77.

Рис. 77. Заполнение бланка запроса для осуществления поиска по неполному значению поля

5. Сохраните запрос, например под именем «Приблизительный поиск», а затем проверьте, как он работает.

7.5. Обработка множественных данных

Создав в таблице «Личные сведения» поле «Доплаты» с множественным значением данных, мы добились компактности их хранения, но встаёт вопрос, как работать с такими данными? Предположим, что планово-экономическому отделу необходимо разработать фонд заработной платы сотрудников организации, при этом, требуется знать величину надбавки для каждого сотрудника, его ставку (оклад по должности) и суммарный коэффициент, на который увеличивается оклад сотрудника. Используя возможности Access 2010, эта процедура не вызывает затруднений. Для начала необходимо продумать, в каких исходных таблицах можно найти необходимые значения данных, а затем приступить к формированию запроса.

1. Создайте новый запрос в режиме Конструктора. Перенесите на поле конструктора таблицы, как показано на рисунке 78.

Рис. 78. Подготовка запроса для работы с множественным значением данных

2. В бланке запроса добавьте новую строку с наименованием «Групповая операция». Для этого можно кликнуть правой кнопкой мыши по бланку запроса, а затем выбрать строку  или на ленте активизировать элемент  - итоги (на вкладке Работа с запросами). Раскройте список выбора функций при работе с групповыми данными в столбце для таблицы «Доплаты» (Рис. 79), и выберите функцию Sum.

Рис. 79. Список для выбора функций при работе с групповыми данными

3. Сохраните запрос, например под именем «Фамилия и список выплат», запустите запрос на выполнение, результаты показаны на рисунке 80.

Рис. 80. Результаты работы запроса с обработкой множественных данных

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

7.6. Технология использования Построителя выражений для выполнения вычислений

7.6.1. Знакомство с надстройкой Построитель выражений

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

• Раздел в верхней части окна содержит поле, в котором создаётся выражение.
• Раздел в нижней части окна предназначен для создания элементов выражений и вставки их в поле выражения. Допускается непосредственный ввод выражения с клавиатуры. Этот раздел разделён на три вертикальных поля. В левом поле (Элементы выражений) выводятся папки, содержащие объекты базы данных - , встроенные и определённые пользователем функции - , константы - , операторы -  и выражения - . Среднее поле (Категории выражений) служит для выбора элемента или типа элементов из папки, заданной в левом поле. В правом поле (Значения выражений) выводится список значений для элементов, заданных в левом и среднем полях. Например, на рисунке 81 в построителе выражений показано, как отображается информация в полях Построителя выражений.

Рис. 81. Общий вид построителя выражений

7.6.2. Создание выражения для проведения вычислений в таблице

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

1. Запустить создание нового запроса в режиме Конструктор. На поле конструктора поместите таблицу «Личные сведения»
2. В бланк запроса перенесите последовательно поля: «Фамилия», «Год поступления», «Должность».
3. В пустом поле щёлкните мышкой, и на ленте щёлкните по пиктограмме , после чего откроется Построитель выражений.
4. В верхнем поле Построителя введите выражение - Набрал стаж:2013-, в поле «Элементы выражений» раскройте объект «Таблицы» и щёлкните по значку , в поле «Категории выражений» дважды щёлкните по - , после чего, в  верхнем поле Построителя будет сформирована запись - , нажмите на кнопку .
5. В бланке запроса отобразится, создаваемое выражение (Рис. 82), сохраните запрос, например под именем «Фактический стаж работы.

Рис. 82. Бланк запроса для вычисления фактического стажа работы сотрудника

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

Рис. 83. Результаты вычислений фактически отработанных лет сотрудниками

7.6.3. Проведение сложных вычислений

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

1. Создайте новый запрос в Конструкторе, в качестве источника данных выберите таблицу из запроса «Фамилия и список выплат» (Рис. 84).

Рис. 84. Базовая таблица для формирования ведомости на выдачу заработной платы сотрудникам

2. Перенесите в бланк запроса необходимые поля для проведения расчётов заработной платы (Рис. 85).

Рис. 85. Бланк запроса с перечнем полей

3. В свободном столбце бланка щёлкните мышкой и создайте выражение «Всего:» с помощью Построителя, которое позволяет вычислить причитающуюся сумму сотруднику за месяц «Всего», с учётом надбавки за стаж, доплаты к окладу (коэффициента повышения ставки) и ставки за должность, как показано на рисунке 86.

Рис. 86. Выражение (формула) для вычисления причитающейся суммы заработной платы сотруднику за месяц

            Формула состоит из четырёх слагаемых, на рисунке 86 каждое слагаемое выделено в отдельную строку. Суммарный коэффициент доплаты умножается на ставку сотрудника, затем складывается со ставкой, после чего прибавляется надбавка за стаж работы. Так как значения выражения выбирались из окна Категорий Построителя, то в окно переносились полные значения (название таблиц, откуда они получены, название таблицы, из которой переносились и наименование поля). Обратите внимание, что наименования таблиц и полей в таблицах заключены в квадратные скобки, а символ «!» – восклицательный знак обозначает объединение, выбранных параметров. В том случае, если составлять такую формулу вручную, достаточно указать только наименования полей, из бланка запроса при его составлении, тогда запись формулы будет компактнее и понятнее. Вот, что следовало бы вручную записать в выражение окна Построителя:

Всего:[Sum-Коэффициент]*[Оклад по должности]+[Оклад по должности]+[Надбавка]

          Попробуйте создать такое выражение и посмотрите на результат. На самом деле мы хотели подготовить ведомость для выплаты заработной платы за месяц, поэтому требуется в запрос ввести ещё одно поле «Выдать на руки». На рисунке 87 показано выражение: «Выдать на руки:0,87*[Всего]», которое отображается в дополнительном поле бланка запроса.

Рис. 87. Создание поля в запросе и установление его свойств

            При выплате заработной платы удобно оперировать с цифрами, которые отображаются, как тип «Денежный», поэтому установим в окне свойств формат, созданного поля. Для выполнения этой операции  в бланке запроса щёлкните правой кнопкой мыши по полю «Выдать на руки», а затем в меню выберите строку с командой . В окне свойств (Рис. 87) раскройте список в строке «Формат поля» и установите «Денежный». После чего можно сохранить запрос и проверить, как он работает. Результат выполнения запроса представлен на рисунке 88.

Рис. 88. Таблица с данными по заработной плате сотрудников

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

1. Какая последовательность запуска Построителя выражений при составлении запросов?
2. Как использовать поле «Элементы выражений» для создания выражения?
3. Как связывается бланк запроса с Построителем выражений?
4. Из каких элементов состоит «Выражение?
5. Что обозначают открытая и закрытая квадратные скобки в выражении?
6. Достаточно ли при составлении сложного выражения, состоящего из нескольких арифметических или логических операций, указывать имена полей, над которыми выполняются действия?