Запросы в MS Access XP

 ОГЛАВЛЕНИЕ

ЗАДАНИЕ 3.    ЗАПРОСЫ В  MS ACCESS XP.. 3

1. ЗАПРОСЫ.. 3

1.1. Типы запросов. 3

2. ЗАПРОСЫ НА ВЫБОРКУ.. 4

2.1. Создание простого запроса. 4

2.2. Простые запросы с использованием Конструктора запросов. 5

2.2.1. Перемещение в окне Конструктора запросов. 5

2.2.2. Бланк запроса. 6

2.2.3. Выбор поля. 6

2.2.4. Изменение порядка расположения полей. 7

Удаление полей из бланка запроса. 8

2.2.5. Запуск запроса. 8

2.2.6. Сохранение запроса. 8

2.3. Вычисления в запросе. 9

2.3.1. Типы выражений в Access. 9

2.3.1.1. Операторы.. 9

2.3.1.2. Функции. 13

Str() 14

2.4. Выполнение запросов с использованием простейших процедур вычислений. 16

2.5. Вычисляемые поля. 17

2.5.1. Конструирование выражения в строке поля. 17

2.5.2. Использование Построителя выражений. 19

2.5.3. Свойства полей. 23

2.6. Критерии выборки в запросе. 24

2.7. Оператор Like  и символы подстановки в запросах. 26

2.8. Запросы с Итоговыми значениями. 26

2.8.1. Групповые функции. 27

2.8.2. Выбор записей в группах. 28

3. ПАРАМЕТРИЧЕСКИЕ ЗАПРОСЫ.. 29

4. ЗАПРОСЫ К НЕСКОЛЬКИМ ТАБЛИЦАМ.. 31

5. ВОПРОСЫ ДЛЯ САМОПОДГОТОВКИ.. 33

6. ЗАДАНИЯ ПО РАЗДЕЛУ.. 35

7. ИТОГОВЫЕ ЗАДАНИЯ ПО РАЗДЕЛУ.. 44

ЗАДАНИЕ 3.    ЗАПРОСЫ В  MS ACCESS XP

1. ЗАПРОСЫ

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

Запросы по сути дела являются «близким родственником» фильтра, работу с которым мы рассмотрели во втором задании.

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

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

Запросы, которые можно использовать в Access, можно разнести на 5 основных категорий.

Запрос на выборку

Это наиболее часто используемый тип запроса.

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

Запрос с параметрами

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

Этот тип отчета – удобен при создании форм и отчетов.

Перекрестный Запрос

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

Запрос на изменение

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

Запрос SQL

Запрос, создаваемый с помощью средств SQL. В базовом курсе не рассматривается.

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

2. ЗАПРОСЫ НА ВЫБОРКУ

2.1. Создание простого запроса

Вам требуется для постоянного просмотра поля: «Фамилия», «Имя», «Подразделение», «Должность» и «Дата приема» таблицы «Сотрудники». Остальные поля вам не нужны. Организуйте простой запрос на выборку.

·  Откройте вкладку запросыдиалогового окна Базы данных.

·  Щелкните на кнопке «Создание запроса с помощью мастера». В ответ на эту команду открывается диалоговое окно «Создание простых запросов».

·  В списке «Таблицы и запросы»  диалогового окна выберите таблицу базы данных, на основе, которой вы хотите создать простой запрос – «Сотрудники».

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

·  Для того чтобы выбрать сразу все поля списка щелкните по кнопке .

Результат выполнения вышеперечисленных операций приведен на рисунке слева.

·  Щелкните на кнопке «Далее».

·  Введите название вашего запроса, например, «Дата приема запрос».

·  Щелкните на кнопке «Готово».

Фрагмент получившегося запроса приведен ниже.

2.2. Простые запросы с использованием Конструктора запросов

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

·  Откройте вкладку запросыдиалогового окна Базы данных.

·  Щелкните на кнопке «Создание запроса в режиме конструктора». В ответ на эту команду открываются диалоговые окна «Запрос на выборку» и  «Добавление таблицы».

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

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

Глядя на появившееся диалоговое окно Конструктора запросов, вы, конечно, заметили, что оно подобно диалоговому окну, которое появлялось при использовании расширенного фильтра (задание 2).

2.2.1. Перемещение в окне Конструктора запросов

Заголовок, расположенный в верхней части окна,  содержит информацию о конкретном окне, типе запроса и его имени.

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

Переключаться между областями можно с помощью щелчка мышью в нужной области, либо нажатием клавиши «Tab».

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

2.2.2. Бланк запроса

Каждая строка в бланке запроса выполняет определенную функцию:

Поле

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

Имя таблицы

Это поле показывает, к какой таблице относится поле

Сортировка

Указывает, как выполняться сортировка по этому полю.

Вывод на экран

Указывает, выводиться ли поле на экран или просто используется для выполнения задания. Поле, выводимое на экран щелчком в квадратике отмечается «галочкой».

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

В этой строке (и строке расположенной ниже) вводятся ограничения поиска (критерии поиска).

2.2.3. Выбор поля

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

Добавление одного поля:

·  Первый способ

·  Выделите имя поля «Фамилия» в области таблиц запроса и перетащите пиктограмму «Поле» (маленький прямоугольник, который появляется при перемещении мыши с нажатой левой кнопкой) в первый (или нужный) столбец бланка. При перетаскивании пиктограммы поле за пределы таблицы, она приобретает вид перечеркнутого кружка. Это означает, что в этот момент нельзя отпускать кнопку мыши. Когда пиктограмма «Поле» попадает в область бланка запроса, то она вновь приобретает вид небольшого прямоугольника.

·  Отпустите пиктограмму «Поле».

·  Второй способ

