lingvohelper.ru → Ноутбук

Формирование запросов. Создание запросов Повторное использование одного источника данных

Запросы – это один из видов документов, используемых в СУБД Access , которые предназначены для обработки данных, хранимых в таблицах

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

Мастер запроса за несколько шагов собирает сведения, необходимые для формирования запроса, а затем автоматически составляет его в зависимости от ответов пользователя на поставленные вопросы.

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

§ Простой запрос.

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

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

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

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

Рисунок 1 Окно БД Штат и окно выбора видов запросов

Простой запрос позволяет создать с помощью Мастера запрос на выборку данных из определенных полей таблиц или запросов, он наиболее удобен для начинающих пользователей. При его выборе запускается Мастер, в первом окне которого (рис. 2) нужно в списке Таблицы и запросы выбрать таблицу, напр., Штат преподавателей, выбрать из перечня её доступных полей те, которые должны присутствовать в запросе и перевести каждое нажатием кнопки [>]. Аналогично в запрос добавляются поля из других таблиц той же БД.

Примечание. Запрос может составляться только по таблицам или только по запросам БД. Объединение в запросе полей из таблицы и запроса не допускается.

Рисунок 2 Выбор полей для запроса.

Перекрестный запрос имеет вид таблицы, в которой выводится до трёх полей (столбцов) исходной таблицы, ячейки одного из оставшихся преобразуются в новые столбцы, а на их пересечении выводится одно из указанных пользователем значений – Дисперсия, Минимум, Максимум, Среднее, Отклонение, Число, Первое, Последнее и др. Например, запрос на рис. 4 является перекрёстным запросом таблицы 1 на рис. 3, в котором величины расстояний стали названиями столбцов:

Вариант Повторяющиеся записи создаёт запрос на поиск повторяющихся записей (строк) в одной таблице или запросе, для таблицы на рис. 3 при заданных полях Расстояние и Стоимость проезда он имеет вид (рис.5)

MS Access позволяет создать такой запрос только для одной таблицы или запроса (не для нескольких таблиц БД), причём в нём нужно задавать

Рисунок 3 Таблица 1 для создания перекрёстного запроса

Рисунок 4 Перекрёстный запрос табл. 1

только те поля, в которых есть полное одновременное совпадение данных из записей (например, поле Транспорт в этот запрос включать нельзя). Дополнительно, для распознавания, можно включить неповторяющееся поле (Город).

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

Рисунок 6 Основная таблица 1

Запрос без подчинённых на сравнение таблиц рис. 3 и рис. 6 выведет на экран несовпадающую строку (рис. 7):

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

4. Формирование запросов к базе данных

4.1 Общее понятие запросов

Запрос - это требование на извлечение данных из таблиц базы, на выполнение вычислений над данными, на внесение изменений в базу данных.

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

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

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

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

Выбирать записи, удовлетворяющие условиям отбора;

Осуществлять вычисления над полями базы данных;

Группировать записи и находить для групп записей или для всех записей итоговые значения по некоторым полям с помощью статистических функций: Sum – сумма значений поля, Avg – среднее значений поля, Max – максимальное из значений поля, Min – минимальное из значений поля, Count – число значений поля и др.

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

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

Существует четыре типа запросов на внесение изменений в базу данных:

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

4) запрос на создание таблицы - создает новую таблицу на основе всех или части данных из одной или нескольких таблиц базы данных.

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

4.2 Создание запроса выбора

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

Окно БД - объект Запросы - [Создать] - окно Новый запрос - выбрать Конструктор - окно Добавление таблицы - выбрать таблицы-источники запроса - [Закрыть].

Окно Конструктора запроса имеет вид как на рис. 4.1.

Рис. 4.1 Окно Конструктора запроса

Оно разделено на две панели.

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

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

· Поле – указывает имена полей, участвующих в формировании запроса;

· Имя таблицы - указывает имена таблиц, которым принадлежат эти поля;

· Сортировка – дает возможность отсортировать записи в результирующей таблице запроса;

· Вывод на экран – позволяет управлять отображением полей в этой таблице;

· Условие отбора – служит для задания условий отбора записей;

