Сегодня мы подробно поговорим о запросах в Access.

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

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

Отсев пустых строк

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

Почему это происходит?
Дело в том, что у нас в таблице tbPerson наряду с владельцами собак записаны и судьи (Петровская, Елец, Терещук). Судьи не имеют права привозить своих собак на выставку, поэтому в строках с их фамилиями пустые ячейки с кличками собак.
Есть два способа удалить пустые строки.
1. Поставить условие на значение клички собаки Is not Null , т.е. НЕ ПУСТА.

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

Вопрос вам: какие настройки нужно поменять в диалоговом окне параметров объединения?

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

До сих пор мы только отбирали записи по различным условиям. Но Access позволяет не только просматривать данные, записанные в таблицах, но и производить ВЫЧИСЛЕНИЯ: по дате рождения определять возраст; из имени, фамилии и отчества делать фамилию с инициалами; по цене единицы товара и его количества определять общую стоимость покупки; по дате выдачи книги в библиотеке определять величину штрафа при задолженности и многое другое. Для вычислений используются встроенные функции (похожие на те, что есть в Excel).

Простейшая операция – это сложение строк . Напишем в ячейке выражение для вывода такой фразы: владелец из города город .
Для этого запишем в верхней строке нового столбца области условий: + “ из города ” + .

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

Выражения для вычислений записываются в самой верхней строке (Поле ) области условий. До сих пор мы писали условия в нижних строках (Условия отбора ).

Чтобы вам не запутаться: в верхней строке мы пишем, ЧТО выводить на экран, а затем (в нижней) – при КАКОМ УСЛОВИИ .

Задание : Напишите выражение для вывода в одной ячейке фамилии владельца и в скобках города, в котором он живет. Вот так: Иванов (Москва) . Город и фамилия должны подставляться из таблицы.

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

Чтобы удобнее было редактировать выражения, существует специальный редактор – «Построитель выражений». Он выглядит так:

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

В построителе выражений вы можете выбирать из библиотеки функции:

и данные из таблиц (можно пользоваться ТОЛЬКО теми таблицами, которые используются в данном запросе и отображены в области данных):

Когда вы двойным щелчком мыши выбираете из списка имя поля или функцию, Access часто вставляет еще слово «выражение», показывая, что НА ЭТО МЕСТО можно вставить другие функции и имена полей. Не забывайте удалять лишние слова «выражение»!

Мы познакомимся с текстовыми и временными функциями, а также с условным оператором Iif(condition; if-true; if-false).

Текстовые функции позволяют преобразовывать строковые переменные:
Left(«Иванов»; 2) = «Ив» оставляет n левых символов
LCase(«Иванов») = иванов делает все буквы строчными
InStr(1; «Иванов»; «но») = 4 находит подстроку (третий аргумент) в строке (второй аргумент), и равняется позиции (от начала) подстроки в строке
Len («Иванов») = 6 выводит количество символов в строке
StrComp(«Иванов»; «Петров») = -1 сравнивает две строки: если они равны, то выдает 0
и другие…

Временные позволяют работать с временными переменными:
Month(#12.04.2007#) = 4
Year(#12.04.2007#) = 2007
Day(#12.04.2007#) = 12.
Now() = 28.04.2008 14:15:42 (текущие дата и время)
Date() = 28.04.2008 (сегодняшняя дата)
DateDiff(«d»; #12.04.2007#; #28.04.2007#) = 16 находит разницу между двумя датами («d» – в днях, “ww”- в неделях, “m” – в месяцах, “yyyy” – в годах и др.)
и другие…

Логические обрабатывают условные выражения:
Iif(<=1; «щенок»; «взрослый») аналог функции ЕСЛИ из Ecxel.
и другие…

Задание : напишите выражение, которое из фамилии, имени и отчества делает фамилию с инициалами. Иванов Иван Иванович -> Иванов И.И.
Задание
Задание
Дополнение : Есть два способа вычислить возраст собаки: один более точный, другой менее:
1) из текущего года вычесть год рождения собаки;
2) с помощью функции DateDiff посчитать, сколько дней прошло с рождения до сегодняшнего дня. Примените в одном из заданий один способ, в другом – другой.

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

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

Устроен этот запрос так:

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

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

Запросы с группировкой

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

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

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

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

Добавляем в область таблиц таблицу с оценками (tbMarks). Группируем оценки по турнирному номеру собаки и среди групповых операций выбираем среднее значение Avg (от англ. average – среднее).

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

Можно также округлить значения до одного знака после запятой: Round(+;1)

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

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

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

Оператору базы данных накануне выставки пришла новая информация:
1) собака Дези заболела и не сможет принять участия в выставке;
2) по ошибке Гардиен, являющийся на самом деле английским сеттером, был указан как ирландский сеттер;
3) владелец Мигунова подала заявку на участие в выставке еще одной своей собаки (кличка: Гарри, порода: сеттер-гордон, пол: м, дата рождения: 15.09.07).

