Процедура формирования запросов ИОН-offline в программе «Референт».
В разделе «Выходной контроль» окна программы «Референт» нужно нажать кнопку («Запросы ФНС») на панели инструментов.
Примечание.
Если программа «Референт» настроена на работу с несколькими почтовыми ящиками, то после нажатия на кнопку «Запросы ФНС» откроется окно «Запросы ФНС. Выбор ящика». В этом окне с помощью кнопки следует выбрать из выпадающего списка наименование организации, с почтового ящика которой нужно отправить запрос, и нажать на кнопку «ОК». 
В данном окне нужно поставить галочки напротив тех КБК, которые будут добавлены в список выбора КБК при формировании запросов в налоговую инспекцию.
Для того чтобы было удобнее найти нужные КБК, используется поле «Фильтр».
После того как все необходимые КБК выбраны, нажмите кнопку «Сохранить».
В поле «Группировка» окна «Запрос ИОН» указывается одно из двух значений:
- группировать по видам платежа;
- не группировать по видам платежа.
Если необходимо сформировать запрос на перечень налоговых деклараций (расчётов) и бухгалтерской отчётности, то нужно заполнить поля «С» и «По» , где указывается период, на который нужен запрос. Также в поле «Отчётность» указывается тип документов:
- все виды деклараций;
- первичные;
- корректирующие (последняя коррекция).
Если нужно получить акт сверки расчётов по налогам, сборам, пени и штрафам, то следует указать дату, на которую требуется сверка (в поле «На дату»), и добавить нужные КБК в список вышеуказанным способом.
После того как все необходимые поля будут заполнены, следует нажать на кнопку «Создать».
Затем в окне «Выходного контроля» появится файл для отправки, который необходимо пометить галочкой и нажать «Отправить».
Данная форма позволяет сформировать запросы в инспекции ФНС России на получение следующих документов:
- перечень налоговых деклараций (расчётов) и бухгалтерской отчётности, предоставленных в отчётном году;
- акт сверки расчётов по налогам, сборам, пеням, штрафам, процентам;
- справка об исполнении налогоплательщиком (плательщиком сборов, налоговым агентом) обязанности по уплате налогов, сборов, пеней, штрафов.
В поле «Отправитель» указывается организация, с почтового ящика которой отправляется запрос. Поле «Направление (ИФНС)» показывает номер транзитной налоговой инспекции (указывается для КНП, во всех остальных случаях номера транзитной ИФНС и конечной совпадают). В поле «Ответственное лицо» указывается Ф.И.О. владельца сертификата ключа подписи.
В поле «Налогоплательщик» нужно указать организацию, для которой создается запрос. Для этого нажмите кнопку , появится окно «Список юридических и физических лиц» Здесь необходимо выделить курсором организацию, для которой создается запрос, и нажать кнопку «Выбрать».
Далее в окне «Запрос ИОН» нужно указать налоговую инспекцию, в которой состоит на учёте налогоплательщик (поле «Конечная ИФНС»). Если в поле «Конечная ИФНС» указать значение «0000», то налогоплательщик получит ответ по всем обособленным подразделениям своей организации. Поле «КПП» нужно заполнить, если необходимо получить информацию по одному конкретному объекту налогообложения, или оставить пустым в случае, когда требуется информация по всем объектам.
Также необходимо указать желаемый формат ответа из ИФНС (XML, XLS, PDF или RTF) в поле «Формат ответа». В поле «Код запроса» указывается, какой именно документ нужно получить из налоговой инспекции:
- справка о состоянии расчётов по налогам, сборам, пени и штрафам;
- выписка операций по расчётам с бюджетом;
- перечень налоговых деклараций (расчётов) и бухгалтерской отчётности;
- акт сверки расчётов по налогам, сборам, пеням, штрафам, процентам.
Справка об исполнении налогоплательщиком (плательщиком сборов, налоговым агентом) обязанности по уплате налогов, сборов, пеней, штрафов.
Окно «Запрос ИОН» меняет вид в соответствии с тем, какое значение установлено в поле «Код запроса».
Если нужно получить справку о состоянии расчётов по налогам, сборам, пени и штрафам, то далее заполняется поле «На дату» (число, на которое хотите получить справку из ИФНС).
Если необходимо сформировать запрос на выписку операций по расчётам с бюджетом, то появляются следующие поля: «Год», «Группировка» и поле, где необходимо указать КБК. В поле «Год» нужно указать год, на который требуется выписка операций. Далее нужно выбрать интересующие КБК. Они добавляются в список с помощью кнопки(при нажатии этой кнопки появляется справочник, из которого выбираются нужные КБК двойным щелчком мыши) и удаляются из списка с помощью кнопки . Также в запросах с типом 2 и 4 имеется возможность ввода КБК вручную. Для этого нужно ввести код КБК в поле «КБК», которое находится над справочником, и нажать клавишу Enter.
Если в запросе с типом «2 - выписка операций по расчётам с бюджетом» не указывать КБК, то в ответ поступит информация по всем налоговым обстоятельствам на заданную дату для налогоплательщика.
Для того чтобы настроить список выбора КБК, сверху в меню выберите «Справочники - КБК...». 
Откроется окно «Выбор КБК».
Инструкция по формированию запросов ИОН-offline в системе «Онлайн-Спринтер»
Для того чтобы воспользоваться услугой ИОН-offline, необходимо:
Инструкция по формированию запросов offline для ПК «1С-Спринтер»
Для формирования запроса:
Запрос в ПФР отправляется аналогичным образом с закладки «ПФР».
Как создать и отправить запрос ИОН-offline в программе Такском-Доклайнер?
Для создания запроса ИОН-offline в программе «Такском-Доклайнер»:
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 Выполнение и сохранение запроса
После формирования запроса его необходимо выполнить. Из окна Конструктора запроса это можно сделать, введя одну из команд.
Запрос (query) – это средство выбора необходимой информации из базы данных. Вопрос, сформированный по отношению к базе данных, и есть запрос.
Применяются два типа запросов: по образцу (QBE – Query by example) и структурированный язык запросов (SQL – Structured Query Language).
QBE - запрос по образцу – средство для отыскания необходимой информации в базе данных. Он формируется не на специальном языке, а путем заполнения бланка запроса в окне Конструктора запросов.
SQL – запросы – это запросы, которые составляются (программистами) из последовательности SQL – инструкций. Эти инструкции задают, что надо сделать с входным набором данных для генерации выходного набора. Все запросы Access строит на основе SQL – запросов, чтобы посмотреть их, необходимо в активном окне проектирования запроса выполнить команду Вид/SQL.
Существует несколько типов запросов: на выборку, на обновление, на добавление, на удаление, перекрестный запрос, создание таблиц. Наиболее распространенным является запрос на выборку. Запросы на выборку используются для отбора нужной пользователю информации, содержащейся в таблицах. Они создаются только для связанных таблиц.
С помощью конструктора можно создать следующие виды запросов:
Простой
По условию
Параметрические
Итоговые
С вычисляемыми полями
При создании query необходимо определить:
Поля в базе данных, по которым будет идти поиск информации
Предмет поиска в базе данных
Перечень полей в результате выполнения запроса
Чтобы вызвать Конструктор запросов , необходимо перейти в окно базы данных. В окне база данных необходимо выбрать вкладку Запросы и дважды щелкнуть на пиктограмме Создание запроса в режиме конструктора . Появится активное окно Добавление таблицы на фоне неактивного окна «Запрос: запрос на выборку».
В окне Добавление таблицы следует выбрать таблицу – источник или несколько таблиц из представленного списка таблиц, на основе которых будет проводиться выбор данных, и щелкнуть на кнопке Добавить . После этого закрыть окно Добавление таблицы , окно «Запрос: запрос на выборку » станет активным.
Окно Конструктора состоит из двух частей – верхней и нижней. В верхней части окна размещается схема данных запроса, которая содержит список таблиц – источников и отражает связь между ними.
В нижней части окна находится Бланк построения запроса QBE (Query by Example), в котором каждая строка выполняет определенную функцию:
Поле – указывает имена полей, которые участвуют в запросе
Имя таблицы – имя таблицы, из которой выбрано это поле
Сортировка – указывает тип сортировки
Вывод на экран – устанавливает флажок просмотра поля на экране
Условия отбора - задаются критерии поиска
Или – задаются дополнительные критерии отбора
Запрос на выборку простой используются для отбора нужной пользователю информации, содержащейся в таблицах.
Запрос 1 Выбрать все затраты организации ОАО «Всестрой»
Все текстовые значения вводятся в кавычках, но если кавычки уже существуют то такое выражение включают в одинарных кавычках.
В окне «Запрос: запрос на выборку» с помощью инструментов формируем query:
Выбрать таблицу – источник, из которой производится выборка записей.
Переместить имена полей с источника в Бланк запроса. Например, из таблицы Организации отбуксировать поле организации в первое поле Бланка запросов, из таблицы затраты отбуксировать поле Затраты во второе поле Бланка запросов, а из таблицы Затраты отбуксировать поле Стоимость в третье поле
Задать принцип сортировки. Курсор мыши переместить в строку Сортировка для любого поля, появится кнопка открытия списка режимов сортировки: по возрастанию и по убыванию. Например, установить в поле Стоимость режим сортировки – по возрастанию.
В строке вывод на экран автоматически устанавливается флажок просмотра найденной информации в поле. Поэтому убрать флажок в поле Организации
В строке "Условия" отбора и строке "Или" необходимо ввести условия ограниченного поиска – критерии поиска. Например, в поле Код организации ввести код 302 т.е. отображать все затраты,организации ОАО «Всестрой»
После завершения формирования запроса закрыть окно Запрос на выборку. Откроется окно диалога Сохранить – ответить Да (ввести имя созданного запроса, например, Образец запроса в режиме Конструктор) и щелкнуть ОК и вернуться в окно базы данных.
Запрос 2 Затраты Иркутских организаций
Запрос по условию
Запрос3 Затраты за первый квартал 2012г ,
Параметрические запросы
Запросы, представляющие собой варианты базового запроса и незначительно отличающиеся друг от друга, называются параметрическими. В параметрическом запросе указывается критерий, который может изменяться по заказу пользователя.
От компании Microsoft предоставляет пользователям широкие возможности для создания и работы с базами данных. В наше время базы данных очень широко используются во всевозможных сферах и областях. В целом, можно сказать, что сейчас без них не обойтись. В связи с этим, очень важно уметь работать с таблицами Access. Основным элементом взаимодействия пользователя с базой данных является запрос. В этой статье подробно рассмотрим, как создать запрос в Access. Давайте же начнём. Поехали!
Прежде всего, разберёмся с тем, что такое запрос. Это элемент базы данных, который служит для выбора нужной информации из таблицы. Они бывают двух видов:
- На выборку (позволяют получать данные и выполнять операции над ними);
- На изменение (позволяют добавлять, удалять и изменять значения).
Теперь перейдём непосредственно к практике. Зайдите на вкладку «Создание» и нажмите кнопку «Конструктор запросов». В открывшемся окне выберите одну или несколько таблиц , из которых необходимо выбрать данные. Нажав кнопку «Выполнить», вы получите всю информацию из выбранных вами таблиц. Для более конкретного отбора перейдите на вкладку «Конструктор» и введите нужное слово или число в поле «Условие отбора:». Например, вы хотите получить список всех сотрудников 1980 года рождения. Для этого пропишите в строке «Условие отбора:» 1980 в колонке «Дата рождения» и нажмите кнопку «Выполнить». Программа моментально выдаст вам результат. Для удобства вы можете сохранить созданный запрос, чтобы пользоваться им в дальнейшем. Воспользуйтесь комбинацией клавиш Ctrl+S и введите название в соответствующем поле, а после нажмите «ОК».
Указав условие для отбора данных, мы получаем нужную выборку
Чтобы посчитать значения в столбце, вы можете использовать так называемые групповые операции. Для этого на вкладке «Конструктор» кликните по кнопке «Итоги». В появившейся строке «Группировка» выберите функцию «Count», позволяющую сложить все ненулевые значения. Чтобы в названии колонки не выводилось слово «Count», в строке «Поле» введите название перед тем, что было указано ранее. Сохранение выполняется точно также при помощи комбинации Ctrl+S.
Аксесс считает непустые ячейки
Теперь создадим параметрический запрос. Он позволяет делать выборку по введённому параметру. Например, по заданной дате. Открыв нужную базу данных , пропишите (без кавычек) в соответствующем столбце «[Укажите дату]». Квадратные скобки обязательны. В появившемся окне введите нужную дату. После этого программа автоматически произведёт выборку по введённой вами дате. Вы можете поставить знаки «>» (больше) и «<» (меньше) чтобы отобразить элементы раньше либо позже заданной даты. Например, список сотрудников, которые сдали отчёты до 01.07.
Как создать перекрёстное обращение? Достаточно найти на панели инструментов кнопку «Перекрёстный» и кликнуть по ней. После этого в пункте «Перекрёстная таблица» выберите в первой ячейке «Заголовки строк», во второй «Заголовки столбцов», а в третьей «Значение». Затем вы можете посчитать значения, использовав уже упомянутую выше функцию «Count». Обратите внимание, что если вы укажете во второй ячейке «Заголовки строк», так же, как в первой, вместо «Заголовки столбцов », то ничего работать не будет. Обязательно учитывайте это.
Делаем перекрестный запрос в базе
Также вы можете сделать обращение на создание таблицы. Как это делается? Достаточно открыть базы данных, с которыми вы работаете, затем заполнить ячейку «Условие отбора» и кликнуть по кнопке «Создание таблицы». Не забывайте нажимать «Выполнить». На основе заданных вами параметров Microsoft Access построит новую таблицу. Например, список всех сотрудников с их контактными данными по профессии «Бухгалтер».
При сохранении нужно указать имя и место размещения новой таблицы
Как вы можете убедиться, программа Access от компании Microsoft позволяет выполнять множество операций. Зная, как сделать запрос в Access, вы будете чувствовать себя значительно увереннее при работе с базами данных. Пишите в комментариях была ли полезна для вас эта статья и задавайте вопросы по рассмотренной теме.
Лекция 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. Создайте запрос на получение списка книг одного автора.
