Лабораторная работа 2_Создание запросов к БД_Оптовая торговля (1. Занятие 2 Создание запросов к бд оптовая торговля

• Название: Занятие 2 Создание запросов к бд оптовая торговля
• Дата: 30.09.2021
• Размер: 0.81 Mb.
• Имя файла: Лабораторная работа 2_Создание запросов к БД_Оптовая торговля (1.doc
• Тип: Занятие; #239327

Лабораторное занятие 2

Создание запросов к БД «»Оптовая торговля»

Цель: изучить процесс проектирования различных видов запросов с помощью Мастера и Конструктора запросов
1 Краткая характеристика запросов
По принципу своего действия все запросы Microsoft Access делятся на несколько видов. Наиболее часто применяются запросы-выборки, производящие простую выборку некоторой информации из одной или нескольких взаимосвязанных таблиц. Полученные данные представляются в виде обычной таблицы, что позволяет создать на ее основе пользовательскую форму, представляющую результат на экране в удобном для восприятия виде. Кроме того, в отличие от обычной таблицы, в запросе, кроме условий выборки, можно задавать дополнительные поля, в которых указывать формулы, оперирующие содержимым других полей. Практически в точности как в Microsoft Excel. В этом случае вместе с отобранными данными во временной таблице появятся и результаты обсчета формул. Таким образом, механизм запросов компенсирует отсутствие в таблице возможности автоматически произвести какие бы то ни было расчеты.

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

Третьей разновидностью запросов является запрос-удаление. Следует помнить, что Microsoft Access обычным образом не позволяет удалять данные, занесенные в таблицу. Вообще-то, в ручном режиме пользователь может удалить одну или несколько строк, но это обычно долговременная операция. Причем, поле типа «Счетчик» при таком удалении не обновляется, а значит, по окончании процедуры последовательность будет нарушена. Следовательно, потом номер последней строки вовсе не свидетельствует о реальном количестве записей в таблице. Подобное затруднение относительно легко разрешается простой процедурой поверки количества строк в таблице, однако это отнимет некоторую часть машинных ресурсов. Таким образом, как только из таблицы требуется что-либо удалить, следует воспользоваться запросом-удалением. В отличие от ручного режима, автоматическое удаление счетчик как раз обновляет, и операция происходит на много быстрее.

2. Создание простого запроса с помощью мастера

Для создания нового запроса следует сначала перейти на вкладку «Запросы» и нажать на кнопку «Создать». Всего предложенное меню Microsoft Access содержит пять возможных вариантов создания запросов (рисунок 2.1).


Рисунок 2.1 – Окно мастера запросов
2.1 Создание запросов в режиме Конструктора.

Профессионалы, хорошо освоившие данную СУБД или пользующиеся непосредственным программированием на языке запросов (SQL), выбирают режим «Конструктор», в котором все операции производятся вручную. Это имеет как плюсы, так и минусы. Слишком много рутинных операций, зато можно сконструировать какой угодно запрос.
2.2 Создание простых запросов с помощью Мастера.

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



Рисунок 2.2 – Мастер построения запросов


Рисунок 2.3

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

Мастер задаст еще один уточняющий вопрос, какого рода ответ вы желаете (рисунок 2.3). Ответов предусмотрено два: подробный список, отсортированный согласно вашим пожеланиям, или итоговый подсчет в стиле – «Всего столько-то».

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

Авторы СУБД MS Access предусмотрели также такой вариант, когда услуги мастера для построения запросов используются исключительно для выполнения большей части черновой работы. Если вы хотите добраться до внутренней механики полученного запроса - выберите вариант «изменение структуры запроса», иначе программа автоматически решит показать получившийся результат. Можно также «попросить» Access вывести подсказку по работе с запросами. Это делается путем выставления галочки против записи «Вывести справку по работе с запросом».

Далее можно нажать экранную кнопку «Готово» и посмотреть на результат. Для изменения существующего запроса используется режим «Конструктор», попасть в который можно выделив нужный запрос и нажав экранную кнопку «Конструктор».
2.3 Создание перекрестных запросов.

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

Перекрестные запросы обладают следующими достоинствами:

1. 
   возможностью обработки значительного объема данных и вывода их в формате, который очень хорошо подходит для автоматического создания графиков и диаграмм;
   
2. 
   простотой и скоростью разработки сложных запросов с несколькими уровнями детализации;
   
3. 
   возможностью указать поле, которое является заголовком строки;
   
4. 
   возможностью задать поле или несколько полей, которые являются заголовками столбцов, а также условия определяющее значения в столбцах.
   

Перекрестные запросы имеют один недостаток – нельзя сортировать таблицу результатов по значениям, содержащимся в столбцах, так как в подавляющем большинстве случаев одновременное упорядочивание данных в столбцах по всем строкам невозможно.
Пример. Рассмотрим таблицу товаров, которая содержит три поля данных: «Kod_t» (код типа товара), «Kod_p» (код поставщика), «Cena_pr» (цена продажи товара). Для каждой категории товаров требуется определить суммы цен товаров с указанием поставщика.

Поместите в окно Конструктора запроса таблицу «Товары». Затем включите строку «Групповая операция», щелкнув на кнопку «Итоги» . После этого отбуксируйте поле данных «Name» в бланк запроса. Для этого поля выберите из списка значение «Группировка».

Во второй столбец бланка запроса отбуксируйте поле данных «Kod_p». Для этого столбца также выберите значение «Группировка».

Последним в бланк запроса отбуксируйте поле «Cena_pr». Для этого поля выберите значение «Sum».

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

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

Выбор данной команды приводит к замене в бланке запроса строки «Вывод на экран» на новую строку «Перекрестная таблица». Все остальные параметры остаются без изменения. Поле «Kod_t» исходной таблицы будет использовано в качестве заголовка строк. Поэтому из поля списка «Перекрестная таблица» выберите элемент «Заголовки строк». Для столбца «Kod_p» выберите из списка «Перекрестная таблица» элемент «Заголовки столбцов».

Сумму цен товаров необходимо поместить в ячейках перекрестной таблицы. Поэтому для этого поля из списка «Перекрестная таблица» следует выбрать элемент «Значение».

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

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

Вариант «Повторяющиеся записи» чаще всего служит основой для запросов-действий, так как он без лишней волокиты позволяет выбрать из общего массива лишь те данные, одно или несколько полей которых повторяются. Например, поискать в таблице «Клиенты» не встречается ли одна и та же фирма несколько раз. При этом сначала нужно определить, какие записи считать дубликатами. В данном случае можно счесть записи с одинаковыми названиями и адресами.
2.5 Отбор записей главной таблицы не имеющих подчиненных записей

Вариант «Записи без подчиненных» в большинстве случаев применяется в таблицах со множеством связей. Этот мастер позволяет найти все записи в главной таблице, которые не имеют связанных записей в подчиненной. Предположим, что на каждого покупателя некоторый магазин заводит реестр покупок. Покупки могут быть разные. Они также могут по-разному оплачиваться (наличные, кредитная карточка или что-нибудь другое) и доставляться (самостоятельно или «с доставкой на дом»). Чтобы каждый раз не задавать старому клиенту массу излишних вопросов, удобно завести отдельную таблицу со всеми необходимыми данными, а в реестре покупок указывать только порядковый номер конкретного клиента в списке. Однако может так получиться, что какой-либо человек перестал пользоваться услугами данного магазина. Вскоре в системе учета возникнет своего рода дыра. Запись о клиенте есть, а упоминаний о его покупках нет. Следовательно, имеет смысл периодически просматривать систему и удалять из нее лишние записи ради экономии машинных ресурсов. Правда, вручную подобная задача далеко не всегда может быть решена успешно, так как человеку свойственно ошибаться. Поэтому куда удобнее, да и надежнее, воспользоваться специально сконструированным запросом, который просмотрит всю базу данных, сличит все записи о покупках с перечнем зарегистрированных покупателей и безошибочно выявит перечень тех, кто не объявлялся сверх положенного срока, а значит, записи о которых можно совершенно безболезненно из реестра удалить.

3. Сортировка записей таблицы

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


Рисунок 2.4 - Конструктор запроса


Для этого поля из списка «Сортировка» выберите способ сортировки. Запустите запрос на исполнение, и MS Access отобразит на экране результат запроса, отображаемый в режиме таблицы, данные в котором отсортированы требуемым образом. Например, результат запроса сортировки поставщиков по наименованию фирмы будет выглядеть так, как показано на рисунке 2.5.

Рисунок 2.5 – Результат сортировки по наименованию фирмы

5. Запросы с критериями поиска

Для выборки данных из таблиц по определенному условию в MS Access используются запросы с критериями поиска. Предварительно задав условие отбора, результатом выполнения запроса будет набор данных удовлетворяющих сформированному критерию. Откройте предварительно созданный простой запрос, содержащий все необходимые поля. В строке «Условие отбора» можно ввести теперь критерии выбора. Для обозначения произвольного количества символов используйте в качестве символа-заменителя звездочку «*», а для обозначения одного произвольного символа знак вопроса «?». Сформулируйте критерии запроса. Например, если для текстового поля будет задан только критерий "А*", то в результате выполнения запроса будут отображены только те записи, текстовое поле которых начинается с буквы «А» (или «а»).

Если требуется отыскать комбинацию нескольких значений, необходимо ввести их в строку «Условие отбора» на бланке запроса QBE, употребляя в качестве разделителя логические операторы AND и OR. Кроме обычных операторов сравнения, MS Access предоставляет три специальных оператора, полезных для отбора данных: BETWEEN, IN, LIKE. Например, если необходимо вывести фамилии продавцов, чей оклад находится в диапазоне от 5000 до 10000, то в строке «Условие отбора» введите критерий: >5000 And <10000

6. 
   Запросы с параметрами
   

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

Чтобы установить параметр необходимо ввести в строку «Условие отбора» имя или фразу, заключенную в квадратные скобки ([ ]). То, что заключено внутри квадратных скобок, MS Access рассматривает как имя параметра. Это имя выводится в диалоговом окне при выполнении запроса, поэтому разумно в качестве имени параметра использовать содержательную фразу. В одном запросе можно задать несколько параметров; при этом имя каждого параметра должно быть уникальным и информативным.

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

Рисунок 2.6 – Запрос с параметром

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

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

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

Выражение 1: [Поле1] “оператор” [Поле2]

В квадратные скобки заключаются только имена полей. MS Access автоматически использует «Выражение 1» в качестве имени вычисленного выражения. Это имя можно изменить на более содержательное.
7.1 Построитель выражений

При построении сложных выражений MS Access предлагает использовать утилиту, называемую «Построитель выражений». Для перехода в «Построитель выражений» необходимо щелкнуть по пустому полю в бланке QBE, а затем – по кнопке «Построить» панели инструментов или контекстного меню.

Построитель выражений позволяет строить сколько угодно сложные выражения, многие из которых иным путем вообще созданы быть не могут. Если необходимо вывести наименования фирм поставщиков товара, представители которых мужчины, вам надлежит отыскать в левом нижнем окне поле «Pol» (принцип поиска аналогичен поиску папки в программе «Проводник»), кликнуть по нему (в верхнем окне появится запись, состоящая из наименования таблицы и имени поля). Потом - нажать мышью на экранную кнопку со знаком равно, а потом опять отыскать в левом нижнем окне, в папке «Константы», значение «ложь» и перенести его в верхнее окно посредством экранной кнопки «ВСТАВИТЬ». Получится выражение [Поставщики]![Pol] = Ложь. Это означает, что запрос должен выбрать из таблицы «Поставщики» только те строки, ячейка «Pol» которых содержит значение «Ложь». Теперь нажмите «ОК», и построитель выражений исчезнет, а само выражение попадет в ячейку «Условие отбора» столбца «Pol». Не беда, что вы увидите лишь небольшую часть сгенерированной текстовой строки. На самом деле она там присутствует полностью, лишь недостаточная ширина столбца не позволяет увидеть ее целиком. Если необходимо измените ширину столбца.
Функции Access SQL, используемые для выполнения расчетов

Функция	Типы полей	Описание
Статистические функции
Avg()	Все типы полей, исключая текстовый, поле MEMO, поле объекта OLE	Вычисляет арифметическое среднее набора чисел, содержащихся в указанном поле запроса
Count()	Все типы полей	Возвращает количество непустых значений, используемых запросом
First()	Все типы полей	Возвращает значение поля из первой записи результирующего набора
Last()	Все типы полей	Возвращает значение поля из последней записи результирующего набора
Max()	Все типы полей, исключая текстовый, поле MEMO, поле объекта OLE	Возвращает максимальное значение из набора, содержащегося в указанном поле
Min()	Все типы полей, исключая текстовый, поле MEMO, поле объекта OLE	Возвращает минимальное значение из набора, содержащегося в указанном поле
StDev() StDevP()	Все типы полей, исключая текстовый, поле MEMO, поле объекта OLE	Возвращает смещенное и несмещенное значение среднеквадратического отклонения, вычисляемого по набору значений, содержащегося в указанном поле
Sum()	Все типы полей, исключая текстовый, поле MEMO, поле объекта OLE	Возвращает сумму набора значений, содержащихся в указанном поле
Var() VarP()	Все типы полей, исключая текстовый, поле MEMO, поле объекта OLE	Возвращает смещенное и несмещенное значение дисперсии отклонения, вычисляемого по набору значений, содержащегося в указанном поле

Функции даты и времени
Data()	Определяет системную дату компьютера
Year(параметр)	Возвращает числовое значение года указанной в качестве параметра даты

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

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

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

8. Многотабличные запросы

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

Во-первых, откройте окно «Конструктора» запроса и добавьте таблицы, в которых содержится необходимая информация. Если ранее с помощью команды «Связи» была установлена связь между таблицами, то MS Access определит это автоматически. Эта связь будет отображена в виде линии, проведенной между полями таблиц. Если связь между таблицами отсутствует, вы должны ее установить.

Далее необходимо определить параметры связи. Щелкните два раза на соединительную линию. В результате откроется диалоговое окно, в котором предлагаются три опции для определения параметров связи (рисунок 2.7).

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

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


9. Запросы на создание таблицы

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

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


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

Для поля с типом данных «Счетчик» выполните одно из следующих действий:

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

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

10. Запросы на добавление записей

Запрос на добавление добавляет группу записей из одной или нескольких таблиц в конец одной или нескольких таблиц. Например, появилось несколько новых клиентов, а также база данных, содержащая сведения о них. Чтобы не вводить все данные вручную, их можно добавить в таблицу «Клиенты». Проектирование запроса на добавление записей в таблицу аналогично построению запроса на создание новой таблицы. Отличие состоит в том, что необходимо выбрать команду «Добавление…».
11. Запросы на удаление записей

С помощью запроса на удаление осуществляется удаление определенных данных из таблиц.

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

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

Для этого в строке меню выберите команду «Запрос»«Удаление». После этого MS Access выключает в бланке запроса строки «Сортировка» и «Вывод на экран». Для запросов на удаление эти строки не нужны. Кроме того, для всех колонок бланка запроса в новой строке «Удаление» отображается текст «Условие». Однако критерии при этом не изменяются.

Если выполнить запрос выбором пиктограммы «Выполнить», MS Access не отобразит на экране результат выполнения запроса. Вместо него появится сообщение о том, сколько записей будет удалено в исходной таблице. Вы можете прервать процесс удаления нажатием кнопки «Отмена». Однако нажатие «OK» приводит к безвозвратному удалению записей, удовлетворяющих критериям запроса.

12. Запросы-обновления записей таблицы

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

Сначала создайте новый запрос выбора и введите критерии для выбора изменяемых записей. Только в том случае, если результат запроса отвечает заданным требованиям, следует преобразовать его в запрос замены. Для этого выберите команду «Запрос»«Обновление». При этом будут выключены строки «Вывод на экран» и «Сортировка» в бланке запроса. Обе эти строки не нужны для запроса замены. Вместо них включается новая строка «Обновление». В эту строку следует ввести новые выражения для заменяемых записей. Обратите внимание, что тип данных выражения должен совпадать с типом данных поля исходной таблицы. Например, если вы хотите обновить записи поля «Summa» таблицы «Товары», значения которых вычислены и находятся в таблице «Сумма», то сформируйте запрос на обновление, как показано на рисунке 2.9.

Рисунок 2.9

ЗАДАНИЯ
1. Создайте запрос на выборку, в результате выполнения которого можно отобразить список всех продавцов с окладом в заданном диапазоне с переменными границами. Запрос назовите «01_Диапазон окладов».

2. Выберите всех поставщиков, создав запрос «02_Города поставщиков», проживающих в определенном городе, название которого не известно заранее.

3. Отсортируйте список товаров сначала по наименованию товара, а затем по цене продажи, сконструировав запрос «03_Сортировка товаров».

4.1 Создайте запрос на создание таблицы «Сумма», где, применив механизм группировки, вычислите значения суммы заказанных товаров по каждому заказу. Воспользуйтесь при этом только полем «Kod» таблицы «Заказы», и новым полем «Итого». Итоговые значения определяются как произведение значений полей «Kol» таблицы «Заказано» и полей «Cena_pr» таблицы «Товары». Назовите запрос «04\1_Сумма заказанных товаров»

4.2 Сконструируйте запрос на обновление поля «Summa» таблицы «Заказы», куда поместите вычисленные значения сумм по каждому заказу. Назовите его «04\2_Обновление сумм заказов»

5. Создайте запрос-выборку товаров, имеющихся на складе, причем поле «Nal» не должно отражаться на экране. Назовите его «05_Наличие товаров».

6. Создайте запрос-выборку товаров «06_Тип-склад» определенного типа в определенной упаковке имеющихся на складе.

7. Сконструируйте перекрестный запрос, заголовками строк которого являются наименования товаров и фирм, заголовками столбцов - вид фасовки товаров, подведите итоги, вычислив минимальное значение цены покупки товара (поле «Cena_p»).

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

Этап 1. Подсчитать сумму оклада в зависимости от категории и стажа;

Этап 2. Подсчитать сумму начислений и сумму вычетов в данном конкретном месяце;

Этап 3. Подсчитать пенсионные отчисления;

вычислить сумму подоходного налога в зависимости от начисленной суммы, вычитая пенсионные отчисления;

Этап 4. Подсчитать сумму выдачи.
ЭТАП 1.

8 Чтобы правильно подсчитать значение оклада сотрудника сначала необходимо определить стаж работы сотрудника, для чего создайте запрос «08_Стаж продавцов» на добавление таблицы «Определение стажа». В формируемую таблицу добавьте поле «Kod» таблицы «Продавцы» и создайте новое поле «Стаж», в котором вычислите стаж сотрудника как разность между текущим годом и годом поступления на работу, используя функции Data() и Year().

9 .1 Создайте таблицу «Показатели», в которой одно единственное поле «MRP» со значением 10930. Для подсчета оклада создайте запрос на обновление поля «Oklad» таблицы «Продавцы». В запрос включите таблицы
Рисунок 2.11
«Определение стажа», «Продавцы», «Константы», «Показатели». Оклад вычисляется как произведение значений поля «Const» таблицы «Константы» на значения поля «MRP» таблицы «Показатели», то есть [Константы]![const]*[Показатели]![MRP]. Поскольку при определении константы необходимо учитывать, стаж и категорию сотрудника, то вторым полем проектируемого запроса будет поле «Стаж» таблицы «Определение стажа», а третьим поле «Cat» (категория) таблицы «Константы». Для второго и третьего полей укажите условия отбора: стаж, вычисленный в таблице «Определение стажа», должен входить в диапазон пороговых значений стажа таблицы «Константы», категория таблицы «Константы» должна совпадать с категорией таблицы «Продавцы». Запрос назовите «09\1_Подсчёт оклада».

9.2 Создайте запрос на добавление поля «Oklad» из таблицы «Продавцы» в таблицу «Начисления».
ЭТАП 2.

10 Так как на начало января суммы начислений и вычетов равны нулю, то в пустую таблицу нужно добавить записи, содержащие порядковые номера продавцов. Для этого создайте запрос на добавление кодов сотрудников из таблицы «Продавцы» в таблицу «10_Месяц_расчет».

11 Промежуточная таблица «Месяц_расчет» не должна содержать данных и перед каждым начислением зарплаты ее необходимо очистить от данных. Для этого воспользуйтесь запросом на удаление, который назовите «11_Очистка таблицы расчета».

12.1 Подсчитаем сумму премии продавцам, которая определяется как 1% от продаж, если сумма продаж за месяц больше 7000. Для этого разработайте запрос на добавление новой таблицы «Сумма1», при помощи которого определите сумму заказанных за конкретный месяц товаров. Назовите запрос «12\1_Запись суммы по месяцам». Бланк запроса представлен на рисунке 2.11.

12.2 Чтобы определить численное значение суммы премии, создайте запрос на обновление «12\2_Запись премии по месяцам». В бланк запроса включите таблицы «Начисления» и «Сумма1». Первым полем этого запроса сделайте обновляемое поле «Prem» таблицы «Начисления», где в строке «Обновление» запишите с помощью построителя формулу вычисления премии: итоговое значение суммы проданных за месяц товаров, вычисленное в таблице «Сумма1», умножается на коэффициент 0,01. Во втором поле запроса выберите только те записи поля «Итого» таблицы «Сумма1», значения которых больше 7000. Бланк запроса представлен на рисунке 2.12.

Рисунок 2.12
Теперь необходимо определить начисленную заработную плату за конкретный месяц. Для этого нужно подсчитать для каждого сотрудника суммы начислений и вычетов и добавить эти данные в таблицу «Месяц_расчет».

12.3 Создайте запрос на добавление, содержащий четыре поля: «Kod_prod», «Mes» (используется таблица «Начисления»), «Всего начислено», «Всего вычеты» (формируются с помощью построителя). Для того, чтобы запрос был универсальным и мог использоваться ежемесячно в поле «Mes» введите параметр, запрашивающий, за какой месяц производить расчет. Назовите запрос «12\3_всего начисления и вычеты»


ЭТАП 3.

13 Создайте запрос на обновление поля «Pens» в таблице «Месяц_расчет», который вычисляется как 10 процентов от оклада. (см. Рисунок 2.13) Назовите его «13_Обновление пенсионных».

Рисунок 2.13

ЭТАП 4.

14 Создайте запрос на обновление «14_Подоходный налог», обновить поле «Pod_nal» в таблице «Месяц_расчёт», вычисляемый из общей суммы начисления, вычитая пенсионный и МРП/10, находим 10 процентов от этой суммы. (см. Рисунок 2.14)

Рисунок 2.14
ЭТАП 5.

1 5 Создайте запрос на обновление поля «Itog» в таблице «Месяц_расчёт», с учетом вычетов: Всего вычтено, пенсионные отчисления, подоходный налог. Запрос назовите «15_К выдаче».
Контрольные вопросы
1. Что представляет собой запрос? Какие виды запросов существуют?

2. Чем отличаются между собой процессы создания запросов с помощью «Мастера запросов» и «Конструктора»?

3. Что такое запрос с параметром? Как он создается?

4. Как разработать запрос на создание таблицы? Чем результат этого запроса отличается от результата запросаөвыборки?

5. Как создать запрос на удаление?

6. Какие особенности имеет перекрестный запрос? Как его разработать?

7. Что происходит в результате выполнения запроса на обновление? Как его создать?

8. Для чего служит «Построитель выражений»?

9. Чем отличаются итоговые запросы от обычных?