·  Выделите имя поля «Имя» в области таблиц запроса и дважды щелкните левой кнопкой мыши на выделенном поле. Заголовок поля переместится в строку бланка «Поле» первого свободного столбца бланка запроса.

Добавление нескольких полей:

·  Для добавления смежных полей

·  Удалите из бланка запроса все ранее занесенные поля командой Правка ð Очистить бланк.

·  Выделите в таблице имя первого нужного вам столбца – «Фамилия».

·  Удерживая нажатой клавишу «Shift», щелкните на последнем поле, которое вы хотите перенести в бланк запроса, например, «Подразделение».

·  Все поля между первым и вторым щелчком окажутся выделенными.

·  Щелкните в области выделения и перетащите пиктограмму «Множество полей» (несколько небольших прямоугольников, расположенных друг над другом)

·  Отпустите пиктограмму «Множество полей» в нужном месте бланка запроса.

·  Для добавления нескольких несмежных полей

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

·  Выделите в таблице имя первого нужного вам столбца - «Фамилия».

·  Удерживая нажатой клавишу «Ctrl», щелкните на необходимых вам полях - «Подразделение» и «Дата рождения».

·  Щелкните в области выделения любого поля и перетащите пиктограмму «Множество полей» в нужное место бланка запроса.

·  Для добавления всех полей таблицы

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

·  Дважды щелкните на заголовке таблицы для выбора всех полей.

·  Щелкните в области выделения любого поля и перетащите пиктограмму «Множество полей» в нужное место бланка запроса.

2.2.4. Изменение порядка расположения полей

Перед запуском запроса на исполнение посмотрите внимательно, устраивает ли вас порядок расположения столбцов. У вас появилось желание поле «Дата рождения» переместить перед полем «Должность».

·  Установите курсор мыши на область маркировки столбца «Дата рождения», который располагается прямо над названием поля. Когда указатель мыши примет вид черной стрелки «ê», щелкните кнопкой мыши для выделения столбца.

·  Нажмите и удерживайте кнопку мыши в этом положении. На конце указателя мыши появится прямоугольник.

·  Не отпуская кнопку, переместите столбец левее поля «Должность». Толстая вертикальная линия показывает, куда вы его переместили (см.рис.).

·  Верните поле «Дата рождения» на прежнееместо.

Удаление полей из бланка запроса

·  Если вы по ошибке перенесли в бланк запроса поле (или несколько

полей), которые вам не нужны, то выделите столбец (п.2.2.4. данного задания). Нажмите клавишу «Del».

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

2.2.5. Запуск запроса

Для запуска простого запроса:

·  Нажмите кнопку «Запуск» , или выполните команду  Запрос ð Запуск.

Для просмотра результатов выполнения простого запроса можно:

·  Нажать кнопку «Вид»  когда на ней изображена таблица.

В результате этих действий на экране компьютера появится таблица, аналогичная полученной нами при простом запросе, сконструированном без помощи «Конструктора» (п. 2.1.).

·  Для возврата в режим «Конструктора» щелкните на кнопке «Вид»   когда на ней изображен Конструктор.

2.2.6. Сохранение запроса

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

Для оперативного сохранения запроса в виде запроса:

·  Выделите команду Файл Þ Сохранить или щелкните по кнопке «Сохранить»  панели инструментов «База данных».

·  запрос также можно сохранить в виде Формы, Отчета и Страницы доступа к данным. Для этого:

·  Выделите команду Файл Þ Сохранить как.

·  Задайте имя и сохраняемый тип.

2.3. Вычисления в запросе

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

2.3.1. Типы выражений в Access

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

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

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

В качестве составляющих выражения (его элементов) выступают Операторы Имена объектов, Функции, Литералы, Константы.

Материал данного раздела является справочным. Его вы будете использовать для создания запросов.

2.3.1.1. Операторы

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

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

Операторы отношения

Равно (=)

Возвращает истинное значение, если значения двух сравниваемых выражений равны: Выражение [Дата рождения]=Date() истинно, если дата в поле [Датарождения]равна текущей дате.

Не равно (<>)

Полная противоположность оператору «Равно»

Меньше (<)

Возвращает истинное значение, если значение в левой части выражения меньше, чем значение в правой. Например, [Цена]<100 истинно, если в этом поле находится значение меньше 100

Больше (>)

Полная противоположность оператору «Меньше»

Меньше или равно (<=)

Возвращает истинное значение, если значение в левой части выражения меньше чем значение в правой части или равно ему. Например, [Цена]<=100 истинно, если значение в поле Цена меньше или равно 100

Больше или равно (>=)

Противоположно по действию оператору «Меньше или равно»

Математические (арифметические) операторы

Оператор

Умножения (*)

Умножение одной величины на другую. Имена полей в формулах заключены в квадратные скобки.

Пример: [Количество] * [Цена] даст значение общей стоимости товаров.

Сложения (+)

Помимо обычной операции сложения двух величин (типа [Общая сумма]+[Налог]) может использоваться для объединения двух символьных строк. Например, Вам может понадобиться объединить поля «Фамилия» и «Имя», что вывести их на экран как одно целое. В этом случае выражение будет иметь вид [Фамилия]+[Имя].

Вычитания (-)

Примером может служить составление счета-фактуры с учетом скидок для постоянных клиентов: [Общая сумма] – [Общая сумма] * [Скидка].

Деления (/)

Например, формула [Вложенная сумма] / [Полученная сумма] даст значение банковского процента за истекший период.

Возведение в степень (^)

Число умножается само на себя столько раз, сколько указано в показателе степени.

Строковые операторы

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

Оператор конкатенации (&)

