Как создать запрос к базе данных Microsoft Access. Создание и изменение запроса с помощью конструктора запросов Отбор данных с помощью запросов в access
Лекция 16. Создание запросов средствами MS Access 2000
Запросы являются единственным средств позволяющим извлекать, необходимую пользователям, информацию, а также средством управления данными. Опираясь на логику реляционных операций, запросы позволяют выполнять:
§ соединение данных нескольких таблиц;
§ отображение только требуемых полей;
§ формирование сложных критериев отбора записей;
§ вычисления с использованием данных из исходных таблиц;
§ группировку информации по каким-либо критериям;
§ модификацию данных в таблицах.
MS Access предоставляет удобные механизмы быстрого создания запросов без особой подготовки, тогда как в других СУБД часто требуются знание языковых средств, для создания запросов. Для создания запросов MS Access предлагает следующие средства:
§ Конструктор запросов – средство конструирования запросов с использованием QBE (Query by example – запрос по образцу), требует минимальных знаний. Средство, уступающее режиму SQL .
§ Простой запрос – позволяет быстро создавать запросы на основе одной или нескольких таблиц, в том числе итоговые запросы.
§ Перекрестный запрос – позволяет создавать результирующие таблицы на основе результатов расчетов, полученных при анализе группы таблиц;
§ Запрос на повторяющиеся записи – средство создания запросов для выявления повторяющихся записей, выполнено в виде мастера.
§ Записи без подчиненных – позволяет создать запрос, отыскивающий те записи главной таблицы, которые не имеют подчиненных записей в дочерних таблицах.
Создание запросов начинается с выбора категории «Запросы» окна диалога «Базы данных». Затем, кнопкой «Создать» может быть вызвано окно диалога «Новый запрос», в котором выбирается один из вариантов создания запроса (рисунок 16.1).
Рисунок 16.1 - Диалоговое окно «Новый запрос»
Создание простых запросов с помощью мастера
Мастер запросов позволяет сформировать запрос на основе одной или нескольких таблиц с возможностью группирования (итоговый запрос), но без определения условий выборки, именно поэтому он получил название «Простой запрос».
Рисунок 16.2 - Окно мастера создания запросов
Процесс создания запроса с помощью мастера сводится к следующим шагам:
§ Запускается мастер создания простых запросов, например командой – Создание запроса с помощью мастера
§ в раскрывающемся списке «Таблицы и запросы» последовательно выбираются таблицы или запросы, информация из которых необходима пользователю, а затем, из списка «Доступные поля» в список «Выбранные поля» перемещаются требуемые поля (рисунок 16.2).
§ На втором шаге работы мастера определяется тип запроса: подробный или итоговый. Если выбран итоговый запрос, то необходимо определить итоговые операции над полями запроса: Max , Min , Sum , Avg или Count .
§ На третьем шаге указываем имя запроса и один из вариантов действий: открыть запрос для просмотра данных или изменить макет запроса , в первом случае результаты запроса будут выданы на экран, а во втором, запрос откроется в режиме конструктора.
Создание запроса в режиме конструктора
Конструктор редко используется для создания запросов, чаще используется как средство модификации уже существующих. Конструктор запросов использует QBE (Query by example – запрос по образцу) позволяющий сформировать относительно сложные запросы на основе специального бланка, заполнение которого позволяет сформировать запрос, безусловно, этот способ уступает непосредственному использованию конструкций языка SQL , но требует значительно меньших знаний.
При необходимости можно использовать язык SQL , для этого необходимо перейти в режим SQL , переход выполняется командой контекстного меню конструктора запросов – «Режим SQL ».
Окно конструктора (рисунок 16.3) разделено на две части. Верхняя часть предназначена для расположения таблиц, на основе которых формируется запрос. Добавление таблиц в запрос осуществляется командой панели инструментов - Отобразить таблицу .
Рисунок 16.3 - Окно конструктора запроса. Пример ввода условия.
Нижняя часть является бланком запроса (QBE), з десь выполняется построение самого запроса. Формирование запроса в QBE осуществляется определением таблиц и полей, участвующих в запросе, параметров вывода, сортировки, указанием условия отбора, вычислений и т.д.
Включение новых полей в запрос выполняется простым перетаскиванием имени поля из списков полей таблиц (верхней части конструктора) в требуемый столбец строки «Поле:», при этом имя таблицы определяется автоматически.
При необходимости сортировки по какому-либо полю в столбце этого поля устанавливается свойство «Сортировка» в значение По возрастанию или По убыванию , выбором из раскрывающегося списка.
Строка – «Вывод на экран» управляет отображением полей в результирующем наборе данных, полученном в результате выполнения запроса.
Для любого из полей можно указать условие отбора, тем самым, выполнив фильтрацию данных.
Поля запроса, наследуют свойства, заданные для соответствующих полей таблицы, но можно изменить формат вывода полей. Для изменения формата вывода полей используется команда контекстного меню – Свойства .
Количество строк в QBE может варьироваться в зависимости от типа создаваемого запроса, например при использовании группировки появится новая строка – «Групповая операция».
Установка критериев отбора записей
Определение критериев выборки в запросах является одной из основных задач. Чтобы сформировать требуемый пользователю набор данных необходимо определить значения условий отбора в строке «Условие отбора:» QBE. В условиях отбора можно использовать логические операторы (or , and , not ), операторы сравнения (<, >, <=, >=, <>, = ), а также операторы Between , In и Like .
Можно задать несколько условий отбора, соединенных логическим оператором или ( or ) , для некоторого поля одним из двух способов:
1. ввести всё условие в одну ячейку строки «Условие отбора:», с использованием логическогооператораили (or ).
2. одну часть условия указать в ячейке строки «Условие отбора:», а вторую часть условия вписать в нижнюю ячейку строки «Или:».
Оба варианта будут эквивалентными, второй вариант изображен на рисунке 16.4. Результатом этого запроса будет список студентов имеющих оценки 4 или 5.
Рисунок 16.4 - Использование логического оператора или ( or ) .
Логическая операцияи ( and ) используется в том случае, когда должны быть выполнены оба условия одновременно и только в этом случае запись будет включена в результирующий набор данных. Например, условие >2 and <5 даст список студентов имеющих оценки 3 и 4.
Логическая операция and может быть использована не только для одного поля, но и для нескольких полей, условия в которых должны выполниться одновременно, однако, в этом случае она используется неявно. На рисунке 16.5 показано неявное использование логической операции and . В результате такого запроса будут выданы все студенты, проживающие в городе «Уфа» и имеющие оценку 5. Фактически, если заглянуть в код SQL (в режиме SQL ) то можно увидеть следующее условие:
[Студенты].[Город] = “Уфа” and [Студенты и занятия].[Балл] = “5”
Рисунок 16.5 – Пример использования логической операции и ( and )
Операторы и и или применяются как отдельно, так и в комбинации. Следует помнить, что условия связанные оператором и выполняются раньше условий, объединенных оператором или .
Оператор B etween позволяет задать диапазон значений, например диапазон от 10 до 20 может быть определен следующим образом:
between 10 and 20
Оператор I n позволяет использовать для сравнения список значений, указываемый в качестве аргумента. Например:
in (“первый”,”второй”,”третий”)
Оператор L ike полезен для поиска образцов в текстовых полях, причем позволяет использовать шаблоны:
* - обозначает любое количество символов;
Любой одиночный символ;
# - указывает, что в данной позиции должна быть цифра.
Например, для выбора фамилии, начинающейся с буквы П и с окончанием “ов” можно записать следующие условие:
Like П*ов
Особый подход к работе с полями типа дата. Значение даты в условиях отбора должно быть заключено в символы #. Например:
>#31.12.1996#
Если требуется выделить временные события конкретного дня, месяца или года, то можно использовать встроенные функции MS Access :
§ Day (дата) – возвращает значение дня месяца в диапазоне от 1 до 31;
§ Month (дата) – возвращает значение месяца в диапазоне от 1 до 12;
§ Year (дата) – возвращает значение года в диапазоне от 100 до 9999;
Текущую дату можно определить функцией Now (), используемую без аргументов.
Вычисляемые поля
В запросах на выборку можно определять вычисляемые поля, значения которых будут получены в результате каких-либо арифметических операций над полями участвующими в запросе. Например, если необходимо определить размер надбавки, равной 15% от оклада, то можно записать в строке «Поле» свободного столбца бланка запросов следующее выражение:
Надбавка: [ Оклад]*0.15
В результате будет создано вычисляемое поле с именем «Надбавка», содержащее результат вычисления для каждой строки полученного набора данных.
При создании выражений для вычисляемых полей можно использовать «Построитель выражений», который можно вызвать кнопкой «Построить», панели инструментов.
В верхней части окна область ввода, в которой редактируется выражение. Выражение может быть введено вручную, но обычно используются кнопки арифметических и логических операторов, расположенные под областью ввода. Нижний, средний список содержит перечень полей входящих в запрос, эти поля можно использовать в выражении. Поля добавляются двойным кликом мыши или нажатием кнопки «Вставить». пи поля полей входящих в запрос, которые можно использовать в выражении.ражений
Допустим, существует запрос «Отпуск товаров со склада», который содержит поля «КолОтп» (количество отпущенное) и «ЦОтп» (цена отпускная), требуется определить сумму продажи. Открываем запрос в режиме конструктора, переходим на пустой столбец и запускаем построитель выражений командой «Построить», контекстного меню . Находим в среднем списке имя поля «ЦОтп» и дважды щелкаем по нему мышкой. Нажимаем кнопку «*» и повторяем ту же последовательность действий для поля «КолОтп». Получаем требуемое выражение (рисунок 16.6).
Рисунок 16.6 - Окно построителя выражений
Прежде чем применить созданное выражение, рекомендуется определить имя вычисляемого поля, например «Сумма»:
Сумма: [ЦОтп]*[КолОтп]
После нажатия «ОК» полученный результат будет помещен в новый столбец бланка QBE.
Контрольные вопросы
1. Что такое запрос?
2. В чем заключаются функции запроса?
3. Какие способы создания запросов существуют в MS Access ?
4. Чем отличаются возможности различных способов создания запросов?
5. Как создается запрос с помощью мастера?
6. Что представляет собой бланк запросов?
7. Как сформировать условия отбора записей?
8. Назовите основные логические операторы.
9. Что представляет собой оператор b etween?
10. В чем назначение оператора Like ?
11. Как создаются вычисляемые поля?
12. Преимущества и недостатки конструктора запросов?
Задания для самостоятельной работы
Задание 1. Создайте запрос «Просроченные книги», по следующему описанию:
1. В окне «Базы данных» выбираем категорию «Запросы».
2. Открываем окно мастера командой «Создание запроса с помощью мастера».
3. В раскрывающемся списке «Таблиц и запросы» выбираем таблицу «Книги». Из списка «Доступные поля» в список «Выбранные поля» перемещаем поля «Название» и «Автор».
4. В раскрывающемся списке «Таблиц и запросы» выбираем таблицу «Экземпляры». Из списка «Доступные поля» в список «Выбранные поля» перемещаем поля «Инвентарный номер», «Дата возврата» и «Наличие».
5. В раскрывающемся списке «Таблиц и запросы» выбираем таблицу «Читатели». Из списка «Доступные поля» в список «Выбранные поля» перемещаем поля «Номер_ЧБ», «ФИО», «Тел_дом», «Тел_раб». Нажимаем кнопку «Далее».
6. На данном этапе выбираем подробный запрос и нажимаем кнопку «Далее».
7. Указываем имя запросаПросроченные_книги и нажимаем кнопку «Готово».
8. Откроем запрос«Просроченные_книги» в режиме конструктора, нажав кнопку «Конструктор» (рисунок 16.7).
9. В бланке запроса в строке «Условие отбора» для поля «Дата_возврата» укажем значение < Now ().
10. В бланке запроса в строке «Условие отбора» для поля «Наличие» установим значение нет и снимем флажок «Вывод на экран».
11. Закроем окно конструктора.
Таким образом, отбираемые записи должны удовлетворять сразу двум условиям: дата возврата должна быть меньше текущей и книга должна отсутствовать в библиотеке.
Задание 2. Создайте простой запрос, на получение информации о том, к каким предметным областям относятся книги.
Задание 3. Из запроса «Просроченные_книги» извлеките информацию о должниках (Номер читательского билета, ФИО, Домашний и рабочий телефоны)
Задание 4. Создайте запрос на получение списка книг одного автора.
Губская Ольга Васильевна
Тема урока : Создание запросов в СУБД Access
11 класс
Цель урока : Повторить основные понятия темы Базы данных, Системы управления базами данных; познакомить учащихся с различными способами создания запросов в БД; научить отбирать записи по нескольким условиям, использовать выражения в запросах
Тип урока, продолжительность урока.
Комбинированный урок, 45 минут.
Средства обучения .
Технические средства: - мультимедийный проектор.
Программные средства обучения: - программа MS Access 2007, MS PowerPoint 2007.
Информационные средства: - созданная учителем база данных «Школьная библиотека», презентация «Создание запросов в СУБД Access 2007», задания для компьютерного практикума и самостоятельной работы; тестовые задания.
План урока:
Организационный момент (1 минута )
Постановка темы и целей урока (1 минута)
Фронтальный опрос (7 минут)
Ознакомление с новым материалом (15 минут)
Первичное осмысление и применение изученного.
- Компьютерный практикум
(10 минут)
- Самостоятельная работа, тестирование по теме (8 минут
)
Подведение итогов урока, выставление оценок (2 минуты)
Домашнее задание (1 минута )
Ход урока
1. Организационный момент
2. Постановка темы и целей урока
Слайд 1
Мы продолжаем изучать базы данных, на прошлых уроках вы познакомились с понятием базы данных, с понятием СУБД, с различными классификациями баз данных, с полями и записями, с типами данных и с операциями над информацией в базах данных. Тема сегодняшнего урока: «Создание запросов в СУБД Access 2007»
Слайд 2
Итак, на этом уроке мы:
1 . Повторим основные понятия темы Базы данных, Системы управления базами данных;
2. Познакомимся с различными способами создания запросов в БД;
3. Научимся отбирать записи по нескольким условиям, использовать выражения в запросах.
3. Фронтальный опрос
Слайд 3
Чтобы повторить основные понятия темы, я предлагаю вам «прогуляться» по полю понятий. Выбираете номер овала, появляется термин и вы формулируете соответствующее правило. (Приложение 1)
4. Ознакомление с новым материалом
Вы научились создавать таблицы, формы. На очереди запросы. Так что же такое запрос?
Слайд 4
Запрос – это требование на отбор записей, хранящихся в таблицах или на выполнение определённых действий с ними.
Запрос это временная таблица. Это значит, что данные в запросах не хранятся постоянно, а только временно вызываются из таблицы по заданному шаблону, в момент активизации запроса. Поэтому в базе данных постоянно сохраняется только шаблон вызова данных, а не сама информация. Следовательно, запрос не занимает много места в БД.
Запросы позволяют :
выбирать нужные поля из разных таблиц БД;
фильтровать и сортировать данные;
производить расчёты;
группировать записи, рассчитывая различные итоговые значения;
вносить изменения сразу в несколько записей;
создавать таблицы как результат запроса.
Слайд 5
Какими бывают запросы?
Запрос на выборку. Осуществляет загрузку и отображение записей в режиме таблицы.
Запрос на изменение. Выполняет операции с записями, удовлетворяющими заданным критериям.
а) запрос на удаление (удаляет заданные записи из таблицы);
б) запрос на обновление (изменяет соответствующие записи таблицы);
в) запрос на добавление (добавляет в конец таблицы новые записи соответствующие условиям отбора).
Запрос с параметрами. П осле запуска на выполнение просит ввести значения определенных параметров.
Перекрёстный запрос. Позволяет выполнить подсчёты по данным одного поля таблицы и группировать результаты.
Слайд 6
Способы создания запросов
SQL;
Конструктор;
Мастер.
Существует универсальный язык запросов, на котором формулируются запросы во многих СУБД. Он называется SQL (Structured Query Language [ strʌk.tʃəd ˈkwɪə.ri ˈlæŋɡwɪdʒ ]) – структурированный язык запросов. В большинстве современных СУБД существуют вспомогательные средства для составления запросов, позволяющие обходиться без изучения SQL . В MS Access это конструктор запросов .
Простой запрос на выборку или один из 3-х специальных запросов можно создать с помощью Мастера , но конструктор является более универсальным способом создания запросов.
Слайд 7
Итак, запрос на выборку - это производная таблица , которая содержит те же структурные элементы, что и обычная таблица (столбцы-поля и строки), и формируется на основе фактических данных системы.
Например, есть база данных Школьная библиотека, в которой создана таблица Книги. Запрос на выборку записей по полям Автор, Название, Год издания будет выглядеть как на слайде.
Рассмотрим процесс создания этого запроса с помощью Мастера.
Слайд 8
1 шаг – на вкладке Создание выбираем Мастер запросов.
2 шаг – в диалоге Новый запрос выбираем Простой запрос à ОК
Слайд 9
3 шаг – в диалоге Создание простых запросов выбираем из списка таблицу
Слайд 10
4 шаг – перемещаем из списка Доступные поля в список Выбранные поля нужные имена полей à нажимаем кнопку Далее >
Слайд 11
5 шаг – выбираем один из вариантов отображения записей с данными: подробный (без выполнения операции сведения) или итоговый с использованием сведения (нахождение суммы, среднего значения, минимума, максимума для определенных записей) à нажимаем кнопку Далее >
Слайд 12
6 шаг – вводим имя запроса à нажимаем кнопку Готово.
Слайд 13
Теперь рассмотрим пример создания запроса при помощи Конструктора.
1 шаг – на вкладке Создание выбираем Конструктор запросов.
2 шаг – в диалоге Добавление таблицы выбираем таблицы, поля из которых будут использоваться в запросе à Добавить à Закрыть
Появляется бланк запроса, в верхней части которого отображаются макеты выбранных таблиц. А нижняя часть содержит такие строки:
Поле , в которой будут отображаться имена выбранных полей;
Имя таблицы , в которой будет отображаться имена таблиц, в состав которых входят соответствующие поля;
Сортировка , в которой при необходимости можно выбрать вид сортировки;
Вывод на экран , содержащая флажок, установленная метка которого свидетельствует о том, что данное будет отображаться в запросе;
Условие отбора , которая может содержать условие – выражение для выборки данных из соответствующего поля;
или – для записи еще одного выражения, которое является частью составного условия.
Слайд 14
3 шаг – выбираем нужные поля.
Чтобы выбрать поле, можно выполнить двойной щелчок по этому полю, или перетащить его из макета таблицы в строку Поле.
Слайд 15
4 шаг – вводим условие отбора и нажимаем кнопку Выполнить .
Слайд 16
5 шаг – закроем вкладку запроса à в окне сохранения структуры запроса выберем Да à введем имя запроса à ОК
Слайд 17
Логическое выражение, представляет собой операцию отношения, при записи которого используются символы >,<,=, ≠, ≤, ≥. Условие отбора одного поля может быть составным, при этом два логических выражения соединяются знаком логической операции.
Рассмотрим несколько примеров
Слайд 18
Из таблицы Книги выбрать рассказы А.П.Чехова. Условий отбора будет два: по полю Автор и по Полю Наименование. Если условия отбора записаны в одной строке, то они соединяются операцией «и».
Слайд 19
Запрос на выборку: Книги, изданные в 1987 году, или в которых больше 300 страниц. Условия отбора по полям Год издания и Количество страниц записываются на разных строках, поэтому они соединяются операцией «или».
Слайд 20
Чтобы создать запрос с параметром, надо в строку отбора ввести в квадратных скобках текст, который будет отображаться в диалоговом окне, открываемом в начале выполнения запроса.
Слайд 21
Рассмотрим некоторые условия отбора.
Слайд 22
И примеры использования символов в шаблонах.
5. Первичное осмысление и применение изученного
.
- Компьютерный практикум
1) Создание запроса в режиме Мастера
Создать запрос на выборку фамилии, имени и класса обучения: откройте базу данных Школьная библиотека откройте таблицу Школа ознакомьтесь с содержанием таблицы выберите кнопку Мастер запросов группы Другие вкладки Создание выберите в окне Новый запрос в списке видов мастеров запросов Простой запрос выберите в списке Таблицы и запросы окна Создание простых запросов таблицу Школа переместите из списка Доступные поля в список Выбранные поля имена полей Фамилия , Имя , Класс Далее Введите имя запроса Класс Готово откройте запрос в режиме Конструктора задайте сортировку по полю Класс посмотрите как выглядит запрос в режиме SQL . Для этого на вкладке запроса вызовите контекстное меню Режим SQL .
2) Создание запроса в режиме Конструктора
Создать запрос на выборку учащихся, у кого номер класса кратен 2 и он живет на улице Пушкина или Ленина: выберите кнопку Конструктор запросов группы Другие вкладки Создание добавьте таблицу Школа закройте окно Добавления таблицы в макете таблицы двойным щелчком по полю переместите в бланк запроса поля Фамилия , Имя , Улица , Класс в строку Условие отбора под полем Улица введите Пушкина Оr Ленина , под полем Класс введите 8 Оr 10 нажмите кнопку Выполнить закройте вкладку 8 класс.
3) Создание запроса с параметрами ( в режиме Конструктора )
Из таблицы Школа возьмите поля Фамилия , Имя , Класс , Хобби . В условие отбора поля Хобби введите =[Введите хобби] нажмите кнопку Выполнить введите значение параметра «футбол» ОК сохраните запрос под именем Футбол .
4) Создание запроса с вычислением (в режиме Конструктора)
Из таблицы Оценки возьмите поля Фамилия , І_семестр , ІІ_семестр в строке Поле в столбце, следующем за столбцом ІІ_семестр , введите выражение Годовая оценка: ([І_семестр]+[ ІІ_семестр])/2 нажмите кнопку Выполнить закройте вкладку запроса, сохранив его под именем Годовая.
- Самостоятельная работа
По таблице Школа создайте запросы:
С ?
Кто не занимается тяжелой атлетикой?
Кто увлекается аэробикой или футболом?
Кто учится в старших классах?
Кто из мальчиков живет на улице Фрунзе?
Кто из мальчиков с голубыми глазами занимается каратэ?
-Тестирование по теме
Тест:
1. База данных служит для:
хранения и упорядочения информации
ведения расчетно-вычислительных операций
обработки текстовой документации
обработки графической информации
2. Длина поля измеряется в:
миллиметрах
пикселях
байтах
символах
3. Записями считаются:
заголовки
столбцы
строки
таблицы
4. Текстовое поле позволяет занести информацию до:
255 символов
20 символов
65536 символов
1 символа
5. Логические данные - это:
денежные данные
текст
одно из двух значений
числа
6. Свойство автоматического наращивания имеет поле:
числовое
счетчик
МЕМО
логическое
7. Ключевое поле должно быть:
непременно счетчиком
обязательно числовым
уникальным
не должно содержать длинных записей
8. Укажите неправильный тип данных в Access
текстовый
параметрический
числовой
логический
9. Что не является элементом СУБД Access?
модули
запросы
макросы
таблицы
ключи
отчеты
запросы
10. Для чего предназначены запросы?
для хранения данных базы
для отбора и обработки данных базы
для ввода данных базы и их просмотра
для автоматического выполнения группы команд
для вывода обработанных данных базы на принтер.
11. Запишите, какую маску для поиска надо использовать, чтобы найти:
слова: кит, кот, кут ______
числа: 123, 1256000, 12,7 ______
даты: 12.12.2011, 03.12.20013, 26.12.2014 ______.
12. Какие условные выражения надо использовать, чтобы найти:
все числа, не меньшие 105 ______
все слова, кроме физика ______
все даты, ранее 12.11.2013 ______.
6. Подведение итогов урока, выставление оценок
7. Домашнее задание: 3.8, № 3, 6
Ответы:
- Самостоятельная работа
У кого фамилия начинается на букву С ? (Запрос возвращает 4 записи) (1 балл)
Кто учится в старших классах? (Запрос возвращает 2 записи) (1 балл)
У кого группа здоровья основная и он занимается футболом или каратэ? (Запрос возвращает 5 записей) (2 балла)
Кто из мальчиков живет на улице Фрунзе? (Запрос возвращает 2 записи) (2 балла)
Кто из мальчиков с голубыми глазами занимается каратэ? (Запрос возвращает 2 записи) (2 балла)
-Тестирование по теме
>=105
Not физика
< 12.11.2013
16-13 ответов – 4 балла
12-9 ответов – 3 балла
8- 5 ответов – 2 балла
4-1 ответ – 1 балл
Для изменения уже существующих запросов и для создания новых запросов используется Конструктор запросов. Для того чтобы открыть запрос в режиме Конструктора, выделите в списке один из существующих запросов, например только что созданный запрос "Сотрудники Запрос", и нажмите кнопку Конструктор (Design) на панели инструментов окна База данных (Database).
Появляется окно Конструктора запросов (рис. 4.5). В верхней части окна отображается таблица (или несколько таблиц, если запрос многотабличный) в том виде, в каком таблицы отображаются в окне Схема данных (Relationship). Таблицы - источники данных для запроса, мы будем называть базовыми таблицами запроса. В нижней части окна находится бланк запроса - таблица, ячейки которой используются для определения запроса. В бланке отображаются все столбцы, включенные в результирующее множество запроса.
Для того чтобы просматривать полностью бланк запроса и все исходные таблицы, используют линейки прокрутки.
В области панелей инструментов Access отображается панель инструментов Конструктор запросов (Queries Design). Эта панель представлена на рис. 4.6, а в табл. 4.1 приведено описание кнопок этой панели инструментов и соответствующие им команды меню.
Рис. 4.5. Запрос "Сотрудники Запрос" в режиме Конструктора запросов
Рис. 4.6. Панель инструментов Конструктор запросов
Таблица 4.1. Описание кнопок на панели инструментов Конструктор запросов
Команда меню | Описание | |
| Вид (View) | Вид, Режим таблицы (View, Datasheet View) | Отображение запроса в различных режимах. Чтобы изменить режим отображения запроса, нажмите стрелку справа от кнопки и в появившемся списке выделите нужный элемент. Если просто нажать эту кнопку, запрос будет отображен в режиме Таблицы |
| Сохранить (Save) | Файл, Сохранить (File, Save) | Сохранение активного запроса |
| Печать (Print) | Печать результатов запроса без открытия диалогового окна Печать | |
| Предварительный просмотр (Print Preview) | Файл, Предварительный просмотр (File, Print Preview) | Предварительный просмотр запроса перед печатью |
| Орфография (Spelling) | Сервис, Орфография (Tools, Spelling) | Проверка орфографии в текстовых полях запроса |
| Вырезать (Cut) | Правка, Вырезать (Edit, Cut) | Удаление выделенных объектов из запроса в буфер обмена Windows |
| Копировать (Сору) | Правка, Копировать (Edit, Copy) | Копирование выделенных объектов запроса в буфер обмена |
| Вставить (Paste) | Правка, Вставить (Edit, Paste) | Вставка содержимого буфера обмена в запрос |
| Формат по образцу (Format Painter) | Копирование параметров форматирования из одного выделенного объекта в другой такого же типа | |
| Отменить (Undo) | Правка, Отменить (Edit, Undo) | Отмена последнего изменения запроса |
| Тип запроса (Query Туре) | Запрос, Выборка (Query, Select Query) | Изменение типа запроса. Для изменения типа запроса щелкните по стрелке справа от кнопки и выберите из списка нужный тип запроса |
| Запуск (Run) | Запрос, Запуск (Query, Run) | Выполнение запроса |
| Отобразить таблицу (Show Table) | Запрос, Добавить таблицу (Query, Show Table) | Выводится диалоговое окно Добавление таблицы (Show Table) |
| Групповые операции (Totals) | Вид, Групповые операции (View, Totals) | Группировка записей в запросе и расчет итоговых значений |
| Набор значений (Top Values) | Отобразить только первые записи запроса. Количество отображаемых записей указывается в поле ввода либо в штуках, либо в процентах | |
| Свойства (Properties) | Вид, Свойства (View, Properties) | Открытие окон свойств выделенных объектов: запроса или поля запроса |
| Построитель (Build) | Вызов построителя для создания выражения. Кнопка доступна только тогда, когда активизировано свойство запроса или поля запроса, которое допускает ввод выражения, например Условие отбора (Criteria) | |
| Окно базы данных (Database Window) | Окно, 1 (Window, 1) | Отображение окна базы данных |
| Новый объект (New Object) | Создание нового объекта базы данных. Чтобы выбрать тип создаваемого объекта, нажмите стрелку справа от кнопки | |
| Справка по Microsoft Access (Microsoft Access Help) | Справка, Справка по Microsoft Access (Help, Microsoft Access Help) | Вызов помощника и получение справки по Access 2002 |
С использованием кнопок панели инструментов вы будете знакомиться по мере изучения работы с запросами, как в данной главе, так и в гл. 8.
А теперь рассмотрим, как создать новый запрос с помощью Конструктора запросов. Для этого необходимо:
>Примеры разных способов включения полей в результат запроса в режиме Конструктора запросов приведены на рис. 4.9-4.11.
Рис. 4.9.
Рис. 4.10.
Чтобы добавить в запрос еще одну таблицу или другой запрос, необходимо:
Замечание
Добавить таблицу или еще один запрос в окно Конструктора запросов можно также путем перетаскивания мышью названия таблицы или запроса из окна базы данных.
Чтобы удалить базовую таблицу из запроса, необходимо выделить ее, щелкнув на любом месте в списке ее полей, и нажать клавишу
Чтобы удалить поле из запроса, выделите нужный столбец в бланке запроса, а затем нажмите клавишу
Поля в таблице, являющейся результатом запроса, отображаются в том порядке, в котором они следуют в бланке запроса. Если требуется изменить порядок их следования, переставьте соответствующим образом столбцы в бланке запроса. Делается это стандартным способом, т. е. сначала выделяется нужный столбец или несколько столбцов, а затем они перетаскиваются мышью на новое место. При этом отпускать кнопку мыши нужно тогда, когда указатель мыши окажется перед тем столбцом, который делжен быть справа от вставляемого столбца. После вставки все столбцы, расположенные справа от указателя, сдвигаются вправо. Если вы перемещаете столбцы в конец запроса, то отпускайте кнопку мыши, когда указатель окажется перед первым свободным столбцом.
В режиме Конструктора запросов можно изменять имена полей запроса. Чтобы переименовать поле, необходимо установить курсор в бланке запроса перед первой буквой его имени и ввести новое имя и символ двоеточия. Пример запроса с измененными именами полей приведен на рис. 4.12. Изменение имени поля в бланке запроса приводит к изменению заголовка столбца при просмотре запроса в режиме таблицы. Кроме того, если на основе запроса создать новый объект, например форму или отчет, в новом объекте будет использовано новое имя поля. Имя поля базовой таблицы при этом не изменяется. Это имя также не изменяется в тех формах и отчетах, которые были созданы на основе запроса до изменения имени поля. Имена полей в запросах должны соответствовать соглашениям об именах Microsoft Access.
Рис. 4.12.
Чтобы использовать новое имя поля только в заголовках столбцов в режиме таблицы или как подпись полей в формах и отчетах, следует задать для этого поля свойство Подпись (Caption), а не переименовывать поле в бланке. (Об установке свойств поля см. разд. "Настройка свойств полей в запросе и свойств самого запроса" гл. 8 )
Ширина столбцов в бланке запроса также может меняться. Осуществляется это простым перетаскиванием мышью границы столбца или двойным щелчком мыши по линии границы, как это делается в таблице Excel.
Замечание
Изменение ширины столбцов в окне Конструктора запросов или в окне расширенного фильтра не влияет на ширину столбцов запроса в режиме Таблицы или объекта, для которого применен фильтр.
В строке Условие отбора (Criteria) и в строке или (Or) указываются условия отбора записей. Такими условиями могут быть логические выражения. Например, (>30), (="Иванов"), (=10) и т. п. (0 создании выражений в условиях запроса см. разд. "Использование выражений в запросах" ниже в этой главе.)
Условия, находящиеся в одной строке, но в разных столбцах бланка, объединяются по логическому оператору And (И). Если нужно объединить условия отбора по логическому оператору Or (ИЛИ), разместите эти условия в разных строках бланка запроса. Пример использования нескольких условий отбора приведен на рис. 4.13.
Рис. 4.13.
Если критерий отбора очень сложный, можно вставлять дополнительные строки условий. Для этого необходимо:
- Выделить строку, которая должна оказаться под вставляемой строкой. Для этого нужно подвести указатель мыши к левой границе строки (он должен превратиться в жирную стрелку, указывающую вправо) и щелкнуть левой кнопкой мыши.
- Выполнить команду Вставка, Строки (Insert, Rows) или нажать клавишу . Новая пустая строка вставляется над выбранной. В нее можно вносить условия отбора.
Для удаления строки условий отбора:
- Щелкните левой кнопкой мыши в любом месте строки.
- Выполните команду Правка, Удалить строки (Edit, Delete Rows).
Чтобы установить порядок сортировки записей в запросе, используйте строку Сортировка (Sort). Для каждого поля, по которому должны сортироваться записи, выберите из списка соответствующий порядок сортировки: по возрастанию (Ascending) или по убыванию (Descending). По умолчанию во всех полях запроса устанавливается значение (отсутствует) (Not sorted). Пример использования поля со списком Сортировка (Sort) приведен на рис. 4.14.
Если нужно отсортировать записи в запросе по нескольким полям, разместите их в бланке запроса таким образом, чтобы они были упорядочены слева направо - именно в таком порядке будет выполняться сортировка.
Можно легко удалить поле из результирующей таблицы запроса, если просто сбросить флажок Вывод на экран (Show) в этом столбце бланка запроса. По умолчанию этот флажок установлен для всех полей запроса. Ситуация, когда поле не должно отображаться в результате запроса, возникает обычно, когда оно включается в бланк запроса только для того, чтобы указать условие отбора или сортировки записей. Пример такого запроса приведен на рис. 4.15. В запрос включены все поля таблицы "Товары" (Products) (это задано в первом столбце бланка запроса), а поля "КодТипа" и "Марка" включены только для того, чтобы указать порядок сортировки. Поэтому флажок Вывод на экран (Show) у этих полей сброшен. В противном случае эти поля вывелись бы дважды.
Рис. 4.14.
Рис. 4.15.
Если вы хотите очистить бланк запроса для того, чтобы создать новый, нужно выполнить команду Правка, Очистить бланк (Edit, Clear Grid).
После формирования бланка запроса его можно сохранить, нажав на кнопку Сохранить (Save) на панели инструментов или выполнив команду меню Файл, Сохранить (File, Save). При этом появляется диалоговое окно, в котором нужно ввести имя сохраняемого запроса (рис. 4.16). Запрос можно сохранить и закрыв его.
Результаты выполнения запроса можно увидеть, переключившись в режим Таблицы (Datasheet View) с помощью кнопки Вид (View) или нажав кнопку Запуск (Run) на панели инструментов.
Width="351" height="182">
Рис. 4.16. Диалоговое окно Сохранение
Результат выполнения запроса, созданного в режиме Конструктора, приведен на рис. 4.17.
Microsoft Access 2007
2.4. Microsoft Access 2007
2.4.5. Создание запросов и поиск информации в базе данных
В СУБД Access 2007 можно создавать queries для отображения требуемых полей из записей одной или нескольких таблиц.
В СУБД Access 2007 применяются различные типы запросов : на выборку, на обновление, на добавление, на удаление, перекрестный query, выполнение вычислений, создание таблиц. Наиболее распространенным является query на выборку. Применяются два типа запросов: query по образцу (QBE) и query на основе структурированного языка запросов (SQL).
Запросы на выборку используются для отбора требуемой пользователю информации, содержащейся в нескольких таблицах. Они создаются только для связанных таблиц. Queries могут основываться как на нескольких таблицах, так и существующих запросах. СУБД Access 2007 включает такие средства создания запросов, как Мастер и Конструктор.
Кроме того, в СУБД Access 2007 существует множество средств для поиска и отображения информации, которая хранится в базе данных. Данные в таблицах можно отсортировать на основе любого поля или комбинации полей. Для извлечения из базы данных необходимых записей можно отфильтровать таблицу, применив средства фильтрации.
На скриншоте (рисунок 1) средства сортировки и фильтрации выделены скругленным прямоугольником красного цвета.
Рис. 1.
Рассмотрим создание запроса на выборку с помощью Конструктора
Для создания нового пустого запроса в режиме конструктора надо щелкнуть на пиктограмме Конструктор запросов (рисунок 2).
Рис. 2.
Откроется активное окно диалога Добавление таблицы (рисунок 3) на фоне неактивного окна «Запрос1». В этом окне можно выбрать таблицы и queries для создания новых запросов.
Рис. 3.
В окне Добавление таблицы следует выбрать несколько таблиц из представленного списка таблиц, на основе которых будет проводиться выбор данных, и щелкнуть на кнопке Добавить. После этого закрыть окно Добавление таблицы, а окно «Запрос1» станет активным (рисунок 4).
Рис. 4.
Окно Конструктора состоит из двух частей – верхней и нижней. В верхней части окна размещается схема данных запроса, которая содержит список связанных таблиц. В нижней части окна находится Бланк построения запроса QBE, в котором каждая строка выполняет определенную функцию.
Переместим имена полей с таблиц-источников в Бланк. Из таблицы Группы студентов переместим поле Название в первое поле Бланка, из таблицы Студенты переместим поле Фамилии во второе поле, а из таблицы Успеваемость переместим поле Оценка в третье поле и из таблицы Дисциплины переместим поле Название в четвертое поле Бланка запросов.
При необходимости можно задать принцип сортировки (по возрастанию или по убыванию) результатов запроса. В строке "Вывод на экран" автоматически устанавливается флажок просмотра информации.
Условия ограниченного поиска или критерий поиска информации вводится в строке "Условия" отбора и строке "Или". Например, введем критерий поиска - "5/A" в строке "Условия" для поля Оценка. В этом случае в результате выполнения запроса на экране будут отображаться все фамилии студентов, которые получили оценку 5/A (рисунок. 5).
Рис. 5.
Далее надо закрыть окно запроса Запрос1, появится окно диалога Сохранить, ответить - Да и ввести имя запроса, например "Успеваемость студентов". Для запуска запроса дважды щелкнем на query "Успеваемость студентов", откроется таблица с результатами выполненного запроса (рис. 6).
Рис. 6.
Далее создаем параметрический query или query с параметрами. Создаем этот query также как и предыдущий, в режиме конструктора, но только в строке Условия отбора для поля Фамилия введем условие отбора в виде приглашения в квадратных скобках, например [Введите фамилию]. В этом случае в результате выполнения запроса на экране будет отображаться фамилия студента и все дисциплины, по которым он получил оценку.
Закрыть окно запроса на выборку. На вопрос о сохранении изменения ответить - Да и ввести имя запроса, например "Параметрический query". Запустим Параметрический query, дважды щелкнув на нем. В открывшемся на экране окне диалога «Введите значение параметра» надо ввести фамилию студента, информацию об успеваемости которого необходимо получить (рис. 8).
Рис. 7.
Затем надо щелкнуть на кнопке ОК, откроется таблица с результатами выполненного запроса (рис. 8).
Рис. 8.
В некоторых случаях для создания запросов можно использовать Мастер запросов. После создания запросов на выборку информации из БД Access 2007 можно приступать к формированию форм.
В простейшем случае запрос реализует выбор из одной таблицы нужных полей, записей, соответствующих заданным условиям отбора, и просмотр результатов выполнения запроса.
Конструирование запросов на выборку с условиями отбора
Рассмотрим запросы на выборку в Access на примере получения информации из таблицы ТОВАР базы данных Поставка товаров .
Задача 1 . Пусть необходимо выбрать ряд характеристик товара по его наименованию.
- Для создания запроса в окне базы данных выберите вкладку ленты - Создание (Create) и в группе Запросы (Queries) нажмите кнопку Конструктор запросов (Query Design). Откроется пустое окно запроса на выборку в режиме конструктора - ЗапросN (QueryN) и диалоговое окно Добавление таблицы (Show Table) (рис. 4.2).
- В окне Добавление таблицы (Show Table) выберите таблицу ТОВАР и нажмите кнопку Добавить (Add). Выбранная таблица будет отображена в области схемы данных запроса. Закройте окно Добавление таблицы (Show Table), нажав кнопку Закрыть (Close).
В результате выполненных действий в окне конструктора запросов (рис. 4.1) в верхней панели появится схема данных запроса, которая включает выбранные для данного запроса таблицы. В данном случае одну таблицу ТОВАР. Таблица представлена списком полей. Первая строка в списке полей таблицы, отмеченная звездочкой (*), обозначает все множество полей таблицы. Нижняя панель является бланком запроса, который нужно заполнить.
Кроме того, на ленте появляется и автоматически активизируется новая вкладка (Query Tools | Design) (на рис. 4.3 представлена на часть этой вкладки), на которой цветом выделен тип созданного запроса - Выборка (Select). Таким образом, по умолчанию всегда создается запрос на выборку. Команды этой вкладки представляют инструментарий для выполнения необходимых действий при создании запроса. Эта вкладка открывается, когда в режиме конструктора создается новый запрос или редактируется существующий.
- Для удаления любой таблицы из схемы данных запроса установите на нее курсор мыши и нажмите клавишу
. Для добавления ― нажмите кнопку Отобразить таблицу (Show Table) в группе Настройка запроса (Query Setup) на вкладке Работа с запросами | Конструктор (Query Tools | Design) или выполните команду Добавить таблицу (Show Table) в контекстном меню, вызываемом на схеме данных запроса. - В окне конструктора (рис. 4.4) последовательно перетащите из списка полей таблицы ТОВАР поля НАИМ_ТОВ, ЦЕНА, НАЛИЧИЕ_ТОВ в столбцы бланка запроса в строку Поле (Field).
- Для включения нужных полей из таблицы в соответствующие столбцы запроса можно воспользоваться следующими приемами:
- в первой строке бланка запроса Поле (Field) щелчком мыши вызвать появление кнопки списка и выбрать из списка нужное поле. Список содержит поля таблиц, представленных в схеме данных запроса;
- дважды щелкнуть на имени поля таблицы в схеме данных запроса;
- для включения всех полей таблицы можно перетащить или дважды щелкнуть на символе * (звездочка) в списке полей таблицы в схеме данных запроса.
- Если вы по ошибке перетащили в бланке запроса ненужное поле, удалите его. Для этого переместите курсор в область маркировки столбца сверху, где он примет вид черной стрелки, направленной вниз, и щелкните кнопкой мыши. Столбец выделится. Нажмите клавишу
или выполните команду Удалить столбцы (Delete Columns) в группе Настройка запроса (Query Setup). - В строке Вывод на экран (Show) отметьте поля, иначе они не будут включены в таблицу запроса.
- Запишите в строке Условия отбора (Criteria) наименование товара, как показа-но в бланке запроса на рис. 4.4. Так как выражение в условии отбора не содержит оператора, то по умолчанию используется оператор =. Используемое в выражении текстовое значение вводится в двойных кавычках, которые добавляются автоматически.
- Выполните запрос, щелкнув на кнопке Выполнить (Run) или на кнопке Режим (View) в группе Результаты (Results). На экране появится окно запроса в режиме таблицы с записью из таблицы ТОВАР, отвечающей заданным условиям отбора.
ЗАМЕЧАНИЕ
Окно запроса в режиме таблицы аналогично окну просмотра таблицы базы данных. Через некоторые таблицы запроса может производиться изменение данных базовой таблицы, лежащей в основе запроса. Запрос, просматриваемый в режиме таблицы, в отличие от таблицы базы данных Access 2010, не имеет столбца Щелкнуть для добавления
(Click to Add), предназначенного для изменения структуры таблицы. В этом режиме на вкладке ленты Главная
(Home) доступны те же кнопки, что и при открытии таблицы базы данных.
- Если при вводе сложного наименования товара вы допустили неточность, товар не будет найден в таблице. Использование операторов шаблона - звездочка (*) и вопросительный знак (?) (стандарт ANSI-89, используемый для запросов по умолчанию) или знак процента (%) и подчеркивания (_) (ANSI-92, рекомендуемый как стандарт для SQL Server), упрощает поиск нужных строк и позволяет избежать многих ошибок. Введите вместо полного имени товара Корпус* или Корпус%. Выполните запрос. Если в поле наименования товара одно значение начинается со слова «Корпус», результат выполнения запроса будет таким же, как в предыдущем случае. После выполнения запроса введенное выражение будет дополнено оператором Like «Корпус*». Этот оператор позволяет использовать символы шаблона при поиске в текстовых полях.
- Если необходимо найти несколько товаров, используйте оператор In. Он позволяет выполнить проверку на равенство любому значению из списка, который задается в круглых скобках. Запишите в строке условий отбора In («Корпуc MiniTower»;»HDD Maxtor 20GB»;»FDD 3,5″). В таблице запроса будет выведено три строки. В операторе In не допускается использование символов шаблона.
- Сохраните запрос, щелкнув на вкладке Файл (File) и выполнив команду Сохранить (Save). В окне Сохранение (Save As) введите имя запроса Пример1. Заметим, что имя запроса не должно совпадать не только с именами имеющихся запросов, но и с именами таблиц в базе данных.
- Закройте текущий запрос по команде контекстного меню Закрыть (Close) или нажав кнопку окна запроса Закрыть (Close).
- Выполните сохраненный запрос, выделив запрос в области навигации и выбрав в контекстном меню команду Открыть (Open).
- Для редактирования запроса выделите его в области навигации и выполните в контекстном меню команду Конструктор (Design View).
Задача 2. Пусть надо выбрать товары, цена которых не более 1000 руб., и НДС не более 10%, а также выбрать товары, цена которых более 2500 руб. Результат должен содержать наименование товара (НАИМ_ТОВ), его цену (ЦЕНА) и НДС (СТАВКА_НДС).
- Создайте новый запрос в режиме конструктора, добавьте таблицу ТОВАР. В окне конструктора (рис. 4.5) последовательно перетащите из списка полей таблицы ТОВАР в бланк запроса поля НАИМ_ТОВ, ЦЕНА, СТАВКА_НДС.
- Запишите Условия отбора (Criteria), как показано в бланке запроса на рис. 4.5. Между условиями, записанными в одной строке, выполняется логическая операция AND. Между условиями, записанными в разных строках, выполняется логическая операция OR.
- Выполните запрос, щелкните на кнопке Выполнить (Run) в группе Результаты (Results). На экране появится окно запроса в режиме таблицы с записями из таблицы ТОВАР, отвечающими заданным условиям отбора.
- Сохраните запрос, выполнив соответствующую команду в контекстном меню запроса, которое вызывается при установке курсора на заголовок запроса. Дайте ему имя Пример2 .
Задача 3 . Пусть надо выбрать все накладные за заданный период. Результат должен содержать номер накладной (НОМ_НАК), код склада (КОД_СК), дату отгрузки (ДАТА_ОТГР) и общую стоимость отгруженного товара (СУММА_НАКЛ).
- Создайте новый запрос в режиме конструктора, добавьте таблицу НАКЛАДНАЯ. В окне конструктора последовательно перетащите из списка полей таблицы НАКЛАДНАЯ в бланк запроса все необходимые поля.
- Для поля ДАТА_ОТГР в строке Условия отбора (Criteria) запишите Between #11.01.2008# And #31.03.2008#. Оператор Between задает интервал дат (в ANSI-92 вместо знака # используются одинарные кавычки ‘). Кроме того, этот оператор позволяет задать интервал для числового значения.
Для закрепления смотрим видеоурок:
Вычисляемые поля в запросах Access .
