Сортировка записей в выборке

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

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

Отсортируем данные запроса по убыванию значений в поле Дата заказа. Для этого в поле Дата заказа в графе Сортировка из раскрывающегося списка выберем требуемый порядок сортировки (рис.7.3).

Проведение группировки и групповых вычислений

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

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

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

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

 

Наиболее часто употребляемые групповые функции

Обозначение	Функция	Назначение
SUM	Сумма	Вычисляет сумму сгруппированных данных
AVG	Среднее	Вычисляет среднее значение
MIN	Минимум	Находит наименьшее значение
MAX	Максимум	Находит наибольшее значение
COUNT	Счетчик	Считает количество сгруппированных записей

 

Сгруппируем заказы по дате и подсчитаем количество заказанных гарнитуров. В бланк конструирования нашего запроса включим групповую операцию. Для поля Количество выберем функцию Sum (рис. 7.3)

Рис.7.3. Групповая операция в бланке конструирования запроса

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

В поле Наименование товара используется подавление вывода значений. Ему назначается статус условия, чтобы значения этого поля не мешали группировке (рис. 7.3).

 

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

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

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

[Количество] * [Цена]

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

Выражение 1: [Количество] * [Цена]

Access автоматически использует «Выражение 1» в качестве имени вычисляемого поля, это имя нужно заменить на более содержательное, например, «Сумма заказа». Окончательный вид выражения для вычисляемого поля должен быть следующим:

Сумма заказа: [Количество] * [Цена]

 

Создадим вычисляемое поле Сумма заказа.В отдельную колонку бланка нашего запроса введем выражение:

Сумма заказа: [Количество] * [Цена]

Для вычисляемого поля выберем групповую операцию Sum (рис.7.4).

 

Рис.7.4. Вычисляемое поле в запросе

 

Сохранение запроса. Запрос будет сохранен в области навигации запросов. Изменения запроса производятся в режиме Конструктора.

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

a) сведения о покупателях из Новосибирска;

b) сведения о покупателях из Новосибирска и Бердска;

c) сведения о покупателях из Новосибирска, фамилии руководителей которых начинаются на «Л».

Задание 3. Из таблицТовар на базе и Поставщик сформируйте список товаров зарубежных производителей.

Задание 4. Найдите контактную информацию поставщиков столов по цене более 10 тыс.р. и шкафов по цене менее 50 тыс.р.

Задание 5. Найдите информацию о товарах, заказанных до 20 июня 2011 г. (за исключением товаров из Минска)

Задание 6. Для товаров в ценовом диапазоне от 10 тыс.р. до 50 тыс.р. рассчитать сумму наценки в размере 15% и цены реализации с учетом наценки.

Задание 7. Вычислить сумму скидки в размере 2% от заказов на сумму свыше 200 тыс.р.

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

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

Задание 8. Создайте на основе таблицы Поставщик параметрический запрос для вывода информации о поставщиках из конкретного города. В качестве запрашиваемого параметра используйте поле Город.

 

Запрашиваемый параметр вводится в квадратных скобках в строке Условие отбора конструктора запросов (рис. 7.5).

Рис.7.5. Параметрический запрос в режиме конструктора

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

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

Задание 9. Организуйте автоматическое увеличение цены товаров низкой ценовой категории (менее 20 тыс.р.) на 10 %.

Для этого создайте запрос на обновление данных таблицы Товар на базе.

В окне конструктора запросов на ленте выберите пункт Обновление.

Для поля Цена установите правильное условие отбора!

Формулу [Цена]*1,1 введите в строку Обновление поля Цена.

Результаты выполнения запроса отразятся в таблице Товар на базе.

 

Основы SQL

Когда формируете запрос в Конструкторе запросов, то на самом деле этот запрос формируется на языке структурированных запросов (Structured Query Language – SQL).

