Запросы на выборку в Access. Как задать условия отбора записей в запросе? Условия отбора в запросах access все кроме

В большинстве современных СУБД имеется свой вариант QBE, незначительно отличающийся от первого описания QBE, предложенного в конце 70-х годов ХХ в. Рассмотрим некоторые возможности QBE СУБД MS Access.

Используем для примера таблицу БД, которая относится к торговле (рис. 3.10). Имя таблицы TYPE (типы товаров). Она имеет столбцы: товар – названия товара; цвет – его цвет; стоимость – стоимость товара.

Выборка данных может осуществляться по следующим вариантам:

1. Простая выборка, например: «Вывести товары зеленого цвета из таблицы TYPE».

2. Простая выборка с упорядочиванием.

3. Выборка с квалификаторами (условиями). Выбор записей из исходной таблицы может быть основан на: а) точном совпадении; б) частичном совпадении; в) сравнении.

Запросы позволяют получать результирующие таблицы, поля которых удовлетворяют определённым условиям (критериям). Эти условия задают в бланке запроса в строке Условия отбора. Условиями отбора являются логические выражения, состоящие из операторов и операндов. Используются операторы сравнения =, < , >, <> (не равно), Between, In, Like и и логические операторы And, Or, Not. Допускается применять шаблоны с подстановочными символами.

Рис. 3.10. Пример таблицы БД

Если точное значение не известно или необходимо вводить значение не полностью, то удобно использовать шаблон (образец) с подстановочными символами (знаками). Примеры подстановочных символов:

* - соответствует любому количеству любых символов. Пример: 77* - для нахождения всех телефонов с номерами, начинающимися на 77.

? - соответствует одному текстовому символу. Пример: 77-4?-0? - для нахождения всех телефонов с номерами, содержащими четыре указанные цифры.

Шаблоны используются совместно с оператором Like . Этот оператор позволяет создавать шаблоны, использующие подстановочные символы при поиске в текстовых полях. Например, фамилия сотрудника известна неточно. Это может быть Петров, Петровский, Пеотровский и т.п. Тогда следует использовать для выборки в строке Условие запись Like "Пе*".

Известно, что имя состоит из 4-х букв. Тогда подойдет запись Like "????".

Оператор Between задаёт интервал значений. Например, Between 1 And 5

(указанные края интервалов в выборку включаются).

Оператор In выполняет проверку на равенство любому значению из списка, заданному в круглых скобках. Например, In("ручка";"духи").

Логические операции И, ИЛИ могут быть заданы явно в выражении условия с помощью операторов AND и OR . Например, «духи» OR «карандаш».

В качестве операндов в запросах могут использоваться литералы, константы, идентификаторы (ссылки).

Литералами являются конкретные значения, воспринимаемые системой так, как они записаны. Литералом может быть число, дата, строка. Например, 1146, #31.01.02 #, "Липецк".

Константами являются постоянные значения, которые определены в Access. Например, True, False, Null, Да, Нет.

Идентификатор осуществляет ссылку на поле, элемент управления или свойство. Идентификаторами могут быть имена полей, таблиц, форм, и так далее. Они заключаются в квадратные скобки. Ссылка на конкретное значение должна указывать на его местоположение в иерархии объектов в БД. Ссылка на поле в таблице имеет вид [Имя таблицы]![Имя Поля]. Например, [Сотрудники]![Фамилия].

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

Условие точного несовпадения значений одного из полей. Если в таблице надо найти записи, значения полей которых не удовлетворяют определенному условию, то используется оператор Not . Оператор Not или <> вводится перед сравниваемым значением. Пример. Отобрать все записи таблицы TYPE, за исключением записей «карандаш» в поле Товар . Для этого в бланке запроса в столбце поля Товар в строке Условие отбора вводится Not " карандаш".

Условие неточного совпадения. Выбор записей по условию неточного

совпадения значений можно осуществить, используя оператор Like. Этот оператор позволяет найти требуемые записи, зная лишь приблизительное написание текстовой величины. В операторе Like можно использовать шаблоны с подстановочными символами, что расширяет возможности поиска записей при неточном задании условий. Пример условия отбора: Like “[д-к]*”. Здесь - (минус) соответствует любому символу из диапазона. Диапазон необходимо указывать по возрастанию (д-к, но не к-д).

Выбор записей по диапазону значений. Для задания диапазона значений в окне конструктора запросов используются операторы >, <, Between, Like. Их можно применять с текстовыми, числовыми полями и полями типа даты. Примеры: в строке Условие отбора возможно ввести: >100.00 AND < 500.00; Between # 01.01.97 # AND #31.03.97#; Like “*”. Напомним, что символ # применяется для данных типа «дата/время».

Пример 1. Запрос с точным несовпадением в одном поле и с условием сравнения в другом поле. Запрос на выборку из таблицы TYPE всех не красных товаров, цена которых более 5, представлен на рис. 3.11 (запрос создан в режиме Конструктора ).

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

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

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

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

