Создание запроса на выборку с помощью МастераПри создании query необходимо определить: -Поля в базе данных, по которым будет идти поиск информации -Предмет поиска в базе данных -Перечень полей в результате выполнения запроса В окне база данных выбрать вкладку Запросы и дважды щелкнуть на пиктограмме Создание query с помощью мастера, появится окно Создание простых запросов.
В окне мастера выбрать необходимую таблицу (таблицу - источник) из опции Таблицы и запросы и выбрать поля данных. Если query формируется на основе нескольких таблиц, необходимо повторить действия для каждой таблицы – источника. Затем в окне Мастера надо выбрать подробный или итоговый отчет и щелкнуть на кнопке Далее. После этого необходимо задать имя запроса и выбрать один из вариантов дальнейшего действия: Открыть query для просмотра данных или Изменить макет запроса и нажать кнопку Готово. В результате чего получите готовый query.
35. Создание запросов выбора в режиме Конструктора. Вычисления в запросах. Групповые операции в запросах. Для создания запросов к БД существует специальный язык запросов. Он называется SQL (StructuredQueryLanguage – структурированный язык запросов). Но MSAccess использует более простое средство, которое называется бланком запроса по образцу. С его помощью можно сформировать запрос простымиприемами, перетаскивая элементы запроса между окнами в Конструкторе запросов. Конструктор запросов позволяет создавать новые и изменять существующие запросы, поэтому он является основным способом. При создании запросов в режиме Конструктора открывается специальный бланк, называемый бланком запросов по образцу. Этот бланк состоит из двух областей. В верхней части отображаются структуры таблиц, к которым адресован запрос, а нижняя часть представляет собой таблицу описания запроса, которая имеет 5 основных строк. Количество столбцов в запросе определяется количеством выбранных полей, которые будут использованы в запросе. Назначение строк в бланке Конструктора запросов: 1-я строка – Поле предназначена для ввода имени поля запроса, переименования его названия, создания вычисляемого выражения; 2-я строка – Имя таблицы предназначена для задания имени таблицы – источника данных поля запроса; 3-я строка – Сортировка предназначена для указания порядка сортировки в данном поле. Может принимать следующие значения: по возрастанию, по убыванию, отсутствует; 4-я строка – Вывод на экран содержит флажок, указывающий на то, будут ли выводиться значения поля на экран при просмотре результатов запроса; 5-я строка – Условие отбора содержит критерий, по которому будет происходить отбор записей в результирующую таблицу. Пример:
Потом можно закрыть окно Конструктора запроса. Перед закрытием MS Access запросит имя, под которым сохранить запрос. Запросы всегда следует называть в контексте с той информацией, которую они возвращают, поэтому данный запрос можно назвать «Бобров». Для того чтобы просмотреть результат работы запроса его либо просто открывают двойным щелчком мыши по названию запроса, либо выбирают опцию «Открыть» правой кнопкой мыши. ВЫЧИСЛЕНИЯ В ЗАПРОСАХ. Поле, содержимое которого является результатом расчета, основанного на математических операциях с участием значений полей БД, числовых и текстовых литералов, встроенных функций и т.п., называется вычисляемым полем. Вычисляемое поле существует только в результирующей таблице запроса, т.е. в исходных таблицах БД такое поле не создается и данных в существующих полях не изменяет. Для создания запроса, производящего вычисления в полях, служит тот же бланк запроса Конструктора. Разница только в том, что в одном из столбцов вместо имени поля записывается формула для расчета новых значений поля. В формулу чаще всего входят заключенные в квадратные скобки названия полей БД и знаки математических операций, например, можно создать новое поле: Сумма продажи: [Цена] * [Количество], где Сумма продажи – это название нового вычисляемого поля запроса, которое указывается вначале строки перед двоеточием, за которым следует непосредственно формула для расчета. Возможности вычисляемых полей распространяются и на операцию конкатенации (сцепки) нескольких текстовых полей. Поэтому, чтобы вывести, например, Фамилию, Имя и Отчество в одном поле, создают выражение в поле запроса по формуле: ФИО: [Фамилия]&" "&[Имя]&" "&[Отчество]. При этом в строке Имя таблицы необходимо выбрать таблицу «Читатели». Для построения условий отбора могут применяться пять групп (критериев) условий отбора: 1. Сравнение, используются элементарные математические функции (=, <, >, <=, >=, <>); 2. Принадлежность диапазону, используется оператор Between … and …; 3. Соответствие шаблону, используется оператор Like "шаблон"; 4. Принадлежность множеству значений, используется оператор In (список значений через запятые). 5. Проверка на пустые значения, операторы NotNull, Null. Кроме этого все перечисленные условия могут быть скомбинированы логическими функциями and(и), or(или), NOT (не). ГРУППОВЫЕ ОПЕРАЦИИ В ЗАПРОСАХ. Для задания вычислений итоговых значений в обычный бланк Конструктора запросов добавляют возможность выбора групповой операции. Это делается с помощью иконки (рис. 6) на панели инструментов Конструктора запросов, в результате в бланке запроса появляется строка Групповая операция:.
Если выполнить запрос с этой строкой, то получится набор записей, содержащий по одной строке для каждого уникального значения поля запроса, но без итогов. Для вычисления итогов операция Группировка в строке Групповая операция: должна быть заменена на конкретную итоговую функцию. MSAccess предоставляет девять встроенных функций, обеспечивающих выполнение групповых операций, которые можно выбрать в выпадающем списке столбцов в строке Групповая операция:. К итоговым функциям относятся: Sum – возвращает сумму всех значений данного поля в каждой группе. Используется только для числовых или денежных полей. Avg – возвращает среднее арифметическое всех значений данного поля в каждой группе. Используется только для числовых или денежных полей. При вычислении функции исключаются значения Null. Min – возвращает наименьшее значение, найденное в данном поле внутри каждой группы, для числовых полей возвращает наименьшее значение, для текстовых полей – наименьшее из символьных значений независимо от регистра, игнорирует значения Null. Мах – возвращает наибольшее значение, найденное в данном поле внутри каждой группы, для числовых полей возвращает наибольшее значение, для текстовых полей – наибольшее из символьных значений независимо от регистра, игнорирует значения Null. Count – возвращает число записей, в которых значения данного поля отличны от Null. Чтобы подсчитать число записей в каждой группе с учетом значений Null в строку Поле: вводится выражение Count (*). StDev – возвращает стандартное отклонение всех значений данного поля в каждой группе. Эта функция применяется только к числовым или денежным полям. Если в группе меньше двух строк, возвращает значение Null. Var – возвращает дисперсию значений данного поля в каждой группе. Эта функция применима только к числовым или денежным полям. Если в группе менее двух строк, возвращает значение Null. First – возвращает первое значение данного поля в группе. Last – возвращает последнее значение данного поля в группе.
36. Перекрестные запросы. Запросы действия и параметрические запросы ПЕРЕКРЕСТНЫЕ ЗАПРОСЫ. Перекрестные запросы используют для расчетов и представления данных в табличной структуре, облегчающей их анализ. Перекрестный запрос подсчитывает сумму, среднее число значений или выполняет другие итоговые расчеты, после чего результаты группируются в виде таблицы по двум полям данных, один из которых определяет загоовки столбцов, а другой – заголовки строк новой таблицы. Перекрестный запрос создается с помощью соответствующего Мастера или самостоятельно в режиме Конструктора запроса. В бланке Конструктора запроса нужно указать поля, значения которых будут заголовками столбцов и строк, а также поле, значения которого следует использовать в вычислениях. В перекрестном запросе, в котором заголовками столбцов являются, например, названия месяцев, можно выводить их в хронологическом, а не в алфавитном порядке, как это делается по умолчанию, или выводить только конкретно указанные месяцы. Для этого используют окно свойств перекрестного запроса, открытого в режиме Конструктора. Окно открывается выбором опции Свойства, при нажатии правой кнопки мыши в фоновой области окна Конструктора запроса (вне бланка запроса и списка полей). В появившемся окне свойств в строке Заголовки столбцов вводят заголовки столбцов перекрестной таблицы, в том порядке, в котором их следует выводить в запросе. Заголовки столбцов разделяют точкой с запятой. Введенные заголовки столбцов должны точно соответствовать заголовкам столбцов в запросе в режиме таблицы. ЗАПРОСЫ-ДЕЙСТВИЯ позволяют модифицировать данные в таблицах: удалять, обновлять, добавлять записи. Эти запросы называют еще запросами на изменение и выделяют четыре типа: Запросы на создание таблицы – создают таблицы на основании данных, содержащихся в результирующем множестве запроса. Запросы на добавление записей – позволяют добавлять в таблицу записи, создаваемые запросом. Запросы на обновление – изменяют значения существующих полей в соответствии с заданным критерием. Запросы на удаление – удаляют записи из одной или нескольких таблиц одновременно. Запросы на изменение. Позволяют изменять таблицы БД – обновлять, дополнять новыми записями, удалять некоторые записи, а также создавать новые таблицы в БД. Для создания запросов на изменение в MSAccess необходимо открыть Конструктор запросов, как при создании обычного запроса, а затем на панели инструментов Конструктора в разделе Запросы выбрать иконку с нужным типом запроса на изменение (рис. 13).
Рис. 13 Запрос на обновление изменяет значения заданных полей в определенной группе записей одной или нескольких таблиц. Например, Конструктор запроса на обновление: увеличить стоимость московских издательств на 10%, будет выглядеть, как показано на рис. 14.
Запрос на создание таблицы позволяет создать новую таблицу на основании всех либо какой-то части данных имеющихся таблиц. Например, запрос на создание таблицы: создать архивную таблицу, содержащую все записи о выдаче книг за два года, например, 1996 и 1997 год, будет выглядеть, как показано на рис. 15. Запрос на удаление удаляет определенную группу записей из одной или нескольких таблиц. С помощью такого запроса можно удалить только всю запись, а не отдельные поля внутри ее. Например, запрос на удаление: удалить все заархивированные записи из таблицы «Выдача книг», будет выглядеть, как показано на рис. 16.
Рис. 16. Запрос на удаление Запрос на добавление добавляет группу записей из одной таблицы в конец другой таблицы. Запрос полезен для объединения таблиц, хранящихся в разных файлах. Запрос на добавление: восстановить в таблицу «Выдача книг» записи из архива, будет выглядеть, как показано на рис. 17.
Рис. 17. Запрос на добавление
ЗАПРОС С ПАРАМЕТРАМИ – это запрос, при выполнении которого в диалоговом окне пользователю выдается приглашение ввести какие-либо данные, необходимые для формирования результирующего множества запроса. Количество приглашений для ввода данных в одном запросе практически не ограничено. Запросы с параметрами удобно использовать в качестве основы для формирования форм и отчетов. Например, можно создать месячный отчет о заказах. При выполнении данного отчета на экране будет появляться приглашение ввести месяц года, заказы за который интересуют пользователя. После ввода месяца пользователю представляется требуемый отчет. Для создания параметрического запроса необходимо сначала создать любой другой запрос или использовать уже имеющийся в БД. Для каждого поля, в которое предполагается вводить параметры, в строку Условие отбора: добавляют текст приглашения, заключенный в квадратные скобки [ ]. Это приглашение будет выводиться при запуске запроса. Текст подсказки должен отличаться от имени поля, но может включать его. При создании параметрических запросов необходимо определять тип данных для вводимых параметров. Это делается с помощью пункта меню Параметры запроса, который открывает дополнительное окно. В предложенном окне в колонке Параметр указывается содержимое квадратных скобок в точном соответствии с бланком запроса, а затем выбирается нужный тип данных
37. Создание и изменение отчётов в СУБД Access. Работа с надписями и полями. Размещение графических материалов в отчёте. Создание вычисляемых полей. Отчет является эффективным средством представления данных в печатном формате. Имея возможность управлять размером и внешним видом всех элементов отчета, пользователь может отобразить сведения желаемым образом. Большинство отчетов являются присоединенными к одной или нескольким таблицам и запросам из БД, так как источником записей отчета являются поля в базовых таблицах и запросах. Отчет не должен включать все поля из каждой таблицы или запроса, на основе которых он создается. Отчет получает данные из базового источника записей, но другие данные, такие, как заголовок, подзаголовки, визы исполнителей, дата и номера страниц, сохраняются в Конструкторе отчета. В MSAccess используется несколько видов отчетов: Одноколонный отчет (в столбец) – это столбец текста, содержащий названия полей и их значения из всех записей таблицы или запроса. Многоколонный отчет – создается из отчета в одну колонку и позволяет вывести данные отчета в несколько колонок. Табличный отчет – отчет, имеющий табличную форму. Отчет с группировкой данных и подведением итогов – создается из табличного отчета объединением данных в группы с подсчетом итогов. Перекрестный отчет – строится на основе перекрестных запросов и содержит итоговые данные. Составной отчет – отчет, имеющий сложную структуру, включающий один или несколько отчетов. Отчет, полученный слиянием документов с MSWord (составной документ). Почтовые наклейки – специальный тип многоколонного отчета, предназначенный для печати имен и адресов групп. Отчет по связанным таблицам – отчет, позволяющий выводить данные из нескольких таблиц, имеющих связь типа «один-ко-многим». Отчет может состоять из следующих разделов: Заголовок отчета – отображается только на первой странице и определяет высоту области заголовка отчета, который может содержать текст, графику и другие элементы управления. Верхний колонтитул – выводится на верху каждой страницы; как правило, содержит заголовки столбцов. Заголовок группы – печатается перед первой записью, входящей в группу; обычно содержит итоговые данные по записям, входящим в группу. Область данных – предназначена для отображения записей источника данных отчета. Может содержать вычисляемые поля, предназначенные для отображения в отчетах значений выражений на основе исходных данных. Область примечания группы – отображается после обработки последней записи группы; обычно содержит итоговые данные по записям, входящим в группу. Нижний колонтитул – выводится внизу каждой страницы; содержит, например, номер страницы отчета, дату печати отчета и т. д. Раздел примечаний – отображается только внизу последней страницы; сюда помещают поля с итоговыми значениями по всем записям, включенным в отчет. Отчет может быть создан при помощи Мастера отчетов (автоотчет), или в режиме Конструктора. Обычно используют оба способа. Мастер отчетов позволяет ускорить процесс создания отчета, работа в нем производится в пошаговом режиме в диалоге с пользователем. Доработать созданный мастером отчет можно в режиме Конструктора. В режиме Конструктора доступны такие свойства отчета, как Сортировкаи группировка данных, которые расположены на панели инструментов Конструктора отчетов. В диалоговом окне Сортировка и группировка можно определить поле или выражение, по которому будут сгруппированы данные, а также сгруппированные данные можно отсортировать по возрастанию или убыванию. Например, можно группировать данные двумя способами – по категориям и. по диапазону значений, который может быть как числовым, так и алфавитным.
|
Рис.14. Запрос на обновление
Рис. 15. Запрос на создание таблицы
