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

 

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

Access позволяет создавать запросы двух типов: QBE-запросы, SQL-запросы.

QBE-запросы (Query By Example) – запросы, строящиеся с помощью конструктора запросов, представляющего собой графический инструмент для создания запросов по образцу.

SQL-запросы – запросы, строящиеся при помощи унифицированного набора инструкций SQL(Structurred Query Language – структурированный язык запросов).

Все запросы делятся на две группы: запросы на выборку, запросы-действия.

Запросы-выборки осуществляют выборку данных из таблиц в соответствии с заданными условиями. К этой группе запросов относятся следующие.

Запрос к связанным таблицам – позволяет производить выборку данных из связанных таблиц.

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

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

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

Запрос с критерием поиска – позволяет производить отбор записей в соответствии с заданным критерием поиска.

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

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

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

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

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

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

В Access можно создавать запросы с помощью при помощи Мастера запросов и с помощью Конструктора.

Для создания запросов при помощи Мастера имеются следующие мастера:

§ простой запрос;

§ перекрестный запрос;

§ Повторяющиеся записи

§ записи без подчиненных записей.

При создании запроса с помощью мастера производится пошаговое выполнение действий в диалоговом окне Мастера запросов.

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

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

И ещё одним ценным свойством запросов является их способность выполнять итоговые вычисления.

При создании запросов в режиме Конструктора открывается специальный бланк, называемый бланком запросов по образцу (рис. 3.9). Это бланк состоит из двух областей. В верхней части отражаются структуры таблиц, к которым адресован запрос, а нижняя часть представляет собой таблицу описания запроса, которая имеет 5 основных строк. Количество столбцов в запросе определяется количеством выбранных полей, которые будут использоваться в запросе.

 

 

Рис. 3.9 Бланк заполнения запросов по образцу

 

Назначение строк в бланке Конструктора запросов:

1-я строка Поле предназначена для ввода имени поля запроса и обозначения поля источника данных или вычисляемого выражения;

2-я строка Имя таблицы предназначена для задания имени таблицы источника данных;

3-я строка Сортировка предназначена для указания порядка сортировки в данном поле. Может принимать следующие значения: по возрастанию, по убыванию, отсутствует;

4-я строка Вывод на экран содержит флажок, указывающий на то, будет ли выводиться соответствующее значение поля;

5-я строка Условие отбора содержит критерий, по которому будет происходить отбор записей в результирующую таблицу.

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

При создании макета-запроса, в общем случае, необходимо выполнить четыре базовые операции:

1. указать системе, какие поля и из каких таблиц мы хотим включить в запрос;

2. описать вычисляемые поля, т.е. поля, значения которых являются функциями значений существующих полей (например, стоимость продукции – это произведение цены на количество);

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

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

Запрос к связанным таблицам – это производная таблица, которая содержит те же структурные элементы, что и обычная таблица (столбцы-поля и строки), и оформляется на основе фактических данных системы.

Сначала надо выполнить стандартные операции:

Нажать вкладку Создание - Другие и выбрать Конструктор запросов.

Откроется окно Запрос на выборку и диалоговое окно Добавление таблицы (рис 3.10).

В окне Добавление таблицы выделите нужную таблицу, и нажмите кнопку Добавить.

 

Рис. 3.10. Диалоговое окно для добавления таблиц при создании запросов

 

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

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

Строка Имя таблицы заполняется автоматически при перетаскивании поля левой кнопкой мыши.

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

Условие отбора. По каждому полю можно создать своё условие отбора. Для добавления в строку Условие отбора значения вы можете использовать любое выражение, которое способно принимать значение True или False. При создании условия используют операции сравнения: больше (>), меньше (<), больше либо равно (>=), меньше либо равно (<=), не равно (<>). Условие отбора может осуществляться по одному полю или по несколько полям одновременно. Если отбор осуществляется по нескольким полям и условия для этих полей записаны в донной строке, то между этими условиями выполняется логическая операция AND, между условиями, записанными в разных строках, выполняется логическая операция OR. Может быть также использована еще одна логическая операция отрицания NOT.Если выражения связаны оператором AND, результат будет принимать значение True лишь тогда, когда истины все выражения, связанные этим оператором. В отличие от этого, операция OR истинна, когда истинно хотя бы одно или сразу оба выражения. Операция NOT выдает значение, противоположное обрабатываемому выражению, - истину, если выражение ложно, и ложь, если выражение истинно. Кроме обычных операторов сравнения, MS Access предоставляет четыре специальных оператора, полезных для отбора данных: BETWEEN, IS, IN, LIKE. Between – определяет, находится ли числовое значение в определенном диапазоне значений (например Between (100) AND (100)). Is – при использовании вместе с нулем (Null) определяет, является ли значение нулем или нет (например Is Null или Is Not Null). In – определяет, является ли строковое значение элементом списка значений (например, In(“Москва”, ”Киев”,”Санткт-Петербург”). Like – определяет, начинается ли строковое значение с указанных символов (например, Like “Ив*” или Like “db??”).Где * ‑ соответствует любому количеству символов,? -- соответствует одному символу, # ‑ соответствует одной цифре. Кроме того константы даты и времени выделяются символами # (например, #26/04/2005#).

Сохранение запроса осуществляется закрытием окна бланка запроса (правая кнопка мыши – закрыть). При закрытии запроса введите его имя.

Запрос с параметром – это специальный тип запросов позволяет пользователю самому ввести критерии отбора данных на этапе запуска запроса. Этим приёмом обеспечивается гибкость работы с базой. Запрос с параметром отличается от обычного запроса тем, что в параметр запроса вводится в структуре в то поле, по которому осуществляется отбор. Для этого параметр запроса, заключается в квадратные скобки и записывается в строке Условие отбора. По умолчанию Access назначает параметром запроса текстовый тип данных. Сохраните запрос на диске. И когда вы откроете этот запрос, Access отображает диалоговое окно, предлагающее вам ввести некоторые данные, которые будут использоваться в качестве условий отбора для запроса. (Вкладка Создание - конструктор запросов – вкладка Конструктор – показать или скрыть – параметр)

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

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

сумма: [цена]*[количество]

где: сумма: - название нового поля,

[цена] – название первогополя, участвующего в вычислении,

* - знак операции умножения,

[количество] – название второго поля, участвующего в вычислении.

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

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

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

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

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

 

Рис. 3.11. Бланк запроса с использованием операций группировки

 

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

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

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

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

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

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

Логика использования запросов на изменение такая:

· создаётся запрос на выборку, который отбирает данные из разных таблиц или сам создаёт новые данные путём вычисления;

· после запуска образуется временная результирующая таблица;

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

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

1. Создаётся запрос на выборку, в который войдут все поля базовой таблицы плюс вычисляемое поле.

2. При нажатии на кнопку Вид - можно убедиться, что запрос работает, как положено и создаёт результирующую таблицу, более полную, чем базовая.

3. Далее использование пункта меню Запрос, который доступен только в режиме конструктора, и выбор команды Создание таблицы вкладки конструктор – тип запроса

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

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