Добавление полей на основе условий отбора;

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

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

Создание таблицы для экспорта в другую базу данных Microsoft Access;

Создание отчетов, содержащих данные нескольких таблиц;

Создание резервной копии таблицы.

Создание архивной таблицы, содержащей старые записи;

Повышение быстродействия форм и отчетов, базирующихся на многотабличных запросах или выражениях SQL.

Выбор данных из одной таблицы

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

После выполнения запроса на выборку Microsoft Access создает набор записей, содержащий отобранные данные, с которыми можно работать также как и с таблицей.

Проще всего создать запрос на основе одной таблицы так: открыть окно базы данных, выбрать в окне базы данных вкладку Запросы , нажать кнопку Создать, в новом окне выбрать режим Конструктор и Ok . В следующем окне "Добавление таблицы" выбрать нужную таблицу, а затем нажать кнопки Добавить и Закрыть .

Окно конструктора запросов (рис.10.1) разделено на две части. В верхней части находятся списки полей таблиц или запросов, на основе которых создается новый запрос. В нижней - располагается бланк QBE (запрос по образцу), в котором выполняется работа по созданию запроса. Каждый столбец бланка представляет одно поле, используемое в запросе.

Первая строка бланка запроса служит для выбора полей, которым можно присвоить имена, используемые при выводе записей запросов. Во второй строке бланка запроса выводится имя таблицы, из которой выбрано поле. В третьей строке бланка можно указать, для каких столбцов нужно проводить сортировку. Флажки в строке бланка Вывод на экран отвечают за вывод полей в наборе записей. По умолчанию выводятся все поля, включенные в бланк запроса. Для ввода условия отбора записей используется строка Условие отбора .

Рис.10.1. Окно конструктора запросов

Включение полей в запрос . Чтобы включить поле в бланк запроса, нужно его выделить в таблице и мышью перетащить в соответствующее поле бланка запроса.

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

Ввод условий отбора. Если нужно отобрать записи с конкретным значением поля, его нужно ввести в ячейку Условие отбора этого поля. Текстовое значение в качестве условия заключается в кавычки. При задании условия отбора можно пользоваться знаками отношений < , >, >=, <=, =,< > и логическими операциями or , and .

Кроме этого Access предоставляет специальные операторы для отбора данных, выводимых в запросе:

between - определяет диапазон значений. Between 10 and 20 означает то же самое, что и выражение >=10 and <=20 ;

in - задает используемый для сравнения список значений. Выражение in (“ wa ”,” ca ”,” id ”) означает то же самое, что и выражение “ wa ” or “ ca ” or “ id ” ;

like - этот оператор позволяет при отборе текстовых полей использовать символы: ?, *,# . Символ # указывает, что в данной позиции должна стоять цифра, символы ? и * имеют то же назначение, что и в именах файлов OC MS DOS.

Например, like “ B *” - означает, что нужно выбрать поля, начинающиеся с буквы В .

Условия отбора для дат и времени Access обрабатывает в любом формате. При вводе дату или время необходимо заключать в символы #. Например, #15 Апрель 1998#, #15/04/98# определяют одну и ту же дату.

Access предоставляет несколько функций, которые можно использовать при задании условий отбора для дат и времени:

day (дата) - возвращает значение дня месяца в диапазоне от 1 до 31. Если нужно отобрать записи с определенными днями месяца - задается вычисляемое поле, например, day([Дата_заказа]) и вводится условие отбора, например, >10. В этом случае выбираются все записи поля, вычисляемое поле которых >10;

month (дата) - возвращает значение месяца года в диапазоне от 1 до 12;

year (дата) - возвращает значение года в диапазоне от 100 до 9999;

weekday (дата) - возвращает целое число от 1(Воскресенье) до 7(Суббота), соответствующее дню недели;

date () - возвращает текущую системную дату.

Вычисляемые поля . Можно выполнить вычисления с любыми полями таблицы и сделать вычисляемое выражение новым полем в наборе записей. При этом можно использовать любые функции, встроенные в Access, и выполнять над полями таблицы арифметические операции с помощью операторов: +, -, *, /, \, ^, mod, &. Например, пусть имеется имя поля с именем “Количество ”, где записано количество единиц товара и поле “Цена ” , где записана стоимость единицы товара. Тогда для подсчета стоимости товара в пустое поле бланка запроса нужно ввести выражение Количество*Цена и значения этих полей будет перемножено.

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

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

Для этого в строку "Условие отбора" вводится фраза в квадратных скобках, которая будет выводиться в качестве "подсказки" в процессе диалога, например [Введите фамилию]. Таких параметров может быть несколько, каждый для своего поля, при этом имя каждого параметра должно быть уникальным.

Сортировка данных . Обычно Access выводит записи в том порядке, в каком они выбираются из базы данных. Можно изменить последовательность вывода данных, задав порядок сортировки По возрастанию или По убыванию .

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

Access предоставляет несколько функций для обеспечения групповых операций. Основные из них:

