Отбор данных с помощью фильтра. Сортировка данных. Итоговые операции. Поиск и замена. Запрос на выборку данных из одной и нескольких таблиц. Вычисляемые поля в запросах. Параметрический запрос. Запрос с группировкой. Сортировка несмежных полей
Откроем файл базы данных БД_Студенты_4. Выбираем Включить содержимое. Вспомним, как создавать формы и создадим форму по всем полям студентов и назовём её Студенты
Откроем таблицу Студенты. Раскроем в заголовке поля Деятельность, список его свойств, снимем галочку Выделить всё и активируем галочку Музыкант:
Жмём ОК и таблица отобразит записи студентов музыкантов, а заголовок поля Деятельность, отобразит значок с фильтром. Кроме этого значка, режим фильтра виден на вкладке Главная в группе Сортировка и фильтр – выделена кнопка Фильтр:
Раскроем поле Деятельность, выберем галочку Выделить всё и жмём ОК.
Отобразим студентов которые помогают библиотеке , но другим способом: правый клик в ячейке Библиотекарь и выбираем Равно «Библиотекарь». Отменим эту операцию опять же правый клик в поле Библиотекарь и Снять фильтр с Деятельность:
Если бы мы отменили предыдущий фильтр активной кнопкой Фильтр из Главная/Сортировка и фильтр, то она бы снова отобразила всех кассиров.
Отобразим с помощью фильтра студентов, стипендия которых больше 800: правый клик по любой ячейке поля Стипендия, выбираем Числовые фильтры/Больше, в диалоге вводим 800 и ОК. Таблица отобразит записи студентов, стипендия которых больше чем 800. Снимем фильтр кнопкой Фильтр на Главная/Сортировка и фильтр.
Отобразим в этой таблице студенток со стипендиями до 800р и студентов со стипендиями: раскроем в заголовке поля Пол список, выберем галочку Женский и ОК. Затем раскроем в заголовке поля Стипендия список, выберем пункт Числовые фильтры/Меньше, в диалоге вводим 800 и ОК. Получили ????? строк. Теперь в этом же условии нужно ЕЩЁ отобразить мужчин, стипендия которых больше 800р: Не снимая предыдущего фильтра, выбираем команду Главная/Сортировка и фильтр/Дополнительно/Изменить фильтр (не перепутать кнопку Дополнительно, из группы Записи):
Появится бланк с нашим условием: Женский <= 800. Внизу две вкладки Найти и Или, активируем вкладку Или и вводим ещё одно условие в поле Пол – Мужской а в поле Стипендия — >=800.
Теперь отобразим результат кнопкой Фильтр на Главная/Сортировка и фильтр. Будут отображены строки согласно нашему сложному условию.
Снимем фильтр кнопкой Фильтр на вкладке Главная группа Сортировка и фильтр. Закроем таблицу Студенты.
Откроем форму Студенты, найдём всех IT-специалистов: правый клик по полю Деятельность (не заголовку) и выберем пункт Равно «IT-специалист» и теперь мы можем в форме пролистать только IT-специалистов.
Снимем фильтр кнопкой Фильтр на Главная/Сортировка и фильтр. Закроем форму.
Откроем таблицу Студенты. Отсортируем поле Деятельность по алфавиту: выделим за заголовок поле Деятельность и выполним команду Главная/Сортировка и фильтр/По возрастанию.
Отменим сортировку командой Главная/Сортировка и фильтр/Удалить сортировку.
Отсортируем по ФИО: выделим за заголовки одновременно три столбца Фамилия, Имя, Отчество и выполним команду Главная/Сортировка и фильтр/По возрастанию. Найдём однофамильцев и проверим результат сортировки их имён. Отменим сортировку пунктом Удалить сортировку на Главная/Сортировка и фильтр.
Однако отсортировать одновременно несмежные поля такой командой нельзя. Несмежные поля сортируются с помощью запросов.
Операция Итоги, помогают быстро получить некоторые вычисления. Для таблицы Студенты нажмём на вкладке Главная в группе Записи кнопку Итоги:
Внизу программы, появится строка Итог, щёлкнем в этой строке в ячейке поля Стипендия , появится раскрывающийся список:
Поскольку поле Стипендия содержит числовые данные, то в списке можно выбрать команды для получения суммы, среднего значения, количество записей, максимального и минимального значений в этом поле и другие команды. Проверим количество записей в этом поле:
Поскольку каждый студент содержит стипендию, то результат будет равен количеству записей таблицы(число записей показано внизу таблицы правее строки Запись, если отключить кнопку Итоги).
Теперь удалим две стипендии и проверим – итоговая ячейка отобразила минус два значения.
Также можно получить итоговый результат по отфильтрованному значению. Например, отобразим с помощью фильтра только студентов:
и проверим результат в итоговой ячейке. Снимем операцию Итоги, повторным нажатием на кнопку Итоги на вкладке Главная.
Найдём участников КВН: щёлкнем в любой ячейке поля Деятельность, далее Главная/Найти , заполним в диалоге поля:
Образец – Участник КВН, Поиск в — Текущее поле, Совпадение — С начала поля, Просмотр — Все. Последовательно нажимаем Найти далее до появления диалога с окончанием поиска.
Теперь поменяем всех музыкантов, участниками КВН: щёлкнем в любой ячейке поля Деятельность, далее команда Главная/Заменить заполним в диалоге поля
Образец – Музыкант, Заменить на – Участник КВН, Поиск — Текущее поле, Совпадение — С любой частью поля, Просмотр — Все. Нажимаем Заменить всё. Каждый музыкант, теперь будет только участником КВН. При этом отмена действий не возможна. Возможно только повторная замена КВНщиков на музыкантов, НО, если перед первой заменой КВНщики УЖЕ были, то после второй замены они получатся музыкантами.
Замена не сработает для поля, которое связано с первичной таблицей, а также, если поле с фиксированным набором значений, ограничено списком.
Аналогично можно произвести поиск в форме. Если открыть форму в режиме формы, щёлкнуть в поле с данными и выполнить команду Главная/Заменить.
Закроем все открытые объекты в базе данных.
Запросы служат для обработки табличной информации. Они производят отбор записей из одной или нескольких таблиц. Запросы могут сортировать несмежные поля таблиц, и производит вычисления для определённого вывода данных.
Запрос 1
В базе БД_Студенты_4, создадим запрос командой: Создание/Запросы/Конструктор запросов:
В конструкторе запроса появится диалог Добавление таблицы для выбора источника данных, откуда будем вести запрос. Выбираем таблицу Студенты, жмём Добавить и Закрыть:
В поле конструктора запросов появится панель с полями таблицы Студенты.
Удалим эту панель клавишей [Del], а затем снова добавим командой Конструктор/Настройка запроса/Отобразить таблицу:
Теперь двойной клик по полям в панели, добавим в запрос поля НомерСтудБилета, Фамилия, Имя, Деятельность и Стипендия:
Поля добавляются слева направо. Добавим в запрос поле Пол и удалим его для практики. Выделим в запросе поле Пол (чёрная стрелка над заголовком) и удалим клавишей [Del]:
Мы забыли поместить поле Отчество. Методом перетаскивания тянем из панели поле Отчество и располагаем в запросе поверх поля Деятельность:
И наконец, переместим поле Деятельность за полем Стипендия: Курсор над заголовком поля Деятельность так, чтобы курсор был в виде белой стрелки (если будет чёрная, двигаем немного вниз) затем располагаем курсор над правой границей поля Стипендия:
Запускаем запрос с отображением результата нашей работы: Конструктор/Результаты/Выполнить:
Получился результат как в таблице, но только с теми полями, которые мы отобразили. Возвращаемся обратно в режим конструктора правым кликом по вкладке и выбираем пункт Конструктор:
Упорядочим записи по возрастанию стипендии: в строке Сортировка нашего запроса, для поля Стипендия выбираем по возрастанию
Запускаем запрос и проверяем, что все студенты отсортированы по окладу. Вернёмся в режим конструктора и уберём сортировку по окладу.
Двойным кликом добавим в запрос поле КодФакультета и аналогично отсортируем это поле по возрастанию:
запускаем запрос и проверяем, что поле КодФакультета отсортирован. Вернёмся в режим конструктора.
Теперь мы хотим, чтобы ФИО в каждом факультете тоже были отсортированы. Если мы добавим ещё сортировку по возрастанию для ФИО:
и запустим запрос, то в результате сортировка будет неправильной т.к. сортировка выполнилась слева направо. Проблема решается так:
- добавляется ещё одно такое же поле в нашем случае это КодФакультета,
- сортируется это поле
- отключается видимость этого поля
Причём поле КодФакультета должно быть крайним левым в запросе:
Добавим в запрос, второе поле КодФакультета левее ФИО и применим ему сортировку по возрастанию, а сортировку для первого поля КодФакультета снимем. Запустим запрос. В результате появилось вымышленное имя для второго поля КодФакультета, поскольку два одинаковых поля в таблице быть не должно. Вернёмся в конструктор, и снимем галку Вывод на экран для левого поля КодФакультета :
и снова запустим запрос. В результате мы получили то, что хотели.
Правый клик по вкладке, сохраним запрос под именем Сортировка студентов по факультетам. Правый клик по вкладке, закроем запрос.
В левой панели программы MS Access, появился новый объект Запрос, если он не появился, выберите команду Все объекты Access:
Теперь дважды кликните по нему и программа запустит запрос.
Запрос 2
Создадим новый запрос в режиме конструктора:
В диалоге снова выбираем таблицу Студенты, жмём Добавить и Закрыть. Обратим внимание на звёздочку в панели с полями:
Сделаем двойной клик по этой звёздочке, мы получим в запросе всего одно поле (со звёздочкой):
Звёздочка заменяет отображение всех полей таблицы, если мы запустим запрос. Запустим запрос и увидим отображение всех полей таблицы. В чём удобство этого символа?
Если мы хотим вывести все поля таблицы, но при этом что бы выполнялось какое-то условие только для одного поля, мы добавим это поле в запрос и применим нужное для нас условие. Например, заставим запрос выводить все поля таблицы, но только для факультета 3: добавим в запрос поле КодФакультета, затем в строку Условие отбора введём 3:
Теперь запустим запрос. Появилась таблица, но с лишним полем для КодФакультета, вернёмся в конструктор, и отключим видимость для поля КодФакультета:
Снова запустим запрос для проверки изменений. Вернёмся в конструктор. Выделим оба поля и удалим их клавишей [Del]:
Добавим в конструктор запроса, поля Фамилия, Имя, Отчество, Деятельность и Стипендия. Отобразим студентов, у которых стипендия от 800 до 1000 включительно: Курсор в строку Условие отбора для поля Стипендия и вводим between 800 and 1000 (что бы лучше было видно текст, нажмём [Shift+F2])
ОК, запускаем запрос для проверки. Должно быть 19 записей.
Возвращаемся в конструктор, и отобразим студентов, у которых стипендия за пределами предыдущего диапазона, т.е. меньше 800 и больше 1000: изменим наше условие и добавим к нему логическое отрицание – Not:
Not between 800 and 1000
Запускаем запрос для проверки. Должно быть 16 записей.
Диапазоны
Вместо оператора between можно воспользоваться операторами сравнений, >,<, так, вместо between 800 and 1000 можно ввести
>= 800 and <= 1000
Но вместо Not between 800 and 1000 мы вводим
<800 or >1000
Отобразить студентов рождённых в 80-90 г.р включительно, т.е. между началом 1980 и концом 1979 года. И сохранить запрос под именем Студенты 80-90 года рождения.
Запрос 3
Создадим новый запрос в режиме конструктора:
В диалоге снова выбираем таблицу Студенты, жмём Добавить и Закрыть. Добавим в запрос поля НомерСтудБилета, Фамилия, Имя, Отчество, Деятельность и Стипендия. Отобразим всех пешеходов: в строке Условие отбора поля Деятельность вводим лаборант, нажимаем [Tab] или щёлкнем в любом месте запроса. Запустим запрос, для проверки работы условия.
Аналогично отобразим всех волонтёров (в этом же запросе), но поскольку слева и справа в названии этой деятельности добавлено ещё и пояснение, то в условии требуется добавить управляемые символы *: введём в строку Условие отбора — *волонтёр*. Программа подставит Like и поместит условие в кавычки:
Запустим запрос для проверки. Должно быть 6 записей. Правый клик по вкладке, сохраним запрос под именем Волонтёры и закроем его.
Запрос 4
Создадим новый запрос в режиме конструктора:
В диалоге снова выбираем таблицу Студенты, жмём Добавить и Закрыть. Добавим в запрос поля Фамилия, Имя, Отчество, и Хобби. Запустим его и убедимся, что в поле Хобби, есть данные и пустые ячейки. Отобразим всех, у кого НЕТ хобби: в режиме запроса в строку Условие отбора для поля Хобби вводим null, запустим запрос и проверяем всех, у кого нет хобби. Аналогично отобразим всех, у кого есть хобби: в режиме конструктора туда же вводим not null и запустим запрос для проверки.
Вернёмся в конструктор, и введём предыдущие два условия в альтернативной форме: not* — результат тот же, что и при null и * — тоже что и not null. Правый клик по вкладке, сохраним запрос под именем Нет хобби. Закроем запрос.
Запрос 5
Создадим новый запрос в режиме конструктора:
В диалоге снова выбираем таблицу Студенты, жмём Добавить и Закрыть. Добавим в запрос поля Фамилия, Имя, Отчество, Деятельность и ГражданствоРФ. Запустим его и убедимся, что в поле ГражданствоРФ установлены флажки, т.е. это логический тип, а условия для логических типов содержат либо 1(Истина), либо 0(Ложь).
В режиме конструктора в условие отбора для поля ГражданствоРФ, введём 0, запустим запрос и проверим, должно быть 6 записей, а в поле ГражданствоРФ флажки пустые. Сохраним запрос под именем Нет гражданства
Создать новый запрос, который отображает студентов, у которых код телефона либо 903, либо 910.
Подсказка. Использовать два условия одно в строке Условие отбора, другое в строке или. Сохранить запрос под именем Моб. телефон 903 910 и закрыть его.
Запрос 6
Создадим новый запрос в режиме конструктора:
Двойной клик, и выбираем таблицы Студенты, Факультеты, Экскурсии и жмём Закрыть:
Таблицы Студенты и Факультеты связаны, но таблицу, с которой связана таблица Экскурсии, мы не отобразили. Какой может быть результат, если отобразить поля несвязанных таблиц? Поместим в запрос поля Фамилия, Имя, Отчество и Название:
Запустим запрос. Результат будет неожиданный: 175 записей. Т.е. каждая запись! таблицы Экскурсии, содержит все записи несвязанной с ней таблицей.
Вернёмся в конструктор и добавим таблицу, которая связана с таблицей Экскурсии и с таблицей Студенты: правый клик по пустому полю с панелями, пункт Добавить таблицу, в диалоге добавим таблицу Экскурсанты и жмём Закрыть:
Другими словами, при связи МНОГИЕ-КО-МНОГИМ ( ∞ : ∞ ), должны быть добавлены все три таблицы, а формировать запрос с несвязанными таблицами нельзя!!!
Удалим все поля в запросе. Теперь отобразим студентов из определённого факультета, которые посетили ту или иную экскурсию: из таблицы Студенты выбираем НомерСтудБилета и ФИО. Из таблицы Факультеты берём поле Факультет. Из таблицы Экскурсии берём поля Название и ДатаЭкскурсии. Запускаем, проверяем и возвращаемся в конструктор.
Теперь отобразим все экскурсии в 2014 году: в условии отбора поля ДатаЭкскурсии, введём *2014, запустим и проверим результат. Сохраним запрос под именем Экскурсии 2014 и закроем его.
Создать новый запрос, который отображает студентов (НомерСтудБилета, ФИО), которые сдали сертификацию(Название) в 2013-2014 г.г. Запрос сохраните под именем Сертификация 2013-2014
Запрос 7 (Посчитаем бонус по формуле 41,3% от стипендии)
Создадим новый запрос в режиме конструктора:
В диалоге выбираем одну таблицу Студенты, жмём Добавить и Закрыть. Добавим в запрос поля: НомерСтудБилета, ФИО и Стипендия. Установим курсор правее поля Стипендия в ячейку Поле:
Откроем построитель выражений (Есть 3 способа) 1-й: Конструктор/Настройка запросов/Построитель:
2-й: Правый клик там где курсор и выбираем Построить:
3-й: жмём [Ctrl+F2], там где курсор
В верхнем поле диалога Построитель выражений пишем: Бонус:(двоеточие обязательно), в левом нижнем окне раскрываем: БД_Студенты_4/Таблицы/Студенты, в среднем окне двойной клик по полю Стипендия:
В верхнем окне диалога, строка «Выражение» нам не нужна, поэтому щёлкнем по ней и удалим, в результате имеем:
Бонус: [Студенты]![Стипендия]
Теперь вычислим процент от стипендии: в верхнем окне щёлкнем правее строки и умножим на 0,413:
Бонус: [Студенты]![Стипендия]*0,413
Нажимаем ОК(обратим внимание на галочку видимости в вычисляемом поле) и запускаем запрос для анализа результата. Вернёмся в конструктор.
Свойства вычисляемого поля
Поскольку вычисляемое поле не является табличным, значит настроить его формат, можно в конструкторе запроса: правый клик по названию поля и выбираем пункт Свойства. Появится панель Окно свойств. В свойстве Формат поля выберем денежный формат:
Сохраним запрос под именем Расчёт итогов, но не закрываем его.
Запрос 8(Посчитаем налог(13%) от стипендии + бонуса)
В режиме конструктора правее поля Бонус, щёлкнем, чтобы создать ещё одно вычисляемое поле и откроем построитель выражений комбинацией [Ctrl+F2]:
В верхнее окно пишем Налог:, а в левом нижнем окне, в отличии от предыдущего запроса, выделяем имя нашего запроса(оно есть, потому что мы его сохранили) и в среднем окне, двойной клик по полю Стипендия:
Строка «Выражение» нам не нужна, поэтому щёлкнем по ней и удалим.
Поскольку поле Бонус вычисляемое(т.е. не табличное), то его НЕЛЬЗЯ использовать в вычислениях, например, вот так:
[Бонус]*0,13
Мы должны снова вычислить бонус, затем вычислить процент от стипендия + процент от вычисленного бонуса:
[Стипендия] * (1+0,413) * 0,13
Откуда эта формула?
Если помнить, что 3 + 3 * 2 = 3(1+2), тогда:
([Стипендия]*0,413) *0,13+([Стипендия]*0,13) =
([Стипендия]*0,13) *0,413+([Стипендия]*0,13) =
[Стипендия] * 0,13 * (0,413+1)
Запускаем запрос для проверки. Вернёмся в конструктор.
Запрос 9(Посчитаем итоговую сумму, которую получит студент, т.е. стипендия + бонус без 13%)
В режиме конструктора правее поля Налог, щёлкнем, чтобы создать ещё одно вычисляемое поле и откроем построитель выражений комбинацией [Ctrl+F2]. В верхнее поле пишем СуммаРУБ:, в левом нижнем поле, выделяем имя нашего запроса и в среднем поле, двойной клик по полю Стипендия:
Строка «Выражение» в верхнем окне нам также не нужна, поэтому щёлкнем по ней и удалим.
Мы должны снова вычислить премию, затем вычислить (100%-13%) процентов от оклада и вновь вычисленной премии:
[Стипендия] *(1+0,413)*0,87
Посчитайте сумму с предыдущего запроса в долларах и в евро. Т.е. добавьте ещё два вычисляемого поля. Подсказка: используйте формулу из предыдущего запроса. Сохраните и закройте запрос Расчёт итогов.
Запрос 10(Объединение текстовых полей)
Пусть есть таблица Фрукты и требуется объединить два поля Яблоки и Груши. Формула для построителя будет такой:
[Фрукты]![Яблоки]&[Фрукты]![Груши]
Результат этого объединения: ЯблокиГруши<>
То же самое объединение, только с пробелом:
[Фрукты]![Яблоки]&» «&[Фрукты]![Груши]
Результат этого объединения: Яблоки Груши
Объединим поля Фамилия, Имя и Отчество с помощью вычисляемого поля: создадим новый запрос в режиме конструктора:
В диалоге выбираем одну таблицу Студенты, жмём Добавить и Закрыть.
Курсор в строку Поле и вызываем построитель выражений [Ctrl+F2]. В верхнем окне вводим имя вычисляемого поля ФИО:
В нижнем левом окне в нашей базе данных выделяем таблицу Студенты. В среднем окне двойной клик по полю Фамилия, в верхнем окне, выделяем строку «Выражение», удаляем её. После фамилии вводим &» «& (два амперсанда, внутри две английские кавычки, внутри пробел). В верхнее окно добавим поле Имя, и аналогично соединим с полем Отчество. Итоговый вид выражения:
ФИО: [Студенты]![Фамилия] & » » & [Студенты]![Имя] & » » & [Студенты]![Отчество]
Запустим запрос и проверим, что имя поля ФИО и каждая ячейка поля содержит три слова. Теперь вычислим возраст студентов по формуле сегодняшняя дата минус дата рождения:
В конструкторе добавим ещё одно вычисляемое поле. В построителе выражений в верхнем окне вводим Возраст:. В левом нижнем окне выделяем пункт Общие выражения, в среднем окне выделяем пункт Текущая дата и двойной клик по Date() в правом окне:
Строка «Выражение» в верхнем окне нам также не нужна, поэтому щёлкнем по ней и удалим.
Отнимаем дату рождения: добавляем в выражение минус и в таблице Студенты, двойной клик по полю Дата рождения.
ОК и запускаем запрос для проверки результата. Мы получили возраст в днях. Самостоятельно переведём дни в годы. Сохраним запрос под именем Возраст студентов и закроем его.
Запрос 11 (Вывод студентов из конкретного факультета)
Создадим новый запрос в режиме конструктора:
В диалоге выбираем одну таблицу Студенты, жмём Добавить и Закрыть. Добавим в запрос поля: НомерСтудБилета, ФИО и КодФакультета
Параметрический запрос позволяет пользователю, во время запуска запроса, выбрать условие отбора и затем выполнить запрос. Т.е. здесь идёт автоматическая подстановка значения в поле Условие отбора.
Добавим для поля КодФакультета, в строку условие текст [Код факультета?]. Теперь запустим запрос. Появится диалог с предложением ввести значение, мы выбираем, только то значение, которое может быть в поле КодФакультета таблицы Студенты. Введём любую цифру от 1 до 4 и нажмём ОК.
Сохраним запрос под именем Выбор факультета, закроем его, а теперь запустим его двойным кликом из панели объектов слева, введём другую цифру и ОК.
Запрос 12 (Посчитаем по факультету, сумму стипендий, среднее значение от стипендии, минимальную стипендию и сколько студентов на факультете)
Создадим новый запрос в режиме конструктора:
В диалоге выбираем одну таблицу Студенты, жмём Добавить и Закрыть. Нужно добавить ещё одну таблицу, но мы уже закрыли диалог с таблицами, тогда отобразим снова диалог с таблицами командой Конструктор/Настройка запроса/Отобразить таблицу:
В диалоге добавляем таблицу Факультеты, жмём Добавить и Закрыть.
Добавим в запрос поле: Факультет, теперь выполним команду Конструктор/Показать или скрыть/Итоги:
В запросе для поля Факультет, появилась новая строка Групповая операция с операцией Группировка. Добавим в запрос четыре поля Стипендия, с групповыми операциями Sum, Avg, Min и Count для суммы, среднего значения, минимального и количества.
Итак, мы выбрали одно поле, по которому будет проходить ГРУППИРОВКА, и поля, по которым будут проходить ГРУППОВЫЕ ОПЕРАЦИИ.
Запускаем запрос и видим результат работы групповых операций по каждому из ??? факультетов. Единственный минус, это неудобные имена четырёх полей с группировкой. Исправим САМОСТОЯТЕЛЬНО имена четырёх полей на Сумма, Средняя стипендия, Минимальная стипендия и Кол-во студентов: в конструкторе с помощью панели Окно свойств и свойства Подпись. Запустим запрос и проверим имена новых полей.
Сохраним запрос под именем Расчёт по факультетам.
- Посчитайте по деятельности, сколько студентов и средняя стипендия по деятельности.
- Создать запрос для определения количества посетителей каждой экскурсий.
- Рассчитать длительность экскурсии и стоимость одного дня экскурсии
запрос 1 — выполняется через групповые операции
запрос 2 — выполняется через групповые операции
запрос 3 — выполняется через вычисляемое поле
0 Responses to “4. Microsoft Access. Уровень 1. Обработка данных”