 |
1.Для создания запроса в окне базы данных выберите вкладку ленты — Создание и в группе Запросы нажмите кнопку Конструктор запросов. Откроется пустое окно запроса на выборку в режиме конструктора — ЗапросN и диалоговое окно Добавление таблицы.
|
|
|
|
1. Для создания запроса в окне базы данных выберите вкладку ленты — Создание и в группе Запросы нажмите кнопку Конструктор запросов. Откроется пустое окно запроса на выборку в режиме конструктора — ЗапросN и диалоговое окно Добавление таблицы.
Рис. 4. 31
2. В окне Добавление таблицы выберите таблицу Студенты и нажмите кнопку Добавить. Выбранная таблица будет отображена в области схемы данных запроса. Закройте окно Добавление таблицы, нажав кнопку Закрыть.
Рис. 4. 32
В результате выполненных действий в окне конструктора запросов в верхней панели появится схема данных запроса, которая включает выбранные для данного запроса таблицы. В данном случае одну таблицу Студенты. Таблица представлена списком полей. Первая строка в списке полей таблицы, отмеченная звездочкой (*), обозначает все множество полей таблицы. Нижняя панель является бланком запроса, который нужно заполнить.
Кроме того, на ленте появляется и автоматически активизируется новая вкладка Работа с запросами | Конструктор, на которой цветом выделен тип созданного запроса — Выборка. Таким образом, по умолчанию всегда создается запрос на выборку. Команды этой вкладки представляют инструментарий для выполнения необходимых действий при создании запроса. Эта вкладка открывается, когда в режиме конструктора создается новый запрос или редактируется существующий.
Рис. 4. 33
3. Для удаления любой таблицы из схемы данных запроса установите на нее курсор мыши и нажмите клавишу < Delete>. Для добавления ― нажмите кнопку Отобразить таблицу в группе Настройка запроса на вкладке Работа с запросами | Конструктор или выполните команду Добавить таблицу в контекстном меню, вызываемом на схеме данных запроса.
|
|
|
4. В окне конструктора последовательно перетащите из списка полей таблицы Студенты поля «Фамилия», «Имя», «Математика», «Информатика», «Фузкультура» в столбцы бланка запроса в строку.
5. Для включения нужных полей из таблицы в соответствующие столбцы запроса можно воспользоваться следующими приемами:
• в первой строке бланка запроса Поле щелчком мыши вызвать появление кнопки списка и выбрать из списка нужное поле. Список содержит поля таблиц, представленных в схеме данных запроса;
• дважды щелкнуть на имени поля таблицы в схеме
данных запроса;
• для включения всех полей таблицы можно перетащить или дважды щелкнуть на символе * (звездочка) в списке полей таблицы в схеме данных запроса.
Рис. 4. 34
6. Если вы по ошибке перетащили в бланке запроса ненужное поле, удалите его. Для этого переместите курсор в область маркировки столбца сверху, где он примет вид черной стрелки, направленной вниз, и щелкните кнопкой мыши. Столбец выделится. Нажмите клавишу < Delete> или выполните команду Удалить столбцы в группе Настройка запроса.
7. В строке Вывод на экран отметьте поля, иначе они не будут включены в таблицу запроса.
8. Запишите в строке Условия отбора наименование Математика, Напишите выражение 56 (или любой балл). Так как выражение в условии отбора не содержит оператора, то по умолчанию используется оператор =. Используемое в выражении значение вводится в двойных кавычках, которые добавляются автоматически.
9. Выполните запрос, щелкнув на кнопке Выполнить или на кнопке Режим в группе Результаты. На экране появится окно запроса в режиме таблицы с записью из таблицы Студенты, отвечающей заданным условиям отбора.
Рис. 4. 35
Изменив условие на ― > =50‖, то отобразятся все студенты, у которых балл по математике больше или равен 50.
|
|
|
Рис. 4. 36
Замечание
Окно запроса в режиме таблицы аналогично окну просмотра таблицы базы данных. Через некоторые таблицы запроса может производиться изменение данных базовой таблицы, лежащей в основе запроса. Запрос, просматриваемый в режиме таблицы, в отличие от таблицы базы данных Access 2010, не имеет столбца Щелкнуть для добавления, предназначенного для изменения структуры таблицы. В этом режиме на вкладке ленты Главная доступны те же кнопки, что и при открытии таблицы базы данных.
10. Если при вводе сложного текстового выражения вы допустили неточность, значение не будет найден в таблице. Использование операторов шаблона — звездочка (*) и вопросительный знак (? ) (стандарт ANSI-89, используемый для запросов по умолчанию) или знак процента (%) и подчеркивания (_) (ANSI-92, рекомендуемый как стандарт для SQL Server), упрощает поиск нужных строк и позволяет избежать многих ошибок.
11. Сохраните запрос, щелкнув на вкладке Файл и выполнив команду Сохранить. В окне Сохранение введите имя запроса «Запрос на выборку». Заметим, что имя запроса не должно совпадать не только с именами имеющихся запросов, но и с именами таблиц в базе данных.
12. Закройте текущий запрос по команде контекстного меню Закрыть или нажав кнопку окна запроса Закрыть.
13. Выполните сохраненный запрос, выделив запрос в области навигации и выбрав в контекстном меню команду Открыть.
14. Для редактирования запроса выделите его в области навигации и выполните в контекстном меню команду Конструктор.
В запросе можно совмещать несколько условий для нескольких полей. Тогда результатом запроса будет таблица объединяющая в себе все условия.
Рис. 4. 37
Замечание
Если необходимо задать условие для выбора записей из определенного промежутка времени, то необходимо записать Between #11. 01. 2008# And #31. 03. 2008#. Оператор Between задает интервал дат (в ANSI-92 вместо знака # используются одинарные кавычки ‗ ). Кроме того, этот оператор позволяет задать интервал для числового значения.
Важно. Если вы внесете какие-либо изменения в таблице запроса, то эти же изменения отразятся в данных исходных таблиц.
Использование вычисляемого поля в запросах
Очень часто возникает потребность выполнить какие-то вычисления в запросе. Для этого используется «Построитель выражений».
|
|
|
Задача. Определим средний балл каждого студента.
1. Начальные действия аналогичны шагам создания Запроса на выборку. На основе таблицы Студенты, создадим запрос с вычисляемым полем. Для запроса выберем поля Фамилия_Имя, Математика, Информатика и Физкультура.
2.
| . |
Добавим новое поле. Для этого встанем в графе Поле, нового столбца запроса, и нажмѐ м пиктограмму «Построителя выражений»
Рис. 4. 38
И на экране появится окно «Построителя выражения»:
Рис. 4. 39
Именно в этом окне можно выстраивать не только простые арифметические выражения, но и сложные выражения с логическими функциями.
Разберѐ м содержимое окна Построителя выражений. В верхней части будет строиться, непосредственно, выражение. Именно тут можно формировать и редактировать построенное выражение.
Ниже располагаются пиктограммы арифметических операций, логические операторы, оператора сравнения и скобки.
В нижней части окна располагается три окошка (рассмотрим с лева на право):
3. Окно функций, таблиц, запросов и т. д. Именно в этом окне вы можете выбирать функции для вашего выражения, а так же поля, которые будут участвовать в вашем выражении.
4. В зависимости от того, что вы выберите в первом окне. Если в первом окне вы выбираете объект БД (таблица, запрос и т. д. ), то в этом окне будут отображены поля этого объекта. Если в первом окне выбираете функции, то во втором окне появится перечень категорий функций.
5. В данном окне отобразится перечень функций категории, выбранной во втором окне.
6. Наша задача состоит в вычислении среднего балла каждого студента. Формула должна выглядеть следующим образом:
В первом окне в категории Таблицы выбираем таблицу Студенты, во втором последовательно выбираем поля Информатика, Математика и Физкультура, соединяя их знаком «+». Что бы сумма посчиталась правильно, полученное выражение заключить в скобки. Далее ставим знак деления, и т. к. у нас всего 3 три предмета, цифру «3».
|
|
|
Рис. 4. 40
В результате выполнения данного запроса получится такая таблица:
Рис. 4. 41
7. Приведѐ м данные нашего запроса в «удобный» вид: округлим средний балл до десятых и озаглавим поле с этими данными. Для этого вернѐ мся в Конструктор запросов и снова откроем Построитель выражений (предварительно встав на вычисляемое поле).
В окне построителя «Выражение1» заменим на «Средний балл» - заголовок поля.
Для округления результата нужно все выражение поместить в специальную функцию округления и указать до какого знака после запятой необходимо округлять. Для этого в первом окне выберем Функции-Встроенные функции. В категориях выберем Математические, и среди математических функциях найдем функцию Round. Вместо «number» поместим все наше выражение, вместо «precision» ставим цифру 1 (1 знак после запятой). Конечное выражение будет выглядеть так:
Средний балл: Round (([Студенты]! [Математика] +
[Студенты]! [Информатика] + [Студенты]! [Физкультура])/3; 1)
А конечная таблица запроса будет выглядеть следующим образом:
Рис. 4. 42
Созданный запрос сохраним как «Средний балл».
Задача
Определить возраст преподавателей.
1. Запрос построим на основе таблицы Преподаватели, нам потребуются поля: Фамилия, Имя, Дата рождения;
2. Для вычисления возраста применим следующую формулу:
Т. к. Access даты вычисляются в днях, поэтому полученный результат необходимо делить на 365, что бы получить полное количество лет. А что бы получить правильный результат, у полученного числа необходимо отбросить дробную часть. Именно отбросить, т. к. округление даст неверный результат!
Функция Int(выражение), которая из выражения берет только целую часть, находится в категории Математические.
Для вызова текущей даты используется функция Now() (категория Дата/Время). Использование данной функции позволяет не вносить корректировку при каждом открытии БД, т. к. данная функция обращается к системной дате и, в связи с этим, пересчѐ т функции происходит автоматически при открытии БД.
3. Не забудем переименовать поле в Возраст.
Конечное выражение будет выглядеть следующим образом: Возраст: Int (( Now () - [Преподаватели]! [Дата рождения] )/365) Результирующая таблица:
Рис. 4. 43
Сформированный запрос сохраним как «Возраст».
Запросы с параметрами
Запрос с параметрами представляет собой специальный «интерактивный» тип запроса: прежде чем запрос будет выполнен, на экран выводится диалоговое окно с приглашением ввести один или ряд параметров, по которым проводится отбор записей в соответствующем поле. Запрос, таким образом, допускает некоторую модификацию при каждом своем запуске, что очень удобно, например при генерировании месячных или квартальных отчетов о доходах предприятия. Запросы с параметрами могут быть отнесены к отдельному типу условно, так как ввод параметра можно организовать для запросов на выборку, запросов на изменение и перекрестных запросов.
|
|
|
Задача
Вывести список студентов, балл которых по математике выше заданного пользователем.
1. Создадим запрос на основе таблицы Студенты;
2. В полях запроса разместим поля: Фамилия_Имя, Математика;
3. В графе Условие отбора введем условие «> [критерий отбора]»:
Рис. 4. 44
4. Сохраним запрос под именем Отбор студентов;
5. При запуске запроса, на экране появится диалоговое окно, в котором пользователь вводит балл.
Рис. 4. 45
6. В таблице запроса отобразятся только те записи, которые удовлетворяют условию.
Рис. 4. 46
Такое окно запроса будет появляться каждый раз при выполнении запроса.
В запросах с качестве параметра можно использовать любые логические операторы. Сам параметр отбора необходимо заключить в [ ].
Групповые операции в запросах
Групповые операции в запросах Access позволяют выделить группы записей с одинаковыми значениями в указанных полях и вычислить итоговые данные для каждой из групп по другим полям, используя одну из статистических функций. Статистические функции применимы, прежде всего, к полям с типом данных Числовой, Денежный, Дата/время.
В Access предусматривается девять статистических функций:
• Sum — сумма значений некоторого поля для группы;
• Avg — среднее от всех значений поля в группе;
• Max, Min — максимальное, минимальное значение поля в группе;
• Count — число значений поля в группе без учета пустых значений;
• StDev — среднеквадратичное отклонение от среднего значения поля в группе;
• Var — дисперсия значений поля в группе;
• First и Last — значение поля из первой или последней записи в группе.
Результат запроса с использованием групповых операций содержит по одной записи для каждой группы. В запрос, прежде всего, включаются поля, по которым производится группировка, и поля, для которых выполняются статистические функции. Кроме этих полей в запрос могут включаться поля, по которым задаются условия отбора.
Задача
Определить:
• Количество студентов у каждого преподавателя;
• Наибольший балл по математике (среди студентов данного преподавателя); Наименьший балл по информатике (среди студентов данного преподавателя); Средний балл по физкультуре (среди студентов данного преподавателя).
1. Данный запрос будет основан на двух таблицах: Преподаватели и Студенты, поэтому в Конструктор запросов добавляем обе эти таблицы. Из таблицы Преподаватели выбираем поле Фамилия, из таблицы Студенты – поля Фамилия_Имя, Информатика, Математика, Физкультура;
2. Чтобы в запрос на выборку включить групповые операции, необходимо активировать пиктограмму Итоги: 
Среди перечисления категорий поля запроса появится строка Групповая операция. Именно в этой строке мы будем указывать вид групповой операции.
3. Т. к. группировать данные мы будем по преподавателям, то в графе Групповые операции поля Фамилия (Преподавателя) оставляем слово Группировка. В аналогичной графе поля Фамилия_имя (Студента) выберем операцию Count.
4. Аналогичным образом в полях предметов расставим команды Max, Min, Avg.
Рис. 4. 47
Результирующая таблица:
Рис. 4. 48
Сохраним запрос под именем Групповой запрос.
Исполняемые запросы в ACCESS
Существует ряд запросов, получивших название
Исполняемые запросы. Результат выполнения таких запросов, не всегда отображается в таблице. Существуют следующие виды Исполняемых запросов:
• Запрос на создание таблиц;
• Запрос на добавление данных;
• Запрос на обновление данных;
• Запрос на удаление данных.
Запрос на создание таблиц
Запрос на создание таблицы Access используется для сохранения результата запроса. Этот вид запроса основан на запросе на выборку, но, в отличие от него, сохраняет таблицу с результатами запроса.
1. Сформируем запрос на создание таблицы на примере ранее полученного запроса на выборку с групповыми
вычислениями Групповой запрос;
2. В области навигации выделите названный запрос и с помощью команды контекстного меню откройте его в режиме конструктора.
3. Преобразуйте этот запрос на выборку в запрос на создание таблицы, выполнив команду Создание таблицы в группе Тип запроса на вкладке конструктора 
или выбрав команду контекстного меню запроса Тип запроса | Создание таблицы;
4. В окне Создание таблицы введите имя создаваемой таблицы
Статистика студентов;
Рис. 4. 49
5. Для того чтобы просмотреть, какие записи будут помещены в новую таблицу, щелкните по кнопке Режим на ленте
конструктора запросов в группе Результаты;
6. Выполните запрос, чтобы таблица Статистика Студентов была сохранена в базе данных. Теперь эту таблицу можно увидеть в списке таблиц области навигации.
Рис. 4. 50
Запрос на обновление данных
Запрос на обновление Access может быть использован для обновления данных в полях базовых таблиц. Изменения вносятся в группу записей, отбираемых с помощью указанных пользователем условий отбора. Значения для изменений в полях определяются в бланке запроса в строке Обновление.
Задача
В таблице Студенты поменять Направление обучения тем студентам, у которых Код преподавателя равен 2.
1. Для формирования запроса на обновление сначала создайте запрос на выборку на основе таблицы Студенты, выбрав поля Преподаватель и Направление_обучения;
2. Преобразуйте запрос на выборку в запрос на обновление,
щелкнув на кнопке Обновление 
, размещенной на вкладке ленты Конструктор или выбрав команду Обновление из списка Тип запроса в контекстном меню запроса. После выполнения этой команды в бланке запроса появляется строка Обновление;
Рис. 4. 51
3. В графе Условие отбора поля Преподаватель введем условие обновления «=2». В графе Обновление поля Направление_обучения введем «Информационные
технологии»;
Рис. 4. 52
4. Для обновления содержимого Направление_обучения
выполните запрос, нажав кнопку Выполнить 
на вкладке ленты Конструктор. Открывается диалоговое окно с сообщением о числе обновляемых записей и вопросом о продолжении операции обновления. Подтвердите обновление записей.
Рис. 4. 53
5. Посмотреть результаты выполнения запроса, можно открыв таблицу Студенты:
До выполнения запроса После выполнения запроса
Рис. 4. 54
Созданный запрос назовѐ м «Изменение профессии».
Запрос на удаление данных
Запрос на удаление может быть использован для удаления данных в полях базовых таблиц. Запрос на удаление позволяет удалить записи из одной таблицы или из нескольких взаимосвязанных таблиц, для связи с которыми установлен параметр обеспечения целостности данных каскадное удаление связанных записей. В схему данных запроса включается таблица, из которой должны удаляться записи, и связанные с ней таблицы, для полей которых требуется задать условия отбора удаляемых записей.
Задача
Удалить всех студентов, у которых по всем трѐ м предметам балл ниже 40.
1. Создайте запрос на выборку. Добавьте в схему данных запроса таблицу Студенты, из которой удаляются записи.
2. Преобразуйте запрос на выборку в запрос на удаление, выбрав на вкладке ленты Конструктор тип запроса Удаление
или выполнив команду контекстного меню запроса Тип запроса | Удаление;
3. После преобразования запроса в запрос на удаление Access в его бланке появляется строка Удаление;
4. Далее приступайте к формированию бланка запроса. Переместите символ звездочки (*) из списка полей таблицы Студенты в бланк запроса. В строке Удаление в столбце этого поля появляется значение Из;
5. Для того чтобы задать условия отбора удаляемых записей, переместите с помощью мыши в бланк запроса поля Математика, Информатика и Физкультура. В строке Удаление под именем этих полей появится значение Условие. Запишите в строку Условие отбора для этих полей значение «< 40».
Важно. Следует иметь в виду, что удаленные записи нельзя восстановить. Поэтому перед удалением записей выполните предварительный просмотр удаляемых записей, нажав кнопку Режим на вкладке ленты.
6. Для удаления записей на вкладке ленты нажмите кнопку Выполнить. При выполнении запроса будет сообщено об удалении записей. До выполнения запроса:
Рис. 4. 55
После выполнения запроса:
Рис. 4. 56
Записи 4 и 9 были удалены.
Сохраним запрос под именем «Отчисление».
Важно
Результаты работы запроса на удаление зависят от отношений между таблицами и установленных в схеме базы данных параметров обеспечения целостности для их связей.
Запрос на добавление данных
Запрос на добавление данных удобно использовать, когда необходимо объединить несколько таблиц с аналогичными полями. Запрос подобного типа используется когда необходимо данные из одной таблицы перенести или скопировать в другую таблицу.
Важно
Данные можно перенести только в однотипные данные.
§5. Формы
Формы являются основой разработки диалоговых приложений пользователя для работы с базой данных. Формы, адекватные формам первичных документов, позволяют выполнять загрузку справочных, плановых и оперативно-учетных данных, в любой момент просматривать и редактировать содержимое ранее введенных в базу данных документов, оформлять новые документы.
Формы обеспечивают удобную работу с данными одной или нескольких взаимосвязанных таблиц, которые выводятся на экран с использованием ее макета, разработанного пользователем. Работая с формой, пользователь может добавлять, удалять и изменять записи таблиц, получать расчетные данные. В процессе работы может осуществляться контроль вводимых данных, могут проверяться ограничения на доступ к данным, выводиться необходимые дополнительные сведения.
Форма состоит из элементов управления, которые отображают поля таблиц, и графические элементы, не связанные с полями таблиц. Графические элементы управления предназначены, прежде всего, для разработки макета формы: полей таблиц и запросов, надписей, внедряемых объектов (рисунков, диаграмм), вычисляемых полей, кнопок, выполняющих печать, открывающих другие объекты или задачи.
Однотабличные формы в Access
Однотабличные формы в Access предназначены для загрузки, просмотра и корректировки данных одной таблицы. Источником данных такой формы служит единственная таблица. Она может быть легко создана одним щелчком мыши с помощью команд автоматического создания формы: Форма, Разделенная форма или Несколько элементов, размещенных на вкладке ленты Создание в группе Формы.
Рис. 4. 57
Для последующей настройки формы в соответствии с требованиями пользователя ее можно доработать в режиме макета или конструктора. Можно удалить из формы ненужные поля, изменить расположение элементов управления и подобрать их размеры, добавить новые элементы управления, произвести вычисления, задать свойства формы и ее элементов управления.
В форме, созданной с помощью команды Несколько элементов, отображается сразу несколько записей, и все поля записи размещаются в одной строке, что удобно для ввода данных из простых справочников, имеющих только табличную часть. Этот макет формы аналогичен отображению записей таблицы в режиме таблицы, однако при этом предоставляются многочисленные возможности по настройке отображения и дополнения новыми элементами.
В форме, созданной с помощью команды Разделенная форма, одновременно отображаются данные в двух представлениях — в одном ее разделе записи отображаются в виде таблицы, в другом выводится единственная выделенная в таблице запись, предназначенная для удобной работы с ее данными.
Общим для этих команд является то, что они автоматически создают для выбранной таблицы форму, не вступая в диалог с пользователем, и сразу выводят на экран форму в режиме макета.
Создать однотабличные формы в Access можно с помощью команды Мастер форм, размещенной на вкладке ленты Создание в группе Формы. В диалоговых окнах мастера пользователь выбирает поля, которые надо включать в форму, способ отображения записей, стиль оформления.
Создайте однотабличную форму Студенты для ввода, просмотра и корректировки данных таблицы Студенты в базе данных Университет. Поскольку в таблице есть поле с фотографией, требующее большой области для отображения, одновременно выводите в форме поля только одной записи таблицы.
Чтобы источником записей формы стала таблица Студенты, выберите ее в области навигации и выполните команду Форма на вкладке ленты Создание. Эта команда обеспечит автоматическое создание формы на основе только одной выбранной таблицы. Завершается работа команды отображением формы в режиме макета. При этом в форме отображаются поля одной записи таблицы, расположенные в макете в столбик. Заголовок формы соответствует имени таблицы источника Студенты. Форма:
Рис. 4. 58
Разделенная форма:
Рис. 4. 59
Несколько Элементов:
Рис. 4. 60
Выбор отображения данных в форме зависит только от назначения использования формы.
Для работы оставим обычную форму (первая в списке). Назовем ее Форма Студенты.
Для изменения внешнего дизайне формы используется Режим Макет (режим по умолчанию).
Для изменения и настройки программной составляющей формы (внутренние настройки, вычисляемые поля и т. д. ) используется Режим Конструктор:
Рис. 4. 61
Для внесения, изменения и удаления данных, используется Режим Форма:
Рис. 4. 62
Задача
Заполнить Поле Фото в таблице Студенты.
1. Переведем форму в Режим Форма;
2. Выберем подходящее фото, скопируем его;
3. Перейдем к форме. На поле Фото вызываем контекстное меню и выбираем Вставить:
Рис. 4. 63
Важно
Все изменения вносимые в Форме, Запросе автоматически отражаются и в исходной таблице!
При создании формы для связанных таблиц, внешний вид будет иметь немного другой вид.
Форма для таблицы Преподаватели (с вложенной таблицей):
Рис. 4. 64
Такого вида формы называются Подчиненная Форма. Т. е. на каждую запись из таблицы Преподаватели, в таблице отображаются только те студенты, у которых Код_преподавателя соответствует данному преподавателю.
§6. Отчеты
Создание отчетов
Задача. На основе одного из запросов создать отчет.
1. Выделим любой из запросов (например, Средний балл)
(рис. 4. 65).
Рис. 4. 64Выделение нужного объекта в БД
2. На вкладке «Создание», кликнем на пиктограмме ОТЧЕТ (рис. 4. 65).
Рис. 4. 65 Создание Отчета
3. Программа сама сформировала отчет, на основе выбранного объекта (в качестве объекта может выступать не только отчет, но и сами таблицы) (рис. 4. 66).
Рис. 4. 66 Отчет созданный автоматически по выбранному объекту
4. Отчет отобразится в режиме «Макета». Этот режим позволяет изменять «внешние» настройки отчета. Тут можно изменить шрифт, размеры колонок, добавить тему оформления
(рис. 4. 67).
Рис. 4. 66 Режим «Макет»
5. В режиме «Конструктор», макет выглядит более специфично и для работы в этом режиме, необходимы более обширные знания работы СУБД.
6. В режиме «Представление Отчета» можно просмотреть готовый документ (как он будет выглядеть при распечатке) (рис.
4. 67).
Рис. 4. 67 Режим отображения Отчета
|
|
|