sum - вычисляет сумму всех значений заданного поля в каждой группе. Используется только для числовых и денежных полей;

avg - Вычисляет среднее арифметическое значение всех значений данного поля в каждой группе;

min , max - вычисляет наименьшее (наибольшее) значение поля внутри группы;

count - вычисляет число записей, в которых значения данного поля отличны от Null .

Выбор записей, формирующих группы . В группы итогового запроса можно не включать некоторые записи. Для этого нужно добавить в бланк запроса одно или несколько полей для фильтра. Для создания фильтра в строке Групповая операция выбирают установку Условие , снимают флажок Вывод на экран для этого поля и вводят условие отбора.

Условия отбора для полей запроса задаются в одноименной строке. Если условия в этой строке определены для нескольких полей, то они связаны логической функцией «И». Если условия отбора разных полей определены в разных строках: Условие отбора и Или , то такие условия связаны логической функцией «ИЛИ». Запрос может иметь сложный критерий, в соответствии с которым для полей определены условия отбора в обеих строках.

Условие отбора записей – это набор предопределенных в Access и устанавливаемых в запросе пользователем правил. При построении критерия (условия) отбора в запросах могут использоваться выражения, операторы и функции Access.

Оператор Between позволяет задать интервал для числового значения, например: Between 300 And 500

Оператор IN позволяет выполнить проверку на равенство любому значению из списка, который задается в круглых скобках, например: IN(3181,3185) или IN("Москва","Санкт-Петербург","Казань")

Оператор Like позволяет использовать образцы и символы шаблона при поиске данных, например: Like "Михайлов" или Like "М*"

Допускается использование в литералах символов шаблона * , ?, #, [список], [!список].

? – в позиции шаблона может быть любой символ: (0 – 9), (Aa – Zz), (Аа – Яя);

* - любое количество символов;

# - в позиции может быть любая цифра;

[список] – в позиции может быть любой символ из списка;

[!список] – в позиции может быть любой символ за исключением символов из списка.

При написании шаблон заключается в двойные кавычки.

Like «А*» – в поле Фамилия все фамилии, начинающиеся на А.

Like «*/1/99» – в поле Дата – все записи за январь 1999г.

Like «*» – в поле Имя – любое имя, начинающееся с указанных букв.

Запросы - действия

Выполнение запроса - действия приводит к изменению содержимого базы данных. При выполнении таких запросов следует быть осторожным, так как необдуманное применение этих запросов может привести к необратимой утрате информации в базе данных. Поэтому Access автоматически помечает в окне базы данных запросы - действия символом «!».

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

Существует 4 типа запросов на изменение:

Запрос на добавление;

Запрос на обновление;

Запрос на удаление;

Запрос на создание таблицы.

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

Для создания запроса необходимо выполнить следующие действия:

Создать запрос на выборку и отладить его (добавить таблицы, значения полей которых будут использоваться для добавления записей);

Отменить свойство Вывод на экран для полей запроса;

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

Указать имя таблицы, куда будут добавляться записи;

Выполнить команду ЗАПРОС/Запуск.

Если принимающая таблица содержит ключевое поле, то и добавляемые записи должны иметь такое же ключевое поле (по условиям целостности БД).

Технология создания других типов запросов - действий аналогична.

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

Запрос на удаление позволяет удалять записи из одной или нескольких таблиц одновременно. Запрос на удаление удаляет записи таблицы, удовлетворяющие критериям отбора, целиком, поэтому если требуется удалить значения отдельных полей записи, следует создать запрос на обновление. В процессе выполнения этого запроса Access отображает данные, которые будут удалены. Для того, чтобы иметь возможность просматривать все поля удаляемых записей, следует перетащить мышью из первой строки списка полей таблицы, записи которой требуется удалить, символ "*" в первую строку бланка запроса, в первый свободный столбец. При этом в этом столбце в строке Поле появится имя таблицы, а в строке с именем Удаление - значение Из.

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

Формы

Форма является удобным средством для просмотра БД, а также для ввода дан­ных и их корректировки. В форме обычно отображаются поля одной строки таблицы или за­проса. Однако в форме можно отображать данные нескольких связанных таблиц или запросов. Применение форм позволяет упростить ввод данных в БД и сократить количество допускаемых ошибок ввода. Для этого форма снабжается:

Форматами ввода;

Условиями проверки вводимых данных;

Масками ввода для ввода стандартизованной информации;

Пояснительным текстом;

Группировкой данных, приближающей ее вид к бумажному бланку.

При проектировании формы можно использовать текстовые строки, рисунки и линии, кнопки, списковые окна и т.п. Форма проектируется при помощи панели эле­ме­нтов.

Форма может размещаться на одном экране или нескольких экранных страницах.

В форме может быть разрешено или запрещено корректировать определенные данные или вообще ввод новых записей.

С формой можно работать в 3-х режимах:

В режиме конструктора;

В режиме формы;

В режиме таблицы.

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

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

Создание формы осуществляется либо с помощью мастера, либо в режиме конструктора.