Приступим к редактированию базы данных.
1) Удалить запись с Дези.
Создаем запрос quDelDog. Тип запроса – на удаление. Со сменой типа запроса несколько изменяется и область условий. Появилась новая ячейка Удаление . Под ней вы указываете условие, по которому нужно отобрать записи, подлежащие удалению. Даже если вы указываете условие отбора для одного поля, удалена будет ВСЯ запись.

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

2) У Гардиена изменить ирландский сеттер на английский сеттер.
Создаем запрос quUpdateDog. Тип запроса – на обновление. Находим Гардиена и обновляем у него породу.

Откройте таблицу tbDog и убедитесь, что у Гардиена порода – английский сеттер.

3) Добавить запись с Гарри.
Создаем запрос quAddDog. Тип запроса – на добавление. У запросов на добавление есть одна особенность: в области таблиц отображены НЕ те таблицы, КУДА вы добавляете запись, а ОТКУДА вы берете данные (если это требуется). Целевую таблицу (в которую добавляются записи) вы указываете в диалоговом окне, которое появляется сразу, как только вы установите тип запроса (на добавление):

Поскольку мы не берем данные из других таблиц, а создаем новую запись, то область таблиц должна быть ПУСТОЙ! (там не должно быть ни одной таблицы). В области условий в строке Поле вы пишете, ЧТО добавить (новое значение для каждого поля), а в строке Добавление КУДА (имена полей):

Откройте таблицу tbDog и убедитесь, что Гарри в ней появился!

Язык запросов SQL

Когда вы нажимаете на «восклицательный знак», запрос выполняется. Так это представляется на взгляд новичка.
Профессионалы знают, что на самом деле в этот момент выполняется инструкция на специальном языке запросов SQL. Дело в том, что Access – не единственная система управления базами данных (СУБД). Может быть, вы слышали о таких СУБД в Интернете как MySQL, FreeBSD??? Access просто предлагает очень удобный интерфейс для работы с БД, а в других СУ нет никакой кнопочки с восклицательным знаком. Зато там обязательно есть специальное окошко, в котором можно писать SQL-инструкции.
Access тоже позволяет редактировать запросы в режиме SQL-инструкций:

Правила языка SQL не так сложны. Вы можете сами в этом убедиться! Сделайте простой запрос на выборку (например, выведите кличку, породу и дату рождения собаки по кличке Гарри). Теперь откройте запрос на поиск Гарри в режиме SQL!
Инструкция устроена очень просто:
SELECT поле1, поле2,…
FROM таблица1, таблица2,…
WHERE условие1, условие2,…

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

Запросы с параметром, запросы на группировку, запросы с вычислениями представляют собой такие же SQL-запросы, но только с несколько более сложными условиями отбора. Язык SQL – незаменимый инструмент для тех, кто работает с базами данных!

Задания

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

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

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

1. выборка

quSelectDog : Найдите всех шар-пеев и сеттеров-гордонов из регионов (НЕ из Москвы); используйте оператор «не равно».

2. оператор Like

quLike : Найдите всех абонентов МТС (те, у кого номер мобильного телефона начинается с 8(916)…).

3. выражения

quEvalText : Напишите выражение, которое из фамилии, имени и отчества делает фамилию с инициалами. Иванов Иван Иванович -> Иванов И.И.
quEvalDate : напишите выражение, которое вычисляет, сколько собаке лет по дате ее рождения.
quEvalIif : напишите выражение, которое определяет по возрасту, в какой возрастной категории выступает собака: «щенок» - до года; «юниор» - от года до двух лет; «сеньор» - старше двух лет.
Дополнение : Примените в одном задании один способ вычислить возраст собаки, в другом – другой: 1) из текущего года вычесть год рождения собаки; 2) с помощью функции DateDiff посчитать, сколько дней прошло с рождения до сегодняшнего дня.

4. параметр

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

5. группировка

quGroup : Выясните, какая собака вызвала наиболее противоречивые мнения судей.

6. редактирование БД

