Как самостоятельно составлять запросы в microsoft access

Вычисления в запросе БД Access 2003

2010-02-28

В БД Microsoft Access вычисляемые поля могут быть созданы в запросе, форме или отчете. Вычисляемые поля используются для различных целей, в том числе и для проведения расчетов, Для выполнения подсчетов в вычисляемые поля объектов базы данных Access вводят Выражения. Выражения — это формулы аналогичные формулам Excel, но только вместо ссылок на ячейки в них используются имена полей. Ввод выражений возможен как вручную, так и с помощью Построителя выражений.

Выражения могут содержать следующие элементы:

• идентификаторы (имя поля и элемента управления, которые заключаются в квадратные скобки);
• операторы (арифметические, логические, сравнения и другие операторы);
• функции (готовые формулы, которые состоят из имени и аргумента, помещенного в круглые скобки)
• константы (элементы, которые не изменяются в Выражении, например Null, Истина, Ложь);
• значения (например, значения даты и времени, численные положительные или отрицательные значения).

Примеры Выражения:

• =/1,2, где «=» — опрератор, — имя поля, а 1,2 — значение;
• Date(), где Date — имя функции текущей даты, которая не имеет аргумента.

Рассмотрим создание вычисляемых полей в бланке запроса базы данных Access 2003. Для ввода Выражения в запрос его необходимо открыть в режиме Конструктора. Чтобы создать вычисляемое поле в запросе (в существующем или вновь создаваемом запросе) открытого в режиме конструктора, необходимо ввести Выражение в строку Поле свободного столбца бланка запроса. Выражение может выполнять вычисления, обрабатывать текст, указывать время и дату, проверять данные и т.д. Необходимо отметить, что каждая формула вычисляется только для отдельной строки таблицы бланка запроса.

Рассмотрим создание вычисляемого поля в бланке запроса на примере базы данных Training_students_VP. Постановка задачи: в запросе на выборку (Запрос 3) создать поле, в котором должны отображаться итоговые оценки (ниже на 20% от полученных по результатам рейтинга). Для решения этой задачи откроем бланк «Запроса 3» в режиме конструктора (Рис. 1).

Рис. 1.

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

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

=/1,2

Рис. 2.

В результате в бланке запроса появится вычисляемое поле =/1,2

Рис. 3.

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

Рис. 4.

С учетом вышеизложенного следует изменить название вычисляемого поля и отформатировать его.
Для этого необходимо вернуться в режим конструктора и слово Выражение1 заменить на «Итог_оценка».
Затем щелкнуть правой кнопкой мыши на вычисляемое поле и в контекстном меню выбрать команду Свойства, откроется диалоговое окно Свойства поля (Рис. 5)

Рис. 5.

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

О Microsoft Access

Microsoft Access – программный продукт компании Microsoft, являющейся реляционной СУБД. Имеет огромные возможности при организации базы данных, создания отдельного приложения, которое может взаимодействовать с множеством других СУБД. Наиболее часто встречающееся решение клиент-сервер, где в качестве клиента выступает приложение, написанное в Access (язык VBA, формы и многое другое), а сервером является СУБД Microsoft SQL Server. Однако Access поддерживает и взаимодействие с другими СУБД, например, такими как: MySql или PostgreSQL. О Access можно разговаривать долго, но цель сегодняшней статьи именно создание запросов (объектов) из Access.

Переходим к практике и начнем с простой базы mdb, т.е. как там создать эти самые запросы.

2.4. Microsoft Access 2007

2.4.5. Создание запросов и поиск информации в базе данных

В СУБД Access 2007 можно создавать queries для отображения требуемых полей из записей одной или нескольких таблиц.

В СУБД Access 2007 применяются различные типы запросов: на выборку, на обновление, на добавление, на удаление, перекрестный query, выполнение вычислений, создание таблиц. Наиболее распространенным является query на выборку. Применяются два типа запросов: query по образцу (QBE) и query на основе структурированного языка запросов (SQL).

Запросы на выборку используются для отбора требуемой пользователю информации, содержащейся в нескольких таблицах. Они создаются только для связанных таблиц. Queries могут основываться как на нескольких таблицах, так и существующих запросах. СУБД Access 2007 включает такие средства создания запросов, как Мастер и Конструктор.

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