Типы форм

В Access можно создать формы следующих типов:

Форма в столбец или полноэкранная форма;

Ленточная форма;

Табличная форма;

Форма главная / подчиненная;

Сводная таблица;

Форма - диаграмма.

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

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

Табличная форма отображает данные в режиме таблицы.

Форма главная/подчиненная представляет собой совокуп­ность формы в столбец и табличной. Ее имеет смысл создавать при работе со связанными таблицами, в которых установлена связь типа «один-ко-многим».

Форма Сводная таблица выполняется мастером создания сводных таблиц Excel на основе таблиц и запросов Access (мастер сводных таблиц является объектом, внедренным в Access, чтобы использовать его в Access необходимо установить Excel). Сводная таблица представляет собой перекрестную таблицу данных, в которой итоговые данные располагаются на пересечении строк и столбцов с текущими значениями параметров.

Форма с диаграммой . В Access в форму можно вставить диаграмму, созданную Microsoft Graph. Graph является внедряемым OLE приложением и может быть запущен из Access. С внедренной диаграммой можно работать так же, как и с любым объектом OLE.

Конструирование форм

При создании новой формы появляется диалоговое окно Новая форма , в котором следует выбрать:

Способ создания формы;

Источник данных (из списка).

Access предлагает следующие способы создания формы:

1. С применением Автоформы . Автоформа позволяет созда­вать формы трех стандартных типов: в столбец, ленточную, табличную. При этом в форму вставляются все поля источника данных.

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

3. С помощью конструктора форм. Форма конструируется пользователем в окне конструктора форм.

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

Источником данных формы являются одна или несколько связанных таблиц и/или запросов.

Структура формы

Форма состоит из пяти основных разделов:

1. Заголовок формы. Содержимое области заголовка формы выводится в верхней части окна формы.

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

3. Область данных. Область данных содержит поля, в которых отображаются данные.

4. Нижний колонтитул. Содержимое области нижнего колонтитула (дата, № страницы и т.д.) отображаются на каждой экранной странице в нижней части формы.

5. Примечание формы. Содержимое этой области выводится внизу последней экранной страницы формы.

Форма может содержать все разделы или только некоторые из них.

Свойства формы

Как любой объект Access, форма имеет свойства. Значения этих свойств определяют внешний вид формы. Окно "Свойства" формы можно вызвать, например, щелкнув правой клавишей мыши по черному квадрату на пересечении линеек и из контекстного меню выбрать команду СВОЙСТВА .

Окно свойств выделенного объекта содержит следующие вкладки:

Макет – свойства, задающие макет формы;

Данные – свойства, определяющие источник данных, тип данных, формат и т.д.;

События – перечень событий, связанных с объектом;

Все – перечень всех свойств.

Основные свойства формы:

Подпись (это свойство расположено на вкладке МАКЕТ) – задает название формы, которое выводится в строку заголовка в окне формы.

Режим по умолчан ию – определяет режим открытия формы (простая форма, ленточная, таблица).

Допустимые режимы – свойство указывает, можно ли переходить из режима таблицы в режим формы и обратно с помощью команд меню ВИД. Свойство может принимать следующие значения:

все – можно;

таблица – нельзя, возможен только просмотр в режиме таблицы;

форма – нельзя, возможен только просмотр в режиме формы.

Разрешить изменение определяет, можно ли через форму изменять данные, т.е. задает статус "Только для чтения".

Разрешить удаление определяет, может ли пользователь удалять данные через форму.

Разрешить добавление определяет, может ли пользователь добавлять записи через форму.

Ввод данных определяет режим открытия формы. Может принимать значения "Да" (форма открывается только для добавления новых записей) и "Нет" (в форму выводятся существующие записи).

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

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

Полосы прокрутки;

Кнопка оконного меню;

Кнопка размеров окна;

Кнопка закрытия окна;

Тип границы окна;

Кнопка контекстной справки.

Поле номера записи определяет, будут и выводиться в окно формы кнопки перехода по записям.

Элементы управления формой

Элементом управления называют любой объект формы или отчета, который служит для вывода данных на экран, оформления или выполнения макрокоманд. Элементы управления могут быть связанными, вычисляемыми или свободными.

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

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

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

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

Основными элементами управления являются:

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

Свободная надпись используется для задания заголовков, комментариев. Создается кнопкой "Надпись" панели инструментов.

Подпись создается одновременно с созданием того элемента, к которому она присоединена. Подпись используется совместно с полями, флажками, переключателями, списками.

Поля – это элементы, предназначенные для отображения данных или для ввода данных. Поля могут быть присоединенными или свободными. Содержимое свободных полей нигде не сохраняется.

Поле является основным элементом управления при работе с базами данных, поскольку позволяет отображать и редактировать данные таблиц баз данных.

Добавление свободного поля в форму выполняется кнопкой "Поле" панели элементов. Добавление присоединенного поля (связанного с полем таблицы) осуществляется в режиме конструктора следующим образом:

