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

Обновлено: 07.07.2024

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

Простой запрос

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

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

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

Повторяющиеся записи

Запрос такого типа позволяет выбирать из таблицы или простого запроса повторяющиеся записи.

Записи без подчиненных

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

При создании макета запроса в общем случае необходимо выполнить следующие базовые операции:

1) указать системе, какие поля и из каких таблиц мы хотим включить в запрос;

2) указать тип запроса (по умолчанию установлен запрос на выборку);

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

4) описать групповые операции над записями исходных таблиц;

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

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

Создать запрос Адреса для вывода фамилий, имен и адресов студентов .

1) перейдем на вкладку Запросы и щелкнем на кнопке С оздать с помощью мастера, чтобы перейти к созданию запроса;

2) на экране появится диалоговое окно Новый запрос, предназначенное для выбора способа построения запроса;

3) выберем значение Простои запрос и нажмем кнопку ОК;

4) в первом диалоговом окне в списке Таблицы/Запросы выберем таблицу, поля которой будут анализироваться при выполнении запроса (Сведения о студентах);

5) в списке Доступные поля отметим поле Фамилия и перенесем его в список Выбранные поля:

6) те же действия выполним для полей Имя и Адрес. Нажмем кнопку Д алее;

7) в последнем окне присвоим запросу имя Адреса. После этого нажмем кнопку Готово.

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

введем в поле номера >4 – отбор записей, номер которых больше 4;

для даты рождения >12.07.76, - вывод всех, кто родился позже указанной даты;

для фамилий И * – отбор начинающихся на указанную букву.

Примеры использования символов задания шаблонов

обозначает один любой символ

обозначает любое количество любых символов

обозначает любую цифру

Кроме операций сравнения можно использовать арифметические (+ - * / ^), логические ( Or , And , Not ), например, введем в поле фамилия >=В * and ,- отбор записей, чья фамилия начинается на букву от В до Д

Примеры условных выражений

Вывод записей, которые

Имеют значение Пермь

Не имеют значение Пермь

Начинаются на букву от А-Л

Начинаются на букву от П-Я

Имеют значение 50

Имеют значение даты позднее 01.01.2004

Имеют значение месяца февраль

Начинаются с буквы Р

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

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

1) Для выполнения задания воспользуемся конструктором запросов. Активируем в левом меню БД объект запросы и вызовем режим создания запроса с помощью конструктора.

Рис. 14. Диалоговое окно создания запроса.

Рис. 15. Окно конструктора запроса.

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

Рис. 16. Режим конструктора запроса

В верхней половине отображается выбранная таблица.

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

2) Щелчком мыши в строке поле активируем список и последовательно выберем все поля таблицы Таблица расширяется вправо автоматически, когда пользователь добавляет в запрос новые поля.

Задание 11.

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

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

3) Сохранить запрос с именем Фамилия.

4) Выполнить запрос.

Самостоятельная работа № 3

1. Создайте запрос Телефоны, позволяющий выводить фамилии, имена, телефоны студентов.

2. Измените запрос так, чтобы можно было получить данные одного конкретного студента

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

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

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

1) открыть запрос в режиме конструктора;

2) установить указатель в позицию, в которую требуется ввести выражение, и нажать правую клавишу мыши;

3) в контекстном меню выбрать команду П остроить. Другой вариант -нажать кнопку П остроить на панели инструментов.

Построитель выражений состоит из трех разделов.

Рис 17. Построитель выражений

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

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

Кроме традиционных знаков математических действий существует еще несколько операторов:

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

Возведение в степень.

Операция получения остатка от деления целых частей аргументов.

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

? - знак вопроса означает любой одиночный символ

* - звездочка означает любую последовательность символов

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

Чтобы вывести полный список операторов, выберите папку Операторы в нижнем левом поле и нужный тип в среднем поле. В правом поле будут выведены все операторы выбранного типа.

В нижней части окна построителя находятся три поля.

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

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

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

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

Для решения поставленной задачи можно воспользоваться готовым запросом Рейтинг студентов.

Выполним следующие действия:

1) в окне базы данных перейдем на вкладку Запросы и щелкнем на кнопке С оздать, чтобы перейти к созданию запроса;

2) на экране появится диалоговое окно Новый запрос, предназначенное для выбора способа построения запроса;