В реляционной модели объекты реального мира и взаимосвязи между ними представляются с помощью совокупности связанных между собой таблиц (отношений). В результате разработки реляционной модели данных был создан язык SQL. Все современные профессиональные реляционные СУБД используют язык SQL,

Чтобы просмотреть создаваемые Access команды SQL, выберите команду Вид | Режим SQL.

Оператор SELECT – один из наиболее важных и самых распространенных операторов SQL. Он позволяет производить выборки данных из таблиц и преобразовывать к нужному виду полученные результаты.

 

Оператор SELECT определяет поля (столбцы), которые будут входить в результат выполнения запроса.

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

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

Порядок предложений и фраз в операторе SELECT не может быть изменен.

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

Параметр WHERE.

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

Существует пять основных типов условий поиска:

1. Сравнение: сравниваются результаты вычисления одного выражения с результатами вычисления другого.

2. Диапазон: проверяется, попадает ли результат вычисления выражения в заданный диапазон значений.

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

4. Соответствие шаблону: проверяется, отвечает ли некоторое строковое значение заданному шаблону.

5. Значение NULL: проверяется, содержит ли данный столбец определитель NULL (неизвестное значение).

 

Параметр ORDER BY.

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

Сортировка может выполняться по нескольким полям, в этом случае они перечисляются за ключевым словом ORDER BY через запятую.

 

Задание 10.

Ниже приведены примеры семи запросов на языке SQL. Прокомментируйте их.

 

SELECT Покупатель.[Наименование покупателя], Покупатель.Город, Покупатель.Телефон

FROM Покупатель

WHERE (((Покупатель.Город)="Новосибирск"));

 

SELECT Покупатель.[Наименование покупателя], Покупатель.Город, Покупатель.Телефон

FROM Покупатель

WHERE (((Покупатель.Город)="Новосибирск")) OR (((Покупатель.Город)="Бердск"));

 

SELECT Покупатель.[Наименование покупателя], Покупатель.Город, Покупатель.[Фамилия руководителя], Покупатель.Телефон

FROM Покупатель

WHERE (((Покупатель.Город)="Новосибирск") AND ((Покупатель.[Фамилия руководителя]) Like "Л*"));

 

SELECT Поставщик.[Наименование поставщика], Поставщик.Страна, [Товар на базе].[Наименование товара]

FROM Поставщик INNER JOIN [Товар на базе] ON Поставщик.[Код поставщика] = [Товар на базе].[Код поставщика]

WHERE ((Not (Поставщик.Страна)="Россия"));

 

SELECT Поставщик.[Наименование поставщика], Поставщик.Страна, [Товар на базе].[Наименование товара]

FROM Поставщик INNER JOIN [Товар на базе] ON Поставщик.[Код поставщика] = [Товар на базе].[Код поставщика]

WHERE ((Not (Поставщик.Страна)="Россия"))

ORDER BY Поставщик.[Наименование поставщика];

 

SELECT Поставщик.[Наименование поставщика], Поставщик.Страна, [Товар на базе].[Наименование товара]

FROM Поставщик INNER JOIN [Товар на базе] ON Поставщик.[Код поставщика] = [Товар на базе].[Код поставщика]

WHERE ((Not (Поставщик.Страна)="Россия"))

ORDER BY Поставщик.[Наименование поставщика], [Товар на базе].[Наименование товара] DESC;

 

UPDATE [Товар на базе] SET [Товар на базе].Цена = [Цена]*1.1;

Вопросы и упражнения

1. С помощью запроса узнать, кто из контрагентов обслуживаются одинаковыми банками?

2. С помощью запроса определить, кто из менеджеров является лидером продаж?

3. С помощью запроса определить, кто из менеджеров оформил заказы на диваны и гарнитуры в первой декаде 2011 г.

4. Сколько заказов было осуществлено каждым покупателем?

5. Поясните, почему кнопки одних типов запросов помечены красными восклицательными знаками, а другие – черными?

Тема 8. Создание макросов

