Пример. Таблица Студент содержит записи об отчисленных студентах. Их время от времени надо удалять из этой таблицы и хранить (добавлять) в другой таблице, например, тСтудентОтчисл.
Решение. 1-ый этап. Создать таблицу тСтудентОтчисл.
Эта таблица должна иметь ту же структуру, что и таблица Студент. Поэтому легче создать таблицу тСтудентОтчисл методом копирования:
· В области переходов открыть вкладку Таблицыи установить курсор на таблицу Студент – щелкнуть правой кнопкой мыши, выбрать команду Копировать
· выполнить команду Вставить (курсор на том же месте)
· в открывшемся окне Вставка таблицы ввести имя таблицы тСтудентОтчисл,установить флажок(.) только структура.
Далее. Открыть таблицу тСтудентОтчисл в режиме Конструктора и изменить для поля ИД тип данных Счетчик на Числовой, оставив поле ключевым. Статус поля Ключевое не позволит добавить в таблицу запись об одном и том же студенте несколько раз.
2-ой этап. Создать запрос. Вкладка Создание – группа Другие – кнопка Конструктор запроса – добавить таблицу Студент – перенести все поля (поочередно, а не кнопкой *, находящейся в макете таблицы) – в группе Тип запроса выбрать Тип запроса: Добавление – выбрать таблицу тСдудентОтчисл («приемник») – задать опцию (.) в текущей базе данных – ОК. В Конструкторе запроса появится новая строка Добавление и в ней все поля таблицы тСтудентОтчисл. Для поля Состояние по строке Условие отбора ввести букву «о» (отчислен). Сохранить запрос под именем, например зСтудентОтчислдобавл. Выполнить запрос. При каждом новом выполнении запроса в таблицу тСтудентОтчисл будут добавляться записи о новых отчисленных студентах.
Пример. Удалить записи об отчисленных студентах из таблицы Студент после их переноса в таблицу тСтудентОтчисл.
Решение.Таблица Студент связана с таблицей Успеваемость, поэтому нельзя просто удалить записи в таблице Студент. В таблице Успеваемость в поле ИДСтудент будет ссылка на несуществующие записи в таблице Студент. Для предотвращения этого надо проверить какой тип связи установлен между таблицами:вкладкаРабота с базами данных - Схема данных –щелкнуть на линии связи между таблицами Студент и Успеваемость – выбрать команду Изменить связь – установить (если не установлен) флажок (.) обеспечение целостности данных ифлажок(.) каскадное удаление связанных записей.
· На основании таблицы Студент создать запрос, включив в него только поле Состояние
· В группе Тип запроса выбрать Тип запроса: Удаление. В конструкторе запроса появится строка Удаление
· По этой строке появится значение Условие
· По строке Условия отбора ввести букву «о»
· Сохранить запрос под именем зСтудентОтчислУдал
· Выполнить запрос. При каждом выполнении запроса из таблицы Студент будут удаляться записи на отчисленных студентов. Кроме того, будут удаляться и все связанные с ними записи в таблице Успеваемость.
В таблице Студент появится строка с признаком «удаление».
Запросы на добавление и удаление должны рассматриваться как одна операция. Для этих целей создается макрос, вызывающий поочередно эти запросы.
Сводные таблицы служат для анализа данных. Они составляются на основании таблицы или запроса, в которых большое количество записей. Сводные таблицы легко трансформировать (менять местами строки и столбцы, добавлять, удалять поля, устанавливать условия фильтрации и т.д.). На основании одной исходной таблицы/запроса можно создать несколько различных сводных таблиц. Они служат для отображения данных в удобном виде, но данные в них нельзя изменять.
Пример. Создать сводную таблицу, отображающую оценки студентов по всем предметам
Решение.Для создания сводной таблицы сначала создадим запрос с полями: название факультета, номер группы, фамилия, название предмета, оценка. Для этого выполним следующие операции:
· Вкладка Создание – группа Другие – кнопка Конструктор запроса – добавить таблицы Факультет, Группа, Студент, Предмет, Успеваемость. Перенести поля: название факультета, номер группы, фамилия, название предмета, оценка. Задать в конструкторе запроса по строке Сортировка вид сортирования (по возрастанию) по полям: название факультета, номер группы, фамилия, название предмета. Сохранить запрос под именем зСводтабл.Далее надо на основании этого запроса создать сводную таблицу:
· Открыть запрос зсводтабл в режиме Сводная таблица (Двойным щелчком открыть запрос, а затем в группе Режимы выбрать Сводная таблица). Откроется макет пустой сводной таблицы, содержащий окно Список полей сводной таблицыи области для заголовков строк, столбцов, значений и фильтра.
· Перетащить поле Название факультетав областьПеретащить сюда поля фильтра,
· Перетащить поля Номер группы и Фамилия в область «Перетащить сюда поля строк»,
· Перетащить поле Название предмета в область «Перетащить сюда поля столбцов»,
· Перетащить поле Оценка в область «Перетащить сюда поля итогов».
· Появится сводная таблица следующего вида (Окно Список полей сводной таблицы можно закрыть. Для его открытия на вкладке Конструктор в группе Показать или скрыть нажать кнопку Список полей):
Работу по переносу полей в сводной таблице можно выполнить иначе:
· В окне Список полей сводной таблицы выделяем нужное поле, например, Название факультета
· Откроем список, щелкнув на кнопке со стрелкой, расположенной в правом нижнем углу этого окна
· Выполняем аналогичную работу: выбираем поляНомер группыи Фамилия и строкуСтроки,кнопкаДобавить в,
· Выбираем поле Название предмета и строку Столбцы, кнопка Добавить в,
· Выбираем поле Оценка и строку Данные, кнопка Добавить в.
Рядом с каждым заголовком строки/столбца имеются значки «+» и «-«. При нажатии значка «-» скрываются значения, расположенные в этой строке/столбце, а заголовок остается видимым. Соответственно, когда нажимается значок «+», скрытые значения отображаются.
Отображение/скрытие общих и промежуточных итогов
Общие итоги показываются автоматически в последнем столбце и в последней строке сводной таблицы. Если общих итогов нет (как в нашей таблице), то эти столбец и строку можно удалить.
Удаление столбцаОбщие итоги: щелкнуть на поле Название предметадля выделения всей области с названиями предметов. В группе Сервис щелкнуть на кнопке Итоги. Для восстановления столбца Общие итоги опять щелкнуть на кнопке Итоги.
Для удаления строки Общие итоги надо щелкнуть на поле Номер группы,в группе Сервис щелкнуть на кнопке Итоги. Для удаления строки Итого по каждому студенту надо щелкнуть на поле Фамилия, группа Сервис – кнопка Итоги. Для восстановления итоговых строк – повторить операции.
Изменение структуры сводной таблицы:В случае необходимости можно изменить порядок расположения полей, добавить или удалить поле. Для перемещения поля надо его выделить и перетащить за другое поле или перед ним. При перетаскивании появляется синяя вертикальная линия. Удаление поля выполняется клавишей Delete.
Вкладка Конструктор автоматически появляется на ленте вкладок при открытии запроса в режиме Сводная таблица. Она содержит группы кнопок и команд, позволяющие выполнять фильтрацию строк и столбцов, отображать общие итоги, вычислять итоги, форматировать сводную таблицу.
Пример. Отобразить записи только по предмету Информ. технологии.
Решение.В окне Сводной таблицы щелкнуть на кнопке со стрелкой, расположенной справа в поле Название предмета. В открывшемся окне оставить флажок только у предмета Информ. технологии (сначала удалить все флажки удалив флажок у опции Все, а затем поставить флажок у опции Информ. иехнологии).
Удаление фильтра: Восстановить удаленные флажки.
Пример. Показать записи только на тех студентов, которые получили определенную оценку.
Решение.Перетащить в окне конструктора сводной таблицы поле Оценка из окна Список полей сводной таблицы в область « Перетащите сюда поля фильтра». Открыть в этой области список у поля Оценка и оставить флажки у нужной (нужных) оценки. Обращаем внимание, что значения оценок в области данных таблицы исчезнут. Для появления в этой области только заданных оценок надо перетащить поле Оценка из окна Список полей сводной таблицы назад в эту область. В таблице отразятся данные только на отбираемые оценки.
Восстановление поля Оценка в области данных.Выполнить аналогичные операции, но в поле Оценка в области фильтра включить опцию Все.
Пример. Показать оценки студентов только по первым двум предметам.
· Выделить область с названиями предметов, щелкнув на поле Название предмета
· Из списка Первые/последние на вкладке конструктора сводной таблицы (группа Фильтр и сортировка) выбрать пункт Показать только первые, выбрать из списка значение 2.
Отмена фильтрации. Из списка Первые/последние выбрать пункт Показать все.