3) выберем значение Конструктор и нажмем кнопку ОК;

4) добавим таблицу Сведения о студентах, необходимую для создания запроса и запрос Рейтинг студентов;

5) в первую колонку строки Поле бланка запроса поместим поле Фамилия таблицы Сведения о студентах;

6) в строке Сортировка установим значение по возрастанию;

7) во вторую колонку поместим поле Avg_оценка , в котором хранятся средние значения;

8) в строке Условие отбора запустим Построитель выражений;

9) в левой части построителя выберем объект Запрос, а из раскрывшегося списка - запрос Рейтинг студентов;

10) в средней части построителя выражений выберем поле, необходимое для построение выражения - Avg_оценка и вставим его в поле выражений;

11) зададим значение

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

13) аналогично, выберем таблицу Сведения о студентах и поле Пол;

14) укажем условие отбора - ~ "ж";

15) нажмем кнопку ОК после чего окно построителя закроется и мы вернемся в бланк запроса;

16) снимем флажок в строке Вывод на экран столбца Avg_оценка ;

17) сохраним запрос.

Группировка в запросах

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

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

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

В нашем случае отношение между таблицами уже задано, между полями таблиц проведена линия.

1) в окне базы данных перейдем на вкладку Запросы и щелкнем на кнопке С оздать, чтобы перейти к созданию запроса;

2) на экране появится диалоговое окно Новый запрос, предназначенное для выбора способа построения запроса;

3) выберем значение Конструктор и нажмем кнопку ОК;

4) в результате на экране появится два окна: окно конструктора запросов Запрос: Запрос на выборку и окно выбора таблиц Добавление таблицы . Окно Добавление таблицы состоит из трех вкладок, содержащих перечни объектов, предлагаемых программой для проектирования запроса: Таблицы, Запросы, Таблицы и запросы. В бланке запроса укажем параметры запроса и данные, которые нужно отобрать, а также способ их отображения на экране;

5) перейдем на вкладку Таблицы, маркируем таблицу Сведения о студентах и щелкнем на кнопке Д обавить. Затем маркируем таблицу Успеваемость группы и снова выполним щелчок на кнопке Д обавить. Закроем диалоговое окно (кнопка З акрыть). В окне проектирования запроса появятся имена выбранных таблиц;

6) установим тип запроса Выборка, нажав кнопку Тип запроса на панели инструментов;

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

8) аналогично во вторую колонку строки Поле вставим имя поля Имя из таблицы Сведения о студентах;

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

10) аналогичные операции выполним для поля Имя таблицы Сведения о студентах;

11) вставим в бланк запроса поле Оценка из таблицы Годовая Успеваемость группы;

12) в бланке запроса добавим строку Групповые операции;

13) в полях Фамилия и Имя строки Групповые операции установим значение Группировка:

14) в поле Оценка установим значение Avg . Нам необходимо, чтобы после запятой стояло только одно число (например, 3.4 или 4.5). Для этого пало указать формат вывода результатов в запросе. Выполним следующие операции: в строке Групповая операция поля Оценка щелкнем правой клавишей мыши и из раскрывшегося меню выберем команду Свойства. В строке Формат поля окна Свойства поля введем значение Фиксированный, а в поле Число десятичных знаков -ч исловой. Можно также указать подпись поля - Средний балл.

15) сохраним запрос с именем Рейтинг студентов.

Самостоятельная работа № 4

1. Исправьте запрос Рейтинг студентов так, чтобы выводились данные только хорошо успевающих студентов.

2. Создать запрос Наименьшая оценка, позволяющий выводить минимальную оценку каждого студента группы.

Сложные запросы

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

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

Для создания запроса на обновление выполните следующее:

1) предварительно составьте запрос на выборку;

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

3) после этого вернитесь в режим конструктора и активизируйте команду Обновление из меню Запрос;

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

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

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

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

1) предварительно подготовьте запрос на выборку;

2) выполните составленный запрос для проверки его правильности;

3) вернитесь в режим конструктора и в меню Запрос выберите команда Создание таблицы, в которое необходимо ввести имя новой таблицы;

4) выполните запрос, нажав кнопку с восклицательным знаком на панели инструментов;

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

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

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

1) для отбора добавляемых записей сначала составьте запрос на выборку;

