Условия отбора записей - это набор предопределенных в Access или задаваемых вами правил. Они указывают Access, какие записи вы хотите просмотреть в динамическом наборе данных. Иначе говоря, с помощью условий отбора записей вы создаете ограничивающие фильтры, чтобы указать, какие записи следует найти, а какие - оставить за пределами динамического набора данных.

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

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

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

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

Это средство является довольно мощным инструментом. Учтите, что вы лишь задали образец данных, a Access не только интерпретировала его, но и использовала для создания динамического набора данных запроса. Это как раз то, что называют запросом по образцу (query by example): вы вводите образец и выполняете запрос на его основе.

Для удаления критерия из ячейки выделите ее содержимое и либо нажмите клавишу , либо выберите команду Правка Удалить (Edit Delete).

Условия можно также задавать для полей с данными типа Числовой (Numeric), Дата/время (Date/Time) и Логический (Yes/No).

Access может сравнивать значение поля типа Дата/время (Date/Time) с заданным значением, используя следующие операторы сравнения: <, >, = и их комбинацию. Обратите внимание, что Access автоматически размещает значения даты между разделителями (символами #). Разделители позволяют Access отличать данные типа Дата/время (Date/Time) от данных типа Текстовый (Text). Так же, как при вводе текстовых образцов, вам необязательно вводить знаки #, поскольку Access понимает, что вам нужно, и сама преобразует формат ввода.

Запросы подробно

Запрос является объектом БД. Он представляет собой сформулированную информационную потребность.

При работе с запросом можно выделить два этапа: формирование (проектирование) и выполнение. При выполнении запроса выбирается информация из всех таблиц БД в соответствии с критерием запроса.

Формирование запроса производится в Конструкторе запросовкомандами:

Вставка– Запрос;

Инструментом Новый объект–Запрос;

ВкладкаЗапрос – Создать запрос с помощью Конструктора;

ВкладкаЗапрос – кнопка Создать запрос с помощью Мастера.

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

Наличие "галочки" в строке "Вывод на экран" означает присутствие данного поля в таблице результатов поиска. Критерии запроса устанавливаются в строке "Условие отбора" и последующих строках, связанных логическим оператором OR. Все критерии отбора, указанные в одной строке, объединяются оператором AND.

В качестве "Условия отбора" могут быть выражения (вычисляемое поле), даты, текст, которые вносятся либо вручную, либо инструментом , либо с помощью команды контекстного меню Построить. Константы типа Дата/Время заключаются в #.

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

Вычисляемые поля в запросах

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

Для построения выражений имеется специальное средство Построитель выражений, вызываемый правой кнопкой мыши на поле или кнопкой Построить.

Рис.2. Окно Построителя выражений

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

Параметрические запросы

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

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

Итоговые запросы

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

Возможные операции в строке "Групповые операции":

SUM - сложение;

AVG - среднее значение;

MIN - минимальное значение;

МАХ - максимальное значение;

COUNT- количество записей со значениями (без пустых значений);

STDEV - стандартное отклонение;

VAR - дисперсия;

FIRST- значение в первой записи;

LAST - значение в последней записи.

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

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

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

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

Модификация БД с помощью запросов на изменение

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

Запрос на обновление

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

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

При обновлении полей следует иметь в виду, что если при проектировании таблицы в свойствах поля было указано "Условие на значение", то при обновлении этого поля условие может быть нарушено, чего не допустит MS Access. Поэтому нужно или изменить условие на значение, или удалить это условие в Конструкторе.

Запрос на добавление

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

Кроме того, при необходимости добавить данные в таблицу БД из другой базы можно также использовать запросы на добавление.

Запрос на удаление

"Старые" или неиспользуемые записи таблиц можно удалить, но обязательно сначала произвести выборку и проверить ее. Целесообразно сделать копию.

Запрос на добавление

1. Создайте путем копирования дубликат таблицы CONT без данных, назвав ее MEDAL (Медаль), для отбора медалистов. Для этого в контекстном меню для таблицы CONT выберите Копировать, затем выполните команду Вставить, в параметрах вставки укажите "Только структура" (см рис.). Просмотрите таблицу MEDAL - она должна быть пустой и иметь такую же структуру, как и таблица CONT.

2. Отберите в таблицу MEDAL записи обо всех абитуриентах-медалистах. Для этого:

Создайте запрос на выборку, включив в него все поля таблицы CONT (Контингент) в любой последовательности, и критерий по полю mdl (медаль) (Да в строке Условие отбора), запустите запрос для проверки правильности отбора записей;

Измените статус запроса "Выборка" на запрос "Добавление" (Запрос- Добавление… ), в появившемся окне задайте имя таблицы для добавления MEDAL , обратите внимание на появление строки "Добавление" в бланке запроса;

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

Самостоятельно создайте:

Запрос на добавление записей в таблицу HOSTEL (Общежитие) для всех иногородних (не москвичей);

Запрос на добавление записей в таблицу STUD (Студенты) для тех абитуриентов, у которых суммарный балл по экзаменам больше 30.

Запрос на удаление

6. Удалите из таблицы CONT (Контингент) записи об абитуриентах, получивших хотя бы по одному из экзаменов менее 5 баллов.

Создайте запрос на выборку, включив в него все поля таблицы CONT (Контингент) и критерий по полям с экзаменационными оценками, запустите запрос для проверки правильности отбора записей;

Измените статус запроса "Выборка" на запрос "Удаление". (Запрос- Удаление… )

Литералы - конкретные значения, воспринимаемые Access так, как они записаны. В качестве литералов могут быть использованы числа, текстовые строки, даты. Текстовые строки заключаются в двойные кавычки, даты - в знаки (#). Например, 567, "Информатика", #1-Января-99#.

Константы - не изменяющиеся значения, которые определены в Access, например, True, False, Да, Нет, Null .

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

Во многих случаях ссылка на конкретное значение должна указывать точное его местоположение в иерархии объектов базы данных, начиная с объекта верхнего уровня. Если необходимо указать ссылку на поле в конкретной таблице, форме, отчете, то перед именем поля ставится имя таблицы, формы, отчета, также заключенное в квадратные скобки и отделенное от имени поля восклицательным знаком. Например, ссылка на поле в таблице примет вид: [Имя таблицы]! [Имя поля] , а ссылка на свойство DefaultValue элемента управления Дата рождения в форме СТУДЕНТ: Forms! [СТУДЕНТ]! [ Дата рождения].DefaultValue

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

Эти операторы определяют операцию над одним или несколькими операндами.

Если выражение в условии отбора не содержит оператора, то по умолчанию используется оператор =.

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

Допускается использование операторов шаблона - звездочка (*) и вопросительный знак (?).

Оператор Between позволяет задать интервал для числового значения и даты. Например:

Between 10 And 100

задает интервал от 10 до 100; можно задать интервал дат:

Between #01.01.1997* And #31.12.1997*

Оператор in позволяет выполнить проверку на равенство любому значению из списка, который задается в круглых скобках. Например:

In ("Математики";"Информатики"; " Истории")

Оператор Like позволяет использовать образцы, использующие символы шаблона, при поиске в текстовых полях. Например: Like "Иванов* "

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

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

Логические операции

Логические операции "И", "ИЛИ"

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

Вычисляемые поля

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

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

Выражение вводится в бланк запроса в пустое поле строки Поле . После нажатия клавиши или перемещения курсора в другое поле строки, перед выражением в этом поле строки добавляется имя поля Выражeние N . N - целое число, увеличивающееся на единицу для каждого нового создаваемого вычисляемого поля в запросе. Имя вычисляемого поля, стоящее перед выражением, отделяется от него двоеточием. Например:

Выражение!: [Цена]*[Количество] где Цена и Количество - имена полей.

Имя вычисляемого поля - выражение1 становится заголовком столбца в таб­лице с результатами выполнения запроса. Это имя можно изменить.

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

Встроенные функции

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

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

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

Присвоение пользовательских имен вычисляемым полям

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

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

Для ограничения списка записей, получаемых в результате работы запроса, только удовлетворяющими определенным условиям - в бланке запроса предусмотрены поля для условий отбора. Коротко про это было рассказано в "Шаг 22 - Создание запроса на выборку" , теперь настало время разобраться более подробно.

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

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

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

Для этой цели служит специальная команда языка SQL , которая выглядит так:

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

По умолчанию Access определяет тип вводимых данных как Текстовый. Если же параметр задает условие отбора из столбца с данными типа Числовой или Дата/Время , то необходимо вручную назначить тип данных. Это делается следующим образом:

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

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

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

1. Выделите в окне базы данных (рис. 17.4) значок запроса Перечень контактов.

Рис. 17.4. Окно базы данных Access

2. Щелкните на кнопке Конструктор .

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

4. В левом списке построителя щелкните на папке Операторы .

5. В среднем списке выберите категорию Сравнения .

6. В правом списке дважды щелкните на пункте Between, чтобы добавить этот оператор в ноле формулы.

7. Щелчком выделите в поле формулы первый местозаполнитель «Выражение».

8. В левом списке построителя выражений двойным щелчком откройте папку Функции.

9. Щелкните на папке Встроенные функции, содержащей стандартные функции Access.

10. В среднем списке построителя выражений щелкните на пункте Дата/время .

11. В правом списке дважды щелкните на функции DateValue, чтобы заменить ею местозаполнитель «Выражение».

12. Нажмите два раза клавишу -> , выделив местозаполнитель «stringexpr».

13. Введите текст "1.12.99".

14. Повторяя шаги 7-13, замените второй местозаполнитель «Выражение» на выражение DateValue ("31.12.99").

У вас должна получиться формула Between DateValue ("1.12.99") And DateValue ("31.12.99"). Она проверяет условие нахождения даты в интервале от 1 до 31 декабря 1999 г., то есть отбирает те записи, значение поля Дата которых относится к декабрю 1999 г.

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

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

16. Сбросьте флажок Вывод на экран первого столбца запроса (рис. 17.5).

Рис. 17.5. Скрытие поля в Access

17. Щелчком на кнопке Запуск панели инструментов выполните запрос.

Сегодня мы подробно поговорим о запросах в 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-инструкций для запросов на обновление, изменение, удаление.