· или – позволяет объединять условия отбора логической операцией ИЛИ. При этом условия отбора могут указываться в нескольких строках бланка запроса.

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

· перетащить поле из списка полей в крайнюю слева свободную клетку строки Поле;

· дважды щелкнуть по имени поля в списке полей;

· щелкнуть в клетке строки Поле и из раскрывающегося списка выбрать нужное поле.

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

· дважды щелкнуть по имени таблицы и перетащить все выделенные поля в клетку строки Поле;

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

Для очистки отдельных столбцов бланка запроса используется команда - Правка/Удалить столбцы.

Для очистки всего бланка запроса служит команда - Правка/Очистить бланк.

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

Формирование запроса с применением сортировки.

Сортировка возможна по одному или нескольким полям одновременно. Для сортировки по нескольким полям поля располагаются в бланке запроса в том порядке, в котором требуется выполнять сортировку. В Access записи сортируются сначала по самому левому полю, затем по полю, расположенному в следующем столбце справа и т.д. Для задания порядка сортировки необходимо щелкнуть в строке Сортировка для поля, по которому сортируются записи, и из раскрывающегося списка выбрать – по возрастанию или по убыванию. Пример создания запроса с применением сортировки см. в Приложении 9-10.

Формирование запроса с условиями отбора.

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

Если выражения вводятся в несколько клеток одной строки Условие отбора, то они автоматически объединяются с помощью логического оператора And. Если выражения вводятся в разные строки бланка запроса, то Access объединяет их логическим оператором Or. Пример создания запроса с условием для создаваемой базы данных см. в Приложении 11-12.

В условии отбора может быть использована конструкция Between (между). Например, Between 100 and 200. Результат построения запроса с использованием конструкции Between см. в Приложении 13-14.

Формирование запроса с вычисляемым полем.

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

СТОИМОСТЬ: [ЦЕНА]*[КОЛИЧЕСТВО]

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

Для проектируемой базы данных запрос с вычисляемым полем имеет вид – см. в Приложении 15-16.

Формирование запроса с группировкой.

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

Для создания запроса с группировкой выполняется следующее:

· перетаскивается в первую клетку строки Поле то поле, по которому производится группировка записей. Затем перетаскиваются в последующие клетки поля, по которым подводятся итоги;

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

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

Формирование запроса с параметрами

Если необходимо часто выполнять один и тот же запрос выбора, меняя только в условиях отбора значения полей, то целесообразно создать запрос с параметрами. В строках Условие отбора, или для полей, играющих роль параметров, вводится в квадратных скобках текст приглашения на ввод интересующих пользователя значений этих полей. Этот текст будет выводиться в диалоговом окне "Введите значение параметра при выполнении запроса". Результат построения запроса с параметрами приведен в Приложении 17-18.

4.3 Создание перекрестного запроса

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

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

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

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

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

Результат создания перекрестного запроса представлен в Приложении 19-20.

4.4 Создание запросов действия

Формирование запроса на создание таблицы БД.

После открытия окна конструктора запроса вводится команда Запрос / Создание таблицы

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

Формирование запроса на обновление полей таблицы БД

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

Формирование запроса на добавление записей к таблице БД.

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

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

Формирование запроса на удаление записей из таблицы БД.

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

В этом случае при удалении записей из подчиненной таблицы в окне конструктора запроса вводится команда Запрос / Удаление. В результате в бланке запроса появится новая строка Удаление. Тогда из списка полей подчиненной таблицы перетаскивается в бланк запроса символ звездочки (*), после чего в строке Удаление для этого поля отобразится значение Из. Затем из списка полей главной таблицы перетаскиваются поля, участвующие в условии отбора удаляемых записей, и для них в строке Удаление появится значение Условие. После этого задаются условия отбора удаляемых записей.

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

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

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

4.5 Выполнение и сохранение запроса

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

Процедура формирования запросов ИОН-offline в программе «Референт».

В разделе «Выходной контроль» окна программы «Референт» нужно нажать кнопку («Запросы ФНС») на панели инструментов.