2) выполните составленный запрос и оцените результат, переключились в режим таблицы с помощью команды Режим таблицы из меню Вид;

3) после этого вернитесь в режим конструктора и активизируйте команду Добавление из меню Запрос;

4) в открывшемся при этом диалоговом окне Добавление задайте в поле Имя таблицы имя таблицы, к которой вы будете присоединять данные из выбранного набора записей;

5) после нажатия кнопки OK Access добавляет в бланк запроса строку Добавление. В эту строку автоматически или в ручную вставляются имена тех полей целевой таблицы, которые совпадают с именами полей запроса;

6) выполните запрос, нажав кнопку Запуск на панели инструментов;

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

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

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

Для создания запроса на удаление выполните следующее:

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

3) после этого вернитесь в режим конструктора и активизируйте команду Удаление из меню Запрос;

4) Access добавит в бланк запроса строку Удаление и введет в ее ячейки значение Условие. Это означает, что пользователь может установить дополнительные критерии отбора;

5) выполните запрос, нажав кнопку Запуск на панели инструментов;

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

Удалите из таблицы Выборка дней рождения учеников данные об учениках, родившихся с I по 12 число.

Простой запрос

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

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

Повторяющиеся записи

Запрос такого типа позволяет выбирать из таблицы или простого запроса повторяющиеся записи.

Записи без подчиненных

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

При создании макета запроса в общем случае необходимо выполнить следующие базовые операции:

1) указать системе, какие поля и из каких таблиц мы хотим включить в запрос;

2) указать тип запроса (по умолчанию установлен запрос на выборку);

4) описать групповые операции над записями исходных таблиц;

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

Создать запрос Адреса для вывода фамилий, имен и адресов студентов .

2) на экране появится диалоговое окно Новый запрос, предназначенное для выбора способа построения запроса;

3) выберем значение Простои запрос и нажмем кнопку ОК;

5) в списке Доступные поля отметим поле Фамилия и перенесем его в список Выбранные поля:

6) те же действия выполним для полей Имя и Адрес. Нажмем кнопку Д алее;

7) в последнем окне присвоим запросу имя Адреса. После этого нажмем кнопку Готово.

введем в поле номера >4 – отбор записей, номер которых больше 4;

для даты рождения >12.07.76, - вывод всех, кто родился позже указанной даты;

для фамилий И * – отбор начинающихся на указанную букву.

Примеры использования символов задания шаблонов

обозначает один любой символ

обозначает любое количество любых символов

обозначает любую цифру

Примеры условных выражений

Вывод записей, которые

Имеют значение Пермь

Не имеют значение Пермь

Начинаются на букву от А-Л

Начинаются на букву от П-Я

Имеют значение 50

Имеют значение даты позднее 01.01.2004

Имеют значение месяца февраль

Начинаются с буквы Р

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

Рис. 14. Диалоговое окно создания запроса.

Рис. 15. Окно конструктора запроса.

Рис. 16. Режим конструктора запроса

В верхней половине отображается выбранная таблица.

Задание 11.

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

3) Сохранить запрос с именем Фамилия.

4) Выполнить запрос.

Самостоятельная работа № 3

1. Создайте запрос Телефоны, позволяющий выводить фамилии, имена, телефоны студентов.

2. Измените запрос так, чтобы можно было получить данные одного конкретного студента

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

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

1) открыть запрос в режиме конструктора;

2) установить указатель в позицию, в которую требуется ввести выражение, и нажать правую клавишу мыши;

Построитель выражений состоит из трех разделов.

Рис 17. Построитель выражений

Кроме традиционных знаков математических действий существует еще несколько операторов:

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

Возведение в степень.

Операция получения остатка от деления целых частей аргументов.

? - знак вопроса означает любой одиночный символ

* - звездочка означает любую последовательность символов

В нижней части окна построителя находятся три поля.

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

Для решения поставленной задачи можно воспользоваться готовым запросом Рейтинг студентов.

Выполним следующие действия:

2) на экране появится диалоговое окно Новый запрос, предназначенное для выбора способа построения запроса;

3) выберем значение Конструктор и нажмем кнопку ОК;

4) добавим таблицу Сведения о студентах, необходимую для создания запроса и запрос Рейтинг студентов;