На скриншоте (рисунок 1) средства сортировки и фильтрации выделены скругленным прямоугольником красного цвета.

Рис. 1.

Рассмотрим создание запроса на выборку с помощью Конструктора

Для создания нового пустого запроса в режиме конструктора надо щелкнуть на пиктограмме Конструктор запросов (рисунок 2).

Рис. 2.

Откроется активное окно диалога Добавление таблицы (рисунок 3) на фоне неактивного окна «Запрос1». В этом окне можно выбрать таблицы и queries для создания новых запросов.

Рис. 3.

В окне Добавление таблицы следует выбрать несколько таблиц из представленного списка таблиц, на основе которых будет проводиться выбор данных, и щелкнуть на кнопке Добавить. После этого закрыть окно Добавление таблицы, а окно «Запрос1» станет активным (рисунок 4).

Рис. 4.

Окно Конструктора состоит из двух частей – верхней и нижней. В верхней части окна размещается схема данных запроса, которая содержит список связанных таблиц. В нижней части окна находится Бланк построения запроса QBE, в котором каждая строка выполняет определенную функцию.

Переместим имена полей с таблиц-источников в Бланк. Из таблицы Группы студентов переместим поле Название в первое поле Бланка, из таблицы Студенты переместим поле Фамилии во второе поле, а из таблицы Успеваемость переместим поле Оценка в третье поле и из таблицы Дисциплины переместим поле Название в четвертое поле Бланка запросов.

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

Условия ограниченного поиска или критерий поиска информации вводится в строке «Условия» отбора и строке «Или». Например, введем критерий поиска — «5/A» в строке «Условия» для поля Оценка. В этом случае в результате выполнения запроса на экране будут отображаться все фамилии студентов, которые получили оценку 5/A (рисунок. 5).

Рис. 5.

Далее надо закрыть окно запроса Запрос1, появится окно диалога Сохранить, ответить — Да и ввести имя запроса, например «Успеваемость студентов». Для запуска запроса дважды щелкнем на query «Успеваемость студентов», откроется таблица с результатами выполненного запроса (рис. 6).

Рис. 6.

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

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

Рис. 7.

Затем надо щелкнуть на кнопке ОК, откроется таблица с результатами выполненного запроса (рис. 8).

Рис. 8.

В некоторых случаях для создания запросов можно использовать Мастер запросов. После создания запросов на выборку информации из БД Access 2007 можно приступать к формированию форм.

Далее >>> Раздел: 2.4.6. Создание форм для ввода данных в таблицы базы данных Access 2007

2.4. Microsoft Access 2007

2.4.2. Создание базы данных (таблиц и связей между ними) в Access 2007

Рассмотрим этапы создания БД «Деканат» с помощью СУБД Access 2007. Сначала составляем модель «сущность – связь» для базы данных «Деканат». Этапы проектирования модели «сущность – связь» изложены в разделе «Создание БД. Этапы проектирования».

После создания модели запускаем приложение Access 2007. Открывается окно приложение Access 2007 на странице Приступая к работе с Microsoft Access 2007. В разделе Новая пустая база данных щелкаем на пиктограмме Новая база данных. В правой части окна появится информация об имени файла и указана директория для его хранения. По умолчанию имя файла — База данных1.accdb.

Изменить имя файла и путь к директории для хранения файла БД можно в окне «Файл новой базы данных» щелкнув на пиктограмме «Поиск расположения для размещения базы данных». Установив имя файла — Деканат_2007.accdb и требуемое имя директории в окне «Файл новой базы данных», надо щелкнуть на кнопке ОК, окно закроется.

Далее необходимо щелкнуть на кнопке Создать, чтобы создать пустую базу данных. При создании новой пустой базы данных окно приложения Access 2007 открывается на контекстной вкладке «Режим таблицы». В окне отображается новая пустая таблица с именем Таблица 1 в режиме таблица, представленная на Рис. 1.

Рис. 1.

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

Рис. 2.

Откроется окно Сохранение, в котором надо указать имя Группы студентов и нажать кнопку ОК.

Рис. 3.

