Начисляется тогда и только тогда, когда студент сирота)

Рис.6.67. Форма с вычисляемым полем "Стипендия"

Рис.6.66. Форма на таблицу "Сессия" с полем "Семестр"

3. Затем пишем или щелкаем знак "=" и аналогично вставляем значение логического выражения "Истина" из папки "Константы" (рис.6.65).

4. Пишем ";" и переходим к написанию значения логического выражения, когда оно истинно, а по условию задачи размер стипендии для студентов сирот составляет 1000 руб, значит, пишем 1000 (рис.6.65). Значение логического выражения, когда оно ложно мы не пишем, т.к. это не задано по условию задачи, т.е. в данном случае на экране будет пустое поле.

5. Щелкаем кнопку "Ok" в "Построителе выражений" и в окне свойств объекта во вкладке "Все" устанавливаем размер шрифта – 14 пунктов, насыщенность – жирный и цвет светло-синий или синий.

6. После установки атрибутов текста закрываем окно свойств объекта, закрываем "Конструктор" и проверяем работу вычисляемого поля. При щелчке на объекте-флажке "Сирота" в поле "Стипендия" должно появляться число 1000 (рис.6.67).

Изменить вычисляемое поле "Стипендия" таким образом, чтобы учитывались следующие положения правил по начислению стипендий:

1. Стипендия студентам, сдавшим сессию на хорошие и отличные оценки, начисляется в размере 400 руб.

2. Стипендия студентам, сдавшим сессию на отличные оценки, начисляется в размере 600 руб.

3. Стипендия студентам-сиротам увеличивается на 1000 руб. по сравнению с размером стипендий, указанных в пунктах 1 и 2.

4. Стипендия студентам-сиротам выплачивается в размере 1000 руб, если студент не сдал сессию.

5. Создать аналогичную форму по таблице "Студенты" и добавить 7 записей.

6. С помощью формы добавить 7 записей в таблицу "Сессия" так, чтобы было 2 студента с неудовлетворительными оценками, 2 студента с отличными оценками и 6 человек с хорошими и удовлетворительными оценками.

7. Оценить структуру таблицы "Сессия", выяснив, что вместо двух полей "Учебный Год" и "Сессия" разумнее ввести одно поле "Семестр" со своими ограничениями на значения. Удалить поле "Учебный Год" таблицы "Сессия", а поле "Сессия" изменить на поле "Семестр", со своим типом данных и ограничениями.

8. Изменить в соответствии с изменениями в пункте 7 форму "Результаты сессии и стипендия студентов".

В результате форма будет выглядеть аналогично рис.6.68.

 

6.5.4. Запросы и выборки

 

Запросы используются для извлечения из БД нужной пользователю информации. Поэтому "золотым правилом" создания БД является следующее:

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

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

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

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

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

Запрос с параметрами — это запрос, при выполнении отображающий в собственном диалоговом окне приглашение ввести данные. Часто пользователю приходится иметь дело с запросами, которые имеют одинаковую структуру, но различающимися условиями отбора. В этом случае следует сформировать запрос с параметрами. При выполнении запроса СУБД будет запрашивать у пользователя конкретные значения параметров.

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

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

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

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

Ø на добавление записей. Запрос на добавление добавляет группу записей из одной или нескольких таблиц в конец одной или нескольких таблиц.

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

Запрос SQL — это запрос, создаваемый при помощи инструкций SQL. Инструкция SQL - это выражение, определяющее команду SQL, например, SELECT, UPDATE или DELETE, и включающее предложения, например, WHERE или ORDER BY. Язык SQL (Structured Query Language) используется при создании запросов, а также для обновления и управления реляционными базами данных, такими как базы данных MS Access.

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

К запросам SQL относятся подчиненные запросы, запросы к серверу, управляющие запросы и запросы на объединение. Эти запросы невозможно создать в бланке запроса.

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

Управляющий запрос – это запрос SQL, содержащий инструкции DDL (Data Definition Language— язык описания данных). Такие инструкции позволяют создавать или изменять объекты в БД.

Запрос на объединение – это запрос, в котором оператор UNION используется для объединения результатов двух или нескольких запросов на выборку.

При разработке запросов SQL необходимо создавать инструкции SQL непосредственно в окне запроса в режиме SQL.

6.5.4.2. Разработка и создание запросов в СУБД MS Access

Запросы на выборку