На панели "Конструктор форм" выбирается кнопка "Список полей";

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

В форму можно вводить вычисляемые поля. Вычисляемое поле является свободным полем. Для его создания необходимо выбрать кнопку Поле на панели элементов и вставить в нужное место формы, а затем ввести выражение прямо в элемент Пол " или в качестве значения свойства Данные . В вычисляемом поле выражение должно начинаться со знака «=». Выражения можно набирать вручную или формировать построителем выражений.

Элементы управления Выключатели, Переключатели, Флажки . Принцип работы этих элементов управления совершенно одинаков, они отличаются только внешним видом.

Элементы используются для отображения данных логического типа и возвращают значение (-1) в присоединенное к ним поле таблицы, если кнопка в положении, соответствующем истине, и 0 – в противном случае.

Для отображения заданного состояния можно ввести его значение по умолчанию. если это значение не задано, то элемент будет находиться в состоянии Null, что соответствует значению Ложь.

Группа – элемент управления, предназначенный для размещения нескольких выключателей, переключателей или флажков. Элементы внутри группы функционируют согласовано. Максимальное число элементов – 4, причем одновременно может быть выбран один элемент. Группа возвращает число, которое соответствует номеру выбранного элемента.

Управляющие элементы Флажок и Выключатель могут использоваться не только в группе, но и индивидуально.

Флажок может быть связан с логическим полем базовой таблицы или запроса. Если флажок связан с логическим полем базовой таблицы, то состояние Установлен/Снят соответствует значениям поля.

Флажок может быть свободным элементом. В этом случае он используется в специальных диалоговых окнах для приема данных, вводимых пользователем.

Аналогичным образом может использоваться и элемент управления Выключатель.

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

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

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

Основные свойства списков:

1. Тип источника данных: таблица / запрос; список значений; список полей; функция VBA.

2. Источник данных – указывает фактический источник данных: для таблицы / запроса – имя таблицы / запроса; для списка значений – значения элементов списка через «;» (например, Пол – м;ж).

3. Присоединенный столбец – поле базовой таблицы, к которому присоединен список.

4. Число столбцов – количество столбцов в списке. Если источником данных является список значений, то элементы распределяются из списка по строкам и столбцам.

5. Ширина столбца – задается числовым значением через «;». Можно скрыть присоединенный столбец списка, если он содержит несколько столбцов. Для этого нужно установить ширину столбца равной 0. Значение не отображается при выводе списка, однако при выборе строки, значение из присоединенного столбца попадает в поле базовой таблицы.

6. Число строк – определяет максимальное число строк, отображаемое в поле со списком.

Кнопки – элемент управления, используемый для выполнения какого-либо действия. Для выполнения действия свойство кнопки Нажатие кнопки нужно связать с каким-либо макросом либо с процедурой обработки событий.

Кнопка создается мастером. Мастер позволят создать кнопки 30 разных типов и связывает их с процедурами обработки событий. Свойство Подпись определяет текст на кнопке. Свойство Рисунок определяет рисунок на кнопке.

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

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

Можно изменять размеры элемента Набор вкладок , порядок следования и названия вкладок.

Элемент управления Разрыв страницы используется для указания горизонтальных разрывов между элементами управления в форме. Для перехода по страницам используются клавиши PgUp и PgDn . Вставленный в форму элемент Разрыв страницы помечается небольшой точечной линией на левой границе формы.

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

Подчиненные формы предназначены для отображения одной формы внутри другой. Первичная форма называется главной. Подчиненная форма – та, которая располагается внутри главной.

Подчиненная форма наиболее удобна для вывода таблиц или запросов, связанных отношением «один-ко-многим». При этом главная форма может быть выведена только как простая форма, а подчиненная форма обычно отображается в табличном виде. Главная форма может содержать любое количество подчиненных форм, если каждая подчиненная форма умещается в главную. Допускается возможность создания подчиненной формы двух уровней вложенности

Создать подчиненную форму можно:

Добавив элемент Подчиненная форма в форму;

Перетащив форму из окна базы данных в другую открытую форму;

Мастером подчиненных форм.

Отчеты

Типы отчетов

Отчеты строятся на основании данных таблиц и запросов. Основные типы отчетов:

- отчет в одну колон ку (в столбец) – представляет собой длинный столбец текста, содержащий надписи полей их значения из всех записей таблицы или запроса;

- многоколончатый отчет – создается из отчета в одну колонку и позволяет вывести данные отчета в несколько колонок (колонки газетного типа);

- ленточный отчет – данные располагаются в виде строк и столбцов (как в таблице);

- групповой/итоговый отчет – создается из ленточного отчета объединением данных в группы с подсчетом итогов;

- почтовые наклейки – специальный тип многоколончатого отчета, предназначенный для печати имен и адресов в группах;

- отчет с подчиненным отчетом .

Структура отчета

Основные разделы отчета:

- заголовок отчета – печатается в начале отчета на титульной странице, содержит название отчета;

