Создание запросов в MS Access

 

Помимо таблиц в СУБД существуют объекты, называемые запросами. В MS Access существуют целый ряд запросов: запросы на изменение, которые позволяют добавлять и изменять записи в одной или нескольких таблицах; запросы на создание таблиц, создающие новую таблицу на основе одной или нескольких существующих таблиц; запросы на автоподстановку, автоматически заполняющие поля для новой записи, а также другие типы запросов.

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

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

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

1. В окне базе данных на панели объектов выбрать ярлык «Запросы».

2. Дважды щелкнуть левой кнопкой мыши на строчке «Создание запроса с помощью мастера».

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

4. После нажатия кнопки «Далее» в следующем окне задать имя для нового запроса и выбрать дальнейшие действия (для задания условий отбора записей следует выбрать вариант «Изменить макет запроса»).

Изменение макета запросов производится в режиме Конструктора запросов. На рис. 6 в качестве примера показан вид запроса на выборку данных о сотрудниках с высшим образованием, родившихся в период с 1971 по 1976 гг.

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

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

должны объединятся по логическому оператору ИЛИ (Or), их следует размещать в разных строках бланка запроса.

Сложное условие отбора в одном столбце может создаваться с применением логических операторов (табл. 3.5), которые выполняют действия над логическими значениями True (ИСТИНА) и False (Ложь) или над их битовыми эквивалентами «1» и «0».

Таблица 3.5

Значения логических операторов

	A=1 B=0	A=1 B=1	A=0 B=0	A=0 B=1
A And B	0	1	0	0
A Or B	1	1	0	1
Not A	0	0	1	1
A Xor B	1	0	0	1
A Eqv B	0	1	1	0
A Imp B	0	1	1	1

Кроме логических операторов возможно использование операторов сравнения с образцом (табл. 3.6). В MS Access имеется возможность задавать значение критерия отбора в диалоговом окне. Для этого вместо конкретного значения в условии отбора следует поместить фразу в квадратных скобках (во втором столбце запроса, показанного на рис.6, можно в качестве условия ввести фразу [Образование сотрудника:]).

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

Таблица 3.6

Операторы сравнения с образцом

Оператор	Пример	Описание
Between	Between (-100) And (100)	Определяет, находится ли числовое значение в определенном диапазоне
In	In ("Россия", "Украина", "Белоруссия")	Определяет, является ли строковое значение элементом списка
Like	Like "Ива*" Like "Ива???"	Проверяет, начинается ли строковое значение с указанных символов (символ «*» замещает произвольное число знаков, если требуется указать точное число неизвестных букв, то следует использовать соответствующее количество символов «?»)

 

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

В рассматриваемом примере имеет смысл включить следующие поля: поле «Образование» для того, чтобы задать условие; поле «Год рождения» для вычисления среднего возраста; поле «Фамилия» (или любое другое) для подсчета числа сотрудников в отобранной группе.

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

Для проверяемого поля выбирается вариант «Условие». Для других полей может быть выбрана одна из стандартных статистических функций (например, для поля «Фамилия» - функция Count, подсчитывающая количество записей в группе). Значения стандартных статистических функций MS Access указаны в табл. 3.7. В данной СУБД имеется возможность создавать свои (пользовательские) функции для обработки данных, используя язык Visual Basic for Applications (VBA), но для этого требуется быть специалистом в программировании.

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

 

Таблица 3.7

Статистические функции MS Access

Функция	Описание
Avg	Вычисляет среднее арифметическое набора чисел, содержащихся в указанном поле запроса
Count	Вычисляет количество непустых записей, возвращаемых запросом
First	Возвращает значение поля первой записи результирующего набора
Last	Возвращает значение поля последней записи результирующего набора
Max	Возвращает максимальное значение из набора, содержащегося в указанном поле
Min	Возвращает минимальное значение из набора, содержащегося в указанном поле
StDev (StDevP)	Возвращает смещенное (несмещенное) значение среднеквадратического отклонения, вычисляемого по набору значений, содержащихся в указанном поле
Sum	Возвращает сумму набора значений, содержащихся в заданном поле
Var (VarP)	Возвращает значение смещенной (несмещенной) дисперсии, вычисляемой по набору значений, содержащихся в указанном поле

 

