Запросы на ВЫБОРКУ
При задании условия отбора можно использовать операторы >, <, =, >= (больше или равно),<= (меньше или равно), <> (не равно), Between, In, Like, And, Or, Not.
Найдем всех молодых (моложе 2 лет) собак.
Обратите внимание! Access работает с разными типами данных: строковыми, числовыми, временными, логическими и др. Чтобы показать, к какому типу он отнес данное условие отбора, Access автоматически заключает строки в кавычки («шар-пей»), даты – в решетки (#01.05.2008#), числа оставляет, как есть (15), а логические проверяет, чтобы были либо ИСТИНА, либо ЛОЖЬ.
Составные условия отбора
Если нужно задать несколько условий, то условия для оператора «И» записывайте в одной строчке, а для оператора «ИЛИ» в одном в столбце друг под другом.
Здесь мы находим всех английских сеттеров и сеттеров-гордонов.
А здесь всех щенков (моложе года) шар-пеев.
А кого мы найдем здесь?
Составные условия отбора также можно писать в одну ячейку, используя операторы Or, And.
Вот так будут выглядеть те же запросы, но записанные с помощью операторов:
Все английские сеттеры и сеттеры-гордоны.
Всех юниоры (которым больше года, но меньше двух лет).
Оператор LIKE
Оператор LIKE позволяет делать отбор по фрагменту строки.
Значит, чтобы Access мог отыскать английских, ирландских сеттеров и сеттеров-гордонов, нужно написать в условии отбора LIKE *сеттер*.
Запросы из нескольких таблиц
В запросах мы можем объединять данные из нескольких связанных между собой таблиц.
Отсев пустых строк
Когда связываются несколько таблиц, то могут возникать пустые строки.
Есть два способа удалить пустые строки.
1. Поставить условие на значение клички собаки Is not Null, т.е. НЕ ПУСТА.
2. Или поменять тип связи между таблицами в области таблиц: нужно на линии связи, дающей неточный результат, вызвать контекстное меню, и поменять Параметры объединения.
Запросы с вычислениями
До сих пор мы только отбирали записи по различным условиям. Но Access позволяет не только просматривать данные, записанные в таблицах, но и производить ВЫЧИСЛЕНИЯ: по дате рождения определять возраст; из имени, фамилии и отчества делать фамилию с инициалами; по цене единицы товара и его количества определять общую стоимость покупки; по дате выдачи книги в библиотеке определять величину штрафа при задолженности и многое другое. Для вычислений используются встроенные функции.
Простейшая операция – это сложение строк. Напишем в ячейке выражение для вывода такой фразы: владелец из города город.
Для этого запишем в верхней строке нового столбца области условий: [LastName] + “ из города ” + [City].
Имена полей записываются в квадратных скобках, фрагменты строк –
В кавычках, между ними стоят знаки сложения.
Выражения для вычислений записываются в самой верхней строке (Поле) области условий. До сих пор мы писали условия в нижних строках (Условия отбора).
Чтобы вам не запутаться: в верхней строке мы пишем, ЧТО выводить на экран, а затем (в нижней) – при КАКОМ УСЛОВИИ.
Построитель выражений
Чтобы удобнее было редактировать выражения, существует специальный
редактор – «Построитель выражений». Он выглядит так:
И вызывается с помощью контекстного меню (или на ленте Построитель): нужно поставить курсор на ту ячейку, куда вы будете записывать выражение:
В построителе выражений вы можете выбирать из библиотеки функции:
и данные из таблиц (можно пользоваться ТОЛЬКО теми таблицами, которые используются в данном запросе и отображены в области данных):
Когда вы двойным щелчком мыши выбираете из списка имя поля или функцию, 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([Age]<=1; «щенок»; «взрослый») аналог функции ЕСЛИ из Ecxel.
и другие…
Запросы с параметром
Когда вы запускаете запрос с параметром, он, в отличие от обычного запроса на выборку, не выполняется сразу, а сначала в диалоговом окне просит вас уточнить некоторые условия отбора. Например, мы хотим получить полную информацию о собаке с определенным турнирным номером.
Устроен этот запрос так:
На том месте, где обычно стоит условие отбора, теперь записан вопрос (в квадратных скобках), который будет задан пользователю. А ответ пользователя, как вы уже догадались, будет подставлен в эту ячейку в качестве условия отбора.
Запросы с группировкой. Итоговые запросы
С помощью построителей выражений мы можем производить операции над одной строкой: складывать значения в ячейках, преобразовывать данные.
Но что, если нам надо обработать сразу несколько строк: посчитать сумму баллов, найти количество строк с одинаковым значением заданного поля?
Для этого используется группировка (она очень похожа на подведение итогов в Excel).
Посчитаем, сколько собак каждой породы приехали на выставку. Для этого перебросим в область условий всего два поля: кличка и порода – и вызовем дополнительную строку групповые операции (через контекстное меню (или на ленте) на области условий):
| Count | Количество записей, соответствующее ноле которых не содержит величины Null |
| Мах | Максимальное значение |
| Min | Минимальное значение |
| Avg | Среднее значение поля |
| Sum | Сумма значений поля по всем записям |
Теперь сгруппируем собак по породе и посчитаем количество разных кличек в каждой группе:
Подведем итоги выставки и посчитаем средний балл за экстерьер, средний балл за дрессировку и их сумму.
Добавляем в область таблиц таблицу с оценками (tbMarks). Группируем оценки по турнирному номеру собаки и среди групповых операций выбираем среднее значение Avg (от англ. average – среднее).
Выполните запрос и в режиме просмотра обратите внимание, что столбцы с групповыми операциями имеют двойное название (операция + имя поля). Нам это пригодится при подсчете суммы баллов.
Осталось посчитать сумму. В новом столбце записываете выражение для суммы, (используя имена полей из этого же запроса!). Не забудьте при этом в строке групповых операций поставить Выражение.
Можно также округлить значения до одного знака после запятой: Round([Avg-Exterior]+[Avg-Training];1)