- верхний колонтитул – печатается вверху каждой страницы; как правило, содержит заголовки столбцов;

- заголовок группы – печатается перед обработкой первой записи группы, в качестве заголовка может содержать поле, по которому выполняется группировка;

- область данных – печатается каждая запись из источника данных;

- примечание группы – печатается после обработки последней записи группы; может содержать итоговые данные по записям, входящим в группу;

- нижний колонтитул – печатается внизу каждой страницы, может содержать, например, дату печати отчета, номер страницы отчета;

- примечание отчета – печатается в конце отчета после обработки всех записей, может содержать итоговые данные по всем записям.

Конструирование отчета

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

Технология создания простого отчета в столбец:

1). Находясь на вкладке ОТЧЕТЫ нажать кнопку СОЗДАТЬ .

2). В окне Новый отчет :

Выбрать инструмент Автоотчет в столбец ;

Нажать ОК.

Технология создания многоколончатого отчета:

1). Создать простой отчет в столбец.

2). Выбрать в меню ФАЙЛ команду Параметры страницы . В диалоговом окне Параметры страницы выбрать вкладку Столбцы и задать:

В группе Параметры сетки число столбцов, которые должны выводиться на каждой странице (поле Число столбцов ), ширину межстрочного интервала (поле Интервал ), расстояние между столбцами (поле Столбцов );

В группе Размер столбца ширину столбца (поле Ширина ) и высоту строки (поле Высота );

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

3). На вкладке Страница выбрать ориентацию: Книжная или Альбомная .

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

Технология создания группового/итогового отчета мастером отчетов

1). Находясь на вкладке ОТЧЕТЫ нажат кнопку СОЗДАТЬ .

2). В окне Новый отчет :

Выбрать инструмент Мастер отчетов ;

Выбрать источник данных в виде таблицы или запроса;

Нажать ОК .

3). В окне Создание отчетов (первый шаг) следует сформировать список полей отчета (Выбранные поля ) из списка Доступные поля . Поля следует выбирать в том порядке, в каком они должны следовать в отчете. Если отчет строится на основании данных нескольких таблиц / запросов, следует выбрать из списка Таблица / Запрос имя следующего источника и для него произвести выбор полей.

4). Если источником данных отчета являются связанные таблицы, то на втором шаге в окне Создание отчетов следует определить, как будет выполняться группировка данных.

5). На следующем шаге можно задать собственный способ группировки данных. Для этого из левого списка следует выбрать имя поля, по которому нужно сгруппировать данные. Задав группировку данных, можно с помощью кнопки Группировка задать интервалы группировки.

Для числовых полей можно задать группировку по десяткам, полусотням, сотням и т.д. Для текстовых полей возможна группировка по первой букве, по первым двум, по первым трем, по первым четырем и по первым пяти буквам. Если не требуется специальный интервал группировки, из списка Интервалы группировки следует выбрать значение Обычный .

6). На третьем шаге можно задать порядок сортировки записей внутри группы и, нажав на кнопку Итоги , открыть окно, в котором задать, какие итоговые операции нужно выполнять с числовыми полями тех записей, которые входят в группу; определить, что будет выводиться в отчет: данные и итоги или одни итоги (раздел данных скрыт).

7). На четвертом шаге можно выбрать один из стандартных макетов отчета.

8). На пятом шаге – один из стандартных стилей. Любой стандартный стиль можно настроить, можно создать собственный стиль. Для этого предназначена команда ФОРМАТ / Автоформат .

9). На последнем шаге работы мастера отчету следует задать свое имя или согласиться с тем именем, которое сформировал Access, и определить с помощью переключателей дальнейшие действия – работа с отчетом в режиме конструктора или просмотр отчета.

Технология создания группового / итогового отчета в режиме конструктора

1). Создать новый отчет с помощью одноименной команды – кнопка Создать на вкладке Отчеты в окне БД. Выбрать инструмент – Конструктор , выбрать источник данных (таблица / запрос), нажать ОК. В окне конструктора появится пустой макет отчета.

2). Создать макет отчета:

2.1). В разделе Заголовок отчета разместить элемент управления Надпись . Текст надписи явится заголовком создаваемого отчета. Если раздел заголовка отсутствует в макете отчета, следует выбрать команду ВИД /Заголовок| Примечание отчета .

2.2). В разделе Верхний колонтитул разместить элемент управления Подписи к тем полям, которые будут размещаться в области данных. Если верхний колонтитул отсутствует в макете, следует выбрать команду ВИД /Колонтитулы .

Верхние и нижние колонтитулы можно размещать в таблице различными способами. Способы размещения определяет значение свойства отчета Верхний колонтитул и Нижний колонтитул :

Все страницы – выводятся на все страницы отчета;

Без заголовка – выводятся на все страницы отчета кроме первой, где расположен заголовок;

Без примечания – выводятся на все страницы отчета кроме последней;

Без заголовка / примечания – выводятся на все страницы отчета кроме первой и последней.