Оператор конкатенации позволяет объединять несколько текстовых объектов в одну строку. В какой-то степени этот оператор похож на  оператор сложения,  но в отличие от последнего, при использовании  оператора  & в результате всегда получается новая строка символов. Например, [Фамилия]&[Имя]

Пробелы между полями не добавляются в создаваемую строку. Так, если в поле «Фамилия» записано «Бендер», а в поле «Имя» - «Остап», то выражение [Фамилия] & [Имя]  запишет новую строку «БендерОстап».

Для того, чтобы между фамилией и именем появился пробел, выражение должно быть записано: [Фамилия]& “пробел“&{Имя].

Оператор & позволяет вставлять в текстовую строку числа и даты.

Например, “Отчет составил” & Now() &“  “ & [Бендер].

Результат:  Отчет составил 31.12.03 15:07:01 Бендер

Оператор Like

Оператор Likeсравнивает два строковых объекта с помощью шаблона (проверяет, соответствует ли один объект шаблону другого).

Выражение Like шаблон

Если объект – выражение соответствует объекту- шаблону, то формула возвращает значение Истина.

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

Символы подстановки оператора Like

Символы

Соответствие

?

Заменяет один символ

*

Заменяет любое количество символов

#

Заменяет любую цифру

[Список]

Любой символ из списка

[!Список]

Любой символ не из списка

FПримеры

Выражение [Фамилия] Like “И[Вв]* истинно, если в поле «Фамилия» находится строка текста, которая начинается ИВ или Ив. Таким образом, строка «Иванов», «Иващенко» или «ИВЛЕВ» дает значение истина, а строка «Петров» или «Сидоров» – ложь.

Выражение  АВ1998” Like “AB#### – истинно, так как исходная строка должна начинаться с символов «АВ», за которыми следуют любые четыре цифры.