Откроется таблица Группы студентов в режиме Конструктор

Рис. 4.

Создаем структуру таблицы Группы студентов. В первую строку колонки «Имя поля» вводим код группы студентов (КодГруппы) и нажимаем клавишу Enter. Курсор переместится в колонку Тип данных. Access по умолчанию назначает тип данных — Счетчик. Нажимаем клавишу Enter, при этом курсор переместится в колонку Описание, при необходимости вводим описание данных.

Первой строке таблицы (поле КодГруппы) Access по умолчанию назначает поле первичного ключа. Для первичного ключа в свойствах поля устанавливается значение Индексированного поля: Да (Совпадения не допускаются). Далее заполняем вторую строку (второе поле таблицы), Имя поля — Название, Тип данных — текстовый. Третья строка: Имя поля — Курс, Тип данных — числовой и четвертая строка Имя поля — Семестр, Тип данных — числовой. При этом для имени поля «Название» в разделе свойства поля необходимо установить размер поля — 6.

Рис. 5.

Затем создаем структуры остальных трех таблиц в соответствии с характеристиками таблиц-объектов Студенты, Дисциплины, Успеваемость. Обязательно соблюдайте указанную последовательность создания структуры таблиц.

Необходимо отметить, что в структуре таблицы «Студенты» для поля КодГруппы (вторичный ключ) установите значение Индексированного поля: Да (Совпадения допускаются) и тип данных — мастер подстановок. В структуре таблицы «Успеваемость» для поля КодСтуденты (вторичный ключ) и поля КодДисциплины (вторичный ключ) установите значение Индексированного поля: Да (Совпадения допускаются) и тип данных — мастер подстановок.

Структуры остальных таблиц: Студенты, Дисциплины, Успеваемость:

Рис. 6

Рис. 7

Рис. 8

После этого необходимо установить логические связи между всеми таблицами.

Далее >>> 2.4.3. Установка связей между таблицами в СУБД Access 2007

Как сделать форму главной в access?

На этом шаге вы познакомитесь с видами форм.

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

1. Отображение и изменение данных. Наиболее часто формы создаются именно для вывода и изменения данных в БД. При этом разработчик может задать любой вид отображения информации, т.е. произвольный набор полей из одной или нескольких таблиц или запросов (если поля находятся в разных таблицах, то между этими таблицами должна быть связь). Также можно установить любые ограничения на редактирование данных в форме, т.е. разрешить редактирование одних полей и запретить изменение других.
2. Ввод данных. Формы могут использоваться для ввода новых данных в таблицу, при этом ввод будет выполняться пользователем в том формате, который задан разработчиком. Другими словами, пользователь может вводить значения только для тех полей, которые представлены в форме.
3. Печать. Несмотря на то, что для печати данных в БД используются отчеты, содержимое формы также можно распечатать.
4. Сообщения. Дополнительная возможность использования форм заключается в создании с их помощью различных сообщений, отображаемых впоследствии в той или иной ситуации для облегчения работы пользователя. В таком виде формы используются в приложениях.

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

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

Ленточная — все поля записи выводятся в одну строку, при этом в форме отображаются все записи. В ленточной форме Студенты1, представленной на рисунке 2, отображены все записи из таблицы Студенты.

Табличная — отображение записей осуществляется в режиме таблицы. На рисунке 3 представлена табличная форма Студенты, в которой все записи таблицы Студенты выведены в табличном режиме.

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

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

На следующем шаге рассмотрим создание формы с помощью мастера.

Предыдущий шаг

Расширенный перекрестный запрос

Продолжаем усложнять ситуацию. Еще труднее для понимания является информация о том, как создавать запросы в Access, если присутствует несколько таблиц с данными. Перекрестный запрос уже рассматривался выше, как один из вариантов работы с Мастером. Однако, и в режиме «Конструктора» можно создавать подобный запрос.

Для этого необходимо нажать «Конструктор запросов» — «Перекрестный».

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

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

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

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

Конечно, присутствуют и «подводные камни», которые могут помешать в работе. Например, при создании запроса на сортировку базы данных по значению столбцов система выдает ошибку. То есть доступна только сортировка по стандартным пунктам – «возрастание и убывание».