Для размещения полей в области данных и их подписей в разделе верхнего колонтитула следует использовать метод перетаскивания объектов. Для размещения элементов управления в отчете необходимо:

Раскрыть список полей источника данных щелчком мыши по кнопке Список полей панели инструментов;

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

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

Если для добавляемого в отчет поля источника данных (таблицы / запроса) выбран элемент управления Поле , то в отчет добавляются два элемента: Надпись (подпись ) и Поле , связанных между собой. При выделении одного из них автоматически выделяется другой. При перемещении одного элемента перемещается и другой. Чтобы перетащить только один элемент из пары (например, подпись), необходимо выделить элемент поля, установить указатель мыши на маркер левого верхнего угла (вид – сжатая рука с вытянутым указательным пальцем) и перетащить элемент в нужное место.

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

Кроме связанных полей в отчет можно добавлять и свободные поля (вычисляемые поля).

2.3). Выполнить выравнивание элементов, изменение размеров элементов.

Чтобы изменить размеры элементов, следует:

Выделить все элементы командой ПРАВКА /Выделить все ;

Выполнить команду ФОРМАТ /Размер /По размеру данных .

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

Access позволяет также изменять размеры:

- по узлам сетки – при этом каждый угол элемента управления смещается к ближайшему углу сетки;

- по самому высокому – увеличивает высоту всех элементов до высоты самого высокого;

- по самому низкому;

- по самому широкому;

- по самому узкому .

Выравнивание выполняется для группы выделенных элементов управления командой ФОРМАТ /Выровнять . Для отчетов, содержащих данные в табличной форме, лучше выполнять выравнивание элементов области данных по верхнему или нижнему краю.

Для изменения величины межстрочного интервала в области колонтитулов и области данных нужно переместить нижнюю границу раздела. Чтобы сократить межстрочный интервал – как можно выше. Однако свойство Высота области данных должно иметь значение больше, чем значение этого е свойства самого высокого элемента управления.

2.4). Определить порядок сортировки и группировки данных в отчете. Это делается в окне Сортировка и группировка , которое открывается при выполнении команды ВИД /Сортировка и группировка или кнопки Сортировка и группировка панели инструментов Конструктор отчетов .

Чтобы определить порядок группировки и сортировки записей необходимо:

Из списка Поле / выражение выбрать поле, по значениям которого необходимо выполнять группировку записей. Данное поле будет определять группу записей;

Для группы указать порядок сортировки: по возрастанию, по убыванию;

Задать значения свойствам группы.

Группа может иметь следующие свойства:

1. заголовок группы – определяет будет ли присутствовать в отчете раздел Заголовок группы . Свойство может иметь два значения: Да / Нет . В раздел Заголовок группы может быть помещено значение поля, по которому выполняется группировка;

2. примечание группы – определяет, будет ли присутствовать в отчете раздел Примечание группы (Да / Нет ). Раздел Примечание группы обычно используется для отображения итоговых операций над данными из записей группы;

3. Свойство Группировка определяет способ группировки данных. Список значений этого свойства зависит от типа данных, по которому выполняется группировка. Для текстовых данных это свойство имеет следующие значения:

- по полному значению – группировка выполняется по значению поля;

- по первым символам – группировка выполняется по первым n символам в значении поля. Свойство Интервал задает значение этого n.

Для числовых данных свойство Группировка имеет значения:

- по полному значению ;

Интервал – группировка записей по значениям, попадающим в указанный интервал.

Свойство Интервал задает диапазон значений в интервале, например, если свойство имеет значение 10, то группировка будет осуществляться для записей, значения которых попадают в интервал 0 – 9, 10 – 19 и т.д.

Для полей типа даты / времени свойство Группировка может иметь следующие значения:

По полному значению;

По кварталам;

По месяцам;

По неделям;

По дням;

По часам;

По минутам.

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

- нет – отменяет обязательное расположение элементов группы на странице;

- полную группу – элементы группы печатать обязательно на одной странице;

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

Чтобы заголовок группы повторялся на новой странице, нужно выбрать значение ДА для свойства Повторение раздела в окне свойств заголовка группы.

2.5). Для многостраничного отчета определить расположение разделов отчета. Все разделы кроме колонтитулов имеют свойство Конец страницы , с помощью значений которого можно принудительно начинать новую страницу. Значение свойства Не разрывать :

- отсутствует – печать текущего раздела начинается на текущей странице;

- до раздела – печать текущего раздела начинается на новой странице;

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

Страницы доступа к данным

Страницы доступа к данным представляют собой специальный тип Web-страниц, предназначенный для просмотра и работы через Интернет или интрасеть с данными, хранящимися в базах данных Microsoft Access или базах данных Microsoft SQL Server, а также использовать данные из других источников, таких как Microsoft Excel.

В отличие от других объектов Access, которые являются частью базы данных, т.е. находятся в файле с расширением.mdb, страницы доступа сохраняются отдельно, в виде HTML-файлов. При открытии страницы доступа к данным в Internet Explorer средства Office 2000 обеспечивают получение данных и базы Access, а затем отображение их на странице.

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

