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

Создание таблиц Давайте создадим табличку (будете, например, расписание уроков детям писать, или распорядок дня, или…).Чтобы не запутаться, создадим новый файл. Помните как? Открываем Word, видим Документ 1 (если программа открыта, то Файл? Открыть), сохраняем новый документ

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

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

После включения в бланк запроса полей выбранной таблицы выполняется команда Запрос ® Обновление , после чего в нижней части бланка строка Сортировка заменяется строкой Обновление . В эту строку в ячейку поля, значения которого необходимо изменить, вводится выражение. Например, в ячейку поля Зарплата будет введено выражение 1,5* [оклад ] – 0,13*1,5* [оклад ] , из которого видно, что зарплата каждого сотрудника в 1,5 раза больше его оклада минус 13% вычетов. Наименования полей, значения которых будут использованы при вычислении введенного выражения, должны быть заключены в квадратные скобки.

С помощью запроса на обновление можно изменять данные не всех записей, а выборочно. Для этого в бланк запроса включаются поля, по значениям которых будет вестись отбор записей. Например, в таблице Сотрудник необходимо увеличить оклады на 25% только инженерам. С этой целью в ячейку столбца Оклад вводится выражение 1,25* [Оклад ] , а в ячейку, находящуюся в строке Условие на выборку и в столбце поля Должность , записывается критерий поиска инженер .

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

При запуске запроса на обновление (щелчок на кнопке Запуск ! ) или его открытии в окне базы данных Access предупредит, что будут обновлены значения такого-то количества записей.

Для просмотра результатов расчета после выполнения запроса необходимо в окне базы данных щелкнуть на значке объекта Таблицы , выделить имя таблицы (в данном случае таблицы Сотрудник ) и открыть ее щелчком на кнопке Открыть .

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

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

После включения требуемых полей в запрос необходимо щелкнуть на кнопке S (Групповые операции ), расположенной на инструментальной панели, после чего появится строка Групповые операции . В каждой ячейке этой строки отобразится слово Группировка . Для полей Подразделение и Должность значение Группировка в строке Групповая операция должно быть оставлено. Щелчок на ячейках остальных полей со значением Группировка вызывает появление кнопки выбора данных из списка, из которого выбирается требуемая операция: для поля Должность – Count (количество), для поля Оклад – Sum (сумма), для поля Зарплата – Avg (среднее значение). Кроме указанных функций, также часто используются функции Max (максимальное значение) и Min (минимальное значение). Для полей Подразделение и Должность целесообразно включить сортировку по возрастанию.

Редактирование запросов

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

Чтобы удалить поле, необходимо щёлкнуть на любой ячейке этого поля, а затем выполнить команду Правка ® Удалить .

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

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

Формы

Создание форм

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

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

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

Автоформы бывают трех видов: «в столбец» ленточные и табличные. Табличные формы имеют вид таблиц Автоформа «в столбец» может отображать только одну запись.

Для создания автоформы в окне базы данных надо щелкнуть на значке элемента Формы и затем щелкнуть на кнопке Создать . В диалоговом окне Новая форма выбирается вид автоформы и источник данных: таблица или запрос. После щелчка на кнопке OK автоформа выводится на экран.

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

Создавая форму с помощью Мастера, можно выбрать внешний вид формы: в один столбец (на экране отображается только одна запись), ленточный или табличный. Можно также задать требуемый стиль и имя формы.

Российский университет кооперации

Кафедра информационных технологий

Дисциплина «Информатика»

Подготовила доцент Гудзенчук З.В.

Тема . СУБД Access -запросы на обновление

Общие сведения о запросах

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

В Access запросы можно создавать в двух режимах:

QBE (Query By Example) - запросы по образцу, параметры которых устанавливаются в окне конструктора запросов - бланке запроса;

SQL (Structured Query Language) - структурированный язык запросов, при создании которых применяются операторы и функции языка SQL.

В Access легко переключить запрос из режимаQBE в режимSQL - запроса:

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

Типы запросов

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

Таблица 1

Тип запроса на изменение

Запрос на изменения - это запрос, который позволяет, выполнив одну операцию, внести изменения во многие записи. Существует четыре типа запросов на изменение:

создание таблицы

удаление записей

добавление записей

обновление записей.

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

Ms Access поддерживает два режима блокировок.

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

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

Для задания уровня блокировки данных в Ms Access 2007 следует:

В разделе Дополнительно установлены параметры блокировок Access (рис.6).