Построитель выражения вызывается специальной кнопкой на панели инструментов Конструктора запросов или путем выбора пункта «Построить» во всплывающем при щелчке правой кнопкой мыши на названии поля меню.

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

Для вставки в формулу функции или поля таблицы нужный элемент следует найти через систему навигационных окон и нажать кнопку «Вставить» – функция или ссылка на поле таблицы автоматически вставятся в формулу. Как и в условия, в формулы можно вставлять и параметры, задаваемые пользователем при выполнении запроса, в примере на рис. 8 таким образом включено значение текущего года (фраза в квадратных скобках).

Дополнительные параметры поля запроса (формат поля, число десятичных знаков после запятой, название поля и др.) могут быть изменены в режиме Конструктора при выборе пункта «Свойства» меню, всплывающего при нажатии правой кнопки мыши в области поля.

 

создание форм и отчетов в MS Access

Одной из основных задач создания и использования базы данных является предоставление пользователям необходимой информации на основе существующих данных. В MS Access 2002 для этих целей предназначены формы и отчеты.

Основным отличием форм и отчетов является то, что с помощью форм можно не только просматривать, но и вводить информацию, а отчеты предназначены лишь для просмотра и печати данных.

Самым простым и быстрым способом создания формы или отчета является использование соответствующего. Чтобы с помощью мастера создать простую форму:

1. Щелкните по ярлыку «Формы» в окне базы данных (рис. 4).

2. Дважды щелкните по строчке «Создание формы с помощью мастера».

3. В появившемся диалоговом окне с помощью кнопок со стрелками выберите отображаемые поля из таблиц или запросов и нажмите кнопку «Далее».

4. В следующем окне можно выбрать различные виды форм (вид «Выровненный» является одним из наиболее удобных).

5. В третьем окне можно выбрать стиль оформления формы, после чего следует нажать кнопку «Далее».

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

Открывается созданная форма двойным щелчком на ее имени в окне базы данных или с помощью кнопки «Открыть». Для изменения ее макета следует (как и в случае с таблицами и запросами) открыть ее в режиме Конструктора.

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

Простейшим элементом управления является надпись, содержащая какой-либо текст (название базы данных или отдельного поля, фамилию разработчика и т. п.). Для вставки надписи служит кнопка  Aa  на панели элементов Конструктора форм (см рис.9). Изменять свойства надписи (как и других элементов управления) можно стандартным для офисных приложений способом – вызвать щелчком правой кнопки мыши на выбранном элементе всплывающее меню.

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

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

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

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

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

Для ввода и отображения логических значений True и False или битовых «1» и «0» могут применяться отдельные флажки или переключатели.

Кнопки в формах предназначены для выполнения различных команд (запуск программы, вызов другой формы, выполнение запроса и т. д.). В зависимости от выбранного в диалоговом окне варианта действий могут появиться другие окна, в которых потребуется указать имя таблицы, запроса, полный путь к запускаемой программе, (например,  для стандартного калькулятора Windows XP  – «С:\Windows\System32\calc.exe»).

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

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

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

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

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

 

В нашем случае требуется создать отчет для передачи всех данных из основной таблицы в Excel. Отчет можно составлять и по одной главной таблице, но если это сделать, то в поле, использующем подстановку вместо реального текста будет стоять код поля (порядковый номер во вспомогательной таблице). По этой причине включаем в него все поля основной таблицы «Сотрудники», кроме тех, которые используют подстановку. Эти пропущенные поля следует включить из вспомогательных таблиц «Должности» и «Образование». Пояснения на рисунке на следующей странице.

Когда отчет будет создан его надо экспортировать в Excel, щелкнув правой кнопкой по названию отчета в окне базы данных на странице «Отчеты» - рисунок приводится ниже по тексту.

 

 

 

После этого сохранить отчет на диске в формате Excel