Примечание. Если программа «Референт» настроена на работу с несколькими почтовыми ящиками, то после нажатия на кнопку «Запросы ФНС» откроется окно «Запросы ФНС. Выбор ящика». В этом окне с помощью кнопки следует выбрать из выпадающего списка наименование организации, с почтового ящика которой нужно отправить запрос, и нажать на кнопку «ОК».

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

Для того чтобы было удобнее найти нужные КБК, используется поле «Фильтр».

После того как все необходимые КБК выбраны, нажмите кнопку «Сохранить».

В поле «Группировка» окна «Запрос ИОН» указывается одно из двух значений:

группировать по видам платежа;
не группировать по видам платежа.

Если необходимо сформировать запрос на перечень налоговых деклараций (расчётов) и бухгалтерской отчётности, то нужно заполнить поля «С» и «По» , где указывается период, на который нужен запрос. Также в поле «Отчётность» указывается тип документов:

все виды деклараций;
первичные;
корректирующие (последняя коррекция).

Если нужно получить акт сверки расчётов по налогам, сборам, пени и штрафам, то следует указать дату, на которую требуется сверка (в поле «На дату»), и добавить нужные КБК в список вышеуказанным способом.

После того как все необходимые поля будут заполнены, следует нажать на кнопку «Создать».

Затем в окне «Выходного контроля» появится файл для отправки, который необходимо пометить галочкой и нажать «Отправить».

Данная форма позволяет сформировать запросы в инспекции ФНС России на получение следующих документов:

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

В поле «Отправитель» указывается организация, с почтового ящика которой отправляется запрос. Поле «Направление (ИФНС)» показывает номер транзитной налоговой инспекции (указывается для КНП, во всех остальных случаях номера транзитной ИФНС и конечной совпадают). В поле «Ответственное лицо» указывается Ф.И.О. владельца сертификата ключа подписи.

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

Далее в окне «Запрос ИОН» нужно указать налоговую инспекцию, в которой состоит на учёте налогоплательщик (поле «Конечная ИФНС»). Если в поле «Конечная ИФНС» указать значение «0000», то налогоплательщик получит ответ по всем обособленным подразделениям своей организации. Поле «КПП» нужно заполнить, если необходимо получить информацию по одному конкретному объекту налогообложения, или оставить пустым в случае, когда требуется информация по всем объектам.

Также необходимо указать желаемый формат ответа из ИФНС (XML, XLS, PDF или RTF) в поле «Формат ответа». В поле «Код запроса» указывается, какой именно документ нужно получить из налоговой инспекции:

справка о состоянии расчётов по налогам, сборам, пени и штрафам;
выписка операций по расчётам с бюджетом;
перечень налоговых деклараций (расчётов) и бухгалтерской отчётности;
акт сверки расчётов по налогам, сборам, пеням, штрафам, процентам.

Справка об исполнении налогоплательщиком (плательщиком сборов, налоговым агентом) обязанности по уплате налогов, сборов, пеней, штрафов.

Окно «Запрос ИОН» меняет вид в соответствии с тем, какое значение установлено в поле «Код запроса».

Если нужно получить справку о состоянии расчётов по налогам, сборам, пени и штрафам, то далее заполняется поле «На дату» (число, на которое хотите получить справку из ИФНС).

Если необходимо сформировать запрос на выписку операций по расчётам с бюджетом, то появляются следующие поля: «Год», «Группировка» и поле, где необходимо указать КБК. В поле «Год» нужно указать год, на который требуется выписка операций. Далее нужно выбрать интересующие КБК. Они добавляются в список с помощью кнопки(при нажатии этой кнопки появляется справочник, из которого выбираются нужные КБК двойным щелчком мыши) и удаляются из списка с помощью кнопки . Также в запросах с типом 2 и 4 имеется возможность ввода КБК вручную. Для этого нужно ввести код КБК в поле «КБК», которое находится над справочником, и нажать клавишу Enter.

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

Для того чтобы настроить список выбора КБК, сверху в меню выберите «Справочники - КБК...».

Откроется окно «Выбор КБК».

Инструкция по формированию запросов ИОН-offline в системе «Онлайн-Спринтер»

Для того чтобы воспользоваться услугой ИОН-offline, необходимо:

Инструкция по формированию запросов offline для ПК «1С-Спринтер»