5) в первую колонку строки Поле бланка запроса поместим поле Фамилия таблицы Сведения о студентах;

6) в строке Сортировка установим значение по возрастанию;

7) во вторую колонку поместим поле Avg_оценка , в котором хранятся средние значения;

8) в строке Условие отбора запустим Построитель выражений;

9) в левой части построителя выберем объект Запрос, а из раскрывшегося списка - запрос Рейтинг студентов;

11) зададим значение

13) аналогично, выберем таблицу Сведения о студентах и поле Пол;

14) укажем условие отбора - ~ "ж";

15) нажмем кнопку ОК после чего окно построителя закроется и мы вернемся в бланк запроса;

16) снимем флажок в строке Вывод на экран столбца Avg_оценка ;

17) сохраним запрос.

Группировка в запросах

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

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

В нашем случае отношение между таблицами уже задано, между полями таблиц проведена линия.

2) на экране появится диалоговое окно Новый запрос, предназначенное для выбора способа построения запроса;

3) выберем значение Конструктор и нажмем кнопку ОК;

6) установим тип запроса Выборка, нажав кнопку Тип запроса на панели инструментов;

8) аналогично во вторую колонку строки Поле вставим имя поля Имя из таблицы Сведения о студентах;

10) аналогичные операции выполним для поля Имя таблицы Сведения о студентах;

11) вставим в бланк запроса поле Оценка из таблицы Годовая Успеваемость группы;

12) в бланке запроса добавим строку Групповые операции;

13) в полях Фамилия и Имя строки Групповые операции установим значение Группировка:

15) сохраним запрос с именем Рейтинг студентов.

Самостоятельная работа № 4

1. Исправьте запрос Рейтинг студентов так, чтобы выводились данные только хорошо успевающих студентов.

2. Создать запрос Наименьшая оценка, позволяющий выводить минимальную оценку каждого студента группы.

Сложные запросы

Для создания запроса на обновление выполните следующее:

1) предварительно составьте запрос на выборку;

3) после этого вернитесь в режим конструктора и активизируйте команду Обновление из меню Запрос;

1) предварительно подготовьте запрос на выборку;

2) выполните составленный запрос для проверки его правильности;

4) выполните запрос, нажав кнопку с восклицательным знаком на панели инструментов;

1) для отбора добавляемых записей сначала составьте запрос на выборку;

3) после этого вернитесь в режим конструктора и активизируйте команду Добавление из меню Запрос;

6) выполните запрос, нажав кнопку Запуск на панели инструментов;

Для создания запроса на удаление выполните следующее:

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

3) после этого вернитесь в режим конструктора и активизируйте команду Удаление из меню Запрос;

5) выполните запрос, нажав кнопку Запуск на панели инструментов;

Удалите из таблицы Выборка дней рождения учеников данные об учениках, родившихся с I по 12 число.

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

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

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

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

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

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

· Запрос на присоединение ( Append ) – добавляет целые записи или только указанные поля в таблице.

· Запрос на обновление ( Update ) – изменяет данные в существующих таблицах на основании информации в окне Конструктора.

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

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

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

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

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

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

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

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

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

2. Выбрать строку , и раскрыть её.

3. Выбрать строку с наименованием , нажать на кнопку .

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

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

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

1. Как отображаются результаты запроса?

2. Какие основные типы запросов создают в базе данных Access 2010?

3. Чем отличаются запросы на выборку от запросов на изменение?

4. Какие средства предложены в Access 2010 для создания запросов?

5. В каком порядке следует работать с Конструктором запросов?

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

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

1. Поместим таблицы на поле запроса.

2. На бланк запроса перенесём поля из таблиц (Фамилия, Телефон, Фото, Наим_отдела , Должность).

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

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

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

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

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

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

2. Как задать параметр в виде текстового фрагмента?

3. Можно ли задавать несколько параметров в одном запросе для различных полей?

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

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

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

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

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

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

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

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

o Раздел в верхней части окна содержит поле, в котором создаётся выражение.

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

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

3. В пустом поле щёлкните мышкой, и на ленте щёлкните по пиктограмме , после чего откроется Построитель выражений.

5. В бланке запроса отобразится, создаваемое выражение (Рис. 82), сохраните запрос, например под именем «Фактический стаж работы.

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

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

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

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

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

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

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

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

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

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

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