Выражение 10-й пройденный круг” Like [#]*круг – истинно, так как первым символом строки должен быть символ #, а в конце строки должно находится слово «круг».

Логические операторы

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

Оператор

Действие

AND

Используется для выполнения операции логического умножения (конъюнкции)

Выражение 1 AND Выражение 2 

FПример. [Город]=Красноярск AND [Индекс] = 660000 – истинно, если оба выражения – истинны

OR

Используется для логического сложения (дизъюнкции) двух объектов и возвращает истинное значение, если один из них имеет значение –Истина.

Выражение1 OR Выражение 2

FПример [Фамилия] = Бендер OR [Фамилия] = Безенчук – истинно, если в поле «Фамилия» находится одна из указанных фамилий

NOT

Используется для инвертирования значения логического выражения. Он возвращает истинное значение, если условие – Ложно

FПример. NOT [Объем продаж] >=1000 истинно, если «объем продаж» меньше 1000

BETWEEN..AND

Указывает принадлежность значения выражения заданному диапазону

Выражение BETWEEN Значение 1 AND Значение 2

Пример. IF([Сумма займа] BETWEEN 100 AND 1000, «Немедленное погашение», «Погашение через 30 дней»

IN

Проверяет, совпадает ли значение выражения с одним из элементов указанного списка.

Выражение IN (Значение 1, Значение 2, ….) выражение истинно, если «Выражение» совпадает с одним из значений, указанных в списке.

FПример. IF ([Должность] IN («Инженер», «лаборант», «Менеджер»), «Пройти аттестацию», «Уволить».

Если в строку «Должность» будут введены (или найдены при запросе) значения «Инженер», «лаборант» или «Менеджер», то функция  вернет значение – «Пройти аттестацию», в противном случае возвращается строка «Уволить».

IS

Используется только с ключевым словом NuLL, для проверки является ли объект пустым.

FПример. IF([Фамилия] IS NuLL, “Введите фамилию”.

Приоритет операторов

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

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

Математические операторы

Операторы сравнения

Логические операторы

Возведение в степень (^)

Равно (=)

NOT

Умножение (*)

Не равно (<>)

AND

Деление (/)

Меньше (<)

OR

Абсолютное значение  (ABS)

Больше (>)

Сложение (+)

Меньше или равно (<=)

Вычитание (-)

Больше или равно (>=)

Конкатенация (&) строк

Like

2.3.1.2. Функции

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

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

Access использует в своих объектах более сотни различных встроенных функций. Кроме того, используя язык Visual Basic, который поддерживается Access, как и другими приложениями. Вы можете создать свои собственные функции, называемые «Функциями Пользователя».

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

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

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

Кратко рассмотрим наиболее часто употребляемые функции Access.

Функции преобразования типа

Str()

Возвращает число в виде строки.

FПример. Str (1234,56) возвращает строку “1234,56”

Val()

Выделяет число из строки.

FПример. Val (“1234,56”) возвращает число 1234,56;

Val (“10Привет”) возвращает число 10

Format ()

Возвращает строку в формате, определенном Пользователем.

FПример. Format (“123456789”,”@@@-@@@-@@@”) возвращает строку 123-456-789;

Format (#01/31/03#) возвращает строку 31-Января-2003

Функции даты и времени

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

Now() 

Возвращает текущую дату и время. Функция не имеет параметров.

FПример.  31/01/03 22:34:34 PM

Time()

Возвращает текущее время в 12 часовом формате

FПример. 22:34:34 PM

Day()

Возвращает значение дня месяца в диапазоне от 1 до 31. FПример. Если вам необходимо отобрать записи исполнения заказов со значением поля большим, чем 10 число любого месяца то выражение, реализующее это условие будет иметь вид Day([ДатаИсполнения])>10.

Month()

Возвращает значение месяца в диапазоне от 1 до 12.

FПример. Если вы хотите выбрать все записи для сотрудников, родившихся в мае месяце в строку «Условия отбора» введите выражение: (Month([ДатаРождения])=5).

Date()

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

FПример. Если вы хотите отобрать все записи о заказах, сделанных за тридцать дней до сегодняшней даты, введите в строку «Условия отбора» этого поля выражение: Date()-30.

Datepart (интервал, дата)

Возвращает номер квартала или номер недели в  зависимости от того , какой код интервала введен в выражение.

«q» -(определяет квартал года от 1 до 4)

«ww»-(определяет номер недели в году  от 1 до 53)

FПример. Если вы хотите отобрать все записи о заказах, которые были сделаны во 2 квартале, то создайте вычисляемое поле, содержащее выражение: Datepart(“q”, [Дата Заказа]),а в строку «Условия отбора» этого поля введите «=2».

Статистические функции SQL

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

Avg(),

Среднее значение выборки

Count()

Количество элементов в выборке

Max()

Максимальное значение

Min()

Минимальное значение

Sum()

Сумма чисел

Строковые функции

Строковые функции выполняют операции над текстовыми выражениями.

Right()

Возвращает строку символов, которая расположена в исходной строке справа.

FПример. Right(“Привет”,2) возвращает строку «ет».

Len()

Возвращает длину строки.

FПример. Len(“Привет”) возвращает число 6.

Lcase()

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

FПример. Lcase(“КЛИЕНТ”) возвращает строку «клиент».

Статистические функции по подмножеству

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

DАvg()

Возвращает среднее арифметическое набора значений, принадлежащих указанному подмножеству.

FПример. DAvg(“[Общее количество]”, "Заказы автомобилей”) определяет среднее количество заказанных автомобилей.

Dcount()

Возвращает число записей в указанном подмножестве.

Математические функции

Int()

Определяет целую часть числа.

FПример. Int(12233,556) возвращает число 12233.

Fix()

Корректно определяет целое значение для отрицательных чисел.

FПример. Fix(-12345,4321) возвращает число –12345.

Sqr()

Вычисляет корень квадратный числа.

FПример. Sqr(4) возвращает число 2.

2.3.1.3. Специальные операторы идентификации

Оператор «!» (восклицательный знак)

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

FПример. Рассмотрим поле «Дата рождения», которое содержится в формах [Сотрудники] и [Данные о сотрудниках]. Если Вы хотите обратиться  к полю «Дата рождения» в форме [Сотрудники], то используйте зарезервированное слово Forms вместе с восклицательным знаком: Forms![Сотрудники].

Имена форм являются объектами и для ссылки на них всегда нужно использовать квадратные скобки.

2.4. Выполнение запросов с использованием простейших процедур вычислений

G Задание. Выведите на экран данные о сотруднике, имеющем Код сотрудника = 1004.

·  Любым способом создайте простой запрос по таблице «Сотрудники». В него включите все поля таблицы. Перейдите в режим «Конструктора».

·  В ячейке на пересечении  строки «Условие отбора» и столбца «Код сотрудника» напечатайте искомый код.

·  Запустите запрос на выполнение (п.2.2.5.). В таблице будет видна только одна строка с Кодом сотрудника = 1004.

·  Удалите введенное условие. Для этого воспользуйтесь любым из известных вам способов стирания текста в MS Word.

G Задание. Выведите на экран все записи, относящиеся к сотрудникам, у которых коды находятся в диапазоне 1002 – 1004 и 1006 – 1009. Для этого воспользуйтесь операторами отношений и логическим оператором And (п. 2.3.1.1.).

·  В ячейке на пересечении  столбца «Код сотрудника» и строк «Условие отбора» и «Или»,  и напечатайте выражения, приведенные на рисунке.

·  Запустите запрос на выполнение (п.2.2.5.). В таблице будет видны шесть строк с заданными в условии Кодами сотрудников.

G Задание. Самостоятельно организуйте два запрос для

просмотра всех записей со всеми Кодами сотрудников, кроме 1004 (используя оператор «Not») и сотрудников, работающих в Дирекции или Снабжении, имеющих Доплаты  от 2000 до 5000 рублей включительно.

G Задание. Создайте запрос, в котором будут все сотрудники, проживающие на улицах, начинающихся на букву «М». Для этого:

·  Удалите все ранее введенные условия.

·  В ячейке на пересечении  столбца «Адрес» и строки «Условие отбора»  напечатайте оператор Like «М*» или просто напишите М*.

·  Запустите запрос на выполнение.

Задание условий и результат запроса приведены на рисунке.

дG Задание. Вы помните, что в фирме есть сотрудник, фамилия которого начинается на «Без». Самостоятельно найдите его, используя запрос.

·  Сделайте запрос о сотрудниках, фамилии которых подобны «Лопуховский» и «Бендер».

·  Сделайте запрос обо  всех сотрудниках, кроме «Лопухов» и «Бендер».

Теперь перейдем к одному из самых интересных разделов запросов – вычисляемым полям.

2.5. Вычисляемые поля

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

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

2.5.1. Конструирование выражения в строке поля

Используя таблицу «Сотрудники» попробуем создать запрос,  в котором из полей «Фамилия» и «Имя» будут выбраны данные обо всех сотрудниках и результирующая запись будет выведена в одном поле. В этом случае, выражение, реализующее наше задание будет иметь вид:

[Фамилия]&" "&[Имя]

Напомним, что после оператора конкатенации (&) в кавычках стоит знак пробела, для того, чтобы Фамилия, Имя и Отчество не были записаны неразрывно (п.2.3.1.1.).

·  Откройте вкладку Запросыдиалогового окна Базы данных.

·  Щелкните на кнопке «Создание запроса в режиме конструктора».

·  В открывшемся окне «Добавление таблицы» щелчком по кнопке «Добавить» выберите таблицу «Сотрудники», из которой вы хотите извлечь необходимую информацию и щелкните на кнопке «Закрыть» (п.2.2.1.).

·  В строке «Поле» запишите выражение для выборки и размещении в одном поле Фамилии и Имени сотрудников. Слово «Выражение1:» появится автоматически. Далее вы научитесь его изменять.

·  В строке «Сортировка», установите – «По возрастанию»

·  Из таблицы«Сотрудники» выберите поля «Город» и «Адрес» и поместите их во втором и третьем столбцах (п.2.2.3.).

·   Если это необходимо, пометьте галочкой вывод полей на экран.

·  Нажмите кнопку запуск , или выполните команду  Запрос ð Запуск либо нажмите кнопку «Вид»  когда на ней изображена таблица (п.2.2.5.).

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

Писать выражение в Поле не очень удобно из-за того, что  часть выражения уходит за пределы столбца ввода, а также из-за того, что Имя поля «Варажение1» предлагаемое вам Access по умолчанию не информативно. Естественно, что имя поля можно отредактировав позднее.

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

Чтобы избежать этого можно воспользоваться Построителем выражений.

2.5.2. Использование Построителя выражений

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

Премия:Int(((Now()-[Дата приема])/360)*[Зарплата])*1,3)

Из имеющегося запроса «Дата приема запрос» создайте новый запрос, в котором будете начислять заработную плату. Для этого:

·  В Окне базы данных щелкните правой кнопкой мыши на запросе «Дата приема Запрос».

·  Выберите команду «Копировать».

·  Щелкните правой кнопкой мыши в свободной части Окнабазыданных.

·  Выберите команду «Вставить».

·  Укажите новое имя запроса – «Расчет премии Запрос».

·  Щелкните  на кнопке «ОК».

Выделенное желтым цветом не делать.

Вместо поля Зарплата используйте поле Доплата

Так как в созданном запросе отсутствует поле «Зарплата», то создайте ее сами. Для этого:

·  Откройте созданный запрос в режиме Конструктора.

·  Из таблицы «Сотрудники» перетащите в первый свободный столбец поле «Доплата».

·  В строке  «Поле» перед словом Разряд напечатайте «Зарплата:».Это и будет новое имя столбца:

·  Перейдите в режим «Таблица» и удалите имеющиеся значения. Вместо них введите зарплату сотрудников по приведенной ниже таблице или задайте ее самостоятельно (в пределах разумного).

Код сотрудника

Фамилия

Должность

Зарплата

1001

Бендер

Директор

30 000.00р.

1003

Людоедка

Референт

7 500.00р.

1004

Лопухов

Начальник отдела

15 000,00р.

1005

Семенова

Агент

5 000,00р.

1006

Воробьянинов

Агент

5 000,00р.

1007

Безенчук

Плановик

7 500,00р.

1008

Хаименко

Начальник отдела

12 500,00р.

1009

Вороватов

Инженер

9 000,00р.

1010

Круглова

Начальник отдела

15 000,00р.

1011

Соловьева

Бухгалтер-кассир

7 500,00р.

1012

Ермолаева

Бухгалтер-экономист

7 500,00р.

1013

Сидорова

Экономист

7 500,00р.

1014

Безенчук

Представитель

10 000,00р.

1015

Петров

Представитель

10 000,00р.

1016

Ромсанов

Представитель

10 000,00р.

1100

Балаганова

Заместитель директора

20 000,00р.

·  Щелкните на кнопке «Построить»  Панели элементов Конструктор запросов или щелкните правой кнопкой мыши в строке «Поле» Конструктора запросов.

·  В открывшемся контекстном меню выберите команду «Построить» и MS Access откроет окно Построителя выражений.

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

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

Так как мы намерены для построения Выражения в запросе воспользоваться Именами полей запроса «Расчет премии Запрос», то:

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

·  Найдите запрос «Расчет премии Запрос и дважды щелкните на ней для того, чтобы в среднем окне увидеть список полей этого запроса.

Для того чтобы рассчитать величину премии:

·  Дважды щелкните в левом списке на папке «Функции» и затем щелкните на папке «Встроенные функции»

·  В открывшемся в центральном окне списке встроенных в Access функций, выберите группу функций « Математические» и щелкните на ней кнопкой мыши.

·  В открывшемся в правом окне списке функций категории «Математические», выберите функцию Int()- вычисление целой части числа и дважды щелкните на ней, либо щелкните на кнопке окна «Вставить».

В результате выполненных действий функция Int() будет перенесена в окно ввода (см. рисунок)

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

·  Щелкните мышью внутри скобок оператора

·  Щелкните на категории функций «Дата\Время» и введите в выражение функцию «Now()» или функцию «Date()». Отличие между функциями в том, функция Now() помимо текущей даты возвращает, и текущее время, что в нашем случае не имеет принципиального значения.

·  Используя кнопку  под окном ввода, введите знак «минус» или просто напечатайте его.

·  Вновь откройте запрос «Расчет премии Запрос и введите имя поля «Дата приема».

Каждый раз при вводе новой функции или имени поля Access подставляет слово – напоминание (на наш взгляд излишнее) «Выражение». Выделите и удалите его.

Int(Now()-[Дата приема])

·  Для соблюдения порядка вычислений, используя кнопки,   заключите введенную разность поля и функции [Дата приема] – Now()  в скобки. Кстати, скобки вы можете вставить просто с клавиатуры.

·  Для того, чтобы подсчитать количество полных лет, проработанных сотрудниками на предприятии, разделим нацело выражение в скобках на 360 (количество дней в году)

Int((Now()-[Дата приема])/360)

·  Щелкните на соответствующей кнопке  и введите знак умножения или введите ее сами с клавиатуры

·  В списке Имен полей таблицы «Сотрудники» выделите имя «Доплата» и введите его в поле ввода.

·  Последовательно введите знак умножения и величину поясного коэффициента (1,3).

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

·  Щелкните на кнопке «ОК» и созданное выражение будет перенесено в «Конструктор запросов»

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

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

2.5.3. Свойства полей

·  Откройте созданный запрос в режиме Конструктора.

·  Установите курсор мыши в поле «Выражение1» и щелкните правой кнопкой мыши.

·  В открывшемся контекстном меню выберите «Свойства»

·  В открывшемся диалоговом окне « «Свойства поля» введите:

·  В поле «Описание» текст, содержащий описание объекта, выводящегося в окне базы данных, а также описание отдельных полей таблицы или запроса. В вашем случае - это может быть текст: «Расчет премии».

·  В поле «Формат поля» выберите формат представления данных (в нашем примере выберем формат –«Денежный»)

·  В поле «Число десятичных знаков» укажите, сколько десятичных знаков. Должно содержаться в значении поля.

·  При необходимости введите маску ввода.

·  В поле «Подпись»  укажите

имя созданного поля (В нашем примере введите в это поле – «Премия»)

·  Нажмите на кнопку «Закрыть»  диалогового окна для сохранения заданных свойств.

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

В результате Access выведет на экран таблицу, изображенную на рисунке.

2.6. Критерии выборки в запросе

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

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

·  Критерии запроса записываются в строку «Условия отбора» в той колонке, имя которой используется при построении критерия.

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

·  В том случае, если выражения, содержащие критерии отбора, объединяются оператором «Or» («Или») то они должны быть записаны в строке «Или» соответствующей колонки.

Рассмотрим выше сказанное на примере предыдущего запроса.

Вы решили выплатить премию только тем сотрудникам, которые живут в городах «Красноярск» и «Ачинск» и проработали на фирме не менее 2-х лет.

·  Из имеющегося запроса «Расчет премии Запрос» создайте новый запрос, в котором будете начислять премии для заданных условий отбора. Для этого:

·  В Окне базы данных щелкните правой кнопкой мыши на запросе «Расчет премии Запрос».

·  Выберите команду «Копировать».

·  Щелкните правой кнопкой мыши в свободной части Окна базы данных.

·  Выберите команду «Вставить».

·  Укажите новое имя запроса – «Расчет премии Красноярцам и Ачинцам Запрос».

·  Щелкните  на кнопке «ОК».

·  Щелкните на кнопке «Конструктор», для открытия диалогового окна Конструктор запросов.

·  Добавьте в запрос поле «Город».

·  Щелкните правой кнопкой в строке «Условия отбора» поля «Премия». В открывшемся контекстном меню выберите команду «Построить»

·  В поле ввода диалогового окна Построителя выражений введите выражение для отбора сотрудников проработавших на фирме не менее двух лет:

(Date()-[Дата приема])/360>=2

·  Закройте диалоговое окно Построителя выражения (перенесите созданное выражение в строку «Условия отбора»)

·  В строку «Условия отбора» поля «Город» введите условие отбора сотрудников, живущих в г. Красноярске - Красноярск, а в строку «Или» - сотрудников живущих в г. Ачинске – Ачинск. Названия городов не надо помещать в кавычки, это будет сделано автоматически.

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

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

·  Закройте запрос.

2.7. Оператор Like  и символы подстановки в запросах

Очень часто при выполнении запроса известна только часть содержимого поля Для этой цели Access предлагает воспользоваться оператором «Like»  и символами подстановки (п.2.3.1.1.).

Для выполнения отбора данных с использование оператора  «Like» вы должны указать в ячейке «Условия отбора» того поля по содержимому, которого будет вестись отбор записей. Таким образом, Access будет выполнять поиск в заданном поле по «Маске».

G Задание. Вы решили дать премию только Агентам.

·  Откройте в режиме Конструктора запрос «Расчет премии Запрос».

·  Щелкните на строке «Условия отбора» поля «Должность»

·  Введите в эту строку «Like А*»

·  Выполните запрос

·  Результат приведен ниже. Просмотрите его

·  Уберите из запроса оператор «Like А*».

·  Закройте запрос.

2.8. Запросы с Итоговыми значениями

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

G Задание. Вы захотели узнать, какую величину премии получили отделы фирмы и сколько сотрудников каждого подразделения получили премию?

·  Откройте в режиме Конструктора запросов «Расчет премии Запрос».

·  Удалите поля «Имя», «Должность», «Дата приема»,  «Премия».

Для получения итоговых значений по группе:

·  Щелкните на кнопке «Групповые операции»  панели инструментов Конструктора запросов. В результате в бланке запросов добавится строка «Групповые операции».

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

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

2.8.1. Групповые функции

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

Функция

Выполняемая операция

Sum

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

Avg

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

Min

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

Max

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

Count

Возвращается число записей, значение которых отличны от нуля, в каждой группе заданного поля. Для подсчета числа записей с учетом нулевых значений в строку «Поле№» необходимо ввести выражение «Count(*)».

StDev

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

Var

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

First

Возвращает первое значение поля в каждой группе.

Last

Возвращает последнее значение поля в каждой группе.

Вновь возвращаемся к нашему примеру.

·  Щелкните правой кнопкой в колонке «Фамилия» и в открывшемся контекстном меню выберите команду «Свойства».

·  В поле «Подпись» окна «Свойства» введите новое название поля таблицы, например, «Кол. Сотрудн»

·  Щелкните в строке «Групповая операция» и в открывшемся списке функций выберите «Count» для подсчета количества человек в отделе, получивших премию.

·  В строке «Групповая операция» колонки «Подразделение» оставьте «Группировка», так как мы намерены получить итоговые значения по каждому подразделению

·  В строке «Групповая операция» колонки «Зарплата» в списке функций выберите «Sum» для подсчета суммы начисленных премий по группе.

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

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

Max ([Зарплата]) – Min ([Зарплата])

2.8.2. Выбор записей в группах

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

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

Для создания фильтра: 

·  Вызовите на экран Конструктор запросов.

·  Ведите дополнительное поле, по которому Вы хотите провести фильтрацию данных - «Должность».

·  Выберите установку Условиев строке «Групповая операция»

·  ØСнимите флажок «Вывод на экран»

·  ØВ строке « Условия отбора» введите критерий фильтрации данных (Например, «<>Директор»)

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

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

3. ПАРАМЕТРИЧЕСКИЕ ЗАПРОСЫ

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

Access для решения этой задачи предлагает вместо того, чтобы в строку  «Условие отбора» вводить конкретное значение, ввести в нее текст, заключенный в квадратные скобки [].

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

·  Измените предыдущий запрос так, чтобы Access задавал вопрос при каждом его выполнении, например, такой: «Введите название подразделения».

·  При выполнении команды Запрос ð Запуск Access выводит на экран окно «Введите название отдела», в которое вы должны ввести название отдела, по которому вы хотите получить итоговые данные.

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

·  Для ввода параметров запроса (или их изменения) выполните команду Запрос ð Параметры и в открывшемся окне в столбце «Параметр» введите текст параметра фильтрации, а в списке столбца «Тип данных» – выберете

необходимый тип вводимых данных. Для вашего примера этого делать не надо.

·  Сохраните полученный запрос под новым именем «Расчет премии (модифицированный) Запрос».

4. ЗАПРОСЫ К НЕСКОЛЬКИМ ТАБЛИЦАМ

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

Рассмотрим создание запроса к нескольким таблицам на небольшом примере.

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

С этой целью дополним ранее созданную таблицу «Сотрудники» следующими полями: 

Стаж

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

Вредность

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

Надбавка

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

Создадим новую таблицу «Справка», в которую поместим соотношения между разрядом и коэффициентами увеличения минимальной величины оплаты труда, а также суммы доплат, сумму минимальной и заработной платы и величину районного коэффициента. Величины доплат и минимальной заработной платы (тарифной ставки первого разряда) приведены в соответствии с Постановлением РФ от 06.11. 2001 г. № 775 «О повышении тарифных ставок (окладов) ЕТС по оплате труда бюджетной сферы».

RAZR

(Разряд)

KOEFF

(Коэффициент)

DOPL_R

(Допл_Р)

RKF

(РКФ)

MINX

(МИН)

1

1,00

675

0,3

450

2

1,11

750

0,3

450

3

1,23

832,5

0,3

450

4

1,36

915

0,3

450

5

1,51

1020

0,3

450

6

1,67

1125

0,3

450

7

1,84

1245

0,3

450

8

2,02

1365

0,3

450

9

2,22

1400

0,3

450

10

2,44

1540

0,3

450

11

2,68

1687

0,3

450

12

2,89

1820

0,3

450

13

3,12

1967

0,3

450

14

3,36

2114

0,3

450

15

3,62

2282

0,3

450

16

3,90

2597

0,3

450

17

4,20

2646

0,3

450

18

4,50

2835

0,3

450

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

((«KOEFF» * «MINX» + «DOPL_R» + «Доплата») + «KOEFF» * «MINX» * (1 + «Вредность» + «Классность»)) * (1 + «Стаж» + «RKF»)* «Доля ставки»

где величины «KOEFF», «MINX», «DOPL_R» и «RKF» должны быть взяты из таблицы «Справка», а величины «Доплата»), «Вредность», «Классность», «Стаж» и «Доля ставки»  должны быть взяты из таблицы «Сотрудники». При этом величина коэффициента увеличения минимальной величины оплаты труда, зависит от разряда ЕТС каждого сотрудника.

Для создания воспользуемся Конструктором запросов

·  Откройте вкладку Запросыдиалогового окна Базы данных.

·  Щелкните на кнопке «Создание запроса в режиме конструктора».

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

·   

·  Перед созданием запроса установите связи между таблицами.  В нашем примере вы должны связать таблицы с помощью поля «Разряд». Если они уже связаны, то замечательно.

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

·  Щелкните правой кнопкой мыши в строке «Поле» первого свободного столбца, а затем  щелкните на кнопке «Построить»  Панели элементов Конструктора запросов.

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

·  Нажмите кнопку «Запуск» , или выполните команду  Запрос ð Запуск, в результате чего Access выведет на экран таблицу с отобранными   записями.  

·  Сохраните запрос под именем «Расчет зарплаты Запрос».

Теперь потренируйтесь в выполнении заданий. Результаты покажите преподавателю.

5. ВОПРОСЫ ДЛЯ САМОПОДГОТОВКИ

1.  Что такое запрос? Его назначение?

2.  В каком виде представляются на экране компьютера результаты выполнения запроса?

3.  Назовите основное отличие запроса от фильтра.

4.  Какие типы (категории) запросов вы знаете?

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

6.  Для чего предназначен запрос на изменение? Какие действия можно выполнить с помощью этого запроса?

7.  Как создать простой запрос? Назовите основные действия.

8.  Как создать запрос с помощью Конструктора запросов?

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

10.Для чего предназначена строка «Поле» в бланке запроса?

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

12.Можно ли не выводить на экран компьютера (печать) часть полей, которые вы выбрали для запроса?

13.1Можно ли использовать поля, которые вы не выводите на печать (просмотр), для фильтрации данных?

14.Как добавить поле таблицы в бланк запроса?

15.Как добавить несмежные поля в бланк запроса?

16.Как добавить все поля таблицы в бланк запроса?

17.Как изменить порядок столбцов в бланке запроса?

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

19.Для чего предназначена пиктограмма ?

20.Как сохранить созданный запрос?

21.Можно ли при создании запроса произвести какие-либо вычисления на полями?

22.Какие типы операторов вы знаете?

23.Назовите основные  математические операторы.

24.Какие действия могут быть выполнены с помощью операторов «\»  и «/»?

25.Что позволяет делать оператор конкатенации?

26.Для чего предназначен оператор «Like»? Приведите пример записи этого оператора.

27.Какие символы подстановок в шаблоне вы знаете?

28.Что делает символ «#»

29.Чем отличаются действия символов подстановки «*» и «?»?

30.Среди записей в поле фамилии содержаться фамилии: Иванов, Петров, Иващенко, Сидоров.. Какие записи будут выбраны в результате запроса, если в строке «Условия отбора» поля «Фамилия» записано выражение: *ов

31.Какие действия позволяет выполнить оператор «Between…And…»

32.Назовите основные типы функций Access.

33.Расскажите о назначении операторов «Now()».

34.Какие данные выберет Access из таблицы “Сотрудники”, если в строку «Условия отбора»  поля «Дата приема» введено выражение  «>Now()-720”

35.Для чего предназначен Построитель выражений? Поясните основные действия с ним.

6. ЗАДАНИЯ ПО РАЗДЕЛУ

(выполняются полностью или по указанию преподавателя)

G. Задания.

1.  Создайте простой запросиз таблицы «Сотрудники», включив в него поля «Код сотрудника», «Фамилия», «Имя», «Подразделение», «Должность». Выведите на экран только записи, относящиеся к сотрудникам, работающим в Снабжении.

2.  Модифицируйте запрос и выясните, кто из сотрудников ДирекциииСнабжениябыл принят на работу в возрасте от 30 до 40 лет. Сохраните запрос под именем «Запрос1».

3.  Создайте запрос, который позволит выяснить, кто из сотрудников был принят на работу менее чем 4 года назад: операторПроработ годы: Int((Date()-[Дата приема])/360).

Добавьте поле «Дата рождения». Не снимая предыдущего условия найдите всех принятых сотрудников, родившихся в сентябре или январе. Обратите внимание, откуда берется поле «Дата рождения». Оператор

(Month[Дата рождения])=9 Or (Month[Дата рождения])=1). Объясните, что у вас получилось в результате выполнения запроса.

Сохраните запрос под именем «Запрос2».

4.  Скопируйте «Расчет зарплаты Запрос» с новым именем  «Начислено Запрос». В него добавьте вычисляемые поля «Подоходный налог» и «К выдаче».

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

5.  Скопируйте «Запрос2» с новым именем  «Запрос3». Добавьте поле «Разряд». Удалите имеющиеся условия отбора. Используя оператор Between найдите всех сотрудников, имеющих разряд с 10 по 14 включительно и родившихся с  января 1952 года по 1 января 1964 года включительно.

6.  Скопируйте «Запрос3» с новым именем  «Запрос4». Удалите ненужные поля и условия отбора. Найдите всех сотрудников, проработавших на предприятии более 1000 дней или менее двух лет (720 дней).

7.  Скопируйте запрос «Заказ товаров Запрос» с новым именем  «Группировка заказов Запрос».

Оставьте в нем только поля «Количество» и «ДатаИсполнения». Создайте запрос, в котором будут приведены суммарные (итоговые) величины по полю «Количество»  для каждой даты исполнения. 

8.  Модифицируйте запрос «Сотрудники полная таблица Запрос» таким образом, чтобы можно было произвольно указывать выбираемый  разряд сотрудника.

9.  Используя таблицы "Клиенты" - поле "Название"; "Заказано" - поле "Количество"; "Заказы" - поле "ДатаИсполнения" и "Товары" - поле "Описание товара) создайте простой запрос, присвоив ему имя "Заказы клиентов Запрос". Выясните:

·  Кто из клиентов заказывал сухие вина и в каком количестве.

·  Какие товары и в каком количестве были заказаны 16.01.2003г.

·  Какие товары были заказаны в период с 16.01.2003 по 10.02.2003. Последнюю таблицу отсортируйте по возрастанию значения поля «Дата исполнения».

10.Используя таблицы «Товары» (поле «ОписаниеТовара» и «Заказано» (поле «Количество») создайте запрос, который позволит вам выяснить в каком количестве и на какую сумму были проданы товары. Запрос сохраните под именем «Проданные товары Запрос».

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

Один из вариантов получения запроса после проведения сортировки по полю «Описание товара» приведен ниже.

12.На основании предыдущего запроса создайте новый запрос, для того

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

Учтите, что имя объекта не может превышать 64 символа.

Приведен один из вариантов получения запроса после проведения сортировки по полю «Сумма».

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

Приведен один из вариантов получения запроса после проведения сортировки по полю «Сумма».

14.На основе запроса «Реализация товаров поставщиков

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

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

Ниже приведен один из вариантов получения запроса.

16.Создайте запрос на начисление заработной платы, используя запросы «Начислено Запрос» и «Задержка реализации товаров свыше 10 дней Запрос». При этом:

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

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

Запрос сохраните под именем «Нач с уч задержки реализ товаров свыше 10 дней Запрос». 

7. ИТОГОВЫЕ ЗАДАНИЯ ПО РАЗДЕЛУ

(выполняются по собственной инициативе или выполняются по указанию преподавателя)

Используя созданную базу, выполните запросы:

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

2.  Выясните у кого из ваших сотрудников будет  день рождения в текущем месяце.

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

4.  Дополните предыдущий запрос информацией о том, какие именно товары содержались в не выполненных заказах.

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

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