Макросом называют набор из одной или более макрокоманд, выполняющих определенные операции. Макросы необходимы для автоматизации бизнес-процессов.

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

Процесс обработки заказа можно разделить на несколько этапов:

1. Подготовка необходимых для обработки объектов (формы Товар на базе и Заказ на товар).

2. Поиск заказанных товаров в таблице Товар на базе (совпадение по полю Код товара).

3. Подтверждение наличия искомого товара в требуемом количестве.

4. Уменьшение остатка товара на базе на величину заказа.

 

Задание 1. Создание временной таблицы для обработки заказа.

Пояснения.

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

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

1. На вкладке Создание перейдите в Конструктор запросов;

2. Оформите запрос как показано на рис. 8.1.

3. Вычисляемое поле Сумма должно выглядеть следующим образом:

Сумма: [Заказ на товар]![Количество]*[Товар на базе]![Цена].

 

Рис.8.1 Запрос на текущий заказ в режиме конструктора

4. В разделе тип запроса выберите команду Создание таблицы .

5. В открывшемся диалоговом окне присвойте создаваемой таблице имя Текущий заказ (рис. 8.2).

 

Рис.8.2 Создание таблицы Текущий заказ

6. Выберите команду и убедитесь, что таблица отобразилась в списке объектов Области переходов.

7. Присвойте запросу имя: Запрос на текущий заказ.

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

9. Для выполнения следующего задания оформите новый заказ, содержащий одну позицию и зафиксируйте в тетради код заказанного товара.

10. Создайте формы Товар на базе и Текущий заказ любым из известных вам способов.

 

Создание макроса

Ознакомимся с общими принципами создания, запуска и отладки простейших наборов макрокоманд.

1. На вкладке Создание щелкните кнопку Макрос, откроется построитель макросов (рис. 8.3).

 

Рис.8.3. Окно конструктора макросов

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

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

4. Для добавления в макрос других макрокоманд перейдите на следующую строку. Макрокоманды выполняются в порядке их расположения в бланке.

5. Если в макросе некоторая макрокоманда должна выполняться только при определенном условии, то такая макрокоманда должна содержать условное выражение. Если условное выражение ложно, Access игнорирует эту макрокоманду и переходит к ближайшей строке.

 

 

Задание 2. В конструкторе макросов создайте набор макрокоманд, показанный в таблице, и присвойте ему имя Пробный макрос (см. также рис. 8.4).

 

Пробный макрос

Макрокоманда	Аргумент	Значение
ОткрытьЗапрос	Имя запроса	Запрос на Текущий заказ
	Режим	Таблица
	Режим данных	Изменение
ОткрытьФорму	Имя формы	Текущий заказ
	Режим	Форма
	Режим данных	Изменение
ОткрытьФорму	Имя формы	Товар на базе
	Режим	Форма
	Режим данных	Изменение
Окно Сообщения	Сообщение	Формы для обработки открыты

 

1. Выберите команду , а затем команду выполнить.

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

 

Рис. 8.4. Пробный макрос в режиме конструктора

2. Для поиска заказанных товаров в таблице Товар на базе (совпадение по полю Код товара) дополните Пробный макрос следующими командами:

 

Макрокоманда	Аргумент	Значение
ЗапускМакроса	Имя макроса	Макрос2
	Число повторов
	Условие повтора	[Формы]![Текущий заказ]![Код товара]<>[Формы]![Товар на базе]![Код товара]
Окно Сообщения	Сообщение	Заказанный товар найден
Остановить макрос

 

Для заполнения строки Условие повтора воспользуйтесь построителем (рис. 8.5).

 

Рис.8.5. Задание условия повтора

 

3. Создайте Макрос2, необходимый для перемещения по строкам таблицы Товар на базе во время поиска кода заказанного товара.

 

Макрос: Макрос2

Макрокоманда	Аргумент	Значение
НаЗапись	Тип объекта	Форма
	Имя объекта	Товар на базе
	Запись	Следующая

 

4. Запустите Пробный макрос по шагам и убедитесь, что в таблице Товар на базе действительно найден заказанный Вами товар.

 

Примеры макросов

Задание 3. Создайте четыре макроса (их тексты даны ниже), которые обрабатывают заказ покупателя. Главным является макрос Обработка строк заказа, остальные три макроса с именами Макрос1, Макрос2 и Макрос3 являются вспомогательными.

 

Макрос: Обработка строк заказа

Макрокоманда	Аргумент	Значение
ОткрытьЗапрос	Имя запроса	Запрос на Текущий заказ
	Режим	Таблица
	Режим данных	Изменение
ОткрытьФорму	Имя формы	Текущий заказ
	Режим	Форма
	Режим данных	Изменение
ОткрытьФорму	Имя формы	Товар на базе
	Режим	Форма
	Режим данных	Изменение
ЗапускМакроса	Имя макроса	Макрос3
	Число повторов
	Условие повтора	Not IsNull([Формы]![Текущий заказ]![Код товара])
Окно Сообщения	Сообщение	Все строки заказа этого покупателя обработаны
Закрыть	Тип объекта	Форма
	Имя объекта	Товар на базе
Закрыть	Тип объекта	Форма
	Имя объекта	Текущий заказ
Остановить макрос

 

Макрос: Макрос1

Условие	Макрокоманда	Аргумент	Значение
[Формы]![Товар на базе]![Остаток товара]<[Формы]![Текущий заказ]!Количество]	Окно Сообщения	Сообщение	Нет такого количества товара
ЗадатьЗначение	Элемент	[Формы]![Текущий заказ]![Количество]
	Выражение	[Формы]![Товар на базе]![Остаток товара]

 

Макрос: Макрос2

Макрокоманда	Аргумент	Значение
НаЗапись	Тип объекта	Форма
	Имя объекта Запись	Товар на базе Следующая

Макрос: Макрос3

	Макрокоманда	Аргумент	Значение
IsNull([Формы]![Текущий заказ]![Код товара])	ОстановитьМакрос
	НаЗапись	Тип объекта	Форма
		Имя объекта	Товар на базе
		Запись	Первая
	ЗапускМакроса	Имя макроса	Макрос2
		Число повторов
		Условие повтора	[Формы]![Текущий заказ]![Код товара]<>[Формы]![Товар на базе]![Код товара]
	ЗапускМакроса	Имя макроса Число повторов Условие повтора	Макрос1
	ЗадатьЗначение	Элемент	[Формы]![Товар на базе]![Остаток товара]
		Выражение	[Формы]![Товар на базе]![Остаток товара]-[Формы]![Текущий заказ]![Количество]
	НаЗапись	Тип объекта	Форма
		Имя объекта	Текущий заказ
		Запись	Следующая

 

Откройте Макрос1 в режиме конструктора. Сравните полученный вами результат с рис.8.6. Убедитесь в том, что они совпадают.

 

Рис. 8.6. Макрос1 в режиме конструктора

 

Отладка макросов

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

Задание 4.. Произведите отладку макроса Обработка строк заказа и вспомогательных макросов Макрос1, Макрос2 и Макрос3.

Пояснения.

1. Через форму Покупатель – его заказы оформите заказ на единственный товар в количестве меньшем остатка этого товара на базе.

2. Зафиксируйте код заказа, количество заказанного товара и остаток данного товара на складе.

3. Запустите Макрос Обработка строк заказа, указав в качестве текущего код только что созданного заказа. При правильной работе макросов, остаток заказанного товара на базе уменьшится на количество заказанного товара.

Вопросы и упражнения

1. Поясните назначение макросов в Access.

2. Как создаются макросы?

3. Как можно указать условие выполнения одной или нескольких макрокоманд?

4. Какие методы отладки макросов предоставляет Access?

5. Как для запуска макроса можно использовать событие?

6. Как связать кнопку с выполнением определенного макроса?