1. Выделите в окне базы данных (рис. 17.4) значок запроса Перечень контактов.

Рис. 17.4. Окно базы данных Access

2. Щелкните на кнопке Конструктор .

3. В бланке запроса щелкните на ячейке Условие отбора первого столбца правой кнопкой мыши и выберите в контекстном меню команду Построить . Откроется окно построителя выражений.

4. В левом списке построителя щелкните на папке Операторы .

5. В среднем списке выберите категорию Сравнения .

6. В правом списке дважды щелкните на пункте Between, чтобы добавить этот оператор в ноле формулы.

7. Щелчком выделите в поле формулы первый местозаполнитель «Выражение».

8. В левом списке построителя выражений двойным щелчком откройте папку Функции.

9. Щелкните на папке Встроенные функции, содержащей стандартные функции Access.

10. В среднем списке построителя выражений щелкните на пункте Дата/время .

11. В правом списке дважды щелкните на функции DateValue, чтобы заменить ею местозаполнитель «Выражение».

12. Нажмите два раза клавишу -> , выделив местозаполнитель «stringexpr».

13. Введите текст "1.12.99".

14. Повторяя шаги 7-13, замените второй местозаполнитель «Выражение» на выражение DateValue ("31.12.99").

У вас должна получиться формула Between DateValue ("1.12.99") And DateValue ("31.12.99"). Она проверяет условие нахождения даты в интервале от 1 до 31 декабря 1999 г., то есть отбирает те записи, значение поля Дата которых относится к декабрю 1999 г.

Примечание Если вы уже освоили правила построения выражений Access, то можете не пользоваться построителем, а непосредственно вводить выражения в бланк запроса.

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

16. Сбросьте флажок Вывод на экран первого столбца запроса (рис. 17.5).

Рис. 17.5. Скрытие поля в Access

17. Щелчком на кнопке Запуск панели инструментов выполните запрос.

При создании запросов важно правильно сформулировать условия отбора записей из БД. В MS Access доступны следующие возможности:

· простой критерий выборки;

· точное несовпадение значений одного поля;

· неточное совпадение значений поля;

· выбор по диапазону значений;

· объединение критериев нескольких полей;

· условие отбора для результатов итоговых вычислений.

Простой критерий выборки. Записи выбираются по совпадающим значениям поля. Например, из поля Город необходимо выбрать значения Минск. Для этого в бланке запроса в строке Условие отбора в графе Город вводится с клавиатуры значение «Минск».

Точное несовпадение значений одного поля . Из базы выбираются все записи, кроме тех, для которых задано условие. Например, необходимо выбрать все записи с полем Город , кроме тех, которые в этом поле имеют значение Минск . Для этого в строке Условия отбора в графе Город вводится выражение Not «Минск» или<> «Минск» . Логический оператор Not исключает записи со значением Минск , оператор сравнения <> означает «не равно».

Неточное совпадение значений поля . Такое условие можно задавать, если не известны значения полей. Для выборки используется оператор сравнения Like (подобный). Рядом с оператором записывается образец, содержащий или точное значение, например,Like «Петров», или включающий символы шаблонов, например, Like «Пет*» .

Access допускает следующие символы шаблонов:

? - любой один знак;

* - ноль или более знаков;

#- любая одна цифра;

[список знаков] - любой один знак в списке знаков;

[!список знаков] - любой один знак, не входящий в список.

Кроме списка знаков в квадратные скобки может заключаться диапазон символов, например, [Б-Р]. Условие [б-рБ-Р] позволяет выбрать как заглавные, так и прописные буквы.

При условии Like «[БР]*» выбираются все фамилии, которые начинаются на Б или Р.

Выбор по диапазону значений . Для задания диапазона значений используются операторы:

> (больше),

>= (не менее, больше или равно),

< (меньше),

<= (не более, меньше или равно) (например, >= 10).

Between ... аnd ... (служит для проверки принадлежности диапазону, верхняя и нижняя граница которого соединена логическим оператором AND (например, between 1990 and 1995).

Операторы можно употреблять с текстовыми и цифровыми полями, а также с полями дат.

Объединение критериев одного поля . Если на одно поле налагается более одного условий, то условные выражения могут быть соединены с помощью операторов Or (ИЛИ ) и And (И ).

Объединение критериев нескольких полей . В запросе может быть несколько условий отбора. В этом случае имеют место два варианта выборки записей:

запись выбирается только при выполнении всех условий, что соответствует логической операции И . Запрос называется И-запросом ;

запись выбирается при выполнении хотя бы одного условия, что соответствует логической операции ИЛИ . Запрос называется ИЛИ-запросом .

При построении ИЛИ-запроса каждое условие, входящее в критерий, должно располагаться на отдельной строке. При построении И-запроса каждое условие, входящее в критерий, должно располагаться в одной строке.

В итоговых запросах существуют два типа критериев отбора записей.

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