Изменение данных с помощью модифицирующих запросов в MS Access XP. Создание запросов на изменение

ОГЛАВЛЕНИЕ

ЗАДАНИЕ 4.    ИЗМЕНЕНИЕ ДАННЫХ С ПОМОЩЬЮ МОДИФИЦИРУЮЩИХ ЗАПРОСОВ В  MS ACCESS XP. СОЗДАНИЕ ЗАПРОСОВ НА ИЗМЕНЕНИЕ.. 2

1. СОЗДАНИЕ ЗАПРОСОВ НА ОБНОВЛЕНИЕ ДЛЯ ИЗМЕНЕНИЯ ЗНАЧЕНИЙ.. 2

2. ЗАПРОС НА СОЗДАНИЕ ТАБЛИЦЫ.. 4

3. ЗАПРОСЫ УДАЛЕНИЯ.. 6

4. ПОИСК В ТАБЛИЦАХ ЗАПИСЕЙ С ПОМОЩЬЮ МАСТЕРА ЗАПРОСОВ.. 8

4.1. Перекрестный запрос. 9

4.2. Запрос на поиск повторяющихся записей. 12

4.3. Поиск в таблицах записей, не имеющих подчиненных. 14

5. ВОПРОСЫ ДЛЯ САМОПОДГОТОВКИ.. 16

6. ИТОГОВЫЕ ЗАДАНИЯ ПО РАЗДЕЛУ.. 16

ЗАДАНИЕ 4.    ИЗМЕНЕНИЕ ДАННЫХ С ПОМОЩЬЮ МОДИФИЦИРУЮЩИХ ЗАПРОСОВ В  MS ACCESS XP. СОЗДАНИЕ ЗАПРОСОВ НА ИЗМЕНЕНИЕ

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

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

·  Обновление информации в группе записей.

·  Создание таблицы.

·  Удаление записей из таблицы (или группы таблиц).

·  Добавление записей из одной таблицы в другую.

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

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

·  Сохраните в архиве таблицы, в которые вы намерены внести изменения.

·  Создайте запрос на изменение, используя необходимые поля и критерии.

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

·  Запустите запрос на изменение, используя кнопку «Запуск»  на панели инструментов

·  Проверьте внесенные изменения.

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

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

1. СОЗДАНИЕ ЗАПРОСОВ НА ОБНОВЛЕНИЕ ДЛЯ ИЗМЕНЕНИЯ ЗНАЧЕНИЙ

Рассмотрим действие запроса на изменение на примере изменения величины минимальной заработной платы в таблице «Справка». На момент написания данного пособия (начало 2003 года) величина минимальной заработной платы составляла 450 рублей. Предположим, что наши законодатели увеличили ее , до 650 рублей.

F Задание.

Замените 100 руб. на 650 во всех записях таблицы «Справка» в поле «MINX».

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

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

·  Вновь вернитесь в режим Конструктора запросов

·  Выделите команду Запрос Þ Обновление.

·  В поле «Обновление» столбца  «MINX» введите новое значение:  просто напишите число 650

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

·  Выполните команду Запрос Þ Запуск или щелкните на кнопке выполнения запроса «Запуск» .  

·  Появится окно с сообщением о количестве заменяемых записей.

·  Щелкните по кнопке «Да». 

В результате выполнения запроса на обновление была выполнена операция изменения размера минимальной заработной платы со 450 до 650 рублей.

ВНИМАНИЕ! К сожалению, ваша радость по увеличению минимального уровня зарплаты оказалась напрасной.

Вы не заметили, что это был только проект закона.

·  Поэтому вновь верните размер поля «MINX»таблицы «Справка»  прежнее значение 450 рублей. Не забудьте проверить саму таблицу «Справка». 

·  Сохраните запрос под именем «Запрос на обновление MINX».

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

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

2. ЗАПРОС НА СОЗДАНИЕ ТАБЛИЦЫ

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

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

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

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

F Задание.

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

·  Откройте вкладку Запросыдиалогового окна Базы данных.

·  Щелкните на кнопке «Создание запроса в режиме конструктора».

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