Рис. 6 Раздел окна для управления блокировками

Проблема при выполнении запросов

Поскольку в Access по умолчанию включен флажок ,

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

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

Появится окно Параметры безопасности Microsoft Office (рис.7).

Рис.7 Окно оповещения системы безопасности

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

На сумму недоимки коммунальных платежей должны начисляться пени в размере 1/300 ставки рефинансирования Банка России, действующей на день оплаты, за каждый день просрочки уплаты.

Указание Банка России от 31.05.2010 № 2450-У

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

Пени = Сумма*Недоплата*8.75/300

Задание 1. Создать запрос к таблице ОплатаКоммУслуг на обновление значений поля Пени .

Действия

Появится окно

Список полей таблицы ОплатаКоммУслуг появится верхней половине окна Конструктора.

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

Нижняя половина окна бланка запроса первоначально имеет вид (рис.8).

Рис. 8 Нижняя половина окна Конструктора для создания конкретного типа запроса

По умолчанию в бланке запроса содержится 6 строк (количество строк может меняться в зависимости от типа запроса).

В первом столбце нижней половины окна Конструктора выполнить действия для создания запроса на обновление поля Пени (рис.9).

Рис. 9 Окно построителя выражений

Появится окно сообщения (рис.10).

Рис.10 Окно - предупреждение об изменении данных

Рис.11 Окно подтверждения обновления записей

Просмотр результатов выполнения запроса можно, открыв запрос в режиме Таблицы или, открыв таблицу ОплатаКоммУслуг в режиме таблицы.

Задание 2 . Создать запрос к таблице ОплатаКоммУслуг на обновление значений поля СуммаСучетомПени.

Действия

Выполнение запроса на обновление записей столбца СуммаСучетомПени осуществляется аналогично предыдущему заданию, повторением шагов предыдущего задания с 2-го пункта по пункт 5.4.

Затем следует создать выражение для вычисления значений поля СуммаСучетомПени (рис. 12).

Рис. 12 Вид выражения для изменения значений для столбца СумаСучетоПени

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

Рис. 13 Вид результирующей таблицы после выполнения двух запросов на обновление записей

Примечание

Рис. Фрагмент формы с всплывающей подсказкой для кнопки Обновление

На рисунке 16 представлено обновление трех поле при изменении данных в записях с номерами 1,7,12,22,23.

Рис.16 Таблица ОплатаКоммУслуг после выполнения запроса на обновление трех столбцов

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

1. В окне базы данных Access щелкните на кнопке Таблицы .

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

3. В палитре кнопки Новый объект выберите пункт Запрос . Откроется окно диалога Новый запрос , показанное на рис. 17.8. Подобное окно открывается и при щелчке на кнопке Создать окна базы данных. Оно позволяет выбрать наиболее удобный способ создания объекта.

Рис. 17.8. Создание запроса

4. Дважды щелкните на строке Конструктор . Таблица Список, выделенная в окне базы данных на шаге 2, автоматически появится в окне конструктора запросов.

5. Перетащите в бланк запроса поле Дата , значение которого нужно обновлять.

Примечание Название поля, заключенное в квадратные скобки, является ссылкой на значение поля. Чтобы сослаться на поле другой таблицы, нужно в начале указать имя таблицы, а затем имя поля (оба имени в квадратных скобках) и разделить их восклицательным знаком. Например [Контакты]! [Фамилия].

6. В палитре кнопки Тип запроса выберите пункт Обновление . Описание всех возможных вариантов запросов, предлагаемых программой Access, приведено в табл. 17.2. Структура бланка запроса видоизменяется в соответствии с типом запроса. В бланке выбранного варианта запроса на обновление появляется поле Обновление , в которое нужно ввести новое значение поля. Чтобы изменить месяц даты с ноября на декабрь, достаточно прибавить к дате 30 дней.

7. Введите в ячейку Обновление формулу [Дата] +30.

ТАБЛИЦА 17.2. Варианты запросов

8. Чтобы обновлялись только даты, относящиеся к ноябрю 2000 года, введите в ячейку Условие отбора формулу Between DateValue ("1.11.99") And DateValue ("30.11.99"), которая подробно обсуждалась в ранее (рис. 17.12).

9. Закройте запрос, сохранив его под именем Обновление.

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

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

11. В окне базы данных щелкните на кнопке Запросы .

12. Дважды щелкните на значке Обновление .

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

13. Щелкните на кнопке Да и изучите изменения, произошедшие с данными таблицы Список.