1. Какая последовательность запуска Построителя выражений при составлении запросов?

3. Как связывается бланк запроса с Построителем выражений?

4. Из каких элементов состоит «Выражение?

5. Что обозначают открытая и закрытая квадратные скобки в выражении?

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

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

Для расширения возможностей работы с данными в СУБД существует специальный объект – запрос .

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

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

Запрос можно создать с помощью мастера. Но наиболее универсальным является создание запроса с помощью конструктора.

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

СУБД Access позволяет создать разнообразные виды запросов. Их можно разделить на две большие группы: запросы на выборку и специальные запросы.

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

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

В таблице 1 приведена краткая характеристика видов запросов на выборку.

Таблица 1. Запросы на выборку

Тип

Возможности

Выборка
по всем записям

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

Выборка с условием

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

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

Условие отбора указывается в запросе неявно и формируется в момент обращения к запросу.

Запрос с вычисляемыми полями

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

В таблице 2 приведены примеры условий отбора

Таблица 2. Примеры условий отбора

Вид условия

Примечание

Отбор записей

Для текстовых полей кавычки обязательны

значение поля совпадает со словом "Информатика" (Иначе говоря, полное совпадение текстовой строки)

значение поля – текстовая строка, начинающаяся на букву П

значение поля – текстовая строка, содержащая букву п

значение поля равно 500

значение поля больше либо равно 16

значение поля не равно 0

вид записи условия отбора в запросе с параметром

Технология создания запроса на выборку

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

Задание 1. Запрос на выборку

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

Технология работы

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

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

Двойным щелчком выберите

из списка полей таблицы Группа поле Номер группы;
из списка полей таблицы Студент поля Фамилия, Имя, Отчество. Выбранные поля появятся в столбцах нижней части бланка.

Задайте сортировку по полям Номер группы и Фамилия.
Выполните запрос. Для этого щелкните на кнопке Запуск (или меню Запрос/Запуск). Просмотрите результаты отбора. Вы получили список всех студентов, сгруппированный по номерам учебных групп, и в каждой группе фамилии студентов отсортированы по алфавиту.
Перейдите в режим конструктора (Вид/Конструктор).
Для поля Номер группы задайте условие отбора – ДО-11 (или другое значение). Отмените сортировку по группе – она теперь не нужна (Рис. 1).
Выполните запрос снова. Просмотрите результаты отбора. Вы получили список всех студентов указанной учебной группы.
Сохраните запрос (меню Файл/Сохранить или кнопка на панели инструментов). Для этого щелкните на кнопке. Задайте имя запроса – Список группы.
Измените в условии отбора номер группы – ДО-21. Просмотрите результаты запроса.
Закройте запрос. Убедитесь, что его имя появилось в окне Запросы.

Рис. 1. Бланк запроса на выборку

Задание 2. Запрос с параметром

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

Преобразовать запрос Список группы в запрос с параметрами.

Технология работы

Откройте запрос Список группы в режиме конструктора.
В строке Условие отбора для поля Номер группы введите фразу [Введите номер группы] (фразу требуется вводить в КВАДРАТНЫХ СКОБКАХ) (Рис. 2).

Рис. 2. Бланк запроса с параметром

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

Задание 3. Запрос с двумя параметрами

Создать запрос с параметром Дисциплины-Оценки-Группы на основе четырех таблиц.

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

Технология работы

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

из таблицы Дисциплина – поле Название
из таблицы Группа – поле Номер группы
из таблицы Студент – поля Фамилия, Имя, Отчество студента
из таблицы Оценка – поле Оценки

Задайте сортировку по фамилиям.
Задайте параметрическое условие отбора по полю Название в виде фразы [Введите название дисциплины] и параметрическое условие отбора по полю Номер группы – [Введите номер группы ] (Рис. 3).
Добавьте в запрос условие отбора только тех студентов, которые получили оценку 4 или 5. Для этого в строке Условие отбора для поля Оценка введите условие >3 (Рис. 3).

Рис. 3. Запрос с двумя параметрами

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

Задание 4. Вычисляемые поля в запросе

Создать запрос Студенты (выч-поля) по таблице Студент в котором будет вычисляться возраст студента.

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

Технология работы

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

Возраст: Year(Now()-[Студенты]![Дата рождения])-1900

