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

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

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

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

Решение. 1-ый этап. Открыть в Конструкторе таблицу Студент и добавить поле Состояние, выбрать тип поля Текстовый, задать размер поля равный 1, по строке Условия на значения ввести: «» or «а» or «о», по строке «Пустые строки» установить значение Да для поля Состояние.

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

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

2-ой этап. В начале года студент возвращается из академического отпуска, поэтому надо выполнить запрос на обновление, удалив признак «а»: Вкладка Создание – группа Другие – кнопка Конструктор запроса – добавить таблицу Студент – перенести только поле Состояние – по строке Условие отбора ввести «а» - в группе Тип запроса выбрать Тип запроса: обновление. В конструкторе запроса появится новая строка Обновление. Для поля Состояние по этой строке надо указать пустую строку, введя: «». Выполнить запрос – сохранить запрос под именем зобновл. Просмотреть таблицу Студент.

Пример. Таблица Студент содержит записи об отчисленных студентах. Их время от времени надо удалять из этой таблицы и хранить (добавлять) в другой таблице, например, тСтудентОтчисл.

Решение. 1-ый этап. Создать таблицу тСтудентОтчисл.

Эта таблица должна иметь ту же структуру, что и таблица Студент. Поэтому легче создать таблицу тСтудентОтчисл методом копирования:

· В области переходов открыть вкладку Таблицы и установить курсор на таблицу Студент – щелкнуть правой кнопкой мыши, выбрать команду Копировать

· выполнить команду Вставить (курсор на том же месте)

· в открывшемся окне Вставка таблицы ввести имя таблицы тСтудентОтчисл, установить флажок (.) только структура.

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

2-ой этап. Создать запрос. Вкладка Создание – группа Другие – кнопка Конструктор запроса – добавить таблицу Студент – перенести все поля (поочередно, а не кнопкой *, находящейся в макете таблицы) – в группе Тип запроса выбрать Тип запроса: Добавление – выбрать таблицу тСдудентОтчисл («приемник») – задать опцию (.) в текущей базе данных – ОК. В Конструкторе запроса появится новая строка Добавление и в ней все поля таблицы тСтудентОтчисл. Для поля Состояние по строке Условие отбора ввести букву «о» (о тчислен). Сохранить запрос под именем, например зСтудентОтчислдобавл. Выполнить запрос. При каждом новом выполнении запроса в таблицу тСтудентОтчисл будут добавляться записи о новых отчисленных студентах.

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

Пример. Удалить записи об отчисленных студентах из таблицы Студент после их переноса в таблицу тСтудентОтчисл.

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

· На основании таблицы Студент создать запрос, включив в него только поле Состояние

· В группе Тип запроса выбрать Тип запроса: Удаление. В конструкторе запроса появится строка Удаление

· По этой строке появится значение Условие

· По строке Условия отбора ввести букву «о»

· Сохранить запрос под именем зСтудентОтчислУдал

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

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

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

Сводные таблицы

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

Пример. Создать сводную таблицу, отображающую оценки студентов по всем предметам

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

· Вкладка Создание – группа Другие – кнопка Конструктор запроса – добавить таблицы Факультет, Группа, Студент, Предмет, Успеваемость. Перенести поля: название факультета, номер группы, фамилия, название предмета, оценка. Задать в конструкторе запроса по строке Сортировка вид сортирования (по возрастанию) по полям: название факультета, номер группы, фамилия, название предмета. Сохранить запрос под именем зСводтабл. Далее надо на основании этого запроса создать сводную таблицу:

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

· Открыть запрос зсводтабл в режиме Сводная таблица (Двойным щелчком открыть запрос, а затем в группе Режимы выбрать Сводная таблица). Откроется макет пустой сводной таблицы, содержащий окно Список полей сводной таблицы и области для заголовков строк, столбцов, значений и фильтра.

· Перетащить поле Название факультета в область Перетащить сюда поля фильтра,

· Перетащить поля Номер группы и Фамилия в область «Перетащить сюда поля строк»,

· Перетащить поле Название предмета в область «Перетащить сюда поля столбцов»,

· Перетащить поле Оценка в область «Перетащить сюда поля итогов».

· Появится сводная таблица следующего вида (Окно Список полей сводной таблицы можно закрыть. Для его открытия на вкладке Конструктор в группе Показать или скрыть нажать кнопку Список полей):

Работу по переносу полей в сводной таблице можно выполнить иначе:

· В окне Список полей сводной таблицы выделяем нужное поле, например, Название факультета

· Откроем список, щелкнув на кнопке со стрелкой, расположенной в правом нижнем углу этого окна

· Выбираем строку Фильтр, нажимаем кнопку Добавить в,

· Выполняем аналогичную работу: выбираем поля Номер группы и Фамилия и строку Строки, кнопка Добавить в,

· Выбираем поле Название предмета и строку Столбцы, кнопка Добавить в,

· Выбираем поле Оценка и строку Данные, кнопка Добавить в.

Рядом с каждым заголовком строки/столбца имеются значки «+» и «-«. При нажатии значка «-» скрываются значения, расположенные в этой строке/столбце, а заголовок остается видимым. Соответственно, когда нажимается значок «+», скрытые значения отображаются.

Отображение/скрытие общих и промежуточных итогов

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

Удаление столбца Общие итоги: щелкнуть на поле Название предмета для выделения всей области с названиями предметов. В группе Сервис щелкнуть на кнопке Итоги. Для восстановления столбца Общие итоги опять щелкнуть на кнопке Итоги.

Для удаления строки Общие итоги надо щелкнуть на поле Номер группы, в группе Сервис щелкнуть на кнопке Итоги. Для удаления строки Итого по каждому студенту надо щелкнуть на поле Фамилия, группа Сервис – кнопка Итоги. Для восстановления итоговых строк – повторить операции.

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

Вкладка Конструктор Сводной таблицы

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

Фильтрация строк и столбцов в сводной таблице

Пример. Отобразить записи только по предмету Информ. технологии.

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

Удаление фильтра: Восстановить удаленные флажки.

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

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

Восстановление поля Оценка в области данных. Выполнить аналогичные операции, но в поле Оценка в области фильтра включить опцию Все.

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