·  Выберите из таблиц необходимые поля и перенесите их в бланк запроса. Напоминаем, какие поля использовались при расчете заработной платы в предыдущем разделе курса (Задание 3. п. 5):

«KOEFF», «MINX», «DOPL_R» и «RKF» должны быть взяты из таблицы «Справка», а величины «Доплата»), «Вредность», «Классность», «Стаж» и «Доля ставки»,   должны быть взяты из таблицы «Сотрудники». Кроме того, в таблицу необходимо включить поля «Подразделение», «Фамилия» и «Имя» и «Должность».

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

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

·  В открывшемся диалоговом окне введите имя новой таблицы «Зарплата».

·   Выберите базу данных, куда будет помещена новая таблица - текущая

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

·  Щелкните на кнопке «ОК»

·  Перед тем как дать команду на выполнение запроса, целесообразно просмотреть таблицу в режиме Таблицы.

Если вид таблицы и помещенная в нее информация вас удовлетворяет, вернитесь в режим Конструктора запросов (кнопка ) и  нажмите кнопку «Запуск» , или выполните команду  Запрос ð Запуск.

·  Подтвердите создание новой таблицы, щелкнув на кнопке «Да».

В том случае, если вид будущей таблицы вас не устраивает, перейдите в режим Конструктора запросов  и отредактируйте его. Например, можно ввести в запрос поле, содержащее выражение для вычисления величины заработной платы (плановой заработной платы):

Зарплата:((«KOEFF» * «MINX» + «DOPL_R» + «Доплата») + «KOEFF» * «MINX» * (1 + «Вредность» + «Классность»)) * (1 + «Стаж» + «RKF»)* «Доля ставки»

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

3. ЗАПРОСЫ УДАЛЕНИЯ

Если вам необходимо удалить несколько записей в таблице, достаточно выделить их и нажать клавишу «Del». А что делать, если необходимо удалить несколько сотен (тысяч) записей. Такая необходимость может возникнуть при переносе информации за истекший период (квартал, год) в таблицы архива.

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

F Задание.

Вы решили поместить в архив из таблицы «Заказы» все записи о заказах, которые были выполнены  до 31 декабря 2002 года.

·  Основную таблицу – не трогайте. Поэтому, создайте резервную копию таблицы «Заказы», записи из которой вы хотите поместить в архивную таблицу. Назовите ее «Заказы – резервная». Позднее вы ее удалите.

·  Перейдите на вкладку «Запросы» и откройте окно «Конструктора запросов».

·  В окне диалога «Добавление таблицы» дважды щелкните кнопкой мыши на таблице «Заказы – резервная» для добавления ее в окно запроса. Нажмите кнопку «Закрыть»

·  Выберите команду Запрос Þ Удаление либо щелкните на кнопке «Удаление» .

·  Дважды щелкните на звездочке, которая находится в верхней части списка полей таблицы «Заказы – резервная», для перенесения в всех полей таблицы в бланк запроса.

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

·  В строке условие отбора поля «Дата исполнения» бланка запросов введите выражение «<=31.12.02» для отбора записей, подлежащих удалению.

·  Перейдите в режим таблицы для просмотра записей, которые будут удалены из таблицы «Заказы – резервная» после исполнения запроса.

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

·  Access сообщит вам сколько строк (записей) будет удалено в результате выполнения запроса.

·  Нажмите кнопку «ДА» для удаления записей, или кнопку «НЕТ» – для отказа от выполнения запроса.

·  Если вы в будущем хотите использовать этот запрос для удаления записей (естественно за другой период) сохраните его под именем «Запрос на удаление».

G Внимание.

Удаление записей иногда может привести к непредсказуемым результатам, и, прежде всего к нарушению целостности базы данных, так как:

·  Таблица может являться частью отношения «Один – ко – многим».

·  В качестве условия обеспечения целостности данных была использована опция «Каскадное удаление связанных записей».

Допустим, вы решили прекратить поставку товаров, определенным клиентам (например, из одной из стран СНГ) и, соответственно, удалить эти записи из таблицы «Клиенты». Однако, как вы помните, между таблицами «Клиенты» и «Заказы» была установлена связь типа «Один – ко – многим». Если для обеспечения целостности данных была выбрана опция «Каскадное обновление связанных записей», то в результате удаления записей из таблицы «Клиенты» будут удалены и соответствующие записи из таблицы «Заказы». То есть, информация о выполненных и не завершенных сделках будет безвозвратно утеряна!