Для формирования запроса:

Запрос в ПФР отправляется аналогичным образом с закладки «ПФР».

Как создать и отправить запрос ИОН-offline в программе Такском-Доклайнер?

Для создания запроса ИОН-offline в программе «Такском-Доклайнер»:

Лекция 16. Создание запросов средствами MS Access 2000

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

§ соединение данных нескольких таблиц;

§ отображение только требуемых полей;

§ формирование сложных критериев отбора записей;

§ вычисления с использованием данных из исходных таблиц;

§ группировку информации по каким-либо критериям;

§ модификацию данных в таблицах.

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

§ Конструктор запросов – средство конструирования запросов с использованием QBE (Query by example – запрос по образцу), требует минимальных знаний. Средство, уступающее режиму SQL .

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

§ Перекрестный запрос – позволяет создавать результирующие таблицы на основе результатов расчетов, полученных при анализе группы таблиц;

§ Запрос на повторяющиеся записи – средство создания запросов для выявления повторяющихся записей, выполнено в виде мастера.

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

Создание запросов начинается с выбора категории «Запросы» окна диалога «Базы данных». Затем, кнопкой «Создать» может быть вызвано окно диалога «Новый запрос», в котором выбирается один из вариантов создания запроса (рисунок 16.1).

Рисунок 16.1 - Диалоговое окно «Новый запрос»

Создание простых запросов с помощью мастера

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

Рисунок 16.2 - Окно мастера создания запросов

Процесс создания запроса с помощью мастера сводится к следующим шагам:

§ Запускается мастер создания простых запросов, например командой – Создание запроса с помощью мастера

§ в раскрывающемся списке «Таблицы и запросы» последовательно выбираются таблицы или запросы, информация из которых необходима пользователю, а затем, из списка «Доступные поля» в список «Выбранные поля» перемещаются требуемые поля (рисунок 16.2).

§ На втором шаге работы мастера определяется тип запроса: подробный или итоговый. Если выбран итоговый запрос, то необходимо определить итоговые операции над полями запроса: Max , Min , Sum , Avg или Count .

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

Создание запроса в режиме конструктора

Конструктор редко используется для создания запросов, чаще используется как средство модификации уже существующих. Конструктор запросов использует QBE (Query by example – запрос по образцу) позволяющий сформировать относительно сложные запросы на основе специального бланка, заполнение которого позволяет сформировать запрос, безусловно, этот способ уступает непосредственному использованию конструкций языка SQL , но требует значительно меньших знаний.

При необходимости можно использовать язык SQL , для этого необходимо перейти в режим SQL , переход выполняется командой контекстного меню конструктора запросов – «Режим SQL ».

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

Рисунок 16.3 - Окно конструктора запроса. Пример ввода условия.

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

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

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

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

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

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

Количество строк в QBE может варьироваться в зависимости от типа создаваемого запроса, например при использовании группировки появится новая строка – «Групповая операция».

Установка критериев отбора записей

Определение критериев выборки в запросах является одной из основных задач. Чтобы сформировать требуемый пользователю набор данных необходимо определить значения условий отбора в строке «Условие отбора:» QBE. В условиях отбора можно использовать логические операторы (or , and , not ), операторы сравнения (<, >, <=, >=, <>, = ), а также операторы Between , In и Like .

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

1. ввести всё условие в одну ячейку строки «Условие отбора:», с использованием логическогооператораили (or ).

2. одну часть условия указать в ячейке строки «Условие отбора:», а вторую часть условия вписать в нижнюю ячейку строки «Или:».

Оба варианта будут эквивалентными, второй вариант изображен на рисунке 16.4. Результатом этого запроса будет список студентов имеющих оценки 4 или 5.

Рисунок 16.4 - Использование логического оператора или ( or ) .

Логическая операцияи ( and ) используется в том случае, когда должны быть выполнены оба условия одновременно и только в этом случае запись будет включена в результирующий набор данных. Например, условие >2 and <5 даст список студентов имеющих оценки 3 и 4.

