Использование функций в запросах

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

Для вывода текстовых данных из нескольких полей применяется функция конкатенации (сцепления текста), оператор которой записывается символом &.

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

 

[Фамилия сотрудника]&" "&[Имя сотрудника],

 

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

 

Фамилия &" "& Имя

 

В результате выполнения этого выражения в запросе будет создано поле, объединяющее в себе значения сразу двух полей (Иванов Иван, Петров Петр и т.д.).

Часто используемой бывает несколько иная задача - сцепить значение поля Фамилия и лишь первые буквы из полей Имя и Отчество (инициалы).

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

 

Left(String; Length),

 

где String - обязательный аргумент. Строковое выражение, из которого извлекаются символы. Если String имеет значение Null (пустая строка), возвращает значение Null;

Length - обязательный аргумент. Числовое выражение, указывающее число возвращаемых символов. Если Length имеет значение 0, возвращается пустая строка (" "). Если значение Length больше, либо равняется числу символов в строке String, возвращается вся строка.

В нашем случае эта функция для поля Имя должна принять вид:

 

Left([Имя];1).

 

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

 

рис. 5.15. Создание вычисляемого поля в области ввода

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

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

Для создания запроса с параметром необходимо выполнить следующие действия:

1. Открыть окно Конструктора запросов, выбрать необходимые таблицы и заполнить Бланк QBE нужными полями, например, как на рис. 5.16.

 

рис. 5.16. Бланк QBE запроса с параметром

 

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

 

[Введите товар]

 

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

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

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

SQL-запросы

В MS Access имеется три типа запросов, которые не могут быть созданы в режиме Конструктора запросов. Вместо этого необходимо просто ввести SQL-выражение непосредственно в режиме SQL. В режим SQL можно попасть из окна Конструктора запросов с помощью кнопки Вид, расположенной в группе Результаты контекстной вкладки Работа с запросами – Конструктор ленты инструментов. Если раскрыть список кнопки Вид, то можно увидеть строку Режим SQL. Щелкнув по этой строке, попадаем в режим SQL, в котором открывается область ввода, где необходимо писать инструкции с помощью структурированного языка запросов SQL.

Существуют следующие типы SQL-запросов:

- запрос на объединение. Объединяет поля (или столбцы) одной или нескольких таблиц или запросов в один результирующий набор записей (объединение осуществляется с помощью оператора UNION). Пример представлен на рис. 5.17;

 

рис. 5.17. Пример запроса на объединение

- запрос к серверу. Передает SQL-инструкции непосредственно в БД с использованием синтаксиса SQL-команд сервера;

- управляющий запрос. Создает или изменяет таблицы БД.

Для создания любого из этих запросов необходимо выбрать его тип в группе Тип запроса контекстной вкладки Работа с запросами – Конструктор ленты инструментов.

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

Например, запрос, отбирающий из таблицы «Заказы» 10 первых заказов по сумме, в режиме SQL имеет вид, представленный
на рис. 5.18.

 

рис. 5.18. Пример запроса в режиме SQL

 

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

Три из этих основных команд присутствуют в инструкции, приведенной на рис. 5.18.

 

 

Таблица 5.1.

Команда	Назначение
SELECT	С этой команды начинается SQL-выражение. За командой следуют имена полей, которые будут выбраны из таблицы или таблиц (если в команде FROM их будет определено несколько). Наличие этой команды в SQL-запросе обязательно.
FROM	Определяет имена таблиц, содержащих поля, которые определены в команде SELECT. Наличие этой команды в SQL-запросе обязательно.
WHERE	Определяет условия фильтрования (ограничения) просматриваемых записей. Используется при необходимости выводить записи, удовлетворяющие тем или иным условиям.
ORDER BY	Определяет порядок вывода записей в результирующей таблице.

Задания

1. Получите с помощью запроса текущий список цен на товары, расположив товары по типам в алфавитном порядке. Сохраните запрос под именем «ЦеныНаТовары».

2. Сделайте копию запроса, созданного в задании 1, сохранив его под новым именем «ТоварыДешевле1000руб».

3. Измените запрос «ТоварыДешевле1000руб» так, чтобы получить список товаров дешевле 1000 руб.

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

5. Получите список типов товаров, дающий информацию об их поставщиках. Сохраните запрос под именем «ПоставщикиТоваров».

6. Создайте запрос с параметром, позволяющий получить список типов товаров, поставляемых заданным поставщиком. Сохраните запрос под именем «ТоварыПоставляемыеЗаданнымПоставщиком».

7. Измените структуру таблицы «Поставщик» в режиме Конструктора таблиц, добавив в нее поля ФамилияДиректора, ИмяДиректораи ОтчествоДиректора.

8. Заполните поля ФамилияДиректора, ИмяДиректора и ОтчествоДиректора в соответствии со следующей таблицей:

 

 

Фамилия	Имя	Отчество
Антонов	Антон	Антонович
Аланов	Иван	Иванович
Арланов	Петр	Петрович
Автофоров	Семен	Семенович
Коронин	Петр	Иванович
Декин	Семен	Петрович
Эксимеров	Василий	Васильевич
Фениксов	Виктор	Викторович
Гиреев	Владимир	Владимирович
Мономах	Дмитрий	Дмитриевич
Иванов	Борис	Борисович
Велессон	Михаил	Михайлович
Веста	Федор	Федорович

9. Создайте запрос, позволяющий получить информацию о названии поставщика, о поставляемых товарах, фамилии и инициалах директора фирмы. Сохраните запрос под именем «СведенияОПоставщиках».

10. Создайте запрос с помощью оператора BETWEEN, позволяющий получить информацию о товарах, у которых цена оптовая находится в диапазоне 1000 р. - 2000 р. Сохраните запрос под именем «ДиапазонЦен».

11. Создайте запрос с использованием оператора IN, позволяющий получить информацию о товарах, у которых цена оптовая равна 200 р., 1100 р., 2550 р., 4500 р. Сохраните запрос под именем «СписокЦен».

12. Создайте запрос с использованием оператора LIKE, позволяющий получить информацию о товарах, у которых название типа начинается на «видео». Сохраните запрос под именем «Видео».

13. Создайте запрос с использованием оператора NOT BETWEEN, позволяющий получить информацию о товарах, у которых цена розничная не попадает в диапазон от 1000 р. до 3000 р. Сохраните запрос под именем «ЦенаВнеДиапазона».

14. Создайте запрос с использованием оператора NOT IN, позволяющий получить информацию о товарах, у которых цена розничная не равна 200 р., 1100 р., 4500 р. Сохраните запрос под именем «ЦенаКромеУказанных».

15. Создайте запрос с использованием оператора NOT LIKE, позволяющий получить информацию о товарах всех типов, кроме телевизора. Сохраните запрос под именем «ТипыБезТелевизора».

16. (*) Посмотрите какой-либо из созданных запросов в режиме SQL.

17. (*) Создайте запрос в режимме SQL, позволяющий получить информацию о типах, марках и артикулах товаров. Сохраните запрос под именем «ТипМаркаАртикул».

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

Контрольные вопросы

1. Перечислите основные функции запросов.

2. Назовите основные средства создания запросов.

3. Как сохранить запрос под другим именем?

4. Что такое Область ввода?

5. Для чего нужны условия отбора записей?

6. Что такое параметрический запрос?

7. Что такое язык SQL?

8. Какие существуют типы SQL-запросов?

9. Перечислите основные команды языка SQL и определите их основное назначение.