4. ПОИСК В ТАБЛИЦАХ ЗАПИСЕЙ С ПОМОЩЬЮ МАСТЕРА ЗАПРОСОВ

Access имеет еще один инструмент работы с запросами – Мастер запросов. Он вызывается щелчком мыши на кнопке «Создать»  окна Базы данных. Появится окно «Новый запрос». С его помощью можно запустить

создание:

·  Запроса с помощью Конструктора запросов.

·  Простого запроса.

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

·  Поиска повторяющихся записей в простой таблице

или запросе.

·  Поиска записей без подчиненных – записей, которым не соответствует ни одна запись в подчиненной таблице.

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

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

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

G Внимание.

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

Перекрестный запрос - это специальный тип группового запроса, для которого строка «Групповая операция» всегда включена.

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

F Задание.

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

Для построения перекрестного запроса:

·  В окне базы данных откройте окно  «Новый запрос»".

·  Выберите в списке «Перекрестный запрос».

·  Щелкните на кнопке "ОК".

·  В открывшемся диалоговом окне выберите необходимую таблицу (или  ранее созданный запрос). В вашем случае – это может быть запрос «Начислено зарплаты Запрос».

·  Щелкните на кнопке «Далее».

·  На втором шаге Мастера перекрестных запросов выберите имя поля, помещаемого в строку запроса -  «Подразделение»

·   Щелкните на кнопке «Далее».

·  На третьем шаге выберите имя поля, которое должно быть использовано в качестве заголовка столбца - «Фамилия».

·  На четвертом шаге Мастер перекрестных запросов предлагает выбрать поле, по которому будут подводиться итоги и тип итогов (итоговая сумма, среднее и др.). В нашем примере, в качестве поля для подведения итогов выберем поле «Зарплата» и в качестве вида (типа) итогов – оператор Сумма().

Не забудьте включить флажок «Вычислить итоговое значение для каждой строки».

·  На следующем шаге Мастер перекрестных запросов предлагает задать имя созданного перекрестного запроса   (или принять то, которое он формирует по умолчанию, используя для этого имя таблицы или запроса). Согласитесь с последним – «Расчет зарплаты Запрос_перекрестный».

·  Если все действия выполнены правильно – щелкните на кнопке «Готово». В противном случае, щелкните на кнопке «Назад» и внесите необходимые изменения.

Фрагмент получившейся таблицы представлен ниже:

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

F Задание.

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

·  Откройте перекрестный запрос «Расчет зарплаты Запрос_перекрестный» в режиме Конструктора запросов.

·  Добавьте в него таблицу «Сотрудники».

·  Путем перетаскивания свяжите поле «Фамилия» запроса «Расчет зарплаты Запрос» с полем «Фамилия» таблицы «Сотрудники». Поля будут связаны линией.

·  Удалите поле «Подразделение» из нижней части Конструктора запросов.

·  Вместо него вставьте поле «Должность» из таблицы «Сотрудники».

·  В строке «Перекрестная таблица» столбца «Должность» выберите из списка «Заголовки строк».

Окно Конструктора запросов, вызванное для редактирования перекрестного запроса примет следующий вид:

·  Запустите запрос на выполнение.

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

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

4.2. Запрос на поиск повторяющихся записей

F Задание.

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

Для построения запроса для поиска повторяющихся записей:

·  В окне базы данных откройте окно  «Новый запрос»".

·  Выберите в списке «Повторяющиеся записи».

·  Щелкните на кнопке "ОК".

·  В открывшемся диалоговом окне выберите необходимую таблицу (или  ранее созданный запрос). В вашем случае – это таблица  «Заказано».

·  Щелкните на кнопке «Далее».

·  На втором шаге выберите имя поля с повторяющимися значениями - «КодТовара»

·   Щелкните на кнопке «Далее».

·  На третьем шаге выберите имя поля, имеющее числовое значение - «Количество».

·  Щелкните на кнопке «Далее».

