Запросы на ВЫБОРКУ
При задании условия отбора можно использовать операторы >, <, =, >= (больше или равно),<= (меньше или равно), <> (не равно), 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)