Если у вас возникли сложности с курсовой, контрольной, дипломной, рефератом, отчетом по практике, научно-исследовательской и любой другой работой - мы готовы помочь.
Предоплата всего
Подписываем
PAGE 96
ФЕДЕРАЛЬНОЕ АГЕНТСТВО ПО ОБРАЗОВАНИЮ
ГОСУДАРСТВЕННОЕ ОБРАЗОВАТЕЛЬНОЕ УЧРЕЖДЕНИЕ
ВЫСШЕГО ПРОФЕССИОНАЛЬНОГО ОБРАЗОВАНИЯ
«САНКТ - ПЕТЕРБУРГСКИЙ ГОСУДАРСТВЕННЫЙ УНИВЕРСИТЕТ
ЭКОНОМИКИ И ФИНАНСОВ»
КАФЕДРА ИНФОРМАТИКИ
МЕТОДИЧЕСКИЕ УКАЗАНИЯ
Работа в Microsoft Access 2007
ДЛЯ СТУДЕНТОВ ВСЕХ ФОРМ ОБУЧЕНИЯ
ИЗДАТЕЛЬСТВО
САНКТ-ПЕТЕРБУРГСКОГО ГОСУДАРСТВЕННОГО УНИВЕРСИТЕТА
ЭКОНОМИКИ И ФИНАНСОВ
2012
Рекомендовано научно-методическим советом университета
Методические указания для выполнения курсовых работ по дисциплине «Информатика» для студентов всех форм обучения. - СПб.: Изд-во СПбГУЭФ, 2012. –98 с.
Составители: канд. экон. наук, доц. А.В. Саитов,
канд. экон. наук, доц. М.И. Барабанова
Рецензенты:
Издательство СПбГУЭФ, 2012
Проектирование информационных баз данных основывается на исследовании информации, циркулирующей внутри данной предметной области.
Предметная область (ПО) – это совокупность объектов, процессов и связей между ними (банк, завод, склад и так далее). При обследовании ПО выполняется семантический (смысловой) анализ информации данной предметной области.
Рассмотрим некоторые понятия, которые требуются для описания исследуемой ПО.
Информационный объект (ИО) является источником информации. Он может быть материальным (цехи, склады, документы, материалы, поставщики, заказчики и т.д.) и нематериальным (факты, события, процессы, явления и т.д.). ИО - это любая реальная или абстрактная сущность, о которой накапливается информация.
Сущность можно определить как некоторое описание какого-либо объекта. Сведения о сущностях могут поступать из различных сообщений и документов. Каждая сущность характеризуется набором реквизитов.
Реквизиты – это свойства, которыми обладает данная сущность. Реквизит является простейшей неделимой единицей информации, которая отражает количественную (количество, сумма, цена) или качественную (код предприятия, наименование предприятия и так далее) характеристику сущности. Сведения о реквизитах получают на этапе предпроектного обследования, в процессе исследования документооборота. Здесь же учитываются ограничения и допущения.
Состав реквизитов представляет собой структуру ИО. Часто любой ИО называют сущностью из-за его расплывчатого определения.
Совокупность взаимосвязанных данных одной ПО, организованная на машинном носителе средствами системы управления базами данных (СУБД), называется базой данных (БД).
СУБД – это универсальный комплекс программ, предназначенный для создания, ведения и использования базы данных.
В данных методических указаний рассматриваются реляционные базы данных (РБД). Информация в РБД хранится в одной или нескольких взаимосвязанных двумерных таблицах, в каждой из которых находится информация об одном информационном объекте (сущности). Например, сущность ПРЕДПРИЯТИЯ может иметь минимальный набор реквизитов: код предприятия, ИНН, наименование предприятия и адрес.
Если эту информацию представить в виде таблицы, то она будет выглядеть так:
ПРЕДПРИЯТИЯ
|
Код пред-приятия |
ИНН |
Наимено- вание предприятия |
Адрес |
|
101 |
7810313609 |
Заря |
СПб пр. Космонавтов |
|
102 |
7810315905 |
Восток |
СПб пр. Московский |
|
103 |
7811316598 |
Север |
СПб ул. Звёздная |
Конкретные значения реквизитов в одной строке таблицы называются экземпляром сущности. В данной сущности три экземпляра.
Реквизиты, которые однозначно определяют каждый экземпляр сущности, являются ключевыми.
Кандидатов в ключевые реквизиты может быть несколько в одной сущности. Например, Код предприятия и Наименование предприятия. Так как Код предприятия характеризуется компактным значением и удобен для обработки, то его можно выбрать ключом. Кроме того, Наименование предприятия теоретически может повторяться
По значению ключа можно отыскать нужный экземпляр сущности. Другие реквизиты сущности называются неключевыми или описательными. Ключ и описательные реквизиты находятся в функциональной зависимости.
Ключ может быть простым или составным. Простой ключ содержит один реквизит, составной - несколько.
Составной ключ необходим в том случае, если ни один из реквизитов по отдельности не обеспечивает доступа к нужному экземпляру. Например, в сущности План будет составной ключ:
Код предприятия + Код налога
Код предприятия имеет одинаковые значения в нескольких строках, поэтому одного этого реквизита недостаточно.
ПЛАН
|
Код предприятия |
Код налога |
Сумма |
|
101 |
01 |
200 |
|
101 |
02 |
400 |
|
102 |
01 |
150 |
|
102 |
02 |
400 |
|
102 |
03 |
600 |
|
103 |
01 |
800 |
|
103 |
02 |
200 |
|
103 |
03 |
250 |
В таблице ключевые реквизиты для удобства располагают в первых столбцах.
Между всеми сущностями, выявленными в предпроектном обследовании, надо установить типы связей.
Связи могут быть одно - однозначные (1:1), одно -многозначные (1:М), много - многозначные (М:N).
Связь 1:1 имеет место, когда каждому экземпляру одной сущности соответствует только один экземпляр второй сущности и наоборот.
Связь 1:М является наиболее часто используемым типом связи между сущностями. В такой связи каждому экземпляру одной сущности могут соответствовать несколько экземпляров другой сущности. Например, сущности ПРЕДПРИЯТИЯ и ПЛАН связаны по реквизиту Код предприятия. Одному экземпляру сущности ПРЕДПРИЯТИЯ с кодом 101 соответствуют два экземпляра сущности ПЛАН, а каждому экземпляру сущности ПЛАН соответствует один экземпляр сущности ПРЕДПРИЯТИЯ. Такую связь можно представить графически:
связь 1:М
Связь М:N означает что каждому экземпляру одной сущности могут соответствовать несколько экземпляров второй сущности. И, наоборот, каждому экземпляру второй сущности может соответствовать тоже несколько экземпляров первой сущности. При создании реляционной базы данных связь М:N не может быть реализована, так как имеет место неопределённость. Такую связь либо не используют, либо разрывают на две связи типа 1:М.
С этой целью выбирают (или создают дополнительную) сущность, имеющую общие реквизиты.
На основании выявленных связей строится ИНФОРМАЦИОННО-ЛОГИЧЕСКАЯ МОДЕЛЬ (ИЛМ). ИЛМ – это модель данных, отображающая предметную область в виде совокупности информационных объектов и структурных связей между ними.
В качестве примера рассматривается упрощённый вариант деятельности налоговой инспекции. Предметной областью автоматизации являются должностные функции экономиста отдела доходов налоговой инспекции.
Управление любым объектом включает функции:
В отделе доходов налоговой инспекции имеется план поступлений налогов, составленный на год. План составляется в плановом отделе инспекции по представленным расчетам самих предприятий. План поступает в отдел доходов.
Предприятия должны перечислять налоги ежеквартально примерно равными частями, но имеют право делать платежи чаще, более мелкими суммами (например, ежемесячно) или, наоборот, могут погасить весь платеж раньше одним перечислением.
Каждое перечисление налога осуществляется через банк и оформляется отдельным платежным поручением. Сведения об оплате из банка поступают в налоговую инспекцию.
Налоги, поступающие в местный бюджет, установлены законодательством. Условно будем считать, что таких налогов, пять: налог на прибыль в федеральный бюджет, налог на прибыль в территориальный бюджет, налог на имущество, земельный налог, НДС. Установлены предприятия, находящиеся на территории района, которые обязаны выплачивать указанные налоги. Список таких предприятий имеется в налоговой инспекции.
Должностные функции экономиста отдела доходов заключаются:
Автоматизация указанных задач позволит оперативно вести учет доходов, контролировать их поступление, анализировать структуру налоговых поступлений (по видам налогов) и, тем самым, улучшить качество принимаемых решений.
Из комплекса задач, решаемых налоговой инспекцией, рассмотрим две задачи:
Задача 1. Анализ оплаты налогов по заданному предприятию.
Задача 2. Выявление недоплат налогов на заданную дату по всем предприятиям.
В результате обследования предметной области определены следующие входные (первичные) документы:
Справочные документы:
Список предприятий
|
Код предприятия |
ИНН |
Наименование предприятия |
Адрес |
Список налогов
|
Код налога |
Наименование налога |
План поступления платежей
Код предприятия ______________
Наименование предприятия _________________________
|
Код налога |
Наименование налога |
Сумма |
Сведения о платежах по налогам поступают из первичного документа «Платежное поручение» (ПП) следующего вида:
Платежное поручение № _______
“ _____” __________________ 2012 г.
Код предприятия_________
Наименование предприятия _______________________
ИНН предприятия __________________
Банк _______________________ в г. _________________
Сумма оплаты ( прописью ): _______________________
|
Код налога |
Сумма |
Уточним, какие первичные документы и реквизиты будут использованы для решения указанных выше задач.
Для задачи 1: Анализ оплаты налогов по заданному предприятию
|
Реквизит |
Наименование налога |
Плановая сумма |
Фактическая сумма |
Недо-платы |
|
Документ |
Список налогов |
План поступлений |
Платёжное поручение |
Вычис-лить |
Для задачи 2: Выявление недоплат налогов на заданную дату по всем предприятиям
|
Реквизит |
Наименование предприятия |
Наименование налога |
Недоплаты |
|
Документ |
Список предприятий |
Задача 1 |
Задача 1 |
Таким образом, для решения выбранных задач необходимы первичные документы: Список предприятий, Список налогов, План поступления, Платёжное поручение.
По предметной области «Деятельность налоговой инспекции» введём некоторые ограничения:
Одной из основных проблем во взаимоотношениях налоговой инспекции с предприятиями - налогоплательщиками является несвоевременная оплата или неоплата налогов, что усложняет финансовое положение бюджета.
Для решения этой проблемы необходимо своевременно (оперативно) получать информацию о недоплатах, что позволяет принимать соответствующие меры (санкции).
Другой, не менее важной задачей является оценка поступления платежей.
Поэтому поставленные задачи позволят разрешить эти проблемы.
Рассмотрим организационно-экономическую сущность предлагаемого комплекса задач.
Задача 1. Анализ оплаты налогов по заданному предприятию.
Для заданного предприятия надо получить недоплаты по каждому виду налогов. Для решения этой задачи экономист должен иметь по каждому предприятию и каждому виду налогов плановую сумму и суммы оплат. Используются следующие первичные документы, выявленные в результате обследования: Список предприятий, Список налогов, План поступлений, Платёжное поручение.
В процессе решения задачи экономист реализует функции управления: учёта, контроля и анализа.
Экономическая сущность задачи заключается в необходимости оперативного получения информации о недоплатах по каждому виду налога заданного предприятия и принятии управленческих решений для устранения недоплат.
Задача 2. Выявление недоплат налогов на заданную дату по всем предприятиям.
Для решения второй задачи используются таблица «Список предприятий» и результаты первой задачи.
Экономическая сущность задачи состоит в выдаче на заданную дату сведений о предприятиях и налогах, по которым имеются недоплаты. Это необходимо для анализа недоплат и принятия мер по их устранению.
Выходную информацию представим в виде отчетных форм:
Отчёт для задачи 1
Анализ оплаты налогов по предприятию __________
(Предприятие)
|
Наименование налога |
Плановая сумма |
Фактич. сумма |
Недоплаты |
|
… |
… |
… |
… |
|
… |
… |
… |
… |
Отчёт для задачи 2
Недоплаты предприятий на ________
(Дата)
|
Наименование предприятия |
Наименование налога |
Недоплаты |
|
… |
… |
… |
|
Итого по предприятию |
… |
|
|
… |
… |
… |
|
Всего по предприятиям |
… |
Входную информацию делят на условно-постоянную, сохраняющую свои значения на длительный период времени, и, на постоянно меняющуюся оперативно-учётную.
Список предприятий, список налогов относят к условно- постоянной информации, План поступления налогов, платежное поручение относятся к оперативно-учетной информации.
Перечень первичных документов, а также источник и срок поступления представим в виде таблицы:
|
№ п/п |
Наимен. документа |
Реквизиты |
От кого поступает |
Срок поступления |
|
1. |
Список предприятий |
Код предприятия ИНН Наименование предприятия Адрес |
Финансовые органы |
До начала планируемого года |
|
2. |
Список налогов |
Код налога Наименование налога |
Финансовые органы |
До начала планируемого года |
|
3. |
План поступ-ления налогов |
Код предприятия Код налога Плановая сумма |
Плановый отдел налоговой инспекции |
До начала планируемого года |
|
4 |
Платежное поручение |
Номер документа Код предприятия Код налога Сумма поступления Дата перечисления |
Банк |
По мере перечисления |
Реквизитный состав первичных документов:
код предприятия, наименование предприятия, код налога, наименование налога, плановая сумма, номер документа, сумма поступления, дата перечисления.
Необходимо проанализировать каждый реквизит на наличие взаимосвязей с другими реквизитами. Реквизит приобретает смысл только тогда, когда он связан с другими реквизитами, обладающими смысловым единством.
Например, реквизиты Код предприятия и Наименование предприятия отражают сведения о предприятиях. Код налога и Наименование налога - сведения о видах налогов. Наименование предприятия, Наименование налога, Плановая сумма - эти реквизиты отражают сведения о плане поступления всех видов налогов для каждого предприятия. Плановая сумма поступлений зависит от того, к какому предприятию и к какому виду налога она относится.
Среди реквизитов, описывающих сущность, можно выделить один или несколько реквизитов, которые однозначно идентифицируют (определяют) экземпляр сущности. Такой реквизит является ключом. Другими словами, ключевые реквизиты определяются разработчиком базы данных для поиска строки в таблице.
Между ключом и другими реквизитами (описательными) существует функциональная зависимость. Например, существует функциональная зависимость между кодом предприятия и его наименованием.
По ключу можно отыскать любой нужный экземпляр среди других экземпляров сущности. Кандидатов в ключевые реквизиты в группе реквизитов может быть несколько.
Из реквизитов Код предприятия и Наименование предприятия выберем в качестве ключа Код предприятия, так как этот реквизит характеризуется компактным значением и удобен для обработки. В дальнейшем в других сущностях вместо наименования в качестве ключа будем выбирать код.
В группе реквизитов, которая характеризует план поступления налогов, одного реквизита для ключа мало, т.к. каждое предприятие может иметь несколько видов налогов. Значит, Код предприятия не определит однозначно экземпляр сущности (строки). В качестве ключа здесь выступают Код предприятия и Код налога.
Факт поступления платежей из платёжных поручений по предприятиям и видам налогов можно свести в таблицу:
|
Номер док. |
Дата |
Код предприятия |
Наим. предприятия |
ИНН |
Банк |
Код налога |
Сумма |
|
01 |
3.02.11 |
101 |
Заря |
… |
… |
01 |
50 |
|
01 |
3.02.11 |
101 |
Заря |
… |
… |
02 |
50 |
|
01 |
3.02.11 |
102 |
Восток |
… |
… |
03 |
150 |
|
… |
… |
… |
… |
… |
… |
… |
… |
Для устранения избыточной информации при автоматизированной обработке появилась необходимость в представлении данных в виде двух таблиц.
Таблица 1. Все общие сведения о платежах: номер документа, дата перечисления, код предприятия, наименование предприятия, ИНН и банк представлены в таблице ПЛАТЕЖИ один раз для каждого документа.
Для упрощения дальнейшёй работы оставим в таблице ПЛАТЕЖИ только реквизиты: номер документа, дата перечисления и код предприятия.
ПЛАТЕЖИ
|
Номер документа |
Дата |
Код предприятия |
Наим. предприятия |
ИНН |
Банк |
|
01 |
3.02.11 |
101 |
Заря |
… |
… |
|
01 |
3.02.11 |
101 |
Заря |
… |
… |
|
01 |
3.02.11 |
102 |
Восток |
… |
… |
|
… |
… |
… |
… |
… |
… |
Таблица 2. СПЕЦИФИКАЦИЯ ПЛАТЕЖЕЙ включает реквизиты: номер документа, код предприятия, код налога и сумма оплаты.
СПЕЦИФИКАЦИЯ ПЛАТЕЖЕЙ
|
Номер документа |
Код предприятия |
Код налога |
Сумма |
|
01 |
101 |
01 |
50 |
|
01 |
101 |
02 |
50 |
|
01 |
102 |
03 |
150 |
|
… |
… |
… |
… |
Таким образом, выделение спецификации позволяет сократить объём информации в памяти компьютера и трудоёмкость ввода данных.
В качестве составного ключа для первой таблицы используется Номер документа + Код предприятия, так как один номер документа может соответствовать разным предприятиям.
Для второй таблицы этого ключа недостаточно, так как он повторяется. Поэтому используется составной ключ Номер документа + Код предприятия + Код налога.
Покажем на следующей схеме функционально-зависимые реквизиты, не повторяющиеся в каждой группе.
|
|
ИНФОРМАЦИОННЫЕ ОБЪЕКТЫ |
||||
|
ПРЕД-ПРИЯТИЯ |
НАЛОГИ |
ПЛАН |
ПЛАТЕЖИ |
СПЕЦ-Я ПЛАТЕЖЕЙ |
|
|
Код предприятия |
|||||
|
Наименование предприятия |
|||||
|
Код налога |
|||||
|
Наимен. налога |
|||||
|
Плановая сумма |
|||||
|
Номер документа |
|||||
|
Сумма оплаты |
|||||
|
Дата перечисления |
Жирной линией на схеме выделены ключевые реквизиты.
Итак, для решения задач выделены пять групп (ИО) с ключевыми реквизитами (ключи подчеркнуты):
ПРЕДПРИЯТИЯ (Код предприятия, наименование предприятия);
НАЛОГИ (Код налога, наименование налога );
ПЛАН (Код предприятия, код налога, плановая сумма);
ПЛАТЕЖИ (Номер документа, код предприятия, дата перечисления);
СПЕЦ-Я ПЛАТЕЖЕЙ (Номер документа, код предприятия, код налога, сумма оплаты).
В результате анализа получили пять ИО или сущностей. Проведем парный анализ связей между ними:
ПРЕДПРИЯТИЯ ПЛАТЕЖИ (ПРЕДПРИЯТИЯ - главный ИО, ПЛАТЕЖИ - подчиненный ИО). Тип связи 1:М, т.к. одно предприятие может сделать несколько платежей в погашение одного вида налога, но один платеж соответствует одному предприятию. Связь между этими объектами обеспечивается с помощью реквизита Код предприятия.
НАЛОГИ СПЕЦ-Я ПЛАТЕЖЕЙ (НАЛОГИ - главный ИО, СПЕЦ-Я ПЛАТЕЖЕЙ - подчиненный ИО). Тип связи 1:М, т.к. один вид налога может погашаться несколькими суммами, но одна сумма соответствует одному виду налога. Связь между этими объектами обеспечивается с помощью реквизита Код налога.
ПРЕДПРИЯТИЯ ПЛАН (ПРЕДПРИЯТИЯ - главный ИО, ПЛАН - подчиненный ИО). Тип связи 1:М, т.к. каждому предприятию планируются плановые суммы по нескольким видам налогов, но каждая плановая сумма по каждому виду налога относится только к одному предприятию. Связь между этими объектами обеспечивается с помощью реквизита Код предприятия.
НАЛОГИ ПЛАН (НАЛОГИ - главный ИО, ПЛАН - подчиненный ИО). Тип связи 1:М, т.к. каждому виду налога соответствует много плановых сумм, но каждая плановая сумма относится только к одному виду налога. Связь между этими объектами обеспечивается с помощью реквизита Код налога.
ПЛАТЁЖИ СПЕЦ-Я ПЛАТЕЖЕЙ (ПЛАТЁЖИ – главный ИО, СПЕЦ-Я ПЛАТЕЖЕЙ – подчинённый ИО). Тип связи 1:М, т.к. каждому номеру платёжного поручения соответствует оплата по нескольким видам налогов. Но оплата одного вида налога относится к одному номеру платёжного поручения. Связь между этими объектами обеспечивается реквизитами Номер документа и Код предприятия.
После того, как установлены связи между сущностями, возможно построение информационно-логической модели.
ИНФОРМАЦИОННО-ЛОГИЧЕСКАЯ МОДЕЛЬ (ИЛМ) - это модель данных, отображающая предметную область в виде совокупности информационных объектов и структурных связей между ними.
Правила построения ИЛМ связаны с упорядочением ИО по уровням иерархии. Такой способ изображения по уровням называют канонической формой ИЛМ. Рассмотрим формальный способ установления уровней иерархии. Этот способ особенно необходим, когда количество ИО велико.
Способ основан на использовании матрицы смежности – квадратной матрицы (количество строк и столбцов равно числу ИО). Составим исходную матрицу смежности для рассматриваемого примера:
|
Пред- приятия |
Налоги |
План |
Платежи |
Спец-я платежей |
|
|
Предприятия |
1 |
1 |
|||
|
Налоги |
1 |
1 |
|||
|
План |
|||||
|
Платежи |
1 |
||||
|
Спец-я платежей |
|||||
|
Сумма по столбцу |
0 |
0 |
2 |
1 |
2 |
Значения «1» в матрице смежности записывается в том случае, если между ИО в строке и ИО в столбце имеется связь один – ко - многим. Использовать установленные ранее связи.
Нулевые суммы получились по столбцам ПРЕДПРИЯТИЯ и НАЛОГИ. Если сумма по столбцу равна нулю, то столбец и строка с таким же именем вычёркиваются (выделяются серым цветом), а оставшиеся строки и столбцы образуют новую матрицу смежности. Вычеркнутые ИО выносятся на нулевой уровень иерархии. В нашем примере нулевые суммы получились в столбцах ПРЕДПРИЯТИЯ и НАЛОГИ. Следовательно, они определяют нулевой уровень.
После этого получится следующая матрица:
|
План |
Платежи |
Спец-я платежей |
|
|
План |
|||
|
Платежи |
1 |
||
|
Спец-я платежей |
|||
|
Сумма по столбцу |
0 |
0 |
1 |
Во второй матрице смежности нулевые суммы получились в столбцах ПЛАН и ПЛАТЕЖИ. Следовательно, эти столбцы в ней вычёркиваются, а ПЛАН и ПЛАТЕЖИ определяют первый уровень.
|
Спец-я платежей |
|
|
Спец-я платежей |
|
|
Сумма по столбцу |
0 |
СПЕЦ-Я ПЛАТЕЖЕЙ располагается на втором (последнем) уровне.
На рисунке показана каноническая информационно- логическая модель (ИЛМ):
0-й уровень
1-й уровень
2-й уровень
Логическая структура РБД определяется совокупностью логически связанных реляционных таблиц.
Логические связи соответствуют структурным связям между объектами в инфологической модели, каждый ИО в логической структуре отображается соответствующей реляционной таблицей.
Связи между таблицами осуществляются посредством общих реквизитов (ключевых или неключевых). Логическая структура РБД имеет следующий вид:
|
ПРЕДПРИЯТИЯ |
НАЛОГИ |
|||
|
Код пред. |
Наим. пред |
Код налога |
Наим. налога |
|
Код пред. |
Код налога |
Сумма плана |
Номер документа |
Код пред. |
Дата |
|
|
ПЛАН |
ПЛАТЕЖИ |
|
Номер документа |
Код пред. |
Код налога |
Сумма оплаты |
|
СПЕЦ-Я ПЛАТЕЖЕЙ |
Физическое моделирование БД - это способ размещения информации на машинных носителях. Правила перехода от логической реляционной структуры к физической заключаются в следующем:
В процессе физического проектирования РБД необходимо:
Соответствие документов и таблиц БД
|
Имя документа |
Имя таблицы БД |
|
Список предприятий |
ПРЕДПРИЯТИЯ |
|
Список налогов |
НАЛОГИ |
|
План поступления налогов |
ПЛАН |
|
Список платежных поручений |
ПЛАТЕЖИ |
|
СПЕЦ-Я ПЛАТЕЖЕЙ |
Таблицы базы данных заполняются на основе входных (первичных) документов. Например, в таблице ПРЕДПРИЯТИЯ имена полей КОД_ПРЕД и НАИМ_ПРЕД.
Заполнить таблицы исходными данными. Для упрощения решаемых задач сократим количество реквизитов в исходных таблицах. На основании этих данных произвести вручную расчёты для задачи 1 и задачи 2.
Контрольный пример необходим для проверки правильности решения задачи на персональном компьютере.
Следует подчеркнуть, что заполнение таблиц исходными данными в контрольной работе должно осуществляться в полном соответствии с ограничениями, допущениями и особенностями ПО.
Для нашего примера таблицы, заполненные конкретными значениями, выглядят так:
ПРЕДПРИЯТИЯ НАЛОГИ
|
Код пред-приятия |
Наименование предприятия |
Код налога |
Наименование налога |
|
|
101 |
Заря |
01 |
Налог на имущество |
|
|
102 |
Восток |
02 |
Налог на прибыль |
|
|
103 |
Север |
03 |
НДС |
ПЛАН ПЛАТЕЖИ
|
Код пред. |
Код налога |
Сумма плановая |
Номер документа |
Код пред. |
Дата перечис-ления |
|
|
101 |
01 |
200 |
01 |
101 |
3.02.11 |
|
|
101 |
02 |
400 |
01 |
102 |
3.02.11 |
|
|
102 |
01 |
150 |
02 |
101 |
5.02.11 |
|
|
102 |
02 |
400 |
03 |
101 |
15.03.11 |
|
|
102 |
03 |
600 |
03 |
102 |
5.03.11 |
|
|
103 |
01 |
800 |
04 |
101 |
17.04.11 |
|
|
103 |
02 |
200 |
04 |
103 |
10.03.11 |
|
|
103 |
03 |
250 |
05 |
103 |
23.03.11 |
|
|
06 |
102 |
20.04.11 |
СПЕЦИФИКАЦИЯ ПЛАТЕЖЕЙ
|
Номер документа |
Код предприятия |
Код налога |
Сумма оплаты |
|
01 |
101 |
01 |
50 |
|
01 |
101 |
02 |
50 |
|
01 |
102 |
03 |
150 |
|
02 |
101 |
01 |
50 |
|
03 |
101 |
02 |
50 |
|
03 |
102 |
02 |
100 |
|
03 |
102 |
03 |
150 |
|
04 |
101 |
01 |
100 |
|
04 |
103 |
01 |
100 |
|
04 |
103 |
02 |
100 |
|
05 |
103 |
01 |
500 |
|
06 |
102 |
02 |
200 |
|
06 |
102 |
03 |
100 |
Примечание: данные задаются произвольно по количеству значений и содержанию с учётом ограничений.
По приведенным исходным данным выполним вручную решение поставленных задач (контрольный пример).
Задача 1. Анализ оплаты налогов по заданному предприятию.
Отберём платёжные поручения для заданного предприятия «Восток».
По отобранным платёжным поручениям вычисляем общие суммы оплаты по каждому виду налога.
Затем найдём отклонение между фактической и плановой суммой по каждому виду налога для заданного предприятия.
В результате получим:
Анализ оплаты налогов по предприятию Восток
|
Наименование налога |
Сумма плановая |
Сумма оплаты |
Недоплаты |
|
Налог на имущество |
150 |
150 |
|
|
Налог на прибыль |
400 |
300 |
100 |
|
НДС |
600 |
400 |
200 |
Задача 2. Выявление недоплат налогов на заданную дату по всем предприятиям.
Отберём платежи на заданную дату (17.03.11). Из отобранных платёжных поручений рассчитаем общие суммы по каждому виду налога для каждого предприятия.
Найдём недоплаты (план - факт) > 0.
Вычислим итоговые суммы по каждому налогу и в целом по предприятию. В результате получим:
Недоплаты предприятий на 17.03.11
|
Наименование предприятия |
Наименование налога |
Недоплаты |
|
Восток |
НДС |
300 |
|
Налог на прибыль |
300 |
|
|
Налог на имущество |
150 |
|
|
Итого по предприятию |
750 |
|
|
Заря |
Налог на прибыль |
300 |
|
Налог на имущество |
100 |
|
|
Итого по предприятию |
400 |
|
|
Север |
НДС |
250 |
|
Налог на прибыль |
100 |
|
|
Налог на имущество |
700 |
|
|
Итого по предприятию |
1050 |
|
|
Всего |
2200 |
СУБД Access является универсальным средством для создания и обслуживания баз данных, обеспечения доступа к данным, их обработки.
СУБД Access работает с реляционной базой данных и позволяет создавать схему данных, наглядно отражающую связи между таблицами.
РБД - это совокупность взаимосвязанных двумерных (реляционных) таблиц. Каждая таблица отражает одну сущность.
В РБД связи между двумя таблицами обеспечиваются по ключу связи (ключ главной таблицы). В схеме данных, построенной СУБД Access, могут быть использованы только одно - однозначные и одно - многозначные связи. Типы связей были рассмотрены при построении информационно-логической модели предметной области.
Таблицы создаются пользователем для хранения информации в базе данных. Каждой сущности соответствует одна таблица. Столбцы таблицы содержат значения реквизитов и называются полями таблицы. Строки таблицы состоят из значений полей и называются записями таблицы.
Запросы создаются пользователем для выборки необходимых данных из одной или нескольких таблиц или запросов. В запросе можно выполнить вычисления и получить результат в виде таблицы, произвести обновление данных, добавление и удаление записей в таблицах.
Формы предназначены для создания удобных экранных форм первичных документов (привычных для пользователя). Формы используются для ввода и просмотра данных.
Отчеты служат для формирования выходных документов.
После загрузки операционной системы Windows на панели задач нажать кнопку Пуск, выбрать пункт меню Программы. Найти в папке Microsoft Office программу Microsoft Access и запустить ее.
Окно приложения Microsoft Access включает строку меню, панель инструментов и строку состояния. Назначение элементов окна одинаково для всех приложений входящих в Microsoft Office (Word, Excel, Access и др.).
В части 1 методических указаний выполнено проектирование базы данных: построены логическая и физическая модели разработаны данные контрольного примера. На их основе база данных будет реализована практически.
Построение базы данных рекомендуется проводить в следующем порядке:
Пример 1: Создать новую базу данных НАЛОГ.
После загрузки Access открывается диалоговое окно Приступая к работе с Microsoft Office Access, в котором следует выбрать Новая база данных.
В окне Имя файла ввести имя Налог:
Нажать кнопку Создать.
В случае, если появится окно
подтвердите замену существующего файла, нажав кнопку Да.
Закроем открывшуюся автоматически Таблицу 1.
Создание таблиц БД возможно несколькими способами:
Пример 2. Создать структуру таблиц базы данных НАЛОГ.
Во вкладке Создание выберем Конструктор таблиц:
Описание структуры таблиц базы данных НАЛОГ представлено в таблице.
|
Имя таблицы |
Имя поля |
Ключ |
Тип данных |
Размер, формат поля |
|
ПРЕДПРИЯТИЯ |
Код предприятия |
Да |
Текстовый |
3 |
|
Наименование предприятия |
- |
Текстовый |
6 |
|
|
НАЛОГИ |
Код налога |
Да |
Текстовый |
2 |
|
Наименование налога |
- |
Текстовый |
22 |
|
|
Имя таблицы |
Имя поля |
Ключ |
Тип данных |
Размер, формат поля |
|
ПЛАН |
Код предприятия |
Да |
Текстовый |
3 |
|
Код налога |
Да |
Текстовый |
2 |
|
|
Сумма |
- |
Денежный |
||
|
ПЛАТЕЖИ |
№ документа |
Да |
Текстовый |
2 |
|
Код предприятия |
Да |
Текстовый |
3 |
|
|
Дата |
- |
Дата/время |
Краткий формат |
|
|
СПЕЦИФИКАЦИЯ ПЛАТЕЖЕЙ |
№ документа |
Да |
Текстовый |
2 |
|
Код предприятия |
Да |
Текстовый |
3 |
|
|
Код налога |
Да |
Текстовый |
2 |
|
|
Сумма |
- |
Денежный |
Создать структуру таблицы ПРЕДПРИЯТИЯ согласно приведённому описанию.
При создании структуры таблиц в верхней части окна задаются имена полей, тип данных, отмечаются ключевые поля. В нижней части окна в области под названием Свойства поля во вкладке Общие задаются свойства активного поля.
В столбце Имя поля указываются имена полей таблицы, а в столбце Тип данных – тип поля (выбирается из раскрывающегося списка).
Ограничимся следующими типами данных: текстовые, числовые, денежные, дата/время.
Текстовый или символьный тип данных применяют для тех полей, которые не участвуют в расчетах.
Числовой тип задают числовым данным (например, количество).
Денежный тип задают денежным данным (например, сумма, цена).
Тип дата/время - используется для формирования дат.
Рассмотрим некоторые свойства поля:
Размер поля устанавливается для каждого типа данных по определенным правилам.
Для текстового поля размер задается от 1 до 255 байт (или символов). По умолчанию он устанавливается автоматически равным 255 байт. Для экономии памяти размер поля определяется по максимальному размеру данного.
Для числового поля размер выбирается из предлагаемого списка.
Замечание: размер поля определяется максимальным значением этого поля в документе.
Для обозначения ключевого поля надо сделать поле активным и нажать на панели инструментов кнопку Ключевое поле. Слева от имени ключевого поля появится символ Ключ.
Если ключ составной, ключевые поля расположить в начале таблицы, установить курсор слева от Имя поля, нажать левую кнопку мыши и, не отпуская ее, протянуть по всем ключевым полям. Затем на панели инструментов нажать кнопку Ключевое поле. Слева от имени каждого ключевого поля появится символ Ключ.
Можно также выделить необходимые поля при нажатой клавише Ctrl. Это действие необходимо проделать при указании ключевых полей в таблицах ПЛАН, ПЛАТЕЖИ и СПЕЦИФИКАЦИЯ ПЛАТЕЖЕЙ.
Для сохранения структуры таблицы закрыть ее, а затем ввести имя. По умолчанию система предлагает имя Таблица 1, Таблица 2 и так далее.
Примечание. Если первичный ключ не был определен, Microsoft Access при сохранении таблицы спросит, нужно ли создать ключевое поле. Выбрать кнопку Нет, сохранить таблицу, затем открыть ее в режиме конструктора и определить ключевые поля.
Имена объектов Microsoft Access должны содержать не более 255 символов и могут включать любые комбинации букв, цифр, пробелов и специальных символов за исключением точки (.), восклицательного знака (!), надстрочного символа (`) и прямых скобок ([ ]). Имя не должно начинаться с пробела. Сохранить таблицу ПРЕДПРИЯТИЯ. Затем последовательно создать структуры таблиц НАЛОГИ, ПЛАН, ПЛАТЕЖИ, СПЕЦИФИКАЦИЯ ПЛАТЕЖЕЙ.
В режиме Конструктор выполняются действия для корректировки структуры таблицы базы данных.
Вставить новое поле в структуру таблицы. Для этого необходимо выделить строку, перед которой надо ввести новое поле и нажать клавишу Insert. Появится пустая строка для внесения нового поля.
Переместить поля. Выделить поля, которые надо переместить (для выделения одного поля щелкнуть область выделения строки этого поля, для выделения нескольких полей протащить указатель по областям выделения строк этих полей), нажать и удерживать кнопку мыши над областью выделения строки выделенного поля. Microsoft Access отобразит тонкую горизонтальную черту над последней выделенной строкой. Перетащить эту горизонтальную черту на строку, над которой требуется поместить перемещаемые поля.
Примечание. При изменении порядка полей в режиме конструктора таблицы изменяется порядок, в котором эти поля хранятся в таблице, а также порядок столбцов в режиме просмотра этой таблицы.
Удалить поле из описания структуры. Для этого нужно - выделить строку с удаляемым полем и нажать клавишу Delete. Строка будет удалена.
Изменение или удаление ключевого поля рекомендуется выполнять до ввода данных в таблицу и построения схемы базы данных. Для отказа от определения ключевого поля сделать это поле активным в окне конструктора таблицы. Нажать кнопку Ключевое поле на панели инструментов, символ ключ исчезает.
Схема данных в Access строится на основе информационно-логической модели разработанной при проектировании базы данных. В ней определяются и запоминаются связи между таблицами. Эти связи используются при создании форм, запросов, отчетов, а также обеспечивают целостность взаимосвязанных данных при корректировке таблиц.
Пример 3. Создать схему данных для базы данных НАЛОГ.
Для создания схемы данных нажать кнопку Схема данных на панели Работа с базами данных:
Затем в окне Добавление таблицы открыть на панели объектов Таблицы, выделить ПРЕДПРИЯТИЯ, нажать кнопку Добавить; выделить и добавить таблицы ПЛАН, НАЛОГИ, ПЛАТЕЖИ, СПЕЦИФИКАЦИЯ ПЛАТЕЖЕЙ.
Закрыть окно Добавление таблицы. На экране появятся все выбранные таблицы со списками полей. Таблицы можно перемещать с помощью левой кнопки мыши. В главной таблице выбрать поле, по которому устанавливается связь. Затем при нажатой кнопке мыши соединить это поле с соответствующим полем подчиненной таблицы.
При установлении связи между таблицами ПРЕДПРИЯТИЯ и ПЛАТЕЖИ связь осуществляется по полю Код предприятия. После того, как намечена очередная связь между таблицами, появляется окно Изменение связей, в котором должен быть определен тип отношения один-ко-многим.
Если на связь между таблицами наложены условия ссылочной целостности, то Access не позволит:
Эти правила делают невозможным случайное удаление или изменение связанных данных. Их нарушение приведет к выводу на экран диалогового окна с предупреждением, а действие выполнено не будет.
Чтобы преодолеть ограничения на удаление или изменение связанных записей, сохраняя при этом целостность данных, следует включить режимы каскадного обновления и каскадного удаления.
Режимы каскадного удаления записей и каскадного обновления данных в таблицах, на которые наложены условия целостности, позволяют упростить обновление и удаление данных из связанных таблиц.
При установке флажка Каскадное обновление связанных полей изменение значения в ключевом поле главной таблицы приводит к автоматическому обновлению соответствующих значений во всех связанных записях. Например, при изменении значения кода предприятия в таблице ПРЕДПРИЯТИЯ будут изменены значения этого кода предприятия в таблицах ПЛАН и ПЛАТЕЖИ.
При установке флажка Каскадное удаление связанных записей удаление записи в главной таблице приводит к автоматическому удалению связанных записей в подчиненной таблице.
Установить связи между таблицами ПРЕДПРИЯТИЕ – ПЛАН, ПРЕДПРИЯТИЕ – ПЛАТЕЖИ, НАЛОГИ – ПЛАН, НАЛОГИ – СПЕЦИФИКАЦИЯ ПЛАТЕЖЕЙ, ПЛАТЕЖИ – СПЕЦИФИКАЦИЯ ПЛАТЕЖЕЙ.
Поставить флажки Обеспечение целостности данных, Каскадное обновление связанных полей и Каскадное удаление связанных полей. Для всех связей должен быть установлен тип отношения один-ко-многим.
Между таблицами ПЛАТЕЖИ И СПЕЦ-Я ПЛАТЕЖЕЙ связь строится по составному ключу №_ДОК + КОД_ПРЕД.
Для выделения составного ключа нужно нажать клавишу Ctrl и, не отпуская ее, отметить кнопкой мыши поля №_ДОК и КОД_ПРЕД таблицы ПЛАТЕЖИ. Соединить выделенные поля с соответствующими полями таблицы СПЕЦ-Я ПЛАТЕЖЕЙ.
В открывшемся диалоговом окне должен установиться тип отношения один-ко-многим. В окне Связанная таблица/запрос с помощью полей со списками необходимо установить поля связей, соответствующие главной таблице. Поставьте флажки Обеспечение целостности данных, Каскадное обновление связанных полей и Каскадное удаление связанных полей.
После того, как все связи установлены, схема данных должна иметь вид:
Сохранить схему данных.
Замечание: значок означает отношение много, а 1 – один. Такое обозначение на линиях связей появляется при установке флажка Обеспечение ссылочной целостности.
Созданную схему данных можно корректировать:
Замечание: Если после создания схемы данных требуется изменить или удалить ключевое поле в структуре таблицы, то сначала следует удалить связи с этой таблицей в схеме данных.
Пользовательская форма позволяет отображать данные на экране в виде входного (первичного) документа. С помощью форм удобно вводить в таблицы данные входных документов, просматривать или редактировать записи таблиц. Формы создаются после того как разработаны таблицы и схема данных.
Создадим форму с помощью мастера форм, затем отредактируем ее с помощью конструктора.
Рассмотрим пример создания однотабличной формы.
Пример 4. Создать форму для таблицы ПРЕДПРИЯТИЯ с помощью мастера форм.
На вкладке Создание нажать кнопку Другие формы и в развернувшемся списке выбрать Мастер форм.
В открывшемся окне Создание форм выбрать из списка таблицу ПРЕДПРИЯТИЯ.
В Доступные поля выбрать КОД_ПРЕД и НАИМ_ПРЕД, нажав кнопку >>. Эти поля будут перемещены в окно Выбранные поля. Нажать кнопку Далее.
Назначение кнопок:
В следующем окне Создание форм выбрать из списка (в один столбец, ленточный, табличный, выровненный) любой внешний вид формы. Например, в один столбец. Нажать кнопку Далее > .
Следующим шагом выбрать из списка любой стиль, например, Официальный. Нажать кнопку Далее >.
В следующем окне предлагается задать имя для формы. По умолчанию имя формы совпадает с именем таблицы ПРЕДПРИЯТИЯ.
Можно ввести другое имя по своему усмотрению. Ниже, в этом же окне, предлагается выбрать Дальнейшие действия: открытие формы для просмотра и ввода данных или изменение макета формы. Выбрать первое действие и нажать кнопку Готово.
Получим форму, отражающую первую запись таблицы ПРЕДПРИЯТИЯ.
Закрыть форму. В списке форм окна НАЛОГ: база данных появится форма ПРЕДПРИЯТИЯ.
Далее необходимо аналогично создать однотабличные формы Налоги и план. Заполним три созданные формы исходными данными из таблиц:
Таблица ПРЕДПРИЯТИЯ
|
Код предприятия |
Наименование предприятия |
|
101 |
ЗАРЯ |
|
102 |
ВОСТОК |
|
103 |
СЕВЕР |
Таблица НАЛОГИ
|
Код налога |
Наименование налога |
|
01 |
Налог на имущество |
|
02 |
Налог на прибыль |
|
03 |
НДС |
Таблица ПЛАН
|
Код предприятия |
КОД_НАЛ Код налога |
Сумма |
|
101 |
01 |
200,00р. |
|
101 |
02 |
400,00р. |
|
102 |
01 |
150,00р. |
|
102 |
02 |
400,00р. |
|
102 |
03 |
600,00р. |
|
103 |
01 |
800,00р. |
|
103 |
02 |
200,00р. |
|
103 |
03 |
250,00р. |
При заполнении форм данными следует придерживаться определённых требований к последовательности их заполнения в соответствии со схемой данных:
На основе связанных таблиц можно создать составную форму.
Пример 5. Создать составную форму для таблиц ПЛАТЕЖИ и СПЕЦ-Я ПЛАТЕЖЕЙ с помощью мастера форм.
Выполнить последовательность действий:
Появляется составная форма ПЛАТЕЖИ, в которой отражены объединенные данные из таблиц ПЛАТЕЖИ и СПЕЦИФИКАЦИЯ ПЛАТЕЖЕЙ. Закрыть форму.
Для редактирования созданных форм используется Конструктор форм.
Пример 6. Отредактировать созданную форму ПЛАТЕЖИ: ввести общий заголовок “Платежное поручение №” и расположить заголовки полей и поля в соответствующие места формы.
Для редактирования открытой формы можно использовать кнопку панели инструментов Режим, выбрав из списка Конструктор. Если конструктор формы не открыт, щелкните по форме ПЛАТЕЖИ правой кнопкой мыши и выберете из развернувшегося списка Конструктор.
Многотабличная форма состоит из главной формы и подчинённой. В главной и подчинённой формах имеются одни и те же области: Заголовок формы, Область данных, Примечание формы.
В появившемся окне формы ПЛАТЕЖИ установить курсор на горизонтальную границу областей Заголовок формы и Область данных (появляется крестообразный курсор) и протащить вниз левой кнопкой мыши для образования места под заголовок.
На панели элементов Элементы управления вкладки Конструктор
должны быть включены кнопки Выбор объектов и Мастера
Включить на панели элементов кнопку (Надпись), переместить видоизмененный курсор +А в левый верхний угол области заголовка формы. Нажать левую кнопку мыши и, не отпуская ее, растянуть рамку до необходимого размера заголовка.
Ввести заголовок Платежное поручение №, нажать Enter, после чего выделяется рамка заголовка для его редактирования. Изменение размера и цвета шрифта, цвета фона и рамки выполнить с помощью панели инструментов Шрифт.
Замечание: Для выделения редактируемого объекта, необходимо щёлкнуть по нему левой кнопкой мыши.
Microsoft Access автоматически объединяет поля в Макет. Это позволяет перемещать связанные поля, как единое целое. Однако для перемещения полей по отдельности следует этот Макет удалить. Для этого необходимо выделить интересующие нас поля, нажав клавишу Shift и кликая левой кнопкой мыши по полям формы. Затем щелкнуть по выделенным полям правой кнопкой мыши. В развернувшемся списке выбрать Макет и Удалить.
Чтобы изменить расположение поля проще всего воспользоваться командами Вырезать и Вставить контекстного меню, вызывая его щелчком правой кнопки мыши.
Для изменения расположения поля № документа указанным приёмом перенести его в область заголовка формы и расположить после текста платёжное поручение №.
Для повышения эффективности ввода данных в Access используются Поля со списком. Это необходимо, чтобы в главной форме ввод наименования предприятия обеспечивался через выбор из списка, а код предприятия вводился автоматически.
В режиме конструктора формы ПЛАТЕЖИ выберем на панели элементов кнопку Поле со списком и крестообразным курсором в области данных главной формы вычертим прямоугольник, в котором будет располагаться поле со списком. Это поле в дальнейшем будет представлять наименование предприятия.
В появившемся первом окне предлагается выбрать источник значений для списка. Выбрать «Объект «Поле со списком» будет использовать значения из таблицы или запроса». Нажать Далее.
В следующем окне выберем таблицу – источник ПРЕДПРИЯТИЯ и нажмём Далее.
В следующем окне из доступных полей выберем Наименование предприятия, нажмём Далее. В следующем окне порядок сортировки не указываем. Если наименование предприятия не помещается по ширине столбца (это видно в следующем окне), то задать нужную ширину, раздвинув его. Нажать Далее.
В следующем окне отметим переключатель Сохранить в поле и выберем в раскрывшемся списке Код предприятия, в которое будут вводиться значения из списка.
Нажать Далее. В последнем окне можно задать подпись для поля со списком. Нажать Готово.
Выполним редактирование подчинённой формы.
В окне подчиненной формы Спецификация платежей выполнить необходимое редактирование (изменение текста заголовков, формы, шрифта, цвета и так далее). Нажать клавишу Shift и выделить надпись Спецификация платежей и остальную часть подчиненной формы. На выделенном нажать правую кнопку мыши и в раскрывшемся списке выбрать Макет и Удалить. Так же удалить Макет для остальных элементов подчиненной формы.
Выделить надпись Спецификация платежей и удалить её с помощью клавиши Delete.
В области данных подчинённой формы создать поле со списком для наименования налога:
а) После размещения поля со списком открывается окно мастера. В первом окне предлагается выбрать источник значений для списка. Поставьте переключатель на значение «Объект "поле со списком" будет использовать значения из таблицы или запроса». Нажмите кнопку Далее.
б) Выберите таблицу-источник НАЛОГИ и нажмите кнопку Далее. Выберите поле Наименование налога, кнопка Далее.
в) Задайте ширину списка (раздвинув границу курсором мыши) и скройте ключевой столбец. Кнопка Далее.
г) Сохраните значение в поле Код налога. Кнопка Далее и нажмите кнопку Готово.
д) Появившуюся надпись ПолеСоСписком4 удалить, а в области Заголовка подчиненной формы создать надпись Налог.
Для формирования кнопок перехода к следующему или предыдущему документу ПЛАТЕЖИ создадим кнопки. В режиме конструктора на панели элементов щелкнуть кнопку (Кнопка). Видоизменившимся крестообразным курсором начертить кнопку в области заголовка главной формы. В появившемся окне Создание кнопок выбрать в категории Переходы по записям действие Следующая запись и нажать Далее>. Поставить переключатель на текст и ввести текст Следующее ПП. Нажать Далее>, затем Готово.
Аналогично создать кнопку Предыдущее ПП.
- Выбрать в категории Обработка записей действие Добавить запись.
- Выбрать в категории Обработка записей действие Удалить запись
- Выбрать в категории Работа с формой действие Закрыть форму.
Замечание: кнопки можно обозначать рисунком из списка или текстом.
Созданные кнопки дублируют некоторые действия, доступные через элементы управления окна формы.
Чтобы управление было доступно только через кнопки, размещенные в форме, нужно изменить некоторые Свойства формы в режиме конструктора.
Если не открыто Окно свойств главной формы, необходимо щелкнуть правой кнопкой мыши вне области формы за пределами сетки, то есть по тёмному участку. Появляется контекстное меню. Выбрать команду Свойства.
В появившемся Окне свойств на вкладке Макет установить значение Нет для свойств Кнопки перехода, Кнопка закрытия, так как для этих действий в форме созданы соответствующие кнопки. Значение Отсутствуют для свойств Полосы прокрутки и Кнопки размеров окна. Эти элементы управления исчезнут из окна формы.
После этого форма ПЛАТЕЖИ в режиме конструктора примет вид:
В платёжном поручении, как правило, должна быть указана общая сумма платежа. Чтобы получить её в форме, следует создать в подчинённой форме, в области Примечание формы формулу =Sum([Сумма]).
Последовательность действий:
В нашем случае:
а) в первом окне выбрать Функции, а затем Встроенные функции;
б) в третьем окне выбрать из списка функцию Sum или вручную набрать функцию Sum(), удалить содержимое внутри скобок;
в)в первом окне активизировать Спецификация платежей;
г) во втором окне выбрать <Список полей>;
д) в третьем окне щёлкнуть по полю Сумма;
е) нажать кнопку Вставить, получим выражение Sum([СУММА]), нажать ОК;
ж) в окне Свойства установим для этого поля формат и число десятичных знаков. Для этого открыть вкладку Макет, выбрать формат Денежный;
з) закрыть окно Свойства;
и) изменить надпись вместо «поле…» на «Общая сумма платежа».
После этого форма Платежи в режиме конструктора примет вид:
Закрыть конструктор форм.
Для ввода данных в таблицы используются формы.
После ввода данных через формы будут заполнены и таблицы:
ПРЕДПРИЯТИЯ НАЛОГИ
|
Код пред-приятия |
Наименование предприятия |
Код налога |
Наименование налога |
|
|
101 |
Заря |
01 |
Налог на имущество |
|
|
102 |
Восток |
02 |
Налог на прибыль |
|
|
103 |
Север |
03 |
НДС |
ПЛАН ПЛАТЕЖИ
|
Код пред. |
Код налога |
Сумма плановая |
Номер документа |
Код пред. |
Дата перечис-ления |
|
|
101 |
01 |
200 |
01 |
101 |
3.02.11 |
|
|
101 |
02 |
400 |
01 |
102 |
3.02.11 |
|
|
102 |
01 |
150 |
02 |
101 |
5.02.11 |
|
|
102 |
02 |
400 |
03 |
101 |
15.03.11 |
|
|
102 |
03 |
600 |
03 |
102 |
5.03.11 |
|
|
103 |
01 |
800 |
04 |
101 |
17.04.11 |
|
|
103 |
02 |
200 |
04 |
103 |
10.03.11 |
|
|
103 |
03 |
250 |
05 |
103 |
23.03.11 |
|
|
06 |
102 |
20.04.11 |
СПЕЦИФИКАЦИЯ ПЛАТЕЖЕЙ
|
Номер документа |
Код предприятия |
Код налога |
Сумма оплаты |
|
01 |
101 |
01 |
50 |
|
01 |
101 |
02 |
50 |
|
01 |
102 |
03 |
150 |
|
02 |
101 |
01 |
50 |
|
03 |
101 |
02 |
50 |
|
03 |
102 |
02 |
100 |
|
03 |
102 |
03 |
150 |
|
04 |
101 |
01 |
100 |
|
04 |
103 |
01 |
100 |
|
04 |
103 |
02 |
100 |
|
05 |
103 |
01 |
500 |
|
06 |
102 |
02 |
200 |
|
06 |
102 |
03 |
100 |
Исходные данные можно ввести непосредственно в таблицы.
Данные можно корректировать с помощью форм и непосредственно в таблицах.
Если необходимо откорректировать данные непосредственно в таблице, то существует возможность:
Для удаления незавершенной записи достаточно нажать клавишу ESC.
Изменение порядка следования полей рассмотрим на примере.
Пример 7. В таблице ПЛАТЕЖИ поле ДАТА установить перед полем Код предприятия.
Для изменения порядка следования полей:
Эти изменения будут отображаться только при просмотре данных в таблице. Структура таблицы не изменится.
Для обработки данных в СУБД используются запросы. Запрос можно построить на основе одной или нескольких таблиц и запросов.
Запросы можно разделить на несколько видов:
Основные принципы создания запроса заложены в запросе на выборку.
В методическом пособии рассматриваются следующие виды обработки данных в запросах на выборку:
На основе нескольких связанных таблиц можно построить многотабличный запрос.
Пример 8. Используя три таблицы: ПРЕДПРИЯТИЯ, НАЛОГИ, ПЛАН создать запрос, заменив коды предприятий и налогов их наименованиями.
Порядок создания запроса:
В верхней части окна Запрос1:… отображены списки полей таблиц ПЛАН, ПРЕДПРИЯТИЯ и НАЛОГИ, а в нижней находится область для формирования запроса (бланк запроса). Так как запрос создается на основе таблиц, в окне запроса отображаются связи, установленные в схеме данных.
Двойным щелчком по любому полю таблицы его можно поместить в бланк запроса (или перетащить левой кнопкой мыши).
В строку Поле бланка запроса поместить из таблицы ПРЕДПРИЯТИЯ поле Наименование предприятия, из таблицы НАЛОГИ – поле Наименование налога, из таблицы ПЛАН - поле Сумма в столбцы с первого по третий соответственно. Под каждым именем поля в следующей строке появится имя таблицы, из которой взято поле.
Бланк запроса выглядит следующим образом:
Примечание: Значок V (вывод на экран) появляется автоматически. Щелкнув по этому значку, можно отменить вывод поля на экран (убрать значок).
После заполнения бланка выполнить запрос с помощью кнопки (Выполнить) панели Конструктор. На экране появится результат запроса:
Просмотрев результаты запроса, закрыть окно. Сохранить запрос и вместо стандартного имени Запрос 1 ввести в диалоговом окне имя План с расшифровками кодов.
Если требуется получить сведения для одного значения какого-либо поля, то в этом случае необходимо задать его значение в строке Условие отбора.
Пример 9. Из таблицы ПЛАН выбрать сведения по налогу 02. В бланк запроса включить поля: КОД_ПРЕД, КОД_НАЛ, СУММА.
Создать однотабличный запрос на основе таблицы ПЛАН:
Результат запроса:
При необходимости изменения кода налога надо вносить изменения в бланке запроса.
Например, вместо 02 поместить 03 и так далее. Это не удобно, поэтому используют параметр для этого поля. При запуске запроса в диалоговом окне запрашивается значение параметра. Любое его значение вводит пользователь.
Запросы такого типа называют параметрическими. Параметр в бланке запроса размещают в квадратных скобках. Для примера 9 – [Код налога:]. В квадратных скобках нельзя использовать символ точка.
Значение вводимого параметра сохраняется на время выполнения запроса (или группы связанных запросов).
Бланк запроса должен иметь вид:
При выполнении этого запроса на экране появляется диалоговое окно Введите значение параметра, в котором нужно задать код налога.
Таким образом, используя параметр, можно получить результат для любого из видов налога.
Задать в диалоговом окне значение 01. Получится результат:
Дать запросу имя План по заданному коду налога.
Над полями запроса можно выполнять вычисления. При этом создается новое вычисляемое поле. Установить курсор в пустой столбец в строку Поле. Для создания выражения на панели инструментов нажать кнопку Построить. Откроется окно Построитель выражений.
Выражение для вычисляемого поля может состоять из имен полей различных таблиц и запросов, функций, чисел и так далее. Построитель дает возможность выбрать нужные поля из таблиц и запросов, функции из списка функций. Знаки арифметических операций имеются в окне Построитель выражений.
Пример 10. Представить сумму налога по плану в долларах на основании таблицы ПЛАН.
Зная курс доллара (например, 30 рублей), построить выражение для вычисления: СУММА/30
Выражение для вычисления формируется в верхней части окна Построитель выражений :
В верхней части окна появится выражение:
СУММА В $: [ПЛАН] ! [СУММА] / 30
Нажать ОК. Бланк запроса имеет вид:
Выполнить запрос, сохранить его под именем Плановые суммы в $$.
Для представления суммы в долларах с двумя десятичными знаками, в режиме Конструктор установить курсор в этом поле и щёлкнуть в меню кнопку Свойства, во вкладке Общие выбрать Формат поля и, открыв список, выбрать Фиксированный, число десятичных знаков 2.
Результат выполнения запроса:
Рассмотрим применение функций в запросах.
В оперативных документах обычно используются даты. Для выполнения анализа данных по месяцам необходимо преобразовать даты в месяцы. Приложение Access позволяет выполнить такое преобразование несколькими способами. В следующем примере показывается один из способов преобразования даты платежа в месяц платежа.
Пример 11. Определить числовое значение месяца в таблице ПЛАТЕЖИ и его текстовое название.
Шаг 1. Создать новый запрос на основе таблицы ПЛАТЕЖИ. В бланке запроса вывести все поля этой таблицы.
Установить курсор в свободный столбец и вызвать Построитель выражений. Задать имя поля МЕСЯЦ ЧИСЛ:. Выбрать в папке Функции/Встроенные функции категорию Дата/время, а затем функцию Month. Нажать кнопку Вставить. Выделить значение «number». Выбрать в папке Таблицы таблицу ПЛАТЕЖИ, в окне полей таблицы – поле ДАТА и нажать кнопку Вставить. Получится выражение:
МЕСЯЦ ЧИСЛ: Month([ПЛАТЕЖИ]![ДАТА])
Нажать кнопку ОК.
Бланк запроса имеет вид:
После выполнения запроса получим:
Просмотреть полученный результат, сохранить запрос под именем Числовой месяц платежа.
Шаг 2. На основе запроса Числовой месяц платежа создать запрос для получения наименования месяца.
В бланке запроса вывести все поля предыдущего запроса.
Установить курсор в свободный столбец, вызвать Построитель выражений, задать имя поля НАИМЕНОВАНИЕ МЕСЯЦА: выбрать в первом окне Функции / Встроенные функции. Во втором окне выбрать Дата / Время, а затем функцию MonthName. Нажать кнопку Вставить. Выделить в круглых скобках «month»;«abbreviate», выбрать в первом окне Запросы – запрос Числовой месяц платежа, во втором окне МЕСЯЦ ЧИСЛ и нажать кнопку Вставить. В построителе получаем выражение:
НАИМ МЕСЯЦА: MonthName ([Числовой месяц платежа] ! [МЕСЯЦ ЧИСЛ])
Нажать ОК, выполнить запрос, получим:
Сохранить запрос под именем Наименование месяца платежа.
Если месяц необходимо получить как текстовое данное, то следует использовать функцию Format. Это необходимо тогда, когда номер месяца двузначное число (10, 11, 12).
Пример 12. Определить текстовое значение месяца платежа в таблице Платежи.
Создать новый запрос на основе таблицы ПЛАТЕЖИ. В бланке запроса вывести все поля этой таблицы. Установить курсор в поле ДАТА, вызвать Построитель выражений и в нём:
- Задать имя поля МЕСЯЦ ТЕКСТ.
- Выбрать в первом окне Функции / Встроенные функции, во втором окне выбрать Дата / Время, а затем функцию Format, нажать кнопку Вставить.
- Выделить содержимое круглых скобок, выбрать в первом окне в папке Таблицы таблицу ПЛАТЕЖИ, во втором окне выбрать поле ДАТА и нажать кнопку Вставить.
- Набрать вручную остальные символы ; «mm»
( m – латинский символ)
Получим выражение:
МЕСЯЦ ТЕКСТ: Format ([ПЛАТЕЖИ]![ДАТА]; «mm»)
Бланк запроса будет иметь вид:
Выполнить запрос.
Просмотрев полученный результат, сохранить запрос под именем Месяц платежа.
Для получения квартала по дате используется та же функция Format:
Квартал: Format ([ПЛАТЕЖИ]![ДАТА]; «q»)
Бланк запроса будет иметь вид:
Выполнить запрос:
Сохранить запрос под именем Квартал.
С помощью групповых операций записи собираются в группы с одинаковыми значениями указанных полей. Формирование в группы необходимо в тех случаях, когда для других полей этих групп требуется вычислить значение какой-либо статистической функции. Например:
Sum - сумма значений некоторого поля для группы;
Avg - среднее значение некоторого поля для группы;
Max, Min - максимальное и минимальное значение поля в группе;
Count - количество значений поля в группе без учета пустых значений;
Условие - отбор значений по группам, соответствующим условиям отбора
С помощью функции Avg можно подсчитать среднее значение в каждой группе. Функции Min, Max выбирают минимальное или максимальное значение в каждой группе. С помощью функции Count подсчитывается количество записей в каждой группе.
Пример 13. Подсчитать суммы, выплаченные каждым предприятием по каждому виду налога.
В бланк запроса поместить поля таблицы СПЕЦИФИКАЦИЯ ПЛАТЕЖЕЙ в следующей последовательности: Код предприятия, Код налога, Сумма.
На панели инструментов нажать кнопку (Итоги). После этого в бланке запроса появится дополнительная строка Групповая операция.
Согласно поставленной задаче, для полей Код предприятия и Код налога в строке Групповая операция должно быть установлено значение Группировка. Для поля Сумма в строке Групповая операция открыть список и выбрать функцию Sum.
В этом запросе выполнена группировка по полю Код предприятия, затем внутри каждой группы по полю Код налога.
Сначала создаются группы с одинаковыми кодами предприятий, затем внутри них создаются группы с одинаковыми кодами налогов. Затем с помощью функции SUM, выбранной из списка, вычисляются суммы в образованных группах.
|
Код предприятия |
Код налога |
Сумма |
|
|
101 |
01 |
50,00 р. |
|
|
101 |
01 |
50,00 р. |
|
|
101 |
01 |
100,00 р. |
200,00 р. |
|
101 |
02 |
50,00 р. |
|
|
101 |
02 |
50,00 р. |
100,00 р. |
|
102 |
02 |
100,00 р. |
|
|
102 |
02 |
200,00 р. |
300,00 р. |
|
102 |
03 |
150,00 р. |
|
|
102 |
03 |
150,00 р. |
|
|
102 |
03 |
100,00 р. |
400,00 р. |
|
103 |
01 |
100,00 р. |
|
|
103 |
01 |
500,00 р. |
600,00 р. |
|
103 |
02 |
100,00 р. |
100,00 р. |
В результате группировки получены 6 групп, по которым вычисляются общие суммы: 200,00р.; 100,00р.; 300,00р.; 400,00р.; 600,00р.;100,00р.
После выполнения запроса получится следующий результат:
Закрыть окно. Запрос сохранить под именем Суммы выплат по налогам.
Если требуется получить количество платежей каждого предприятия по каждому налогу, то необходимо выбрать из списка функцию COUNT.
Закрыть окно. Запрос сохранить под именем Количество.
Создаются группы и подсчитывается количество платежей по каждому предприятию и каждому налогу.
|
КОД_ПРЕД |
КОД_НАЛ |
СУММА |
|
|
101 |
01 |
50,00 р. |
|
|
101 |
01 |
50,00 р. |
|
|
101 |
01 |
100,00 р. |
3 |
|
101 |
02 |
50,00 р. |
|
|
101 |
02 |
50,00 р. |
2 |
|
102 |
02 |
100,00 р. |
|
|
102 |
02 |
200,00 р. |
2 |
|
102 |
03 |
150,00 р. |
|
|
102 |
03 |
150,00 р. |
|
|
102 |
03 |
100,00 р. |
3 |
|
103 |
01 |
100,00 р. |
|
|
103 |
01 |
500,00 р. |
2 |
|
103 |
02 |
100,00 р. |
1 |
После выполнения запроса получим результат:
Можно создавать запросы, в которых используются не только таблицы, но и результаты запросов. Рассмотрим задачу, для решения которой необходимо создать несколько запросов.
Пример 14. Сделать анализ выполнения плана оплаты налогов по каждому предприятию. Выдать значения долга.
Предположим, что предприятие СЕВЕР (код 103) не осуществляло платежи. Для этого удалить записи из таблиц ПЛАТЕЖИ и СПЕЦИФИКАЦИЯ ПЛАТЕЖЕЙ по 103 предприятию.
Для решения задачи необходимо сравнить суммы по факту и плану по каждому предприятию. Плановые суммы рассчитываются на основании таблицы ПЛАН. Фактические выплаты можно рассчитать по таблице СПЕЦИФИКАЦИЯ ПЛАТЕЖЕЙ.
Предприятия произвели оплату не по всем видам налогов. Поэтому в результате решения этой задачи надо получить сведения о недоплатах и случаях отсутствия оплаты.
Шаг 1. Рассчитать суммарные планы выплат для предприятий. Данные группируются по полю Код предприятия и рассчитывается общая плановая сумма для каждого предприятия.
Вид бланка запроса:
Для изменения заголовка поля Сумма:
В запросном бланке изменений не произойдёт, а после выполнения запроса в заголовке столбца таблицы появится текст План.
Результат выполнения запроса:
Сохранить запрос под именем Сумма План.
Шаг 2. Рассчитать суммарные выплаты предприятий. Запрос конструируется на основе таблицы СПЕЦИФИКАЦИЯ ПЛАТЕЖЕЙ аналогично предыдущему запросу.
Полю Сумма присваивается заголовок Факт.
Результат выполнения запроса:
Сохранить запрос под именем Сумма Факт.
Шаг 3. Результаты Сумма План и Сумма Факт объединить с помощью параметра объединения. Для этого создать запрос:
Связать запросы Сумма План и Сумма Факт по общему полю Код предприятия.
Выделить двойным щелчком линию (щелчком по ней), соединяющую Код предприятия из Сумма План с Код предприятия из Сумма Факт.
В появившемся окне Параметры объединения установить флажок на цифру 2 – «Объединение ВСЕХ записей из «Сумма План» и только тех записей из «Сумма Факт», в которых связанные поля совпадают». Нажать ОК.
Пункт 2 выбираем потому, что надо выбрать из ПЛАН все записи.
Для получения отклонения в пустом столбце создать выражение:
=[Сумма План]![Sum-Сумма] – Nz ([Сумма Факт]![Sum-Сумма])
Здесь использована функция Nz. Она применяется в выражении, в котором могут оказаться пустые значения.
С помощью функции Nz производится замена пустых значений на нули только в формуле.
В результате выполнения запроса получим:
Сохранить запрос под именем План-Факт.
После этого восстановить удалённые записи по предприятию 103 (в таблицах ПЛАТЕЖИ и СПЕЦИФИКАЦИЯ ПЛАТЕЖЕЙ).
Пример 15. Получить недоплаты налогов на заданную дату.
Перед решением этой задачи составим подробное описание последовательности действий (алгоритм) над исходными или уже полученными на предыдущем шаге данными для получения результатов.
Алгоритм можно представить в виде словесного описания или блок-схемы. Для примера 15 составим алгоритм в виде блок-схемы.
Для изображения блоков в блок-схеме используют стандартные графические фигуры. Например,
– начало или конец блок-схемы.
– процесс (операция) над данными.
– документ на магнитном диске.
– логическая операция, по которой принимается решение.
Действия в блок-схеме выполняются последовательно сверху вниз и слева направо.
Рассмотрим решение задачи по шагам.
Шаг 1. Из двух таблиц ПЛАТЕЖИ и СПЕЦИФИКАЦИЯ ПЛАТЕЖЕЙ выбрать записи на заданную дату.
После выполнения запроса на 17.03.11 получим результат:
Сохранить запрос под именем Платежи на дату.
Шаг 2. Создать запрос получения сумм оплаты по каждому предприятию и каждому виду налога на заданную дату. Запрос строится на основе предыдущего запроса Платежи на дату. Бланк запроса будет иметь вид:
После выполнения запроса на 17.03.11 получим результат:
Сохранить запрос под именем Сумма факт на дату.
Шаг 3. Создать запрос на основе таблицы ПЛАН и запроса Сумма факт на дату, объединив таблицу и запрос по общим параметрам Код предприятия и Код налога по аналогии с примером 14 (параметры объединения).
Получить недоплаты налогов на заданную дату.
Бланк запроса имеет вид:
В Построителе выражений создать выражение:
ОТКЛОНЕНИЕ:
[ПЛАН]![СУММА] – Nz([Сумма факт на дату]![Sum-СУММА])
Обратите внимание на условие отбора в поле ОТКЛОНЕНИЕ - >0, то есть должны выводиться только записи, где оплаченная сумма меньше плановой.
В результате выполнения запроса на дату 17.03.11 получим:
Сохранить запрос под именем Отклонение.
Шаг 4. Создать запрос на основе запроса Отклонение для замены кодов предприятий и кодов налогов на их наименования.
В этом запросе используются наименования предприятий и налогов из таблиц-справочников ПРЕДПРИЯТИЯ и НАЛОГИ.
Бланк запроса имеет вид:
Выполнить запрос на дату 17.03.11
Результатом решения поставленной задачи будет следующая таблица:
Сохранить запрос под именем Отклонение итог.
Такой запрос позволяет найти в таблице записи, у которых нет записей в подчинённой таблице.
Пример 16. Получить сведения о предприятиях, которые не произвели оплату.
Удалить из таблицы ПЛАТЕЖИ две записи, которые относятся к предприятию с кодом 103 (Север). Таким образом, данное предприятие не производило выплат. Создать запрос, с помощью которого можно определить неплательщика.
Запрос строим на таблицах ПРЕДПРИЯТИЯ и ПЛАТЕЖИ, последовательно выполняя следующие действия:
Примечание: восстановить удалённые записи в таблице ПЛАТЕЖИ и СПЕЦИФИКАЦИЯ ПЛАТЕЖЕЙ.
Пример 17. Используя сведения о платежах, получить общую оплату по каждому предприятию нарастающим итогом от начала года.
Сначала дополним базу данных таблицей КАЛЕНДАРЬ, где № МЕСЯЦА - ключевое текстовое поле и НАИМЕНОВАНИЕ МЕС - текстовое поле.
|
№ МЕСЯЦА |
НАИМЕНОВАНИЕ МЕСЯЦА |
|
01 |
Январь |
|
02 |
Февраль |
|
03 |
Март |
|
04 |
Апрель |
Количество месяцев в календаре зависит от того периода, на который вычисляются нарастающие итоги.
Рассмотрим решение задачи по шагам.
Шаг 1. На основе таблиц ПЛАТЕЖИ и СПЕЦИФИКАЦИЯ ПЛАТЕЖЕЙ создадим запрос, в котором преобразуем дату в месяц (числовой) и вычислим суммы выплат каждого предприятия по месяцам.
В результате выполнения запроса получим:
Сохраним запрос под именем Суммы выплат по месяцам.
Шаг 2. На основе предыдущего запроса и таблицы календарь создадим запрос, в котором получим суммы нарастающим итогом.
Объекты не связывать!
Группировка выполняется по предприятиям, а затем по месяцам. В этих группах суммируются Sum-Cумма. Суммируются суммы оплаты при условии, что месяц оплаты <= (меньше или равен) месяцу календаря, то есть в пределах календаря.
При выполнении запроса получим:
Назвать запрос Нарастающие суммы.
Шаг 3. Если сумма в каждом следующем месяце не меняется, то получить итоги по последнему месяцу, в котором произошли изменения. Для этого использовать функцию Min для месяца. Запрос строим на основе предыдущего запроса.
Выполнить запрос:
Присвоить запросу имя Нарастающий итог.
Такой запрос создаёт новую таблицу на основе другой таблицы или запроса. Он необходим в том случае, когда надо сохранить, например, результаты запроса или когда невозможно построить запрос непосредственно на другом запросе.
Пример 18. Создать запрос на основе таблицы ПЛАН, подсчитать по каждому предприятию общую сумму налогов. Результаты запроса использовать для создания таблицы.
Последовательность действий:
Подтвердить создание новой таблицы.
Если требуется изменить данные, например, в таблице ПЛАН, то создаётся запрос на обновление.
Пример 19. Требуется увеличить все суммы в таблице ПЛАН на 10%, то есть обновить их.
Последовательность действий:
[ПЛАН] ! [СУММА] + [ПЛАН]![СУММА] *0,1
Бланк запроса примет вид:
Пример 20. Увеличить сумму заданного налога в таблице ПЛАН на заданный процент.
Последовательность действий:
IIF([ПЛАН] ! [КОД_НАЛ] = [Введите обновляемый код налога] ;
[ПЛАН] ! [СУММА] * (1+[Введите % изменения суммы] / 100) ;
[ПЛАН] ! [СУММА] )
Пример 21. Добавить записи в таблицу ПРЕДПРИЯТИЯ.
Последовательность действий:
Пример 22. Удалить запись с заданным кодом предприятия из таблицы ПРЕДПРИЯТИЯ.
Выполнить действия:
Бланк запроса примет вид:
Если при построении схемы данных был установлен параметр целостности Каскадное удаление связанных полей, то будут удалены все выбранные записи главной таблицы и связанные с ними записи подчинённой. В результате у таблиц ПЛАН, ПЛАТЕЖИ, СПЕЦИФИКАЦИЯ ПЛАТЕЖЕЙ удалены записи по 104 предприятию.
Перекрёстные запросы используются для представления данных в виде, удобном для анализа. В перекрёстном запросе данные группируются по вертикали и горизонтали.
Пример 23. Создать перекрёстный запрос на основе таблиц НАЛОГИ и СПЕЦИФИКАЦИЯ ПЛАТЕЖЕЙ, в котором определить по каждому платёжному документу виды налогов и оплаченные по ним суммы.
Для решения задачи выполнить последовательность действий:
Результат запроса:
В полученной таблице показаны документы с оплаченными налогами.
Самостоятельно создать запросы по базе данных налог:
Access позволяет создать макет отчета определенной структуры. В отчет могут быть включены данные одной таблицы, одного запроса или нескольких таблиц и запросов. Макет отчета определяет состав и расположение:
При конструировании макета отчета имеется возможность группировать данные по нескольким уровням. Для каждого уровня могут вычисляться итоги с поясняющим текстом.
Отчет может быть создан с помощью Мастера отчетов или в режиме Конструктора отчетов .
Проще создать отчет с помощью Мастера отчетов, а затем доработать его в режиме Конструктора отчетов.
Рассмотрим создание однотабличного отчета по результатам запроса Долг заданного предприятия.
Пример 24. На основе запроса Долг заданного предприятия создать отчет.
Самостоятельно создайте запросы, рассчитывающие долг заданного предприятия.
Для предприятия 102 результат будет выглядеть следующим образом:
Для создания отчета выполним следующие действия:
Для кода предприятия 102 отчёт примет вид:
Чтобы войти в конструктор отчётов следует нажать кнопку Режим на панели инструментов и выбрать Конструктор. Теперь отчет можно корректировать.
Конструктор отчётов имеет много общего с конструктором форм.
Окно конструктора отчетов содержит следующие разделы:
Пример 25. Выполнить корректировку заголовка и подписей полей отчета ОТЧЁТ 1, вынести переменную Наименование предприятия в заголовок отчета.
В режиме Конструктор отчета щелкнуть левой кнопкой мыши по заголовку ОТЧЕТ 1 (появится выделенная рамка), нажать клавишу Delete .Заголовок ОТЧЕТ 1 будет удален.
Далее удалим Макет для всех элементов в области данных, как мы это делали при работе с Конструктором форм.
Далее на панели элементов щелкнуть по кнопке Аа и перейти с крестообразным курсором в область заголовка отчета. С помощью этого курсора создать прямоугольную область для нового заголовка отчета и ввести АНАЛИЗ ОПЛАТЫ НАЛОГОВ ПО ПРЕДПРИЯТИЮ.
Чтобы откорректировать текст подписи поля отчета, нужно выделить эту подпись и еще раз щелкнуть по ней мышью. Появится мигающий курсор.
Для изменения расположения, цвета фона заголовка, цвета и размера шрифта, необходимо:
Далее нужно вынести поле Наименование предприятия, вырезав его из области данных и вставив в область заголовка отчета. Удалить подпись поля Наименование предприятия в области данных.
В результате выполненной корректировки отчет, для кода предприятия 102 (Восток), примет вид:
Пример 26. На основе запроса Отклонение итог создать отчет, отражающий недоплаты предприятий на заданную дату. Получить итоги по каждому предприятию и общий итог.
Порядок создания этого отчета подобен созданию отчета ОТЧЕТ 1. В окне Создание отчетов выбрать из запроса Отклонение итог поля Наименование предприятия, Наименование налога и Отклонение.
Для получения итогов проверить наличие уровня группировки по предприятиям, затем нажать кнопку два раза.
Для включения в отчет итогов нажать кнопку Итоги, появится диалоговое окно Итоги. Здесь помещаются все числовые поля, для каждого из которых в группе можно выбрать функции Sum, Avg, Min, Max.
В нашем примере единственное числовое поле, по которому надо получить итог, ОТКЛОНЕНИЕ. Установить флажок на вычисляемой функции Sum. Переключатель должен стоять на Показать данные и итоги. Нажать ОК, затем .
Выбрать макет ступенчатый, стиль Обычная. Назвать ОТЧЕТ2. Нажать . После выполнения всех действий по созданию отчета и введения даты 17.03.11 получим следующий отчет:
Пример 27. Выполнить корректировку отчета ОТЧЕТ 2: ввести новый заголовок отчета “Недоплаты предприятий на” и вывести в заголовке заданную дату.
Войти в режим Конструктор отчетов и изменить заголовок.
Для вывода даты в заголовок выполнить следующие действия:
в поле Свободный набрать: [Введите дату] текст должен точно совпадать с текстом в запросе Платежи на дату;
=Sum([ОТКЛОНЕНИЕ]),
выбрать Свойства и установить формат Денежный для этого поля.
В разделе Примечание отчёта вместо Итого поместить ВСЕГО и справа от выражения =Sum([ОТКЛОНЕНИЕ]) установить формат Денежный.
После корректировки для даты 17.03.11 получим отчет:
Вывод отчета на печать можно выполнить с помощью кнопки Печать на панели инструментов или команды Файл/Печать.
Пример 28. Получить по каждому предприятию сведения об оплате в каждом месяце. В отчёте получить по каждому предприятию общую сумму оплаты, а также нарастающие итоги с начала года.
Последовательность действий:
Задать запросу имя Суммы по месяцам.
На основе полученного запроса с помощью мастера отчётов создать отчёт с получением итоговых сумм по каждому предприятию. После редактирования (макет ступенчатый, ориентация книжная, стиль Обычная), задать имя отчёту Нарастающие итоги. Просмотреть отчёт:
Перейти в конструктор отчёта:
Отредактировать отчёт. Дать заголовок новой графе отчёта Нарастающие итоги. Изменить заголовки. Установить денежный формат для итоговой суммы. После редактирования отчёт примет вид:
Созданный отчёт можно просмотреть через форму. Параметр для отчёта может быть выбран в форме из списка.
Пример 29. Получить суммы выплаченные заданным предприятием по каждому виду налога.
Последовательность решения по шагам:
ШАГ 1. Создать запрос на выборку с параметром Код предприятия на основе таблиц ПРЕДПРИЯТИЯ и СПЕЦИФИКАЦИЯ ПЛАТЕЖЕЙ. Получить бланк запроса следующего вида:
Параметр запроса должен быть ключевым полем таблицы ПРЕДПРИЯТИЯ, то есть Код предприятия. Сохранить запрос под именем Запрос 3. Просмотреть запрос, например, по предприятию 101, получим:
ШАГ 2. Построить отчёт на основании полученного запроса Запрос 3 и сохранить его под именем ОТЧЁТ 3. Отредактировать отчёт. Если задать макет табличный, ориентацию книжную и стиль Обычная, то для 101 предприятия получим:
ШАГ 3. Создать форму Форма 3 для просмотра полученного отчёта:
Конструктор формы примет вид:
Выделить щелчком в конструкторе формы область Свободный открыть окно Свойства, открыть вкладку Другие.
Обратите внимание на имя Список 0. Оно задаётся для поля Свободный. Номер списка формирует Access – Список N, где N – число. Запомните его номер и закройте окно. Сохраните форму под именем Форма 3.
Шаг 4. Изменить бланк запроса 3:
В строке Условие отбора получилось выражение:
[Forms] ! [Форма 3] ! [Список 0]
Закрыть запрос и сохранить его под именем Запрос 3.
ШАГ 5. Для просмотра отчёта через форму открыть Форма 3, из предлагаемого списка предприятий выделить нужное предприятие и нажать кнопку Просмотр отчёта 3. Например, выберите ВОСТОК. Появится отчёт для заданного предприятия ВОСТОК:
Для управления в диалоговом режиме объектами базы данных Access (такиеми, как формы, отчеты, запросы и т.д.) можно использовать кнопочные формы (или меню).
Кнопочная форма (КФ) – это панель управления, которая обеспечивает переход к страницам кнопочной формы, а также выполнение отдельных команд. На панели кнопочной формы должны быть размещены кнопки для возвращения к главной странице кнопочной формы и страницам предыдущих уровней и выхода из приложения (базы данных). При создании КФ предусматривается кнопка для изменения самой КФ.
Создание КФ рассмотрим на примере базы данных НАЛОГ.
Разработаем структуру кнопочной формы, представив ее в виде таблицы.
|
Название страницы КФ |
Элементы |
Действие |
|
НАЛОГИ (главная страница) |
ДОБАВЛЕНИЕ ДАННЫХ |
Переход на страницу кнопочной формы Добавление данных в формы |
|
РЕДАКТИРОВА-НИЕ ДАННЫХ |
Переход на страницу кнопочной формы Редактирование данных |
|
|
ПРОСМОТР ОТЧЕТОВ |
Переход на страницу кнопочной формы Открытие отчетов |
|
|
ИЗМЕНЕНИЕ КНОПОЧНОЙ ФОРМЫ |
Выход в Диспетчер кнопочных форм |
|
|
ВЫХОД |
Выход из приложения Access |
|
|
Добавление данных в формы |
ФОРМА ПРЕДПРИЯТИЯ |
Открытие формы ПРЕДПРИЯТИЯ в режиме добавления |
|
ФОРМА ПЛАН |
Открытие формы ПЛАН в режиме добавления |
|
|
ФОРМА НАЛОГИ |
Открытие формы НАЛОГИ в режиме добавления |
|
|
ФОРМА ПЛАТЕЖИ |
Открытие формы ПЛАТЕЖИ в режиме добавления |
|
|
ВОЗВРАТ |
Переход на главную страницу кнопочной формы НАЛОГИ |
|
|
Редактиро-вание данных |
ФОРМА ПРЕДПРИЯТИЯ |
Открытие формы ПРЕДПРИЯТИЯ в режиме редактирования |
|
ФОРМА ПЛАН |
Открытие формы ПЛАН в режиме редактирования |
|
|
ФОРМА НАЛОГИ |
Открытие формы НАЛОГИ в режиме редактирования |
|
|
ФОРМА ПЛАТЕЖИ |
Открытие формы ПЛАТЕЖИ в режиме редактирования |
|
|
ВОЗВРАТ |
Переход на главную страницу кнопочной формы НАЛОГИ |
|
|
Просмотр отчетов |
ОТЧЕТ 1 |
Просмотр отчета ОТЧЕТ 1 |
|
ОТЧЕТ 3 |
Просмотр отчета ОТЧЕТ 3 |
|
|
ВОЗВРАТ |
Переход на главную страницу кнопочной формы НАЛОГИ |
Все страницы кнопочной формы представлены двумя уровнями.
1 уровень - главная страница кнопочной формы НАЛОГИ
2 уровень - страницы Добавление данных в формы, Редактирование данных, Просмотр отчетов, Запросы, Приложения.
Рассмотрим порядок создания кнопочной формы с помощью Диспетчера кнопочных форм (ДКФ):
;
В окне ДКФ всегда существует главная страница кнопочной формы – Главная кнопочная форма (По умолчанию).
С помощью кнопки Создать сформировать последовательно список страниц кнопочной формы:
Редактирование данных,
Добавление данных в формы,
Просмотр отчетов.
Затем аналогично Добавление данных в формы и Просмотр отчетов.
Окно ДКФ примет вид:
Для создания содержания главной страницы кнопочной формы необходимо в окне ДКФ выделить ее в списке и нажать кнопку Изменить.
В окне «Изменение страницы кнопочной формы» создать её элементы с помощью кнопки Создать.
Для создания элемента Добавление данных нажать кнопку Создать и в окне Изменение элемента кнопочной формы заполнить окно
Нажать ОК.
Аналогично создать элементы РЕДАКТИРОВАНИЕ ДАННЫХ (перейти к кнопочной форме Редактирование данных) и ПРОСМОТР ОТЧЕТОВ (перейти к кнопочной форме Просмотр отчётов).
При создании элемента Изменение кнопочной формы выбрать команду Конструктор приложения.
При создании элемента Выход выбрать команду Выйти из приложения.
В результате в окне «Изменение страницы кнопочной формы» созданы все элементы для главной страницы кнопочной формы.
Закрыть все окна, кроме ДКФ.
Для создания содержания страницы Редактирование данных (второй уровень) выделить её в окне ДКФ и нажать кнопку Изменить.
В окне «Изменение страницы кнопочной формы» создать элементы для данной кнопочной формы с помощью кнопки Создать.
Сформировать в окне Изменение элементов кнопочной формы элемент для формы ПЛАТЕЖИ:
Нажать ОК. В окне Изменение страницы кнопочной формы Редактирование данных появился новый элемент – ФОРМА ПЛАТЕЖИ.
Аналогично создать элементы ФОРМА ПРЕДПРИЯТИЯ, ФОРМА ПЛАН и ФОРМА НАЛОГИ.
Для создания элемента возврат нажать кнопку Создать и получить в окне Изменение элемента кнопочной формы:
Нажать ОК. В окне Изменение страницы кнопочной формы отображены все созданные элементы страницы кнопочной формы Редактирование форм:
С помощью кнопок Вверх и Вниз можно изменить порядок расположения элементов.
Закрыть окно Изменение страницы кнопочной формы.
Для создания содержания страницы кнопочной формы Добавление данных в формы (второй уровень) выделить её в окне ДКФ и создать все элементы, используя команду Открыть форму в режиме добавления.
Закрыть окно Изменение страницы кнопочной формы.
Для создания содержания страницы кнопочной формы Просмотр отчетов (второй уровень) выделить её в окне ДКФ и нажать кнопку Изменить
В окне «Изменение страницы кнопочной формы» создать элементы, используя команду Открыть отчет.
В окне «Изменение элементов кнопочной формы» получить:
Нажать ОК.
Для ОТЧЁТ 3 элемент создается по другому:
Нажать ОК.
В результате для этой страницы кнопочной формы должны быть созданы следующие элементы:
С помощью кнопки Закрыть выйти из Диспетчера кнопочных форм.
В окне БД налог перейти к объектам Формы. В списке добавилось название новой формы - Кнопочная форма (Switchboard).
В списке объектов Таблицы добавилась новая таблица –Switchboard Items, в которой отображена структура созданной кнопочной формы.
Для этой базы данных кнопочная форма может быть только одна.
Чтобы главная кнопочная форма запускалась при открытии базы данных, надо задать параметры запуска. Для их формирования нажать кнопку MS Office (слева вверху) и далее кликнуть по кнопке (рядом с кнопкой Выход).
В окне Параметры Access следует найти строку Форма просмотра и выбрать из списка соответствующую форму:
Эта команда реализуется при открытой базе данных и созданной кнопочной форме.
В режиме Конструктор форм можно редактировать кнопочную форму. Например, создать слева рисунок, изменить цвет фона, шрифт и расположение текста заголовка главной кнопочной формы.
Содержание
|
[1] [2] АНАЛИЗ ПРЕДМЕТНОЙ ОБЛАСТИ [2.1] Описание предметной области и функции решаемых задач [2.2] Первичные документы и их реквизиты [2.3] Ограничения предметной области [3] ПОСТАНОВКА ЗАДАЧИ [3.1] Организационно-экономическая сущность решаемых задач [3.2] Описание выходной информации [3.3] Описание входной информации [4] РАЗРАБОТКА ИНФОРМАЦИОННОГО ОБЕСПЕЧЕНИЯ [4.1] Анализ входной информации предметной области и выделение информационных объектов [4.2] Определение связей информационных объектов и построение информационно-логической модели (ИЛМ) [4.3] Определение логической структуры базы данных [4.4] Разработка физической структуры базы данных [4.4.1] ПРЕДПРИЯТИЯ [4.5] Контрольный пример [5] ОСНОВНЫЕ СВЕДЕНИЯ О СУБД ACCESS [5.1] Реляционные базы данных (РБД) [5.2] Объекты Access [5.3] Запуск Access [5.4] Основные элементы окна Access [6] СОЗДАНИЕ НОВОЙ БАЗЫ ДАННЫХ [6.1] Создание файла базы данных [6.2] Создание таблиц базы данных [6.2.1] Создание структуры таблиц в режиме «Конструктор» [6.3] Корректировка структуры таблицы [7] СХЕМА ДАННЫХ В ACCESS [7.1] Создание схемы данных [7.1.1] Обеспечение ссылочной целостности данных [7.2] Корректировка схемы данных [8] СОЗДАНИЕ ПОЛЬЗОВАТЕЛЬСКИХ ФОРМ
[8.1] [8.1.1] Однотабличная форма [8.1.2] Многотабличная форма [8.2] Конструктор форм [8.2.1] Редактирование главной формы [8.2.2] Редактирование подчиненной формы [8.2.3] Создание кнопок [8.3] Вычисление в форме [9] ВВОД И КОРРЕКТИРОВКА ДАННЫХ КОНТРОЛЬНОГО ПРИМЕРА [9.1] Ввод исходных данных в таблицы БД [9.2] Корректировка таблицы [10] СОЗДАНИЕ ЗАПРОСОВ [10.1] Запросы на выборку данных [10.1.1] Использование параметров в запросе [10.1.2] Вычисляемые поля [10.1.3] Вычисления с помощью формул [10.1.4] Применение функций Month, Format [10.2] Групповые операции в запросах [10.3] Группировка [10.4] Использование параметров объединения [10.5] Составление алгоритма решения задачи [10.6] Поиск записей, не имеющих подчиненных [10.7] Вычисления нарастающим итогом [10.8] Запрос на создание таблицы [10.9] Запрос на обновление записей таблицы [10.10] Запрос на добавление записей в таблице [10.11] Запрос на удаление записей в таблице [10.12] Перекрёстный запрос [10.13] Задания для создания запросов на базе данных НАЛОГ [11] СОЗДАНИЕ ОТЧЕТОВ [11.1] Однотабличный отчет [11.1.1] Корректировка отчета с помощью конструктора [11.2] Создание отчёта с получением нарастающего итога [11.3] Просмотр отчёта через форму [12] СОЗДАНИЕ КНОПОЧНОЙ ФОРМЫ [12.0.0.1] ВЫХОД [12.0.0.2] ФОРМА ПЛАН [12.0.0.3] ФОРМА НАЛОГИ [12.0.0.4] ФОРМА НАЛОГИ [12.1] Создание списка страниц кнопочной формы [12.2] Создание главной страницы кнопочной формы (первый уровень) [12.3] Создание кнопочной формы второго уровня [12.4] Запуск главной кнопочной формы при открытии базы данных [12.5] Редактирование кнопочной формы |