Создавать запрос лучше всего с помощью Конструктора. Для этого следует открыть основное меню MS Access, выбрать объект "Запрос" и щелкнуть кнопку "Конструктор" (рис.6.69).

Рис.6.69. Создание запроса с помощью "Конструктора"

Отдать команду "Запросы"–"Конструктор" из главного меню MS Access

В результате мы переходим в бланк запроса, в который необходимо добавить объект (объекты), из которого мы будем извлекать информацию (рис.6.70).

Рис.6.70. Добавление в бланк запроса таблицы, из которой будет извлекаться информация

Будем создавать запрос из таблицы "Сессия".

Выбрать в окне "Добавление таблицы" таблицу "Сессия" и щелкнуть кнопку

"Добавить", а затем кнопку "Закрыть".

В результате выполнения задания список полей таблицы "Сессия" добавится в бланк запроса (рис.6.71).

Рис.6.71. Бланк запроса со списком полей таблицы "Сессия"

Следующим этапом является добавление полей из списка полей таблицы "Сессия" в бланк запроса. Это можно производить тремя способами:

Ø двойным щелчком мыши на нужном поле в списке;

Ø перетаскиванием методом drag&drop соответствующего поля из списка в бланк запроса (рис.6.72);

Ø открытия списка в строке "Поле" бланка запроса (рис.6.72).

Добавить поля "Семестр", "Факультет", Фамилия" и все предметы различными

способами в бланк запроса (рис.6.73).

 

Рис.6.72. Добавление поля в бланк запроса методом drag&drop (левый рис.6.) и из списка полей в бланке запроса (правый рис.6.)

Рис.6.73. Бланк запроса с добавленными полями

Следующим этапом является выбор режима сортировки: либо по возрастанию, либо по убыванию, либо без сортировки (рис.6.74).

Рис.6.74. Выбор режима сортировки

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

Заканчивается оформление запроса заданием условий отбора. При задании условия отбора следует использовать логические операторы такие как ">", "<", "=", ">=", "<=", а также служебные слова "or" – "или", "and" – "и" и др.

 

Сделать выборку из таблицы "Сессия" студентов экономического факультета, у

которых по физике хорошие и отличные оценки.

Выполнение. Для поля "Факультет" устанавливаем ограничение "="Экон"", а для поля "Физика" – ">3" (рис.6.75).

Название факультета не следует писать в кавычках, MS Access сама добавит

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

Рис.6.75. Оформление условий отбора для полей "Факультет" и "Физика"

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

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

Рис.6.76. Результат выполнения запроса задания

Запросы с параметрами

Напомним, что запрос с параметрами — это запрос, который при выполнении просит пользователя ввести данные по одному или нескольким параметрам запроса. Часто пользователю приходиться иметь дело с запросами, которые имеют одинаковую структуру, но различающимися условиями отбора. В этом случае следует сформировать запрос с параметрами. При выполнении запроса СУБД будет запрашивать у пользователя конкретные значения параметров.

Для запроса параметра у пользователя следует добавить название параметра в квадратных скобках и двоеточием в строке "Условие отбора" сразу после условия. Например, мы хотим узнать не только, сколько студентов экономического факультета имеют по физике хорошие и отличные оценки, но и всех сдавших физику студентов. Чтобы не менять условие отбора каждый раз, т.к. структура самого запроса не изменилась, мы добавляем после условия в поле "Физика" название поля в квадратных скобках и с двоеточием (рис.6.77).

Рис.6.77. Создание запроса с параметром

Тогда название этого поля будет выводиться в ходе выполнения запроса (рис.6.78), и мы могли ввести конкретное значение оценки, чтобы узнать, сколько студентов получили по физике больший балл, чем введенное число (рис.6.79)

Рис.6. 78. Диалоговое окно для ввода значения параметра

 

.

Рис.6. 79. Результат работы запроса с параметром - оценка по физике больше 2

Создать запрос с параметром для поля "Факультет"

Существуют четыре типа запроса на изменение: на удаление записи, на обновление записи, на добавление записи и на создание таблицы.

Покажем составление запроса на изменение на примере запроса на удаление. Создадим запрос на удаление для таблицы "Студенты". В начале делаем обычный запрос на выборку с полями "Номер Зачетки" и "Имя" (рис.6.80).

Рис.6.80. Запрос на выборку для таблицы "Студенты"

Затем перетаскиваем в первое поле ("Номер Зачетки") символ звездочки (*) из списка полей таблицы "Студенты" (рис.6.81).