Логическая операция and может быть использована не только для одного поля, но и для нескольких полей, условия в которых должны выполниться одновременно, однако, в этом случае она используется неявно. На рисунке 16.5 показано неявное использование логической операции and . В результате такого запроса будут выданы все студенты, проживающие в городе «Уфа» и имеющие оценку 5. Фактически, если заглянуть в код SQL (в режиме SQL ) то можно увидеть следующее условие:

[Студенты].[Город] = “Уфа” and [Студенты и занятия].[Балл] = “5”

Рисунок 16.5 – Пример использования логической операции и ( and )

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

Оператор B etween позволяет задать диапазон значений, например диапазон от 10 до 20 может быть определен следующим образом:

between 10 and 20

Оператор I n позволяет использовать для сравнения список значений, указываемый в качестве аргумента. Например:

in (“первый”,”второй”,”третий”)

Оператор L ike полезен для поиска образцов в текстовых полях, причем позволяет использовать шаблоны:

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

Любой одиночный символ;

# - указывает, что в данной позиции должна быть цифра.

Например, для выбора фамилии, начинающейся с буквы П и с окончанием “ов” можно записать следующие условие:

Like П*ов

Особый подход к работе с полями типа дата. Значение даты в условиях отбора должно быть заключено в символы #. Например:

>#31.12.1996#

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

§ Day (дата) – возвращает значение дня месяца в диапазоне от 1 до 31;

§ Month (дата) – возвращает значение месяца в диапазоне от 1 до 12;

§ Year (дата) – возвращает значение года в диапазоне от 100 до 9999;

Текущую дату можно определить функцией Now (), используемую без аргументов.

Вычисляемые поля

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

Надбавка: [ Оклад]*0.15

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

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

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

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

Рисунок 16.6 - Окно построителя выражений

Прежде чем применить созданное выражение, рекомендуется определить имя вычисляемого поля, например «Сумма»:

Сумма: [ЦОтп]*[КолОтп]

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

Контрольные вопросы

1. Что такое запрос?

2. В чем заключаются функции запроса?

3. Какие способы создания запросов существуют в MS Access ?

4. Чем отличаются возможности различных способов создания запросов?

5. Как создается запрос с помощью мастера?

6. Что представляет собой бланк запросов?

7. Как сформировать условия отбора записей?

8. Назовите основные логические операторы.

9. Что представляет собой оператор b etween?

10. В чем назначение оператора Like ?

11. Как создаются вычисляемые поля?

12. Преимущества и недостатки конструктора запросов?

Задания для самостоятельной работы

Задание 1. Создайте запрос «Просроченные книги», по следующему описанию:

1. В окне «Базы данных» выбираем категорию «Запросы».

2. Открываем окно мастера командой «Создание запроса с помощью мастера».

3. В раскрывающемся списке «Таблиц и запросы» выбираем таблицу «Книги». Из списка «Доступные поля» в список «Выбранные поля» перемещаем поля «Название» и «Автор».

4. В раскрывающемся списке «Таблиц и запросы» выбираем таблицу «Экземпляры». Из списка «Доступные поля» в список «Выбранные поля» перемещаем поля «Инвентарный номер», «Дата возврата» и «Наличие».

5. В раскрывающемся списке «Таблиц и запросы» выбираем таблицу «Читатели». Из списка «Доступные поля» в список «Выбранные поля» перемещаем поля «Номер_ЧБ», «ФИО», «Тел_дом», «Тел_раб». Нажимаем кнопку «Далее».

6. На данном этапе выбираем подробный запрос и нажимаем кнопку «Далее».

7. Указываем имя запросаПросроченные_книги и нажимаем кнопку «Готово».

8. Откроем запрос«Просроченные_книги» в режиме конструктора, нажав кнопку «Конструктор» (рисунок 16.7).

9. В бланке запроса в строке «Условие отбора» для поля «Дата_возврата» укажем значение < Now ().

10. В бланке запроса в строке «Условие отбора» для поля «Наличие» установим значение нет и снимем флажок «Вывод на экран».

11. Закроем окно конструктора.

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

Задание 2. Создайте простой запрос, на получение информации о том, к каким предметным областям относятся книги.

Задание 3. Из запроса «Просроченные_книги» извлеките информацию о должниках (Номер читательского билета, ФИО, Домашний и рабочий телефоны)

