Вычисления в запросах
Для боле быстрой загрузки базы данных и уменьшения размера внутренних файлов следует придерживаться правила размещать информацию в таблицах, а вычисления производить в запросах.
Вычисляемое поле существует только в результирующей таблице запроса, в базовую таблицу оно не переносится.
Для создания запроса с вычислениями служит тот же бланк простого запроса. Разница только в том, что в одном из пустых столбцов вместо имени записывается формула.
В формулу входят заключенные в квадратные скобки названия полей, участвующих в расчете, и знаки математических операций (рис. 25).
Рис. 25. Пример записи формулы.
Для ввода длинной формулы размер окна раздела Поле недостаточен, поэтому формулу не видно целиком. Границы поля, конечно, можно увеличить, но тогда не будет видно остальных полей запроса. Эти неудобства можно ликвидировать нажатием клавиш Shift+F2 . Откроется диалоговое окно Область ввода (рис. 26), где формула будет видна целиком. (Сама формула вводится с клавиатуры).
Рис. 26. Запись и просмотр формулы в окне Область ввода.
Вводить формулу можно также с помощью построителя выражений (рис. 27), который вызывается на экран клавишами Ctrl+F2.
Рис. 27. Составление формулы в окне Построителя выражений.
В левом столбце открывшегося окна Построитель выражений нужно выбрать таблицу или запрос, тогда в среднем столбце откроется список полей этой таблицы или запроса; необходимо выделить нужное поле и нажать на кнопку Вставить. Поле будет включено в формулу в верхнем окне построителя выражений. Чуть ниже окна с формулой расположена панель инструментов. Кнопки этой панели служат для добавления в формулы математических знаков. Формула записывается без пробелов, если только их вставка специально не оговорена. Кнопка ОК и формула помещаются в окно раздела Поле конструктора запроса.
Поля, входящие в формулу, можно не выводить на экран, то есть снять флажок в разделе Вывод на экран, если они не отображаются в новой таблице, но участвуют в расчетах. Эти поля даже можно не отбирать, достаточно того, чтобы были отобраны таблицы, в которые они входят.
К результирующему полю можно применить сортировку.
3.5. Работа с формами в субд Access
Форма — это объект Access, без которого можно обойтись. Он создан специально для пользователя. Экран принимает удобный для обработки информации вид с учетом того, что пользователь может работать в БД несколько часов. Оттого, каким будет экран компьютера все это время, зависит настроение и здоровье. Часто формы приближают к виду уже известных бумажных документов. Такой подход позволяет специалистам других отраслей легко перейти на электронные документы.
Через формы можно загружать данные в таблицы, просматривать и корректировать их. Работая с формой, пользователь может добавлять и удалять записи в таблицах, изменять значения в полях, получать расчетные данные. В форме можно контролировать вводимые данные, устанавливать ограничения на доступ к информации, выводить необходимые сообщения.
Источником данных для создания форм являются таблицы или запросы. Форму можно создать с помощью мастера или конструктора.
Таким образом, можно сделать вывод, что формы в Access являются главным средством разработки пользовательского интерфейса. В зависимости от того, с какой целью создается форма, разработчик определяет параметры ее внешнего вида.
Как можно сделать вычисления в запросах access
Очень часто возникает необходимость выполнить ряд вычислений в процессе работы запроса и вывести результаты, а не исходные значения. Для этого в запрос вводится специальное поле, содержимое которого является результатом расчета по содержимому других полей. Такое поле называется вычисляемым полем. В исходных таблицах базы данных такое поле не создается и его значение не влияет на содержимое этих таблиц. Это сделано для защиты информации в базе данных. Каждое обращение к базе, может с помощью запросов манипулировать данными и получать любые результаты, но все это будет существовать только в пределах Recordset (набора записей) — временной итоговой таблицы, хранимой только в памяти компьютера.
Для создания запроса, производящего вычисления, служит тот же самый стандартный бланк запроса. Разница заключается только в том, что в одном из столбцов вместо имени поля записывается формула. В формулу вводят заключенные в квадратные скобки названия полей связанных таблиц, принимающих участие в расчете, а также знаки математических операций:
До двоеточия следует название вычисляемого поля, выводимое в заголовке столбца результирующей таблицы. В узкое поле бланка запроса неудобно записывать длинные формулы, но если воспользоваться комбинацией клавиш Shiht+F2, то откроется вспомогательное окно Область ввода. В нем есть возможность ввести сколь угодно длинную формулу, а затем добавить ее в поле запроса, простым нажатием на кнопку ОК.
Установка флага Вывод на экран, приводит к отображению результат вычисляемого поля в результирующей таблице. Формат отображения информации определяется в окне Свойства поля, отрываемом командой Свойства в контекстном меню, правой кнопки мыши. Здесь доступны все возможные форматы представления результатов вычислений.
Кроме того, можно указать параметры сортировки, для первичного анализа выводимой информации. Вот как будет выглядеть результат работы запроса Доход, отсортированный по возрастанию суммы полученного дохода. Числа выводятся с фиксированной длиной поля.
Как можно сделать вычисления в запросах access
На этом шаге будет рассмотрено создание вычисляемых полей.
При создании запросов часто возникает необходимость не только использовать имеющиеся поля таблиц, но и создавать на их основе другие поля, которые называются вычисляемыми. Например, если в таблице какого-либо магазина имеется поле цены на товар и поле количества этого товара, то, исходя из этого, можно создать вычисляемое поле, в котором будет подсчитываться общая стоимость для каждого товара путем перемножения значений цены и количества. Другими словами, в вычисляемом поле могут использоваться арифметические операторы.
С другой стороны, при создании вычисляемого поля на основе содержимого текстовых полей, как правило, используется операция объединения текстовых значений, которая называется конкатенацией. В таблице 1 приведены операторы, которые используются в Access при построении выражений:
Очевидно, что рассмотренные операторы предоставляют достаточно узкий спектр возможностей по созданию выражений. Чтобы создать сложное выражение, используют мощное средство, которое носит название Построитель выражений. Эта программа позволяет построить выражение любой сложности с использованием самых разнообразных функций (рис. 1).
Рис. 1. Построитель выражений
С ее помощью можно выполнять различные вычисления с использованием данных, находящихся в БД — полей таблиц, запросов, форм и отчетов. Все перечисленные объекты БД находятся, соответственно, в папках Таблицы, Запросы, Forms и Reports в левой части Построителя.
Чтобы выбрать требуемый объект, например, таблицу, необходимо выполнить двойной щелчок на папке Таблицы, в результате чего раскроется список всех таблиц БД. После выбора нужной таблицы (или другого объекта) в средней части Построителя отображается список ее полей, значения которых можно включить в создаваемое выражение. Для этого необходимо выбрать требуемое поле и нажать в окне Построителя кнопку Вставить.
При построении выражения кроме названий полей могут использоваться различные операторы (таблица 1), добавление которых выполняется при нажатии соответствующих кнопок в окне Построителя.
Также существует возможность использования в выражении различных функций Access. Для этого в левой части окна необходимо открыть папку Функции и выбрать пункт Встроенные функции. Затем следует в средней части Построителя определить категорию, в которой находится требуемая функция, и выбрать после этого в правой части окна название искомой функций (рис. 2, 3). Создаваемое таким образом выражение отображается в верхней части окна Построителя выражений.
Рис. 2. Построитель выражений. Встроенные функции
Рис. 3. Построитель выражений. Текстовая функция Left
Например, необходимо в запросе ПреподавателиПредметы вместо поля Фамилия добавитъ вычисляемое поле, в котором содержались бы фамилия и инициалы преподавателя.
Для этого необходимо открыть запрос в режиме конструктора. Затем установить курсор в строку Поле первого столбца, т.е. в поле Фамилия. После этого необходимо воспользоваться кнопкой Построить 
, в результате чего будет отображено окно Построитель выражений (рис. 1).
Примечание. Кнопка Построить позволяет не только создавать новые выражения, с ее помощью можно также изменять уже существующее выражение, которое в этом случае должно находиться в текущем поле.
Названия полей в выражении должны указываться в квадратных скобках, однако если поле будет указано без скобок, то Access их добавит автоматически.
Для создания текстовой строки с фамилией и инициалами необходимо несколько раз последовательно выполнить операцию конкатенации следующей структуры:
Фамилия & Символ Пробела & Инициал имени & Символ точки & Инициал отчества & Символ точки.
Все текстовые символы указываются в двойных кавычках, поэтому структура выражения примет такой вид:
Таким образом, начало выражения будет следующим:
Для работы с текстовыми значениями используются функции категории Текстовые. Чтобы получить инициал имени, необходимо воспользоваться одной из функций данной категории, а именно — функцией Lеft, которая возвращает первые n символов указанной строки.
Чтобы добавить функцию в выражение, необходимо дважды щелкнуть на папке Функции и выбрать после этого раздел Встроенные функции (рис. 2).
Затем во втором столбце необходимо выбрать требуемую категорию, в данном случае Текстовые, и после этого в третьем столбце выбрать нужную функцию, т.е. Left (рис. 3).
После того как функция выбрана, необходимо установить курсор в то место выражения, куда она должна быть вставлена, и нажать кнопку Вставить в окне Построителя выражений. В результате этих действий в выражение будет добавлен шаблон функции Left:
Left («stringexpr»; «n») ,
в котором необходимо указать значения требуемых параметров. В частности, вместо stringexpr нужно указать поле Имя, а вместо n — количество символов, которые необходимо получить из строки, т.е. 1. Таким образом, текущее выражение примет вид:
Необходимо отметить, что для поля Имя не указывалось название таблицы Преподаватели, так как оно было выбрано ранее в строке Имя таблицы окна конструктора запросов. С другой стороны, чтобы добавить в выражение имя поля конкретной таблицы, можно раскрыть папку Таблицы, и выбрать в таблице Преподаватели поле Имя (рис. 4), после чего установить курсор в соответствующее место выражения и нажать кнопку Вставить.
Рис. 4. Построитель выражений. Поле Имя таблицы Преподаватели
Таким образом, название поля с указанием имени таблицы необходимо отображать в следующем формате:
[Имя таблщы] ! [Название поля]
После указания первого инициала необходимо поставить точку, т.е. указать в выражении строку ".". Затем аналогичным способом добавить инициал отчества и снова поставить точку. Таким образом, будет получено следующее выражение:
Чтобы вернуться в окно конструктора запросов, необходимо нажать кнопку ОК. В результате созданное выражение будет находиться в строке Поле первого столбца запроса. Для выполнения запроса следует воспользоваться кнопкой Запуск 
, после чего в запросе будет создано вычисляемое поле Выражение1, в котором содержатся фамилия и инициалы преподавателя.
Названия полей запроса формируются автоматически. Для полей таблиц названия совпадают с названиями соответствующих полей запроса (например, поле Название). С другой стороны, названия вычисляемых полей формируются автоматически; Выражение1, Выражение2 и т. д. Очевидно, что такие названия не являются информативными.
Чтобы установить подпись для поля в таблице или запросе, необходимо в режиме конструктора установить курсор в требуемое поле (в частности, в поле Выражение1) и нажать кнопку Свойства 
. После этого в диалоговом окне Свойства поля можно на вкладке Общие указать в строке Подпись то название поля, которое будет использоваться вместо исходного в режиме редактирования запроса (или таблицы), а также в формах и отчетах. В частности, для вычисляемого поля Выражение1 можно указать подпись ФИО (рис. 5).
Рис. 5. Диалоговое окно Свойства поля
Таким образом, был создан и выполнен запрос на выборку с использованием вычисляемого поля (рис. 6).
Рис. 6. Запрос на выборку с вычисляемым полем ФИО
Создание запроса с вычисляемым полем можно увидеть здесь, а взять клип здесь.
На следующем шаге вы узнаете о групповых операциях, которые можно использовать при создании запроса.
Вычисления в запросах. Создание отчетов
Вычисления в запросах. С помощью запроса можно создать поле, называемое вычисляемым, в котором будет представлен результат расчета (сумма, среднее и т. п.) по содержимому других полей таблицы. Вычисляемое поле существует только в результирующей таблице. Для создания запроса используют бланк запроса по образцу. Разница только в том, что в одном из столбцов вместо имени поля следует записать формулу, в которую входят названия полей (в квадратных скобках), содержащих исходные данные и знаки математических операций. Например,
Цена: [Истрачено (сумма)]/[Количество изделий]
название: [Поле 1] Знак [Поле 2]
Для записи длинной формулы, нажав сочетание клавиш Shift+F2, следует открыть диалоговое окно Область ввода. После ввода формулы щелчком по кнопке ОК ее можно перенести в бланк запроса по образцу. Результаты расчетов будут представлены в результирующей таблице при включении отображения вычисляемого поля.
Запросы на изменение позволяют автоматически создавать новые или изменять имеющиеся таблицы. Запрос на создание таблицы составляют с помощью запроса на выборку, в который войдут все поля базовой таблицы плюс новое вычисляемое поле. Щелкнув по кнопке Вид, следует убедиться, что создана результирующая таблица, более полная, чем базовая. Далее в меню Запрос в режиме Конструктора выбирают команду на создание базовой таблицы, равной результирующей. В этом же меню имеются команды для создания запросов на обновление данных, на добавление и удаление записей.
Создание отчетов. Отчет — это гибкое средство для организации данных при выводе на печать в требуемом виде. Отчеты создаются на основе таблиц, форм, запросов или инструкции SQL. Отчеты предназначены для вывода информации на принтер, поэтому принтер должен быть установлен. Отчет можно создать и при отсутствии принтера. Следует выполнить команды Пуск Настройка -> Принтеры -> Установка принтера. Далее нужно зарегистрировать драйвер принтера, взяв один из драйверов, входящих в дистрибутив ОС.
Для составления отчетов следует выбрать в диалоговом окне База данных вкладку Отчеты и щелкнуть по кнопке Создать. В диалоговом окне Новый отчет можно создать автоотчет, используя режим Мастера или вручную. Мастер автоматически выполнит большинство операций, задавая вопросы, на которые пользователь должен ответить. Со структурой отчета можно ознакомиться, создав какой-либо автоотчет, а затем открыв его в режиме Конструктора.
Структура отчета состоит из 5 разделов: заголовка, верхнего колонтитула, области данных, нижнего колонтитула, примечания.
Раздел заголовка — дается общий заголовок отчета. Раздел верхнего колонтитула используется для печати подзаголовков, номеров страниц (колонцифры), логотипов и др. Область данных предназначена для размещения элементов управления, в которые выдаются данные из таблиц базы для печати на принтере. Раздел нижнего колонтитула используется для печати таких же данных, как и в верхнем колонтитуле. При этом могут использоваться встроенные функции для вывода текущей даты и страниц (функция Now (), Page (), Pages ()) и др. Оператор & (оператор конкатенации) служит при этом для «склеивания» текста, заключенного в кавычки, со значениями, возвращаемыми функциями. Раздел примечаний предназначен для дополнительной информации.
Для получения практических навыков работы по вычислениям в запросах, включая создание вычисляемого поля и итоговые вычисления, и созданию отчетов на экране и в виде бумажного документа в базе данных под управлением СУБД Access используем БД «Студенты», созданную в разделе 6.1:
- 1. Запускаем программу MS Access. Открываем базу данных «Студенты».
- 2. Выполняем вычисления в запросах. С помощью запроса создаем вычисляемое поле «Успев_%» (набор баллов в процентах каждым студентом), в котором представлен результат расчета по содержимому поля «Балл» таблицы «Адрес_Специализация». Для создания запроса заполняем бланк запроса по образцу. В вычисляемом поле вместо имени поля записываем формулу — Ус- пев_%: [Балл]/21*100. Результаты расчетов представлены в результирующей таблице (рис. 6.16).
- 3. На основе таблиц создаем отчеты. В окне базы данных выбираем объект Отчеты и нажимаем кнопку Создать. В появившемся окне выбираем вариант Автоотчет ленточный для таблицы «Адрес_Специализация» (рис. 6.17).
- 4. Создаем отчет с помощью Мастера отчетов. Выбираем таблицу «Успеваемость» и в ней 4 поля, которые будут входить в отчет. Нажимаем кнопку Далее. Затем в диалоговых окнах определяем уровни группировки, порядок полей и сортировку данных в полях. Выбираем табличный макет, альбомную ориентацию
Рис. 6.16. Вычисления в запросах
Рис. 6.17. Автоотчет ленточный
Рис. 6.18. Отчет, составленный с помощью Мастера отчетов
Рис. 6.19. Создание отчета в режиме Конструктора стиль заголовка Обычный. Нажимаем кнопку Готово. Результат представлен на рис. 6.18.
Создаем отчет с помощью Конструктора. Выбираем таблицу «Список рассылки». Создаем автоотчет, а затем открываем его в режиме Конструктора. Заполняем необходимые разделы и отправляем на печать (рис. 6.19).