Системы управления базами данных Access 2007

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Рис. 3.32. Диалоговое окно итогового  запроса

Sum – вычисление суммы значений элементов в поле.

Avg – определение среднего значения элементов в поле.

Min – поиск минимального значения элемента в поле.

Max – поиск максимального значения элемента в поле.  

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

Запросы на выборку с условием

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

• Создадим запрос, позволяющий выбрать все товары фирмы ASUS. Для этого в качестве источника добавим таблицу «Товары на складе», а в строке Условие отбора: в поле Фирма укажем название интересующей нас фирмы.

Рис. 3.33. Условие отбора в бланке запроса

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

Рис. 3.34.Условие отбора с вариативной  частью

• Вывести информацию о заказах на материнские платы превышающие, 5 шт.

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

Рис. 3.35. Условие отбора с операцией сравнения

 

Запросы с использованием параметров

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

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

 

Рис. 3.36. Условие отбора с параметром

 

Запросы с элементами программирования

Создадим запрос, рассчитывающий заработную плату для каждого работника, с учетом того, что ему назначается  премия 10 % от оклада в случае, если отработано боле 24 смен и премия 25 %, если отработано 30 и более смен. В данном случае создадим запрос с вычислениями через  построитель выражений, и будем  использовать функцию управления I I f, схожую по своей структуре со стандартной функцией ветвления: 

IIf(<условие>;<действие, выполняющиеся, когда условие верно >;<действие, выполняющиеся, когда условие не верно>)

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

 

Рис. 3.37. Ввод формулы

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

Зарплата: IIf([Количество смен]>=30; [Персонал]![Оклад за смену]*[Количество смен]*1,25; IIf([Количество смен]>24; [Персонал]![Оклад за смену]*[Количество смен]*1,1; [Персонал]![Оклад за смену]*[Количество смен]))

Создадим запрос, выдающий приветствие  работнику магазина в зависимости  от пола:  Уважаемый …! Уважаемая…!

Здесь мы снова воспользуемся функцией IIf и строковыми функциями. 

Обратите внимание, что при сложении строк образуется новая строка, объединяющая обе, но без пробела. Пробел добавляется  искусственно:

Обращение: IIf([Персонал]![Пол]=«М»;»Уважаемый « +[Персонал]![Фамилия] +” ”+[Персонал]![Имя] +” ” +[Персонал]![Отчество] +” ”+»!»;»Уважаемая  « +[Персонал]![Фамилия] +” ”+[Персонал]![Имя] +” ” +[Персонал]![Отчество]+ ” ”+»!»)

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

 

Рис. 3.38. Ввод ограничений на поле

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

 

Запросы с использованием групповых  операций

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

• Группировка – объединение объектов в группы по какому-то признаку.
• Sum – сумма всех значений в каждой группе, применяется только к числовым и денежным полям;
• Avg – среднее в каждой группе, применяется только к числовым и денежным полям;
• Min – наименьшее значение в этом поле внутри группы, для текстовых полей – наименьшее из символьных независимо от регистра;
• Max – наибольшее значение в этом поле внутри группы, для текстовых – наименьшее из символьных независимо от регистра;
• Count – число записей, в которых значения отличны от Null;
• First – первое значение поля; Last – последнее значение поля.
• С помощью  групповых операций рассчитаем количество мужчин и женщин, работающих в магазине. Для этого сгруппируем по признаку пол  (Группировка) и вычислим количество строк в той и другой группе (Count).

 

Рис. 3.39. Вызов групповых операций

 

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

Рис. 3.40. Выбор групповой операции

Рис. 3.41. Источники для выполнения запроса

 

Запросы на действие

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

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

 

Самостоятельная работа

Создайте запрос, отражающий информацию о заказанном клиенте товаре (название, фирма производитель, краткая характеристика, количество на складе, количество заказа).

1. Рассчитайте общее количество товара на складе.
2. Выведи информацию о товаре, которого на складе находится наибольшее количество.
3. Рассчитайте общее количество процессоров на складе.
4. Выведите информацию о мониторах, количество которых на складе превышает 4 шт.
5. Выведите информацию о товарах, поступивших в 2012 году.
6. Рассчитайте стоимость всех заказов для каждого клиента.
7. Рассчитайте чистую прибыль магазина для определенного периода, вычитая из прибыли от продаж 25% налоговой суммы и расходы на заработную плату.
8. Выделите одноименную группу товаров, пользующуюся наименьшим спросом.
9. Выведите самый низкий по популярности товар.
10. Выделите самого активного клиента.
11. Создайте запрос поиска товара по наименованию.
12. Создайте запрос поиска информации о товаре после ввода какой-то части характеристики товара.
13. Рассчитайте общее количество женщин и мужчин определенного года рождения среди клиентов.
14. Выведите заработную плату работников с именем, начинающимся на букву А.

§25. Эффективное представление данных с помощью форм

Числовые данные на форме можно  представлять в виде диаграмм. Составим форму-диаграмму, описывающую спрос  на товар. Для этого в запрос с  расчетом спроса добавьте таблицу «Товары» и поля с названием товара и  ценой. Затем на закладке Формы выберите команду Создать, а там в списке найдите команду Диаграмма.

В качестве источника выберите исправленный вами запрос «Спрос на товар». В появившемся  диалоговом окне выберите все поля кроме № товара и нажмите кнопку Далее. Затем выберите тип диаграммы (объемная гистограмма) и распределите данные, как показано на рисунке 3.43. 

Рис. 3.43. Этапы создания диаграммы

 

При желании, нажав на кнопку Образец,  вы сможете осуществить предварительный просмотр вашей диаграммы.  После распределения данных диаграмме необходимо дать имя («Диаграмма спроса») и нажать кнопку Готово.

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

Самостоятельно постройте по таблице «Товары»  диаграмму предложения товара.

 

А теперь создадим форму, описывающую  товар с использованием вкладок, диаграмм, расчитываемых полей. Для  этого создадим форму в столбец  по запросу, объединяющему таблицы  «Товары» и «Заказы клинтов» с  помощью мастера форм.

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