Задание 4. Создайте запрос на получение списка книг одного автора.

Тема 2.3. Программные средства презентаций и основы офисного программирования

Тема 2.4. Системы управления базами данных и экспертные системы

2.4.11. Учебная база данных с главной кнопочной формой "Training_students" - Скачать

СУБД и экспертные системы

2.4. Системы управления базами данных и экспертные системы

2.4.4. Создание (формирование) запросов

Запрос (query) – это средство выбора необходимой информации из базы данных. Вопрос, сформированный по отношению к базе данных, и есть запрос. Применяются два типа запросов: по образцу (QBE – Query by example) и структурированный язык запросов (SQL – Structured Query Language).

QBE - запрос по образцу – средство для отыскания необходимой информации в базе данных. Он формируется не на специальном языке, а путем заполнения бланка запроса в окне Конструктора запросов.

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

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

2.4.4.1. Создание запроса на выборку с помощью Мастера

При создании query необходимо определить:

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

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

Рис. 1.

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

Затем в окне Мастера надо выбрать подробный или итоговый отчет и щелкнуть на кнопке Далее. После этого необходимо задать имя запроса и выбрать один из вариантов дальнейшего действия: Открыть query для просмотра данных или Изменить макет запроса и нажать кнопку Готово. В результате чего получите готовый query.

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

С помощью конструктора можно создать следующие виды запросов:

Простой.
По условию.
Параметрические.
Итоговые.
С вычисляемыми полями.

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

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

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

В нижней части окна находится Бланк построения запроса QBE (Query by Example), в котором каждая строка выполняет определенную функцию:

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

Рис. 2.

Запрос на выборку

В окне «Запрос: запрос на выборку» с помощью инструментов формируем query:

Выбрать таблицу – источник, из которой производится выборка записей.
Переместить имена полей с источника в Бланк запроса. Например, из таблицы Группы студентов отбуксировать поле Название в первое поле Бланка запросов, из таблицы Студенты отбуксировать поле Фамилии во второе поле Бланка запросов, а из таблицы Успеваемость отбуксировать поле Оценка в третье поле и из таблицы Дисциплины отбуксировать поле Название в четвертое поле Бланка запросов.
Задать принцип сортировки. Курсор мыши переместить в строку Сортировка для любого поля, появится кнопка открытия списка режимов сортировки: по возрастанию и по убыванию. Например, установить в поле Фамилия режим сортировки – по возрастанию.
В строке вывод на экран автоматически устанавливается флажок просмотра найденной информации в поле.
В строке "Условия" отбора и строке "Или" необходимо ввести условия ограниченного поиска – критерии поиска. Например, в поле Оценка ввести - "отл/A", т.е. отображать все фамилии студентов, которые получили оценки отл/A.
После завершения формирования запроса закрыть окно Запрос на выборку. Откроется окно диалога Сохранить – ответить Да (ввести имя созданного запроса, например, Образец запроса в режиме Конструктор) и щелкнуть ОК и вернуться в окно базы данных.

Рис. 3.

Чтобы открыть query из окна базы данных, необходимо выделить имя запроса и щелкнуть кнопку Открыть, на экране появится окно запрос на выборку с требуемым именем.

Рис. 4.

Чтобы внести изменения в query его необходимо выбрать щелчком мыши в окне базы данных, выполнить щелчок по кнопке Конструктор, внести изменения. Сохранить запрос, повторить его выполнение.

Параметрические запросы

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

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

Создать query в режиме конструктора или открыть существующий запрос в режиме конструктора, например «Образец запроса в режиме Конструктор».
В Бланк запроса в строке Условия отбора ввести условие отбора в виде приглашения в квадратных скобках, например [Введите фамилию].
Закрыть окно Запрос на выборку, на вопрос о сохранении изменения ответить – Да. Вернуться в окно базы данных, где созданный query будет выделен.
Выполнить query, щелкнув по кнопке: Открыть. В появившемся на экране окне диалога «Введите значение параметра» надо ввести, например фамилию студента, информацию об успеваемости которого необходимо получить, выполнить щелчок по кнопке ОК.