Методические указания и контрольные задания для студентов заочной формы обученияЛ.М. Патлань, Л.В. Киселёва, В.Ю. Анисимов
ОСНОВЫ РАБОТЫ С БАЗАМИ ДАННЫХ В MICROSOFT ACCESS Методические указания и контрольные задания для студентов заочной формы обучения
Основы работы с базами данных в Microsoft Access: Методические указания и контрольные задания для студентов заочной формы обучения /Сост. Л.М.Патлань, Л.В. Киселёва, В.Ю.Анисимов; Самарская государственная экономическая академия, 2005, 66с.
Рассмотрена структура и порядок проектирования банков данных. Описываются общие свойства баз данных и их место в структуре информационных систем. На примерах излагаются основные подходы к проектированию реляционных баз данных в СУБД MS Access и отдельных их компонентов: таблиц, запросов, форм, отчётов. Даётся понятие о языке запросов SQL. Рассматриваются возможности взаимодействия между базами данных различного формата и приёмы оптимизации размера файлов баз данных и скорости работы с ними. Содержит тексты практических работ и контрольных заданий. Методические указания предназначены студентам заочной формы обучения для дисциплины "Банки данных".
ОБЩИЕ РЕКОМЕНДАЦИИ По курсу "Банки данных" необходимо выполнить четыре практические работы, описанные в разделе 12 настоящих указаний и контрольное задание, вариант которого определяется как сумма последних двух цифр Вашей зачетной книжки (00 соответствует заданию №19). Варианты заданий приведены в разделе 13. При выполнении работ, кроме приведенных теоретических сведений, Вы можете использовать литературу из приложенного списка или любую другую. К зачету необходимо предъявить файл базы данных, разработанной Вами, со всеми спроектированными компонентами. Распечатки, указанные к выполнению в каждой из работ, должны быть введены в текст контрольного задания как окна Windows (за исключением распечаток работы №1, они должны быть напечатаны отдельно и вставлены в контрольное задание). Контрольное задание с фамилией студента и номером варианта должно быть оформлено в Word и отпечатано на стандартных листах формата А4. Оно должно содержать: · распечатки, требуемые в каждой из четырёх лабораторных работ; · ответ на теоретический вопрос - пункт a) контрольного задания; · окна компонентов базы данных – запросов, форм, отчётов, созданных как результат выполнения пункта b) контрольного задания. Эти окна должны быть вставлены в текст в двух вариантах: в режиме конструктора и в режиме создаваемого компонента. 1. ИНФОРМАЦИОННЫЕ СИСТЕМЫ И СТРУКТУРЫ БАНКОВ ДАННЫХ
Важнейшим условием обеспечения эффективного функционирования любой организации является наличие развитой информационной системы. Информационная система – система, реализующая автоматизированный сбор, хранение, обработку и манипулирование данными. Она включает в себя технические средства обработки данных, программное обеспечение и обслуживающий персонал. Современной формой информационных систем являются банки данных, которые включают в свой состав вычислительную систему, одну или несколько баз данных, систему управления базами данных и набор прикладных программ. Основные функции банков данных: · Хранение данных и их защита (в том числе; обеспечение доступа к данным только пользователям с соответствующими полномочиями); · Модификация данных (обновление, добавление и удаление); · Поиск и отбор данных по запросам пользователей; · Вывод результатов отбора в форме, удобной пользователю. База данных (БД) - организованная структура, предназначенная только для хранения данных и информации. В общем смысле термин "база данных" можно применить к любой совокупности связанной информации, объединенной вместе по определенному признаку (например, расписание движения поездов или книга регистрации заказов). Но обрабатываемая база данных должна быть организована определенным способом - структурирована. Как правило, база данных использует для хранения данных таблицы, состоящие из записей и полей. Система управления базами данных (СУБД) представляет собой совокупность программных и языковых средств, предназначенных для создания, сопровождения и использования БД. Прикладные программы (приложения) в составе банков данных служат для обработки данных, вычислений и формирования выходных документов по заданной форме. Приложение представляет собой программу или комплекс программ, использующих БД и обеспечивающих автоматизацию обработки информации. Приложения могут создаваться как в среде СУБД, так и внешних систем программирования (например, Delphi или C++), использующих свои средства доступа к БД. Приложения разрабатывают в случаях, когда требуется обеспечить удобство работы с БД неквалифицированным пользователям или когда интерфейс СУБД не устраивает пользователя. Процесс создания информационной системы имеет итерационный характер и обычно включает следующие этапы: · Проектирование БД; · Создание файла проекта БД; · Создание БД (формирование и связывание таблиц, ввод данных); · Создание меню приложения; · Создание запросов; · Создание экранных форм, отчетов; · Генерация приложения как исполняемой программы. Этот перечень этапов не является строгим в смысле очередности и обязательности, не все этапы поддерживаются различными СУБД. Во многих случаях для работы с БД достаточно только средств СУБД, например, возможностей создания запросов, форм и отчетов. По характеру использования СУБД делятся на персональные и многопользовательские. Многопользовательские СУБД функционируют по технологии "клиент-сервер", работают в неоднородной вычислительной среде (допускаются разные типы компьютеров и операционных систем), требуют мощных вычислительных ресурсов и имеют высокую стоимость. К современным персональным СУБД относятся Visual Fox Pro, Paradox, Сlipper, dBase, Access и др. Персональные СУБД обеспечивают возможность создания БД и недорогих приложений, работающих с этими БД. Основные свойства персональных СУБД: · Обеспечение целостности данных, означающее, что БД содержит полную и непротиворечивую информацию, оно достигается применением некоторых ограничений на хранимые данные (например, ограничение диапазонов значений, отсутствие повторяющихся записей и т.п.); · Обеспечение безопасности, достигаемое шифрованием программ и данных, применением паролей для доступа; · Поддержка взаимодействия с Windows-приложениями с использованием механизма OLE; · Поддержка работы в сети.
2. КОМПОНЕНТЫ СУБД ACCESS
Одной из современных систем управления базами данных является Access, входящая в пакет Microsoft Office Professional. Компонентами (в ряде источников - объектами) Access являются таблицы, запросы, формы, отчеты, страницы доступа к данным, макросы и модули. Эти компоненты, в свою очередь, состоят из объектов: надписей, прямоугольников и линий, полей и списков, кнопок, переключателей и флажков, графических и OLE- объектов и т.п. Таблица – основа любой базы данных, в Access вся исходная информация хранится в таблицах. Запрос – средство извлечения информации из БД, причем исходные данные для построения запросов могут быть распределены среди нескольких таблиц и (или) запросов. Формы используются для просмотра таблиц и ввода в них информации в экранном окне, они позволяют ограничить объем информации, отображаемой на экране, и представить ее в требуемом виде. Отчет используется для выдачи обработанной и сформатированной информации на печать или экран. Страницы доступа к данным представляют специальный тип Web-страниц, предназначенный для просмотра и работы через Интернет или интрасеть. Такие страницы содержат данные, хранящиеся в базах данных Microsoft Access или в базах данных Microsoft SQL Server. Страница доступа к данным может также включать данные из других источников, таких как Microsoft Excel. Макросы предназначены для автоматизации часто выполняемых операций, каждый макрос содержит одну или несколько макрокоманд, выполняющих определенные действия. В Access нет средств автоматической записи макросов (как в Excel), они выбираются из предлагаемого списка. Модуль – набор описаний, инструкций и процедур (т.е. действий) на языке Microsoft Visual Basic (VBA), собранных в одну программную единицу и сохраненных под общим именем. Существуют два типа процедур (как и в Excel): подпрограммы (Sub) и функции (Function). Модули описывают действия с определенными компонентами и объектами БД. Access хранит все свои компоненты в одном файле, и при открытии базы данных на экран выводятся все вкладки, соответствующие компонентам Access. Можно открывать разные компоненты внутри текущей базы данных. При закрытии файла БД изменения, внесённые в разрабатываемые компоненты и не затрагивающие структуру базы (например, новые данные в таблицах), сохраняются без дополнительных предупреждений. Как и в большинстве приложений Microsoft Office, новую базу данных можно создавать самостоятельно или воспользовавшись мастером создания базы данных.
3. ОСОБЕННОСТИ ПРОЕКТИРОВАНИЯ ТАБЛИЦ
Таблица – это совокупность записей одинакового формата, расположенных в произвольном порядке. Таблица содержит набор данных по конкретной теме, такой как "Клиенты", "Товары" или "Поставщики". Использование отдельной таблицы для каждой темы означает, что соответствующие данные сохранены только один раз, что делает базу данных более эффективной и снижает число ошибок при вводе данных. Данные в таблицах хранятся в записях (строках), состоящих из отдельных полей (столбцов). Формат каждой записи определяется структурой таблицы. Современные СУБД допускают возможность создания таблицы в нескольких режимах: конструктора, мастера, путем ввода данных. Удобнее и более логично создание новой таблицы в режиме конструктора - команда в открытой базе данных ТАБЛИЦЫ/СОЗДАНИЕ ТАБЛИЦЫ В РЕЖИМЕ КОНСТРУКТОРА. В этом случае сначала определяется структуратаблицы, а именно, количество полей, которое будет содержать каждая запись, и характеристики этих полей Характеристики поля включают в себя: имя поля, тип данных, свойства поля и описание. Имена полей могут иметь не более 64 символов и могут содержать буквы, цифры, пробелы, некоторые служебные символы. Имя поля не может начинаться с пробела, два поля в одной таблице не могут иметь одинакового имени. Имя поля уникально в пределах таблицы. Необходимо избегать употребления имен полей, совпадающих с именами встроенных функций или свойств Microsoft Access. Например, имена полей "Name" или "Type" совпадают с соответствующими именами свойств поля. Допустимые типы данных: · Текстовый, он может содержать буквы, цифры и специальные символы. Максимальная ширина поля 255 символов; · Поле MEMO, используется для хранения текста большого объема (до 65535 символов). Обычно в этих полях хранится, например, информация о служащих, фрагменты отчетов или историй болезни; · Числовой, содержит значения, над которыми нужно будет выполнять математические операции (1, 2, 4 или 8 байт). Если поле содержит почтовый индекс или номер телефона, то для его хранения следует использовать текстовое поле; · Даты/Времени (8 байт), может содержать любую дату и время от 1.01.100 до 31.12.9999. Дату и время можно хранить и в текстовых полях, но в этом формате Access автоматически проверяет правильность ввода (например, 13/41/97 или 25:12 недопустимы); · Денежный, аналогичен числовому типу (8 байт), но дополнительно автоматически добавляется фиксированное количество десятичных знаков после запятой, чтобы предотвратить ошибки округления; · Счётчик, используется для автоматической нумерации (идентификации) добавляемой записи, после добавления записи значения полей такого типа изменить нельзя; · Логический, используется для хранения данных, которые могут принимать одно из двух возможных значений (да/нет, вкл/выкл, 1/0), его размер 1 бит; · Объект OLE, содержит документ другого приложения Windows (фрагмент электронной таблицы, звукозапись, фотография сотрудника и т.д.), его размер ограничивается только объемом диска; · Гиперссылка - адрес информации, содержащейся в другом файле; · Мастер подстановок, служит для выбора значений из раскрывающегося списка, содержащего набор постоянных значений или значений из другой таблицы. Каждый из типов данных наделен своими собственными свойствами (характеристика " свойства поля "). Определение свойств полей упрощает ввод данных в таблицу, например, можно задать маску ввода в поле или указать значение, которое автоматически вводится в поле при создании новой записи. Наиболее часто используется свойство поля формат, позволяющее выбрать формат представления поля или создать его заново. Каждое поле может сопровождаться описанием (это необязательный, справочный параметр), текст описания появляется в строке состояния, когда курсор находится в соответствующем поле. Новая таблица может быть создана в нескольких режимах работы: конструктора таблиц, мастера таблиц, импорта таблицы из другого приложения, создания таблицы по умолчанию. Работа в режиме конструктора таблиц дает наиболее широкие возможности по определению параметров создаваемой таблицы, этот же режим используется и для последующей модификации структуры. При сохранении структуры таблицы (команда ФАЙЛ/СОХРАНИТЬ) Access предложит создать первичный ключ, содержащий информацию, однозначно идентифицирующую запись: это не может быть фамилия, имя, город и т.п. Иногда наилучшим решением проблемы является создание поля “Код” с типом данных “Счётчик”, и если не указать иного, Access это делает стандартно при сохранении структуры таблицы.
4. ВВОД И РЕДАКТИРОВАНИЕ ДАННЫХ В ТАБЛИЦЕ
Первоначальный ввод и редактирование данных, как правило, осуществляются в режиме таблицы (команда ФАЙЛ/ОТКРЫТЬ БАЗУ ДАННЫХ/ТАБЛИЦЫ). Для ввода новой записи в конце каждой таблицы всегда есть одна пустая строка, отмеченная *. Если в поле хранится текст большой длины и он не виден полностью в окне таблицы, его можно просмотреть в окне “Увеличение” задав команду <SHIFT>-<F2> в выбранном поле. Эта команда действует как при просмотре в режиме таблицы, так и в режиме формы, ее удобно использовать для ввода и просмотра полей MEMO. Для ввода в новую запись значения того же поля предыдущей записи удобно использовать команду <CTRL>- <’>. Клавишные команды для перемещения по таблице приведены в табл.1. Таблица 1
Некоторые из полей не допускают редактирования: например, в поле типа “Счётчик”: значения увеличиваются и сохраняются автоматически. Невозможно редактирование также блокированных или недоступных полей, для которых заданы соответствующие свойства ("БЛОКИРОВКА" – да, "ДОСТУП" – нет). Не редактируются и вычисляемые поля: они не существуют как отдельные, а вычисляются на основе других полей. Access допускает ряд изменений внешнего вида таблицы в режиме таблицы: изменение ширины колонки, высоты строки, порядка следования полей, скрытие и фиксацию столбцов, вставку нового поля, изменение шрифтов. Эти действия, а также удаление записей производятся стандартными средствами Windows. В Access удаление записей необратимо (в отличие от dBase, Fox Pro и т.д.), поэтому единственный способ восстановить удаленную запись – ввести ее снова! 5. ПОИСК ДАННЫХ В ТАБЛИЦАХ
Для поиска определенной записи в базах данных можно ввести номер нужной записи в поле номера записи в левом нижнем углу окна таблицы. С помощью диалогового окна "Поиск" можно найти записи с конкретным содержанием или определенные значения в полях. Это стандартное средство Windows - команда ПРАВКА/НАЙТИ. Имеется возможность перехода по записям по мере обнаружения каждого вхождения нужного элемента. Если требуется заменить конкретные обнаруженные при поиске значения, следует воспользоваться диалоговым окном "Замена". Искомая информация может быть и не уникальной, например, когда отыскиваются все клиенты, проживающие в одном городе. При поиске могут быть использованы и символы подстановки: * - любое количество букв, цифр или других символов; ? - любой один текстовый символ; [ ] - любой один символ из заключенных в скобки, например, если в образце для поиска задать Снег[ие]рев, будут найдены и Снегирев, и Снегерев; - - любой символ из диапазона, его следует указывать по возрастанию, т.е. [Е-У], а не [У-Е]; # - любая одна цифра. Ускорить поиск нужной записи можно и с применением сортировкиданных по одному или нескольким полям таблицы - команда ЗАПИСИ/СОРТИРОВКА. Чтобы при поиске в таблицах видеть только записи, определяемые заданным критерием, используется фильтрация (ЗАПИСИ/ФИЛЬТР). Например, для того, чтобы из таблицы, приведённой на рис. 5.1, выбрать записи для клиентов, проживающих в Самаре, достаточно открыть нужную таблицу, в поле "ГОРОД" выделить запись "Самара" и задать команду ЗАПИСИ/ФИЛЬТР/ФИЛЬТР ПО ВЫДЕЛЕННОМУ.
Рис. 5.1. Таблица "КЛИЕНТЫ" – исходный вид Результат действия этой команды показан на рис. 5.2.
Рис. 5.2. Таблица "КЛИЕНТЫ" после фильтрации Если необходимо задать критерии выборки записей более, чем по одному полю, удобно применить команду ЗАПИСИ/ФИЛЬТР/РАСШИРЕННЫЙ ФИЛЬТР. Эта команда выводит бланк фильтра (см. рис. 5.3), в который следует ввести поля (перетаскиванием мышью или двойным щелчком) и условия отбора по соответствующему полю (здесь выбираются клиенты, чья фамилия начинается на "П" и имеющие кредит выше 1000).
Рис. 5.3. Пример бланка фильтра После заполнения бланка задается команда ЗАПИСИ/ПРИМЕНИТЬ ФИЛЬТР. Результат действия расширенного фильтра приведён на рис. 5.4.
Рис. 5.4. Результат действия на таблицу "КЛИЕНТЫ" фильтра рис.5.3. Результаты фильтрации отображаются непосредственно в таблице и, как правило, не сохраняются для дальнейшего использования. Чтобы снять фильтр и снова вывести на экран все записи, ранее отображавшиеся в таблице, следует применить команду ЗАПИСИ/УДАЛИТЬ ФИЛЬТР. Условия фильтрации (и обычного, и расширенного фильтра) сохраняются при сохранении таблицы, если предварительно фильтр не удалён. Их можно посмотреть командами ЗАПИСИ/ФИЛЬТР/ИЗМЕНИТЬ ФИЛЬТР или ЗАПИСИ/ФИЛЬТР/РАСШИРЕННЫЙ ФИЛЬТР. Таким образом, в случае необходимости повторной фильтрации по тем же критериям фильтр заново создавать не нужно, достаточно в открытой таблице применить команду ЗАПИСИ/ПРИМЕНИТЬ ФИЛЬТР. Описанные выше приёмы фильтрации применяются и при поиске данных в запросах и формах. Перечисленные простейшие средства поиска данных используются в небольших базах данных и для простых критериев поиска. В более сложных случаях следует использовать запросы (см. раздел 7). 6. ОСОБЕННОСТИ ПРОЕКТИРОВАНИЯ РЕЛЯЦИОННЫХ БАЗ ДАННЫХ
6.1.Понятие реляционной базы данных Можно представить себе одну большую таблицу, в которой регистрируются предварительные заказы товаров, их продажа и подробные сведения о клиентах, совершающих эти сделки, а также о потенциальных клиентах, товарах и поставщиках этих товаров. Структура такой таблицы должна быть примерно такая, как в табл.2.
Таблица 2
В этой таблице содержится много повторяющейся информации, например, сведения о каждом покупателе повторяются для каждого сделанного им заказа или произведенной покупки. Такая структура может явиться причиной ряда проблем. При вводе повторяющихся данных происходит лишняя затрата времени, возрастает и вероятность возникновения ошибок. При изменении данных лишь по одному из полей (например, адреса клиента или цены товара) возникает необходимость корректировки всех записей для этого клиента. Происходит нерациональное использование дискового пространства и увеличение времени выборки информации за счет увеличения размеров базы данных. Поэтому целесообразно разбить эту таблицу на четыре таблицы “КЛИЕНТЫ”, “ЗАКАЗЫ И ПРОДАЖИ”, “ТОВАРЫ” и "ПОСТАВЩИКИ", установив связи между ними, например, так, как показано на рис. 6.1.
Рис. 6.1. Схема данных в реляционной БД Реляционная база данных – совокупность некоторых таблиц с данными, взаимосвязанных между собой определёнными логическими соотношениями (relation).
6.2. Первичные ключи и индексирование Access относится к реляционным базам данных, информация в которых хранится в связанных таблицах. Каждая таблица должна иметь уникальное имя. Для организации связи между таблицами каждая таблица должна содержать одно или несколько полей, однозначно определяющих каждую запись в таблице. Такие поля называют первичным ключом таблицы. Если для таблицы определен первичный ключ, то Microsoft Access предотвращает дублирование ключа или ввод нулевых значений в эти поля. В Access допускается определение первичных ключей трех типов: · Ключевые поля счетчика. Поле счетчика можно задать таким образом, чтобы при добавлении каждой новой записи в таблицу в это поле автоматически вносился порядковый номер. Указание такого поля в качестве ключевого является наиболее простым способом создания первичного ключа. Если до сохранения созданной таблицы ключевые поля не были определены, Access предлагает создать ключевое поле автоматически. При нажатии кнопки "Да" будет создано ключевое поле счетчика. · Простой ключ. Если поле содержит уникальные значения, такие как коды товара или инвентарные номера, то это поле можно определить как первичный ключ. В качестве ключа можно определить любое поле, содержащее данные, если это поле не содержит повторяющихся или нулевых значений. · Составной ключ. В случаях, когда невозможно гарантировать уникальность значений каждого поля, существует возможность создать ключ, состоящий из нескольких полей. Для составного ключа существенным может оказаться порядок образующих ключ полей. Сортировка записей осуществляется в соответствии с порядком ключевых полей, отображаемых в бланке режима конструктора таблицы. Примером применения составного ключа может служить таблица учёта товаров на складе, показанная на рис 6.2. Чтобы однозначно идентифицировать каждый товар, здесь недостаточно одного (основного) инвентарного номера, задаваемого по наименованию товара. Необходим ещё и дополнительный инвентарный номер, задаваемый по цене товара.
Рис. 6.2. Пример таблицы, требующей создания составного ключа Для создания составного ключа следует в режиме конструктора выделить нужные поля, удерживая клавишу CTRL, а затем выполнить команду ПРАВКА/КЛЮЧЕВОЕ ПОЛЕ (кнопка Если порядок полей в составном первичном ключе должен отличаться от порядка полей в таблице, следует выполнить команду ВИД/ИНДЕКСЫ (кнопка
Рис. 6.3. Окно для изменения порядка полей в составном ключе Связь между таблицами осуществляется через совпадающие значения одного или нескольких ключевых полей. Ключи хранятся в индексированном (упорядоченном) виде, что обеспечивает быстрый доступ к записям таблицы во время поиска. Если приходится часто искать записи по полю, не являющемуся ключевым, ускорить поиск можно, проиндексировав таблицу по соответствующим полям. Индексирование позволяет поддерживать записи упорядоченными по выбранному полю. Индекс можно создать по одному (простой индекс) или нескольким полям (составной индекс). Для создания простого индекса используется свойство поля “Индексированное поле”, оно может содержать и не уникальные значения, например, повторяющиеся фамилии. Возможны три типа индексации: Нет, Да (Допускаются совпадения), Да (Совпадения не допускаются). При индексировании по умолчанию задаётся порядок сортировки по возрастанию. Пример окна для создания индексированного поля типа "Да (Совпадения не допускаются)" приведён на рис. 6.4 – это поле "КОД ТОВАРА" таблицы "ТОВАРЫ".
Рис. 6.4. Вид таблицы "ТОВАРЫ" в режиме конструктора Такой тип индексации описывает уникальное поле, т.е. товары могут иметь повторяющиеся значения в полях "КАТЕГОРИЯ", "НАИМЕНОВАНИЕ" и даже "ЦЕНА", но поле "КОД ТОВАРА" однозначно идентифицирует этот товар.
6.3. Связи в базе данных Преимуществом установки связей является поддержка целостности базы данных: данные автоматически защищаются от изменения и удаления тех записей, которые могут нарушить связи между таблицами. Например, невозможно удалить код клиента из таблицы “КЛИЕНТЫ”, если в таблице “ЗАКАЗЫ И ПРОДАЖИ” имеются сведения об операциях с данным клиентом. Связываемые поля обычно имеют одинаковые имена (это не обязательно), и одинаковые типы данных (это обязательно!). Чаще всего связывают ключевое поле одной таблицы с совпадающим полем другой. ACCESS поддерживает три типа отношений между таблицами: · Один-к-одному, когда каждая запись в одной таблице соответствует только одной записи в другой таблице (оба поля уникальные); · Один-ко-многим (например, может быть много записей в таблице "ЗАКАЗЫ И ПРОДАЖИ" для одного и того же клиента); · Много-ко-многим. Создание и удаление связей осуществляется командой СЕРВИС/СХЕМА ДАННЫХ. Создание и удаление связей между открытыми таблицами не допустимо, их нужно предварительно закрыть. В окне “Схема данных” связываемое поле одной таблицы перемещается на соответствующее поле второй таблицы, при этом открывается окно "Изменение связей". Тип отношения устанавливается автоматически с учётом уже заданного свойства связываемых полей "Индексированное поле" (см. рис. 6.5)
Рис. 6.5. Пример окна "Изменение связей" для связывания таблиц "ТОВАРЫ" и "ЗАКАЗЫ И ПРОДАЖИ" В этом же окне необходимо установить флажок “Обеспечение целостности данных” и нажать на кнопку “Создать”. После создания связи в окне “Схема данных” связанные поля будут соединены линией с соответствующими обозначениями. Чтобы преодолеть ограничения на удаление или изменение связанных записей, следует при установлении связи включить режимы "Каскадное обновление связанных полей" и "Каскадное удаление связанных записей". 7. ПРОЕКТИРОВАНИЕ ЗАПРОСОВ
7.1. Порядок создания запроса и условия отбора Запросы, так же, как и фильтры, служат для отбора записей по заданным критериям. Но в отличие от инструмента "Фильтр" запрос – это компонент БД, допускающий многократное использование. Запросы обеспечивают доступ сразу к множеству полей и записей одной и более таблиц, отображают данные в заданном порядке, а также производят вычисления с использованием отобранных записей. Для построения запроса в Access могут быть использованы мастер запросов или конструктор запросов. Исходными компонентами для проектирования запросов могут служить таблицы, уже существующие запросы или те и другие одновременно. Создание запроса обычно начинается с команды ЗАПРОС/СОЗДАТЬ/КОНСТРУКТОР, далее в окне “Добавление таблицы” следует выбрать исходные компоненты нового запроса (таблицы и/или запросы) и нажать последовательно кнопки "Добавить" и "Закрыть". В возникшем окне бланка запроса следует занести: поля результатов запроса (строка "Поле"), необходимость вывода на экран полей результатов запроса (строка "Вывод на экран"), критерии выборки записей (строка "Условия отбора"), критерий упорядочивания результатов отбора (строка "Сортировка") (см. рис. 7.1).
Рис. 7.1. Пример бланка запроса Запуск запроса на выполнение - команда ЗАПРОС/ЗАПУСК (кнопка Критериями могут быть как простейшее точное совпадение значений, так и более сложные условия для работы с текстом, числовыми величинами, датами или комбинациями типов данных. Примеры некоторых типичных выражений, используемых для формирования условий в запросах, приведены в табл. 3. Таблица 3
Часто возникают ситуации, когда необходимо задать условия для нескольких полей таблицы или несколько условий для одного поля. Такие запросы называются И-запросами (если запись выбирается в случае выполнения всех условий) или ИЛИ-запрос (если запись выбирается при выполнении хотя бы одного из указанных условий). Логическое условие может содержать функции, операторы сравнения, операторы Or, And, Not и скобки для изменения порядка выполнения выражения.
7.2. Запросы с применением вычислений
Access позволяет выполнять в запросах вычисления двух типов – встроенные и пользовательские. Они применяются соответственно в итоговых запросах и запросах с вычисляемыми полями. При этом в обоих случаях в результирующей таблице отображаются данные, отсутствующие в исходной. Итоговые запросы - это запросы, выполняющие вычисления в группах записей, они создаются командой ВИД/ГРУППОВЫЕ ОПЕРАЦИИ. Наиболее употребительные типы групповых операций: Sum - определение суммы значений по полю; Avg - среднее значение по полю; Min - минимальное значение по полю; Max - максимальное значение по полю; Count – количество записей; First – первое значение; Last – последнее значение. В примере рис. 7.2 отображён бланк итогового запроса, вычисляющего сумму затрат каждого клиента.
Рис. 7.2. Бланк запроса, использующего групповые операции Результат запуска этого запроса – на рис. 7.3.
Рис. 7.3. Результат выполнения запроса, использующего групповую операцию Sum При разработке запроса с вычисляемыми полями новое вычисляемое поле создается прямо в свободном поле бланка запроса с помощью выражения. Выражение содержит формулы, в которые могут входить поля, константы, встроенные функции, связываемые операторами (+,-,/,*,&,<,>,<>,And,Or,Not,Like, круглые скобки). Для построения выражения удобно использовать окно "Построитель выражений", оно вызывается кнопкой Пример окна "Построитель выражений" для вычисления стоимости покупки партии одного товара, показан на рис. 7.4. Здесь стоимость покупки партии товара вычисляется как произведение цены товара из таблицы "ТОВАРЫ" и количества проданного товара из таблицы "ЗАКАЗЫ И ПРОДАЖИ".
Рис. 7.4. Пример окна "Построитель выражений" Синтаксис встроенных функций Access похож на синтаксис, используемый Excel. Они разделены на категории и могут содержать разное количество аргументов. На рис. 7.5 в правом поле окна "Построитель выражений" виден список функций категории Дата/время, а в поле выражения выбрана функция Now(), возвращающая текущую дату, она не содержит аргументов.
Рис. 7.5. Окно "Построитель выражений", в котором выбирается встроенная функция Now(). Выражение может содержать и вложенные функции. На рис. 7.6 приводится пример выражения, построенного для начисления наценки в 5% на покупку, совершённую в декабре. Здесь используются встроенные функции IIf (ЕСЛИ) и Month (МЕСЯЦ), вложенная в аргумент - условие функции IIf.
Рис. 7.6.Пример выражения с вложенными функциями Результат реализации этого выражения отображён на рис. 7.7. Видно, что для клиента, совершившего покупку в
|
или
).
на панели инструментов), чтобы открыть окно "Индексы". В этом окне и следует указать другой порядок полей для индекса с именем "PrimaryKey" (см. рис.6.3).
). Созданный запрос можно использовать и в дальнейшем, для этого нужно ему присвоить имя и сохранить, при этом имя запроса не должно повторять имя таблицы. Этот сохраненный запрос можно в дальнейшем и модифицировать.
на стандартной панели инструментов. В окне можно выбрать источник данных (таблицы, запросы, функции, константы), поля в этих источниках, конкретные математические и логические операторы, функции, необходимые для построения выражения.