·  На следующем шаге Мастер перекрестных запросов предлагает задать имя созданного перекрестного запроса   (или принять то, которое он формирует по умолчанию, используя для этого имя таблицы или запроса). Согласитесь с последним – «Поиск повторении для Заказано».

·  Если все действия выполнены правильно – щелкните на кнопке «Готово». В противном случае, щелкните на кнопке «Назад» и внесите необходимые изменения.

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

·  Вернитесь в режим Конструктора запросов» и выполните операцию «Группировки» для поля «КодыТоваров»  и операцию «Суммирования» для поля «Количество».

Результаты выполнения этих операций представлены ниже.   

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

4.3. Поиск в таблицах записей, не имеющих подчиненных

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

F Задание.

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

Для выполнения такого запроса:

·  В окне базы данных откройте окно  «Новый запрос»".

·  Выберите в списке «Записи без подчиненных».

·  Щелкните на кнопке "ОК".

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

Щелкните на кнопке «Далее».

·  На следующем шаге  выберите таблицу, содержащую подчиненные записи - «Заказы» и щелкните на кнопке «Далее».

·  На третьем шаге выберите поля, которые содержаться в обеих таблицах -  «Код сотрудника», щелкните на кнопке «Соответствие»  

·  Щелкните на кнопке «Далее».

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

·  На следующем шаге Мастер запросов предлагает задать имя созданного запроса (или принять то, которое он формирует по умолчанию). Согласитесь с последним – «”Сотрудники” без подчиненных в “Заказы”: запрос на выборку».

·  Если все действия выполнены правильно – щелкните на кнопке «Готово». В противном случае, щелкните на кнопке «Назад» и внесите необходимые изменения.

Получившаяся таблица представлена ниже:

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

5. ВОПРОСЫ ДЛЯ САМОПОДГОТОВКИ

1.  Что позволяет делать запрос на выборку?

2.  Для каких целей могут быть использованы запросы на изменение?

3.  Возможно ли, после выполнения запроса на обновление отменить внесенные изменения?

4.  Назовите команду, которая должна быть выполнена для выполнения обновления записей в таблице запроса?

5.  Какую команду дублирует кнопка ?

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

7.  Какую команду дублирует кнопка ?

8.  Для чего предназначена кнопка ? Для, какого типа запроса она может быть использована?

9.  Как найти записи, не имеющие подчиненных записей в другой таблице?

10.. Что такое перекрестный запрос?

11.Как выглядит таблица перекрестного запроса?

6. ИТОГОВЫЕ ЗАДАНИЯ ПО РАЗДЕЛУ

(выполняются по собственной инициативе или выполняются по указанию преподавателя)

1.  Создайте запрос и произведите снижение на 3% цены товаров, находящихся на складе более одного месяца с помощью запроса на изменение. Запрос сохраните под именем «Обновление цены товара, пролежавшего более одного месяца».

2.  Сделайте запрос и уменьшите на 25% стоимость товаров количество заказов на которые за год составило менее 5% от их количества на складе (в натуральном выражении).   Запросы 6_2 и 6_21. месяца с помощью запроса на изменение

3.  Создайте запрос, который позволит выяснить, кто из клиентов не приобретал товаров в вашей фирме с помощью запроса записей, не имеющих подчинения. Сохраните предложенное имя запроса «‘Клиенты’ без подчиненных в ‘Заказы’».

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

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

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

7.  Модифицируйте предыдущий запрос и выясните, с какими клиентами работали эти сотрудники. Запрос сохраните под именем «Работа сотрудников с одинаковыми разрядами с клиентами»

8.  Увеличьте заработную плату сотрудников в запросе «Расчет зарплаты Запрос» на 5% объема сделанных им продаж  с помощью запроса на обновление. Запрос сохраните под именем «Увеличение зарплаты сотрудников на 5%».

9.  В связи с сезонным подорожанием, цена на сыры всех сортов возросла на 10%. Выполните запрос на обновление и измените цены на товары.

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

11.Создайте перекрестный запрос и выясните, какое количество товара определенного вида (поле «Код товара») было продано в соответствии с заказами клиентов (поле «Код заказа»).

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