Рис.6.81. Перетаскивание методом drag&drop символа (*) – "Все поля"

Символ звездочка означает "Все поля". В результате у нас получится следующая картина (рис.6.82).

Рис.6.82. Вид бланка запроса после перетаскивания символа (*)

Следующим шагом является выбор типа запроса на изменения посредством инструмента "Тип запроса" , который находится, как мы видим, рядом с инструментом "Запуск". Щелкаем кнопку открытия списка и выбираем из списка запрос на удаление (рис.6.83).

Рис.6.83. Выбор типа запроса на изменение

После этого действия бланк запроса изменится (рис.6.84) по сравнению со стандартным (рис.6.82).

Рис.6. 84. Бланк запроса на удаление

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

Рис.6.85. Задание условия отбора для поля "Имя"

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

Рис.6.86. Динамическая таблица запроса на удаление

Создать запрос на удаление всех записей студентов юридического факультета

Запросы с вычисляемыми полями

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

1. В бланке запроса пишется имя вычисляемого поля с двоеточием;

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

Для удобства написания формулы расчета вычисляемого поля следует

открыть окно области ввода. Для этого следует щелкнуть мышью в строке написания имени поля и нажать клавиши "Shift + F2" (рис.6.87).

Создать запрос с вычисляемым полем "Средний балл" по таблице "Сессия".

Рис.6.87. Создание вычисляемого поля запроса с помощью "Области ввода"

 

1.5.5. Отчеты

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

Рассмотрим создание отчета на примере отчета с условным названием "Результаты Сессии". Отдельные шаги следует выполнять как задания.

Первый шаг.Запуск "Мастера отчетов". Выбираем в главном меню MS Access объект "Отчеты", пункт "Создание отчета с помощью мастера" и нажимаем кнопку "Создать" (рис.6.88).

Рис.6.88. Главное меню MS Access с выбранным объектом "Отчеты"

Второй шаг.Выбор мастера отчетов и объекта для отчета. В открывшемся окне "Новый отчет" выбираем "Мастер отчетов" и внизу окна из списка таблицу "Сессия", а затем щелкаем кнопку "Ok" (рис.6.89).

Рис.6.89. Выбор мастера отчетов и объекта для отчета

Третий шаг. Выбор полей таблицы "Сессия", которые будут входить в отчет. Выбираем поля "Семестр", "Факультет", "Фамилия" и все названия предметов (рис.6.90). Щелкаем кнопку "Далее".

Рис.6.90. Выбор полей для отчета

Четвертый шаг.Добавление уровней группировки. Для более наглядного представления информации в отчете полезно бывает сгруппировать данные по отдельным характеристикам. В нашем случае можно сгруппировать информацию о результатах сдачи сессии по семестрам, поэтому выбираем поле "Семестр" (рис.6.91). Щелкаем кнопку "Далее".

 

Рис.6.91. Добавление уровня группировки

Пятый шаг.Мастер отчетов предлагает произвести сортировку для записей выбранных полей. Используя эту возможность, выбираем из списка поле "Факультет" и затем поле "Фамилия". Таким образом, сортировка записей будет сначала производиться в зависимости от факультета, а затем по фамилии студента (рис.6.92). Щелкаем кнопку "Далее".

Рис.6.92. Выбор порядка сортировки записей

Шестой шаг.Выбор макета отчета и ориентации страницы. Предлагается выбрать ступенчатый макет и альбомную ориентацию страниц отчета, в связи с большим количеством полей (рис.6.93). Щелкаем кнопку "Далее".

Рис.6.93. Выбор макета и ориентации страниц отчета

Седьмой шаг.Выбор стиля отчета. MS Access предлагает различные стили оформления отчета. Пользователю предлагается сделать самостоятельный выбор стиля оформления отчета (рис.6.94). Щелкаем кнопку "Далее".

Рис.6.94. Выбор стиля отчета

Восьмой шаг.Задание имя отчета. Предлагается назвать отчет "результаты Сессии" (рис.6.95). Щелкаем кнопку "Готово".

Рис.6.95. Задание имени отчета

В результате на экране появляется отчет по таблице "Сессия" (рис.6.96).

Рис.6.96. Предварительный вид отчета по таблице "Сессия"

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

Рис.6.97. Отчет по таблице "Сессия"

Создать отчет по запросу "Запрос по физике".