Для создания формулы проделайте следующие действия

Рис. 4. Формула в Построителе выражений

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

Виды специальных запросов

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

Перекрестные запросы

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

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

Задание 5. Перекрестный запрос с помощью мастера

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

Технология работы

1-й этап. Создание вспомогательного запроса

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

2-й этап. Создание перекрестного запроса

В окне Запросы щелкните на кнопке Создать.
Выберите Перекрестный запрос и нажмите Ok.
На первом шаге работы мастера установите переключатель Запросы, выберите запрос Оценки по предметам.
На следующем шаге выберите поле Название и перенесите его в правое окно (Рис. 5).
На следующем шаге выберите поле Номер группы (Рис. 6).
На следующем шаге выберите поле Оценка и функцию Среднее.
На следующем шаге введите название запроса Средние оценки. Готово. Откроется таблица перекрестного запроса. Обратите внимание на то, что Access создает еще общее итоговое значение средних оценок по каждой группе.
Если итоговые значения средних оценок имеют большое количество десятичных знаков, откройте запрос в режиме конструктора, щелкните правой кнопкой на название итогового поля, выберите в контекстном меню пункт Свойства. На вкладке Общие установите Формат поля – Фиксированный, Число знаков ? 2. Просмотрите результаты запроса (Рис. 7).

Рис. 5. 2-й шаг мастера перекрестного запроса

Рис. 6. 3-й шаг мастера перекрестного запроса

Рис. 7. Результат перекрестного запроса

Запросы на создание таблицы, обновление, удаление

Задание 6. Запрос на создание таблицы

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

На основе этого запроса создать таблицу Студенты-отличники.

Технология работы

Создайте запрос в режиме конструктора.
Включите в него таблицы Группа, Студент и Оценка.
Выберите поля Фамилия, Имя, Отчество и Оценки.
Выполните команду Вид/Групповые операции. В нижней части бланка запроса появится еще одна строка – Групповая операция.
Для поля Оценки установите в этой строке функцию Sum (Рис. 8) и введите в строке Условие отбора значение 20 (это максимальная сумма всех оценок по 4-м дисциплинам).

Рис. 8 Бланк запроса с групповой операцией

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

Задание 7. Запрос на изменение (обновление)

Создайте запрос Изменение оплаты, в котором для групп, которые еще не закончили обучение автоматически будет увеличена оплата за обучение на 10%.

Технология работы

Создайте новый запрос.
Включите в запрос таблицу Группа .
Выберите поля Номер группы, Оплата за семестр, Обучение закончено.
Выполните запрос и просмотрите результаты. Будут отобраны все группы.
Перейдите в режим конструктора (Вид/Конструктор).
В строке Условие отбора для поля Обучение закончено введите значение Ложь.
Выполните запрос. Будут отобраны группы, которые не закончили обучение.
Перейдите в режим конструктора (Вид/Конструктор).
В меню Запрос выберите пункт Обновление. На бланке запроса появится строка Обновление.
В строке Обновление для поля Оплата за семестр введите выражение – условие увеличения оплаты на 10%, которое имеет вид [Оплата за семестр]*1,1. Это выражение можно ввести вручную или использовать построитель выражений.
Выполните запрос ТОЛЬКО 1 раз. Во время выполнения подтвердите согласие на обновление данных.
Закройте запрос и сохраните его с именем Изменение оплаты.
Откройте таблицу Группа.
Просмотрите результаты работы запроса – оплата увеличилась, но только для тех групп, которые не закончили обучение.

Задание 8. Запросы на удаление записей из таблицы

Создайте запрос на удаление из базы данных студента Перлова.

Технология работы

Создайте запрос в режиме конструктора.
Добавьте в запрос таблицу Студент.
Выберите поля Фамилия, Имя, Отчество. В строке условие отбора для поля Фамилия введите значение – Перлов.
Выполните запрос и просмотрите результаты отбора. Должен быть отобран указанный студент.
Перейдите в режим конструктора.
В меню Запрос выберите тип – Удаление.
Сохраните запрос с именем Удаление студента.
Выполните запрос (ВНИМАНИЕ! Результаты запроса необратимы).
Убедитесь, что из базы данных удален студент и все полученные им оценки.

Задание 9. Формы для запросов

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

Читайте также: