LINEBURG


<< Пред. стр.

страница 71
(всего 83)

ОГЛАВЛЕНИЕ

След. стр. >>

ходными данными для запроса являются таблицы и/или другие запросы. Имена
запросов не должны совпадать с именами таблиц БД.

Классификация запросов
Запросы классифицируются различным образом.
1. По числу обрабатываемых таблиц — запросы делятся на однотабличные и
многотабличные (в последнем случае — все таблицы/запросы должны быть
связанными).
2. По типу алгоритмов обработки различают запросы:
О выборки — результат запроса отображается только на экране, может ис­
пользоваться для создания форм, отчетов или страниц доступа, а также
других запросов;
О перекрестный — создание сводной таблицы, содержащей групповые итоги;
О на создание таблицы — автоматическое формирование структуры записи
новой таблицы и загрузка;
О на добавление — ввод новых записей, являющихся результатом выполне­
ния запроса;
О на удаление — удаление группы записей из таблицы.
3. По типу языка запросов.
Используются два типа языка запросов:
О QBE (Query By Example) — запрос по примеру, построенный на основе
реляционного языка запросов графического типа;
О SQL (Structured Query Language) — реляционно-полный язык запросов.
Как правило, запросы создаются с использованием средств языка QBE. За­
просы QBE автоматически транслируются на язык SQL.
4. По стабильности условий фильтрации записей в запросах различают:
О статические запросы с неизменными условиями;
О динамические (гибкие) запросы с изменяемыми условиями.

Создание запросов
Запросы создаются при выполнении команды меню Вставка • Запрос или при
нажатии кнопки Создать на вкладке Запросы. Построение запроса выполняется
в конструкторе запросов, который использует панель инструментов Конструктор
запросов. Для вывода панели инструментов конструктора запросов выполняет­
ся команда меню Вид • Панели инструментов, панель — Конструктор запросов.
Для запроса (рис. 7.35) выбираются источники информации — таблицы и/или
другие запросы, устанавливаются связи между ними. Для каждого запроса опре­
деляется тип, уточняются условия выполнения запроса, состав полей результи­
рующей таблицы — бланк запроса. Для отображения данных в результирую­
щей таблице используется свойство Вывод на экран. Для выбора таблиц или
запросов, используемых в текущем запросе, выполняется команда меню Вид •
581
Основные понятия СУБД Access


Отобразить таблицу или Добавить таблицу. Для удаления ненужной таблицы/за­
проса после выделения таблицы/запроса выполняется команда меню Запрос •
Удалить таблицу или нажимается клавиша Del. В многотабличных запросах все
исходные таблицы/запросы должны быть связанными между собой по общим
ключам связи. С помощью команды меню Вид • Параметры объединения можно
изменить тип объединения.
В бланк запроса с помощью команды меню Вид • Имена таблиц можно включить
строку Имя таблицы, которая идентифицирует источник полей. С помощью ко­
манды меню Вставка • Столбцы добавляются новые столбцы в результирующую
таблицу по месту установки курсора.

-Таблицы и запросы,
участвующие в запросе - Столбцы бланка запроса
ВВ)
*5
Начальный износ
Накопленный износ

Дата постановки
Дата списания J*J



mm Инвентарный о б м Дата постановки на учет
Инвентарный объ< Инвентарный объект ОС Инвентарный объект ОС



,Is Hot Null And Between [Дата начальная 1 And [Дата конечная] Is ЫиИ,




?LL
Управление выводом на экран-1
Выражение для условия отбора записей—'
Рис. 7.35. Схема макета запроса

СУБД Access позволяет использовать построитель выражения для создания вы­
числяемых полей в результирующей таблице и условий отбора записей. Усло­
вия отбора состоят из произвольного числа строк. С помощью команды меню
Вставка • Строки добавляются строки условий отбора. Запросы могут использо­
вать параметры, которые требуют определения типа данных с помощью коман­
ды меню Запрос • Параметры.
По умолчанию в конструкторе создается запрос выборки. Для изменения типа
запроса выполняется соответствующая команда меню: Запрос • Выборка / Пере­
крестный / Создание таблиц / Обновление / Добавление / Удаление.
Для формирования групповых итогов служит команда меню Вид • Групповые опе­
рации, которая обеспечивает выбор статистической функции для вычисления
итогов. Функция указывается для каждого столбца в строке Групповая операция
бланка запроса. В начале результирующей таблицы размещаются поля группи­
ровки, их состав и порядок следования (слева направо) определяют виды ито­
гов.
Для запуска запроса на выполнение из режима конструктора запроса выполня­
ется команда меню Запрос • Запуск или нажимается кнопка со знаком !. Для за-
582 Глава 7. Система управления базами данных MS Access 2000


пуска закрытого запроса требуется на вкладке Запросы выделить объект БД и
нажать кнопку Открыть.
Для формирования специальных запросов на языке SQL выполняется команда
меню Запрос • Запрос SQL. На языке SQL разрабатываются запросы следующих
типов:
Q к серверу — передача команд на сервер БД, который поддерживает драйверы
ODBC для обмена данными;
• управляющие — запросы на создание и реструктуризацию таблиц, создание/
удаление индексов таблиц;
• объединение таблиц — слияние данных нескольких таблиц;
• подчиненный запрос — вложенный запрос в макет запроса.
Для изменения свойств запроса служит команда меню Вид • Свойства, в диало­
говом окне Свойства запроса указывается:
• вывод всех полей результирующей таблицы (игнорирование свойства Вывод
на экран);
• ограничение на число или процент выводимых записей;
• проверка уникальности выводимых записей;
• связь с внешней БД и др.
С запросом можно работать в трех режимах:
• Конструктор — работа с макетом запроса;
• Режим SQL — запрос на языке SQL;
• Режим таблицы — табличное представление.
Перечень запросов, соответствующих БД OC.MDB, рассматриваемых в качест­
ве примеров, приведен в табл. 7.14.
Таблица 7 . 1 4 . Состав з а п р о с о в БД OC.MDB 1

Наименование запроса Тип Примечание
SQL запрос выборки В Условие отбора на языке SQL
Выбытие ОС В Выбытие ОС за указанный интервал дат
в
Выше средней Выборка инвентарных объектов ОС, у которых
стоимости ОС начальная стоимость выше средней. Использование
подзапроса на языке SQL
в
Журнал операций Выборка из ЖХО проводок за определенный
интервал дат



В таблице приняты следующие обозначения: В — запрос выборки, Д — добавление запи­
сей, О — обновление полей записей, Об — запрос SQL на объединение, Пр — перекрест­
ный, С — запрос на создание таблицы, У — запрос на удаление записей, Упр —
управляющий запрос на SQL.
583
Основные понятия СУБД Access


Тип Примечание
Наименование запроса
Вычисление итоговых значений по группе ОС
Итоги ОС по группам В
В Наличие инвентарных объектов ОС по состоянию
Наличие ОС
на указанную дату
в Поступление ОС за указанный интервал дат
Поступление ОС
в Вычисление итогов сальдо начальных, оборотов,
Проверка итогов
сальдо текущих, сумм к закрытию по дебету
по счетам
и кредиту счетов, субсчетов
в Проверка счетов, субсчетов, по которым не заданы
Сальдо не задано
начальные сальдо
в Вычисление средней стоимости инвентарных объектов
Средняя стоимость ОС
ОС, которые не списаны на текущий момент времени
в Проверка соответствия типа счета и начального
Тип счета
сальдо
Добавление инвентарных карточек а/т средств
Добавление в архив И К Д
в таблицу Закрытые ИК ОС
д Используется при работе через форму Проводки
Добавление типовых
для формирования новых записей в ЖХО.
проводок в ЖХО
д
Месячный износ Расчет суммы месячного износа и добавление новых
записей в таблицу Амортизация ОС
о
Месячный износ а/т Расчет амортизации для автотранспортных средств
средств и общего пробега
Обороты О Обновление значений полей ДО и КО в таблице Счет
на основании таблиц Обороты по дебету счета,
Обороту по кредиту счета
Сальдо А О Обновление текущих сальдо для счетов типа «А»
Сальдо А/П О Обновление текущих сальдо для счетов типа «А/П»
Сальдо П О Обновление текущих сальдо для счетов типа «П»
Смена МОЛ О Замена одного кода МОЛ на другой в инвентарных
карточках ОС
Справочник Об Объединение кодов структурных подразделений
из различных справочников (МОЛ и Инвентарные
подразделений
карточки ОС)
Перекрестный Перекрестный запрос для таблицы Инвентарный
Пр
объект ОС
Шахматка Перекрестный запрос для таблицы ЖХО
Пр

Продолжение &
584 Глава 7. Система управления базами данных MS Access 2000


Таблица 7.14. Продолжение

Тип
Наименование запроса Примечание

до Создание таблицы Обороты по дебету счета —
С
дебетовые обороты по проводкам ЖХО за указанный
учетный период

Закрытые карточки ОС Создание таблицы Закрытые И К ОС
С

КО Создание таблицы Обороты по кредиту счета —
С
кредитовые обороты по проводкам ЖХО за
указанный учетный период

Удаление карточек ОС Удаление записей в таблице (отношение «многие
У
к одному»)

Удаление связанных записей двух таблиц (отношение
Удаление объектов ОС У
«один ко многим»)

Удаление Обороты по Упр Удаление таблицы с помощью оператора SQL
дебету счета

Удаление Обороты по Упр Удаление таблицы с помощью оператора SQL
кредиту счета


Запросы на выборку
Запрос на выборку обеспечивает формирование результирующей информации
на экране в момент выполнения запроса. В БД сохраняется лишь схема (макет)
запроса, а не сам результат. Результирующая таблица содержит поля таблиц
или запросов, вычисляемые поля.
В запросе участвуют записи, удовлетворяющие условиям фильтрации, которые
задаются для полей или вычисляемых полей в строках условий в виде:
Оператор сравнения Значение.
Несколько условий для одного и того же поля можно записать как одно условие
с помощью логических связок AND, OR, NOT. Условия фильтрации можно зада­
вать непосредственно в момент выполнения запроса — так называемый «гиб­
кий» запрос или параметрический запрос. Результат запроса можно сгруппиро­
вать по указанным полям для вычисления статистических итоговых функций:
Sum, Count, Avg, Min, Max и др.

Пример 14
Анализ состояния учетных регистров по счетам. Источник данных для запро­
са — таблица Счет. Проверяется соответствие начальных сальдо типу счетов, ве­
дется поиск счетов, по которым не заданы вступительные сальдо. Вычисляются
итоговые показатели: итоговые сальдо (начальное и текущее) по дебету и кре­
диту, итоговые обороты по дебету и кредиту, итоговые суммы к закрытию по
дебету и кредиту.
585
Основные понятия СУБД Access


Проверка соответствия начальных сальдо счетов типу счета
Для счета типа «А» существует только дебетовое сальдо, для счета типа «П» —
только кредитовое сальдо. Для счета типа «А/П» — любое сальдо. Для счета
типа «Р» сальдо не вводится. Требуется найти номера счетов, субсчетов, для ко­
торых эти условия нарушены.
Последовательность действий.
1. Открыть файл OC.MDB с помощью команды меню Файл • Открыть.
2. Перейти на вкладку Запросы.
3. Нажать кнопку Создать, выбрать Конструктор.
4. Добавить таблицу Счет.
5. Результирующая таблица содержит все поля таблицы Счет; для этого вы­
брать поле *.
6. Проверка сальдо для счета типа «А» (активный):
О добавить в бланк запроса поле Тип счета, Вывод на экран — Нет, Условие
отбора — "А";
О добавить в бланк запроса поле Сальдо К, Вывод на экран — Нет, Условие
отбора — Is Not Null (в той же строке).
7. Проверка сальдо для счета типа «П» (пассивный):
О добавить в бланк запроса для поля Тип счета строку Условие отбора или
(вторая строка условия), Вывод на экран — Нет, Условие отбора — указать
"П";
О добавить в бланк запроса поле Сальдо Д, Вывод на экран — Нет, Усл_овие
отбора — Is Not Null (в той же строке).
8. Проверка сальдо для счета типа «Р» (результирующий):
О добавить в бланк запроса для поля Тип счета строку Условие отбора или
(третья строка условия), Вывод на экран — Нет, Условие отбора — указать
"Р";
О в бланке запроса для поля Сальдо К в строке Условие отбора или (третья
строка условия) указать Is Not Null%
О добавить в бланк запроса для поля Тип счета строку Условие отбора или
(четвертая строка условия) указать "Р";
О в бланке запроса для поля Сальдо Д в строке Условие отбора или (четвер­
тая строка условия) указать Is Not Null.
Запрос на языке SQL имеет вид (команда меню Вид • Режим SQL):
SELECT Счет.* FROM Счет WHERE ( ( ( С ч е т . [ Т и п с ч е т а ] ) = " А " ) AND
((Счет.[Сальдо К ] ) I s Not N u l l ) ) OR ( ( ( С ч е т . [ Т и п с ч е т а ] ) = " П " ) AND
((Счет.[Сальдо Д ] ) I s Not N u l l ) ) OR ( ( ( С ч е т . [ Т и п с ч е т а ] ) = " Р " ) AND
((Счет.[Сальдо К ] ) I s Not N u l l ) ) OR ( ( ( С ч е т . [ Т и п с ч е т а ] ) = " Р " ) AND
((Счет.[Сальдо Д ] ) I s Not Null))WITH OWNERACCESS OPTION;
9. Выполнить запрос с помощью команды меню Запрос • Запуск.
10. Сохранить запрос под именем Тип счета.
586 Глава 7. Система управления базами данных MS Access 2000


Поиск счетов, субсчетов, по которым не заданы сальдо
Осуществляется поиск счетов, субсчетов типа «А», «П», «А/П», по которым не
заданы сальдо; для счета типа «А» отсутствует дебетовое сальдо, для счета типа
«П» отсутствует кредитовое, для счета типа «А/П» отсутствует любое сальдо.
Последовательность действий.
1. Перейти на вкладку Запросы.
2. Нажать кнопку Создать, выбрать Конструктор.
3. Добавить таблицу Счет.
4. Результирующая таблица содержит все поля таблицы Счет; для этого вы­
брать поле *.
5. Проверка сальдо для счета типа «А» (активный):
О добавить в бланк запроса поле Тип счета, Вывод на экран — Нет, Условие
отбора — "А";
О добавить в бланк запроса поле Сальдо К, Вывод на экран — Нет, Условие
отбора — >0 (в той же строке).
6. Проверка сальдо для счета типа «П» (пассивный):
О добавить в бланк запроса для поля Тип счета строку Условие отбора или
(вторая строка условия), Вывод на экран — Нет, Условие отбора — указать
-П";
О добавить в бланк запроса поле Сальдо Д, Вывод на экран — Нет, Условие
отбора — >0 (в той же строке).
7. Проверка сальдо для счета типа «А/П» (активно-пассивный):
О добавить в бланк запроса для поля Тип счета строку Условие отбора или
(третья строка условия), Вывод на экран — Нет, Условие отбора — указать
"А/П";
О в бланке запроса для поля Сальдо К в строке Условие отбора или (третья
строка условия) указать >0;
О в бланке запроса для поля Сальдо Д в строке Условие отбора или (третья
строка условия) указать >0.
Запрос на языке SQL имеет вид (команда меню Вид • Режим SQL):
SELECT Счет.* FROM Счет WHERE ( ( ( С ч е т . [Тип с ч е т а ] ) = "А") AND
((Счет.[Сальдо К ] ) >0)) OR ( ( ( С ч е т . [ Т и п с ч е т а ] ) = " П " ) AND
((Счет.[Сальдо Д ] ) >0)) OR ( ( ( С ч е т . [ Т и п с ч е т а ] ) = " А / П " ) AND
((Счет.[Сальдо Д ] ) >0) AND ((Счет.[Сальдо К ] ) >0))WITH
OWNERACCESS OPTION;
8. Выполнить запрос — команда меню Запрос • Запуск.
9. Сохранить запрос под именем Сальдо не задано.
Вычисление итогов по счетам
Вычисляются суммарное начальное дебетовое и кредитовое сальдо, суммарное
текущее дебетовое и кредитовое сальдо, сумма дебетовых и кредитовых оборо-
587
Основные понятия СУБД Access


тов по счетам, сумма к закрытию по дебету и кредиту счетов. Если суммы на­
чальных или текущих дебетовых и кредитовых сальдо не равны между собой
или сумма к закрытию по дебету и кредиту счетов типа «Р» не равна 0, имеет
место ошибка.
Последовательность действий.
1. Перейти на вкладку Запросы.
2. Нажать кнопку Создать, выбрать Конструктор.
3. Добавить таблицу Счет.
4. В бланк запроса поместить поля: Сальдо Д, Сальдо К, ДО, КО, Сальдо Д теку­
щее, Сальдо К текущее, а также вычисляемые выражения:
О IIF([C4eT]![Тип счета]="Р",NZ([Счет]![Сальдо Д]).©))
О IIF([C4eT]![Тип счета]="Р",NZ([Счет]![Сальдо К]),0))
О IIF([Счет]![Тип счета]="Р",NZ([Счет]![Сальдо Д текущее]),©)
О П Р ( [ С ч е т ] .' [Тип C4eTa]="P",NZ([C4eT]! [Сальдо К текущее]) ,0)
5. Вычислить итоги по каждому счету с помощью команды меню Вид • Группо­
вые операции, выбрать операцию Sum для всех полей.
6. Изменить имена итоговых полей. В строке Поле перед каждым полем доба­
вить имя, отделяемое знаком двоеточия от имени поля или выражения: СДН,
СКН, Итого ДО, Итого КО, СДТ, СКТ, ЗНД, ЗНК, ЗТД, ЗТК (Д - дебет, ДО - де­
бетовый оборот, 3 — сумма к закрытию, К — кредит, КО — кредитовый обо­
рот, Н — начальное, С — сальдо, Т — текущее).
Запрос на языке SQL имеет вид (команда меню Вид • Режим SQL):
SELECT БитССчет.[Сальдо Д ] ) AS СДН, 5ит(Счет.[Сальдо К ] ) AS СКН,
БитССчет.ДО) AS [Итого ДО], Sum(Счет.КО) AS [Итого КО],
Sum(C4eT.[Сальдо Д текущее]) AS СДТ, 5ит(Счет.[Сальдо К текущее])
AS СКТ, S u m ( I I F ( [ С ч е т ] ! [ Т И П с ч е т а ] = " Р " , N Z ( [ С ч е т ] ! [ С а л ь д о Д]),0))
AS ЗНД, S u m ( I I F ( [ C 4 e T ] ! [ Т и п с ч е т а ] = " Р " , N Z ( [ С ч е т ] ! [ С а л ь д о К]),0))
AS ЗНК, S u m ( I I F ( [ C 4 e T ] ! [ Т И П с ч е т а ] = " Р " , N Z ( [ С ч е т ] ! [ С а л ь д о Д
т е к у щ е е ] ) , © ) ) AS ЗТД, S u m ( I I F ( [ С ч е т ] ! [ Т и п
с ч е т а ] = " Р " , N Z ( [ С ч е т ] ! [Сальдо К т е к у щ е е ] ) , © ) ) AS ЗТК FROM Счет
WITH OWNERACCESS OPTION;
7. Выполнить запрос — команда меню Запрос • Запуск.
8. Сохранить запрос под именем Проверка итогов по счетам.
Пример 15
Сведения о движении ОС за указанный интервал дат. Источник — таблица Ин­
вентарный объект ОС. Для определения перечня поступивших, выбывших и со­
стоящих на учете ОС требуется подготовить три самостоятельных запроса.
Список поступивших ОС
Запрос должен обеспечить гибкое изменение условий фильтрации — интервала
дат, за которые отслеживается поступление ОС.
588 Глава 7. Система управления базами данных MS Access 2000


Последовательность действий.
1. Перейти на вкладку Запросы.

<< Пред. стр.

страница 71
(всего 83)

ОГЛАВЛЕНИЕ

След. стр. >>

Copyright © Design by: Sunlight webdesign