Незадолго до выставки владелец Гороховец уехал на ПМЖ (постоянное место жительства) в Германию и передал всех собак своему другу Карпову Михаилу Игоревичу. Требуется:
1) quAddOwner : добавить запись о новом владельце;
2) quUpdateOwner : изменить у собак Гороховца ID владельца на ID Карпова;
3) quDelOwner : удалить Гороховца из базы данных.

Жду ваших БД с выполненными заданиями, а также шаблоны SQL-инструкций для запросов на обновление, изменение, удаление.

При создании запросов важно правильно сформулировать условия отбора записей из БД. В MS Access доступны следующие возможности:

· простой критерий выборки;

· точное несовпадение значений одного поля;

· неточное совпадение значений поля;

· выбор по диапазону значений;

· объединение критериев нескольких полей;

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

Простой критерий выборки. Записи выбираются по совпадающим значениям поля. Например, из поля Город необходимо выбрать значения Минск. Для этого в бланке запроса в строке Условие отбора в графе Город вводится с клавиатуры значение «Минск».

Точное несовпадение значений одного поля . Из базы выбираются все записи, кроме тех, для которых задано условие. Например, необходимо выбрать все записи с полем Город , кроме тех, которые в этом поле имеют значение Минск . Для этого в строке Условия отбора в графе Город вводится выражение Not «Минск» или<> «Минск» . Логический оператор Not исключает записи со значением Минск , оператор сравнения <> означает «не равно».

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

Access допускает следующие символы шаблонов:

? - любой один знак;

* - ноль или более знаков;

#- любая одна цифра;

[список знаков] - любой один знак в списке знаков;

[!список знаков] - любой один знак, не входящий в список.

Кроме списка знаков в квадратные скобки может заключаться диапазон символов, например, [Б-Р]. Условие [б-рБ-Р] позволяет выбрать как заглавные, так и прописные буквы.

При условии Like «[БР]*» выбираются все фамилии, которые начинаются на Б или Р.

Выбор по диапазону значений . Для задания диапазона значений используются операторы:

> (больше),

>= (не менее, больше или равно),

< (меньше),

<= (не более, меньше или равно) (например, >= 10).

Between ... аnd ... (служит для проверки принадлежности диапазону, верхняя и нижняя граница которого соединена логическим оператором AND (например, between 1990 and 1995).

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

Объединение критериев одного поля . Если на одно поле налагается более одного условий, то условные выражения могут быть соединены с помощью операторов Or (ИЛИ ) и And (И ).

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

запись выбирается только при выполнении всех условий, что соответствует логической операции И . Запрос называется И-запросом ;

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

При построении ИЛИ-запроса каждое условие, входящее в критерий, должно располагаться на отдельной строке. При построении И-запроса каждое условие, входящее в критерий, должно располагаться в одной строке.

В итоговых запросах существуют два типа критериев отбора записей.

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

Условия отбора - это ограничения, накладываемые на запрос или расширенный фильтр для определения записей, с которыми он будет работать. Например, вместо просмотра всех поставщиков компании, можно просмотреть только поставщиков из Литвы. Для этого необходимо указать условие отбора, отсеивающее все записи, кроме тех, у которых в поле «Страна» указано «Литва».Чтобы задать условие отбора для поля в бланке запроса, введите выражение в ячейку Условие отбора для данного поля. В предыдущем примере выражением является «Литва». Могут быть использованы и более сложные выражения, например, «Between 1000 And 5000».Если запрос содержит связанные таблицы, то в значениях, указанных в условиях отбора для полей из связанных таблиц, учитывается регистр знаков. Они должны соответствовать регистру значений в базовой таблице.

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

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

Что такое параметры объединения в СУБД Microsoft Access, для чего их используют?

Что такое запрос с параметром, и как его можно задать?

Создание запроса с одним параметром

1. В меню Справка выберите Примеры баз данных , а затем Пример базы данных Борей . Когда появится Главная кнопочная форма , закройте ее.
2. Из меню Вид выберите Объекты базы данных , а затем Запросы .
3. Счета , а затем щелкнитеКонструктор .
4. Введите следующую команду в ячейку Условия отбора для поля СтранаПолучателя. Обратите внимание, что введенное выражение должно быть заключено в квадратные скобки.

[Обзор счетов страны]

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

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

1. Запустите Microsoft Access 2002.
2. В меню Справка выберите Примеры баз данных , а затем Пример базы данных Борей . Когда появится Главная кнопочная форма , закройте ее.
3. Из меню Вид выберите Объекты базы данных , а затем Запросы .
4. В окне базы данных щелкните запрос Счета , а затем щелкнитеКонструктор .
5. Введите следующую команду в ячейке Условия отбора для поля ДатаРазмещения.

Между [ввести дату начала] и [ведите дату окончания]

1. В меню Запрос выберите команду Выполнить . При запросе даты начала введите 1/1/1997, затем нажмите кнопку OK . При запросе даты окончания введите 1/31/1997, а затем нажмите кнопку OK , чтобы просмотреть результаты запроса. Обратите внимание на то, что запрос возвращает только записи с датой заказа, выпадающей на январь 1997 года.
2. Закройте запрос без сохранения.

Создание параметров, использующих подстановочные символы

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

1. Запустите Microsoft Access 2002.
2. В меню Справка выберите Примеры баз данных , а затем Пример базы данных Борей . Когда появится Главная кнопочная форма , закройте ее.
3. Из меню Вид выберите Объекты базы данных , а затем Запросы .
4. В окне базы данных щелкните запрос Счета , а затем щелкнитеКонструктор .
5. Введите следующую команду в ячейке Условия отбора для поля Марка:

LIKE "*" & [Введите продукты, содержащие выражение] & "*"

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

Какие групповые операции можно использовать в СУБД MS Access при создании запросов с групповыми операциями? Опишите назначение каждой из них.

Используя условия запроса, вы можете находить в базе данных Access определенные элементы. Если элемент соответствует всем введенным условиям, он отобразится в результатах запроса.

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

Условие запроса - это выражение, которое Access сравнивает со значениями в полях запроса, чтобы определить, следует ли включать в результат записи, содержащие то или иное значение. Например, = "Воронеж" - это выражение, которое Access сравнивает со значениями в текстовом поле запроса. Если значение этого поля в определенной записи равно "Воронеж" , Access включает ее в результаты запроса.

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

В этом разделе

Общие сведения об условиях запроса

Условие похоже на формулу - это строка, которая может включать ссылки на поля, операторы и константы. В Access условия запроса также называются выражениями.

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

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

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

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

Условия, заданные для разных полей в строке Условия , объединяются с помощью оператора AND. Другими словами, условия, заданные в полях "Город" и "ДатаРождения", интерпретируются следующим образом:

Город = "Воронеж" AND ДатаРождения < DateAdd (" гггг ", -40, Date())

1. Поля "Город" и "ДатаРождения" включают условия.

2. Этому условию соответствуют только записи, в которых поле "Город" имеет значение "Воронеж".

3. Этому условию соответствуют только записи людей, которым не менее 40 лет.

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

Что делать, если требуется, чтобы выполнялось только одно из этих условий? Другими словами, как можно ввести альтернативные условия?

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

1. 1. Условие "Город" указывается в строке "Условие отбора".

2. 2. Условие "ДатаРождения" указывается в строке "или".

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

Город = "Чикаго" OR ДатаРождения < DateAdd("гггг", -40, Date())

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

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

Условия для текстовых полей, полей Memo и полей гиперссылок

Примечание: Начиная с версии Access 2013, текстовые поля носят название Краткий текст , а поля Memo - Длинный текст .

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

Условие, заданное для поля "Гиперссылка", по умолчанию применяется к отображаемому тексту, который указан в поле. Чтобы задать условия для конечного URL-адреса, используйте выражение HyperlinkPart . У него следующий синтаксис: HyperlinkPart([Таблица1].[Поле1],1) = "http://www.microsoft.com/" , где "Таблица1" - это имя таблицы, содержащей поле гиперссылки, "Поле1" - это само поле гиперссылки, а "http://www.microsoft.com" - это URL-адрес, который вы хотите найти.

Условия для числовых полей, полей с денежными значениями и полей счетчиков

Следующие примеры относятся к полю "ЦенаЗаЕдиницу", основанном на таблице, в которой хранится информация о товарах. Условие задается в строке Условие отбора поля на бланке запроса.

Условия для полей "Дата/время"

Следующие примеры относятся к полю "ДатаЗаказа", основанном на таблице, в которой хранится информация о заказах. Условие задается в строке Условие отбора поля на бланке запроса.

Условия для полей "Да/Нет"

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

Условия для других полей

Вложения. В строке Условие отбора введите Is Null , чтобы включить записи, которые не содержат вложений. Введите Is Not Null , чтобы включить записи с вложениями.

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

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

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

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

Найдите исходную таблицу в области навигации .

Откройте таблицу в Конструкторе, сделав одно из следующего:

• Щелкните таблицу и нажмите клавиши CTRL+ВВОД .

  Щелкните таблицу правой кнопкой мыши и выберите пункт Конструктор .

Тип данных для каждого поля указан в столбце Тип данных на бланке таблицы.

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

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

Разверните многозначное поле, щелкнув символ плюса (+ ) рядом с ним. Если поле уже развернуто, то выводится минус (- ). Под именем поля вы увидите поле, представляющее одно значение многозначного поля. Это поле будет иметь то же имя, что и многозначное поле, но к нему будет добавлена строка .Значение .

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

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

Каждое значение в многозначном поле будет оцениваться по отдельности на основе указанных условий. Например, допустим, что в многозначном поле хранится список чисел. Если указать условия >5 AND <3 , будут выведены все записи, в которых есть по крайней мере одно значение больше 5 и одно значение меньше 3.

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

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

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

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

Добавление полей на основе условий отбора;

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

Запрос на создание таблицы с оздает новую таблицу на основе всех или части данныхиз одной или нескольких таблиц. Запрос на создание таблицы полезен для выпол­нения следующих действий:

Создание таблицы для экспорта в другую базу данных Microsoft Access;

Создание отчетов, содержащих данные нескольких таблиц;

Создание резервной копии таблицы.

Создание архивной таблицы, содержащей старые записи;

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

Выбор данных из одной таблицы

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

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

Проще всего создать запрос на основе одной таблицы так: открыть окно базы данных, выбрать в окне базы данных вкладку Запросы , нажать кнопку Создать, в новом окне выбрать режим Конструктор и Ok . В следующем окне "Добавление таблицы" выбрать нужную таблицу, а затем нажать кнопки Добавить и Закрыть .

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

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

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

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

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

Ввод условий отбора. Если нужно отобрать записи с конкретным значением поля, его нужно ввести в ячейку Условие отбора этого поля. Текстовое значение в качестве условия заключается в кавычки. При задании условия отбора можно пользоваться знаками отношений < , >, >=, <=, =,< > и логическими операциями or , and .

Кроме этого Access предоставляет специальные операторы для отбора данных, выводимых в запросе:

between - определяет диапазон значений. Between 10 and 20 означает то же самое, что и выражение >=10 and <=20 ;

in - задает используемый для сравнения список значений. Выражение in (“ wa ”,” ca ”,” id ”) означает то же самое, что и выражение “ wa ” or “ ca ” or “ id ” ;

like - этот оператор позволяет при отборе текстовых полей использовать символы: ?, *,# . Символ # указывает, что в данной позиции должна стоять цифра, символы ? и * имеют то же назначение, что и в именах файлов OC MS DOS.

Например, like “ B *” - означает, что нужно выбрать поля, начинающиеся с буквы В .

Условия отбора для дат и времени Access обрабатывает в любом формате. При вводе дату или время необходимо заключать в символы #. Например, #15 Апрель 1998#, #15/04/98# определяют одну и ту же дату.

Access предоставляет несколько функций, которые можно использовать при задании условий отбора для дат и времени:

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

month (дата) - возвращает значение месяца года в диапазоне от 1 до 12;

year (дата) - возвращает значение года в диапазоне от 100 до 9999;

weekday (дата) - возвращает целое число от 1(Воскресенье) до 7(Суббота), соответствующее дню недели;

date () - возвращает текущую системную дату.

Вычисляемые поля . Можно выполнить вычисления с любыми полями таблицы и сделать вычисляемое выражение новым полем в наборе записей. При этом можно использовать любые функции, встроенные в Access, и выполнять над полями таблицы арифметические операции с помощью операторов: +, -, *, /, \, ^, mod, &. Например, пусть имеется имя поля с именем “Количество ”, где записано количество единиц товара и поле “Цена ” , где записана стоимость единицы товара. Тогда для подсчета стоимости товара в пустое поле бланка запроса нужно ввести выражение Количество*Цена и значения этих полей будет перемножено.

Задание имен вычисляемых полей . При создании любого выражения в бланке запроса Access помещает стандартное имя поля “Выражение1 :”. Можно изменить или назначить имена полей, что является важным, если их нужно использовать в отчете или других запросах. Это делается с помощью окна свойств. Для этого нужно щелкнуть по любой ячейке соответствующего столбца, нажать кнопку Свойства на панели инструментов и выбрать Подпись .

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

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

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

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

Access предоставляет несколько функций для обеспечения групповых операций. Основные из них:

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

avg - Вычисляет среднее арифметическое значение всех значений данного поля в каждой группе;

min , max - вычисляет наименьшее (наибольшее) значение поля внутри группы;

count - вычисляет число записей, в которых значения данного поля отличны от Null .

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