Сортировка и фильтрация
Практическое использование MS ACCESS
Целью работыявляется приобретение первоначальных навыков работы в MS ACCESS.
Постановка задачи
Предположим, в Excel имеем следующую таблицу:
Таблица 1
ПРЕДСТАВЛЕНИЕ ДАННЫХ В EXCEL
| ФИО_студента | Предмет | Оценка |
| Иванов И.И. | Информатика | |
| Иванов И.И. | История | |
| Иванов И.И. | Инженерная графика | |
| Иванов И.И. | Высшая математика | |
| Иванов И.И. | Программирование | |
| Петров П.П. | Информатика | |
| ..................... | ................................. | ... |
Если предположить, что ширина первого столбца 25 символов, второго – 40, третьего – один (вполне достаточно чтобы хранить число от двух до пяти), то нетрудно подсчитать, сколько байт (символов) необходимо для хранения информации о 10 000 студентах за 10 семестров (по 5 экзаменов в каждом семестре):
10 000 х 5 х 10 = 500 000 – столько строк будет в таблице;
25 х 500 000 = 1 250 000 байт (в первом столбце);
40 х 500 000 = 2 000 000 байт (во втором столбце);
1 х 500 000 = 500 000 байт (в третьем столбце);
1 250 000 + 2 000 000 + 500 000 = 3 750 000 байт (во всей таблице).
Если учет и хранение этой информации организовать в ACCESS, то получим три таблицы:
Таблица 2
ПРЕДСТАВЛЕНИЕ ДАННЫХ В MS ACCESS
| Таблица Студенты | Таблица Предметы | Таблица Сессия | |||||
| Название поля | Размер поля | Название поля | Размер поля | Название поля | Размер поля | ||
| Код_студента | Код_предмета | Код_студента | |||||
| ФИО | Название_предмета | Код_предмета | |||||
| Оценка |
Таблица Студенты содержит 10 000 записей (Код_студента и ФИО), для хранения которых необходимо: 10 000 х (2+25) = 270 000 байт.
Таблица Предметы содержит 5 х 10 =50 записей (Код_предмета и название предмета – по 5 предметов в семестре, 10 семестров), для хранения которых необходимо: 50 х (2+40) = 2 100 байт.
Таблица Сессия содержит 10 000 записей (Код_студента, Код_предмета и оценку), для хранения которой необходимо: 10 000 х (2+2+1) = 50 000 байт.
Итого для хранения всей информации в Access понадобится:
270 000 + 2 100 + 50 000 = 322 100 байт,
что более чем в 10 раз меньше, чем при проектирование таблицы в Excel.
Второе преимущество – быстрый поиск информации - тоже налицо: быстрее найти все сведения о ком-то из студентов, организуя поиск не по всей фамилии, а всего по двум символам – Коду_студента.
Постановка задачи в Access. Необходимо:
ü создать базу данных, которая содержит оценки студентов одной группы за один семестр (для простоты), т.е. три таблицы (см. табл.2);
ü наполнить ее данными;
ü организовать запросы об «отличниках», «хорошистах», «троечниках» и неуспевающих, а так же начислить стипендию всем студентам (отличникам - 200% от 1900 р., имеющим одну четверку – 100%, остальным – 0);
ü организовать удобные формы для ввода информации по предметам, по студентам;
ü получить отчеты об успеваемости студентов и о начислении стипендии;
ü создать простейшие макросы для открыванием и закрыванием таблиц.
Проектирование таблиц
Запускается Access аналогично другим программным продуктам Microsoft Office.
Сначала следует создать базу данных, в которой будут сохранены все другие Access-объекты: таблицы, запросы, формы и т.д. В левом верхнем углу окна Access нажмите на кнопку Office 
и выберите команду Создать. В правой части окна появится поле для ввода имени базы данных. Щелкните левой кнопкой мыши по значку 
, расположенному справа от этого поля, для выбора папки, в которой будет храниться Ваша база данных (установите папку, в которой Вы все время работаете), и задайте также имя базы данных, например, SESSIYA. После этого щелкните левой кнопкой мыши на кнопке 
Access автоматически добавит расширение имени базы данных .accdb.
2.1. Создание таблицы Студенты в режиме конструктора
Создавать таблицу удобнее в режиме Конструктор. Перейдите в режим Конструктор, щелкнув мышкой на кнопке 
ленты Главная. Открывается диалоговое окно, в котором Access предлагает ввести имя таблицы. Введите имя первой таблицы – Студенты - и нажмите на кнопку 
.
Задайте имена полей, их тип и т.д. (см. табл. 3).
Таблица 3.
ПРОЕКТИРОВАНИЕ ТАБЛИЦЫ СТУДЕНТЫ
| Имя поля | Где задавать | Что задавать |
| Код_студента | Тип данных | Счетчик |
| На ленте Конструктор | Ключевое поле (если не задано по умолчанию - наличие значка  слева от поля) | |
| ФИО | Тип данных | Текстовый |
| Свойства поля, Размер поля | ||
| Свойства поля, Обязательное поле | Да |
Закройте таблицу (не закройте случайно Access!), щелкнув мышью на кнопке 
внутреннего окна (таблицы Студенты), подтвердив сохранение.
2.2. Ввод данных таблицы Студенты
В левой части окна Access, в поле Все таблицы,выделите таблицу Студенты, после чего щелкните правой кнопкой мыши и в открывшемся меню выберите элемент Открыть. Введите в столбце поля ФИО любые четыре фамилии. Закройте таблицу. Если появится диалоговое окно с подтверждением сохранения, сохраните таблицу.
2.3. Создание таблицы Предметы в режиме конструктора
На ленте Созданиенажмите на пиктограмму 
.
Задайте имена полей, их тип и т.д. (см. табл. 4).
Таблица 4.
ПРОЕКТИРОВАНИЕ ТАБЛИЦЫ ПРЕДМЕТЫ
| Имя поля | Где задавать | Что задавать |
| Код_предмета | Тип данных | Счетчик |
| На ленте Конструктор | Ключевое поле | |
| Название_предмета | Тип данных | Текстовый |
| Свойства поля, Размер поля | ||
| Свойства поля, Обязательное поле | Да |
Закройте таблицу, подтвердив сохранение и дав таблице имя Предметы.
2.4. Ввод данных таблицы Предметы
Ввод данных в таблицы Предметы осуществляется аналогично п.2.2.
Введите в таблицу Предметы четыре предмета: информатика, история, высшая математика и физика. Закройте таблицу, подтвердив сохранение.
2.5. Создание таблицы Сессия в режиме конструктора
На ленте Созданиенажмите на пиктограмму для входа в режим Конструктор. Задайте имена полей, их тип и т.д. (см. табл. 4).
Таблица 4.
ПРОЕКТИРОВАНИЕ ТАБЛИЦЫ СЕССИЯ
| Имя поля | Где задавать | Что задавать (записать) |
| Код_студента | Тип данных | Числовой |
| Код_предмета | Тип данных | Числовой |
| Оценка | Тип данных | Числовой |
| Свойства поля, Размер поля | Байт | |
| Свойства поля, Значение по умолчанию | ||
| Свойства поля, Условие на значение | >1 and <6 | |
| Свойства поля, Сообщение об ошибке | Можно вводить только числа от 2 до 5! | |
| Свойства поля, Обязательное поле | Да |
Закройте таблицу, подтвердив сохранение, дав таблице имя Сессияи не создав ключевого поля.
2.6. Ввод данных таблицы Сессия
Щелкните на таблице Сессия в поле Все таблицы двойным щелчком мыши.
Введите в таблицу Сессия для первого студента (код равен единице) оценки «5» по всем предметам (коды предметов от одного до четырех).
2.7. Изменение свойств полей таблицы Сессия в режиме конструктора
Нетрудно заметить, что ввод очень неудобный: необходимо перед собой иметь перечень фамилий студентов и предметов с их кодами. Вспомним, как удобно было задавать, например, тип данных и организуем такой же ввод для полей Код_студента и Код_предмета.
Для таблицы Сессия войдите в конструктор (Вы таблицу не закрывали. Для входа в режим конструктор надо нажать на кнопку 
) и в Типе данных поля Код_студента выберите Мастер подстановоки далее:
- включить Объект ”столбец подстановки” будет использовать значения из таблицы или запроса (данные будут браться из таблицы Студенты), и нажать на кнопку 
;
- в поле Показать задать Таблицы, выделить таблицу Студентыинажать на кнопку ;
- с помощью кнопки 
выбрать поля Код_студента и ФИО инажать на кнопку ;
- задать порядок сортировки элементов списка, выбрав в первом поле ФИО и 
, после чегонажать на кнопку ;
- нажать на кнопку 
;
- появляется сообщение «Перед созданием связи необходимо сохранить таблицу. Выполнить это сейчас?») Нажать на кнопку 
.
Для поля Код_студента в Свойствах поля написать в строке Подпись следующее: ФИО студента (при отсутствии данного значения в качестве подписи использовалось имя поля: Код_студента).
В Типе данных поля Код_предмета выберите Мастер подстановок и далее проделайте следующее:
- включить Объект ”столбец подстановки” будет использовать значения из таблицы или запроса (данные будут браться из таблицы Предметы), и нажать на кнопку ;
- в поле Показать задать Таблицы, выделить таблицу Предметыинажать на кнопку ;
- с помощью кнопки выбрать поля Код_предмета и Название_предмета инажать на кнопку ;
- задать порядок сортировки элементов списка, выбрав в первом поле Название_предмета и , после чегонажать на кнопку ;
- нажать на кнопку ;
- появляется сообщение «Перед созданием связи необходимо сохранить таблицу. Выполнить это сейчас?») Нажать на кнопку .
Для поля Код_предмета в Свойствах поля написать в строке Подпись следующее: Предмет (при отсутствии данного значения в качестве подписи, вы заметили, использовалось имя поля: Код_предмета).
Теперь осталось организовать выбор значений из списка с помощью мышки для поля Оценка (тип данных):
- выбрать из списка Мастер подстановок;
- включить Будет введен фиксированный набор значений, нажать на кнопку ;
- Число столбцов: 1(оставить без изменения), в колонке таблицы Столбец1 ввести столбиком числа от 5 до 2 (для перемещения вниз пользоваться мышкой или клавишей ↓), и нажать на кнопку .
Закройте таблицу, подтвердив ее сохранение.
2.8. Ввод остальных данных таблицы Сессия
Щелкните на таблице Сессия в поле Все таблицы двойным щелчком мыши.
Введите в таблицуСессия для остальных студентов оценки по всем предметам так, чтобы были студенты всех четырех категорий («отличник», «хорошист», «троечник» и «двоечник»). Данные добавляются после последней записи и выбирайте их из раскрывающихся списков.
После ввода данных закройте таблицу Сессия, подтвердив сохранение изменений.
Сортировка и фильтрация
Сортировку и фильтрацию проделайте для таблицыСессия. Откройте ее двойным щелчком мыши в поле Все таблицы. Отсортируйте таблицу:
- по фамилиям студентов в алфавитном порядке (щелчком мыши сделайте текущим столбец ФИО_студента, на ленте Главная нажмите на пиктограмму 
поля Сортировка и фильтр);
- по оценкам (сначала «5», затем «4» и т.д.);
- по предметам в алфавитном порядке.
Отфильтруйте таблицу по следующим признакам:
- оставьте в таблице только «отличные» оценки: кто, по каким предметам получил «пятерки» (сделайте текущим столбец Оценка, станьте на любую запись в этом поле с оценкой «5», нажмите кнопу 
на ленте Главная в поле Сортировка и фильтр и выберите элемент Равно 5);
- оставьте в таблице только все сведения для предмета Информатика (выполняется аналогично).
Для отмены фильтра используется кнопка 
, расположенная на ленте Главная в поле Сортировка и фильтр.
Закройте таблицу, подтвердив сохранение.
Схемы
На ленте Работа с базами данныхнажмите на кнопку 
. В окне Схема данных вы увидите, что таблица Студенты связана с таблицей Сессия по средствам поля Код_студента.
Чтобы убедиться, что структура таблиц правильная, проделайте действия, описанные далее. Щелкните два раза левой кнопкой мыши на линии, связывающей эти таблицы, и, в открывшемся окне установив признак: Обеспечение целостности данных, нажмите на кнопку ОК. Появившиеся символы 1 («единица») и ∞ («бесконечность») говорят о том, что структура таблиц правильная (если не получилось, то в структуре есть ошибки, которые необходимо найти и устранить при разработке «серьезного» продукта, но которые в данном примере Вам особенно не помешают).
Щелкните два раза левой кнопкой мыши на линии, связывающей таблицы Предметы и Сессия по средствам поля Код_предмета, и, установив признак: Обеспечение целостности данных, нажмите на кнопку ОК (выполняется аналогично).
Закройте окно Схема данных.