Методические рекомендации по теме "MS Access"
методическая разработка по информатике и икт (11 класс) на тему

РЕЛЯЦИОННАЯ БАЗА ДАННЫХ НА ПРИМЕРЕ «ACCESS 97».

I. Понятие базы данных. История развития компьютерных баз данных или базы данных разных поколений.

Что такое база данных? Любой из нас, начиная с раннего детства, многократно сталкивался  с «базами данных». Это – всевозможные справочники, энциклопедии и т.п., и самый популярный из всех – телефонный. Да, а наша записная книжка? Это – тоже «база данных», которая есть у каждого из нас. Большая или маленькая, простая или многофункциональная, она – обязательный элемент деловой жизни любого человека. Где бы мы хранили имена, телефоны, адреса, даты рождения и пр. своих многочисленных друзей, знакомых, деловых партнеров, непосредственных начальников, коллег и т.д., если бы не было записных книжек? В необходимых случаях мы обращаемся к ней, чтобы получить нужные сведения.

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

Другими словами, база данных – это организованная совокупность структурированных данных в какой-либо предметной области.

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

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

Сетевая модель подобна иерархической модели. Она представлена теми же компонентами: уровни, узлы, связи, но характер их взаимодействия другой. В этой модели между элементами различных уровней все связи являются свободными, т.е. каждый элемент вышестоящего уровня может быть связан одновременно с любыми элементами следующего уровня. Пример- мировая паутина (WWW).

Что же такое реляционная база данных? Прежде, чем ответить на этот вопрос, сделаем краткий исторический экскурс по наиболее ярким представителям реляционных баз данных разных поколений. Первые компьютерные базы данных появились в 80-х годах. Это были «неповоротливые» среды, с достаточно примитивным инструментом обработки данных, который создавал множество файлов, необходимых для полноценного функционирования базы данных в целом. Для создания более гибких структур в подобных БД, необходимо было использовать их внутренний язык. Интерфейс таких сред напоминал экран DOS. Все команды БД вводились в командной строке, точно так же, как и в DOS. Подобными особенностями обладали базы данных семейства dBase II, dBase III (Например, база данных «Карат»). Базы данных FoxPro и Paradox различных версий были представлены улучшенным (более удобным и многофункциональным) интерфейсом, а также имели усовершенствованные мастера для создания отчетов и среды для написания процедур.

Рассмотрим реляционную модель. Обычно, базы данных, состоящие из двумерных таблиц, принято называть реляционными. Тогда все базы данных семейства dBase II, dBase III, FoxPro и т.п. – реляционные, т.к. данные представляются в них в виде таблиц. Но будет более правильно, если понятие реляционных баз данных будет расширено перечислением их главных черт.

 Понятно, что с усовершенствованием и развитием БД нового поколения, сам термин «реляционная база данных» расширился, т.е. говоря о реляционных БД не достаточно упоминать только о представлении в ней данных в виде двумерных таблиц.

Характерные особенности реляционных баз данных:

• табличное представление данных;
• все СУРБД обрабатывают большие объемы информации, намного больше, чем те, с которыми справляются электронные таблицы;
• СУРБД может легко связывать таблицы так, что для пользователя они будут представляться одной таблицей (создание сложных информационных моделей);
• СУРБД минимизируют общий объем базы данных. Для этого таблицы, содержащие повторяющиеся данные, разбиваются на несколько связанных таблиц;
• СУРБД отличаются от традиционных СУБД тем, что в единственном файле базы данных находятся не только данные, но и различные объекты (например, файл базы данных Access). Хотя идеальный вариант в СУРБД - два файла базы данных. В одном находятся данные, в другом – объекты, модули. Такое разбиение позволяет сделать защиту базы данных более эффективной: защита информации (файл с таблицами) и защита объектов и программ (файл с объектами и модулями).

Итак, это пособие посвящено реляционным базам данных на примере Access 97. В качестве исходного материала для создания базы данных будет рассмотрена финансовая деятельность небольшой фирмы, занимающейся продажей комплектующих к компьютерам. Пусть эта фирма называется «IBM-КОМПЛЕКТ».

II. Подготовка к работе. Запуск. Окно Access 97. Окно базы данных. Объекты БД.

Работа с любой компьютерной программой начинается с ее запуска. Запуск Access 97 ничем не отличается от запуска любой другой программы Windows 95 (98). Прежде всего, необходимо удостовериться: установлена ли эта программа на диске или нет? Во-первых, попробуйте найти значок Access 97 на рабочем столе Windows 95 (98). Во-вторых, зайдите в меню Пуск, команда Программы, проверьте наличие строки Microsoft Access. В-третьих, если установлена панель инструментов Office, то удостоверьтесь, что есть кнопка запуска Access 97. Запуск программы - двойной щелчок на ее ярлычке, либо щелчок на строке Microsoft Access.

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

Перейдем непосредственно к описанию окна Access 97 и окна БД. Большинство элементов окна Access 97 – стандартные. Все офисные приложения Windows 95 (98) имеют аналогичный набор инструментов, за небольшим исключением. Например, кнопки режима Конструктор и т.п.

Программа имеет три основных режима работы:

• Режим запуска. В этом режиме можно выполнять некоторые операции, не открывая базы данных. Например, осуществлять настройку панелей инструментов, использовать служебные программы (создание MDE-файлов, преобразование БД, сжатие БД, восстановление БД), установка общих защитных опций и т.д.
• Режим выполнения. В этом режиме отображаются окна объектов БД. Он имеет различные названия. Это зависит от того, с каким объектом работает пользователь. Например, при работе с таблицей этот режим называется режимом таблицы, при работе с формой – режимом формы и т.п.
• Режим конструктора. В этом режиме создаются и изменяются объекты БД.

Итак, после запуска программы на экране появляется диалоговое окно Microsoft Access (см. рисунок 1).

Рисунок 1.

Программа предлагает вам либо открыть существующую БД, либо создать новую. Возможны два способа создания новой БД: вручную или с использованием Мастера (для разминки можно использовать готовые шаблоны и создать 1-2 базы). Минуем этап описания работы Мастера, т.к. главная трудность при изучении Access 97 заключается, именно, в самостоятельном конструировании БД, а не в использовании готовых шаблонов для построения простейших БД. Базы данных, созданные вручную, позволяют учитывать особенности финансовой деятельности любой фирмы. Выберем опцию Новая база данных. Назовем ее «Компьютеры».

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

Объекты БД.

Таблица. В СУБД вся информация хранится в таблицах. Это базовый объект БД, все остальные объекты создаются на основе существующих таблиц (производные объекты). Каждая строка в таблице – запись БД, а столбец – поле. Запись содержит набор данных об одном объекте, а поле – однородные данные обо всех объектах.

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

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

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

Макросы. Эти объекты служат для автоматизации работы с БД. С их помощью можно управлять событиями: открывать и закрывать объекты БД, изменять их размеры, располагать окна с объектами в нужном порядке, вызывать приложения и управлять ими и т.п. Можно связывать макросы с командами меню, с кнопками, с условиями. Макрос также является производным объектом БД.

Модули. Они также служат для автоматизации работы с БД. Модули еще называют процедурами обработки событий. Они необходимы для выполнения операций, которые невозможно реализовать с помощью команд или макрокоманд БД. Эти модули пишутся на языке VBA. Язык VBA – язык программирования в приложениях, таких как Word, Excel, Access. Хочется сразу отметить, что Access VBA отличается от своих собратьев. Поэтому делать 100% ставку на знание, в частности, Excel VBA – не следует.

III. Проектирование БД. Тип поля. Параметры поля. Поле первичного ключа. Создание таблиц – основных объектов БД. Редактирование проекта таблиц.

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

Разработчик – это человек (опытный пользователь или программист), которой самостоятельно создает новую БД.

В нашей главе мы рассмотрим, как можно создать подобное финансовое приложение, используя такую СУРБД как Access 97.С чего же необходимо начать? Прежде, чем приступить к созданию БД, необходимо продумать ее проект.

Проект – это абстрактная (теоретическая)  модель будущей БД, состоящая из объектов и их связей, необходимых для выполнения поставленных задач.

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

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

Типом поля называется тип данных хранящихся в поле.

Access 97 поддерживает следующие типы полей (см. табл.1).

Таблица 1.

Параметры поля – это ряд свойств, которыми обладает тот или иной тип поля.

Каждый тип поля имеет свой набор параметров.

 Есть группа свойств, которая повторяется в каждом типе поля. Ниже приводятся названия таких свойств и их описание (см. табл. 2).

Таблица 2.

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

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

• Приступите к созданию БД «Компьютеры».
• Откройте Access и выберите в диалоговом окне «Microsoft Access» опцию «Новая база данных».
• Дайте ей имя «Компьютеры».

Итак, вам нужно создать пять таблиц: «Приход», «Расход», «Клиенты», «Поставщики» и «Товары».

Задание.

 Разработайте макеты (структуру) таблиц.

• Начинайте с таблицы «Приход».

• Перейдите на вкладку Таблицы/Создать/Новая таблица.
• Выберите опцию Конструктор и нажмите кнопку ОК (см. рисунок 2).

Рисунок 2.

 Перед вами появится окно Конструктора таблицы.

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

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

Рисунок 3.

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

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

Номер: Размер поля – Длинное целое; Новые значения – Последовательные; Индексированное поле – Да (Совпадения не допускаются).

ДатаПриема: Формат поля – Длинный формат даты; Маска ввода – 99.99.00;0;#; Значение по умолчанию - =Date();Обязательное поле – Да; Индексированное поле – Да (Допускаются совпадения).

КодПоставщика: Размер поля – Длинное целое; Формат поля – Основной; Число десятичных знаков – 0; Подпись – Поставщик; Значение по умолчанию – 0; Обязательное поле – Да; Индексированное поле – Да (Допускаются совпадения).

КодТовара: Размер поля – Целое; Формат поля – Основной; Число десятичных знаков – 0; Подпись – Товар; Значение по умолчанию – 0; Обязательное поле – Да; Индексированное поле – Да (Допускаются совпадения).

Количество: Размер поля – Длинное целое; Формат поля – Основной; Число десятичных знаков – 0; Значение по умолчанию – 0; Обязательное поле – Да; Индексированное поле – Нет.

СуммаУпл: Размер поля – С плавающей точкой (4 байта); Формат поля – Фиксированный; Число десятичных знаков – 2; Значение по умолчанию – 0; Обязательное поле – Да; Индексированное поле – Нет.

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

• Теперь задайте ключевое поле. В нашем случае – это поле Номер. Это поле однозначно определяет каждую запись таблицы (приход товара 1, приход товара 2 и т.д.).
• В режиме Конструктора таблиц поставьте маркер на это поле и нажмите кнопку с изображением ключа в панели инструментов.

•  Аналогично создайте макет таблицы «Поставщики» (см. рисунок 4).

Рисунок 4.

• Опишите свойства полей таблицы «Поставщики».

КодПоставщика: Размер поля – Длинное целое; Новые значения – Последовательные; Индексированное поле – Да (Совпадения не допускаются).

Поставщик: Размер поля – 50; Обязательное поле – Да; Пустые строки – Нет; Индексированное поле – Да (Совпадения не допускаются).

Российский: Формат поля – Да/Нет; Значение по умолчанию – Да; Обязательное поле – Нет; Индексированное поле – Нет.

• Задайте ключевое поле таблицы «Поставщики». Это – поле КодПоставщика. Оно также однозначно определяет записи таблицы.

• Создайте макет таблицы «Товары» (рисунок.5).

Рисунок 5.

• Опишите свойства полей таблицы «Товары».

КодТовара: Размер поля – Целое; Формат поля – Основной; Число десятичных знаков – 0; Значение по умолчанию – 0; Обязательное поле – Да; Индексированное поле – Да (Совпадения не допускаются).

ВидТовара: Размер поля – 60; Обязательное поле – Да; Пустые строки – Нет; Индексированное поле – Да (Совпадения не допускаются).

Ясно, что прежде, чем начать ввод данных в таблицы необходимо еще раз просмотреть структуры таблиц. Все ли правильно оформлено? Изменение структуры таблиц БД (редактирование проекта таблиц) осуществляется достаточно просто в окне Конструктора таблиц: маркируем нужное поле и удаляем его (не торопитесь это делать!!!), или же вставляем перед ним пустую строку, если нам необходимо добавить еще одно поле в таблицу, также можно изменять имена полей, их тип, описание. Просмотрите внимательно проекты своих таблиц («Приход» и «Поставщики») – нет ли ошибок? Если обнаружили какие-то неточности, исправьте их. Все эти действия осуществляются с помощью клавиш Delete, Insert, а также для выделения поля или его фрагмента можно использовать мышь или сочетание клавиш (Shift + клавиши со стрелками).

Редактирование структуры таблицы включает еще один важный момент – создание подстановок. Давайте рассмотрим это.

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

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

1. В строке тип элемента управления выберите опцию «поле со списком».
2. В строке тип источника строк выберите опцию «таблица/запрос»;
3. В строке источник строк нажмите кнопку с тремя точками. Появится окно-диалог Добавление таблицы.
4. Выберите таблицу «Поставщики», нажмите кнопку Добавить и закройте это окно (см. рисунок 6).

Рисунок 6.

5. Перед вами окно конструктора запросов – Инструкция SQL: построитель запросов. Оно разделено на две части: верхняя часть содержит макет таблицы «Поставщики», нижняя – поле для конструирования запроса.
6. Выберите поля КодПоставщика и Поставщик из макета таблицы «Поставщики». Это можно сделать двумя способами: первый - двойной щелчок мыши на имени поля; второй, нажав и удерживая левую кнопку мыши, перетащить эти поля в первую и вторую графы построителя запросов (см. рисунок 7).

Рисунок 7.

7. Закройте это окно, нажав «крестик» в правом верхнем углу. Access запросит сохранение SQL-инструкции, нажмите «Да».

• Продолжите заполнение свойств вкладки «Подстановка».

8. В строке присоединенный столбец установите значение 1.
9. В строке число столбцов – 2.
10. В строке ширина столбцов – 0см;10см.
11. В строке ограничиться списком – Да.

Если вы проделали все шаги, описанные выше, то окно Конструктора таблиц будет выглядеть как на рисунке 8.

Рисунок 8.

• Аналогично создайте подстановку для поля КодТовара.

• В окне Конструктора запросов выберите поля КодТовара и ВидТовара из макета таблицы «Товары» и поместите их в первую и вторую графы построителя запросов.
• Самостоятельно установите свойства вкладки «Подстановка» для поля КодТовара (см. п.1 – 11 для поля КодПоставщика).
• Сохраните макет таблицы «Приход», щелкнув по кнопке  в панели инструментов, и закройте окно с таблицей (кнопка с крестиком в правом верхнем углу).

Такой вид подстановки называется зависимым, т.к. данные берутся из другой таблицы и количество строк подстановки равно количеству строк поля таблицы-источника. Такая подстановка позволяет минимизировать информационный объем таблиц, т.е. наши таблицы не будут содержать повторяющиеся данные (в нашем случае – это поля Поставщик и ВидТовара).

Механизм такой подстановки начнет работать после заполнения данными таблиц «Поставщики» и «Товары».

Практическое задание №1.

1. Создайте самостоятельно структуры таблиц «Расход» и «Клиенты».

Ниже перечислены имена полей таблиц «Расход» и «Клиенты». Тип и свойства полей этих таблиц полностью соответствуют типу и свойствам полей таблиц «Приход» и «Поставщики».

«Расход»:

Номер, ДатаОтпуска, КодКлиента, КодТовара, Количество, ОтпускнаяЦена. Тип и свойства поля ОтпускнаяЦена полностью соответствуют типу и свойствам поля СуммаУпл (см. рисунок 9).

Рисунок 9.

«Клиенты»:

КодКлиента, Клиент, ЮридическоеЛицо. Тип и свойства поля ЮридическоеЛицо полностью соответствуют типу и свойствам поля Российский (см. рисунок 10).

Рисунок 10.

2. Самостоятельно создайте зависимую подстановку для полей КодКлиента и КодТовара таблицы «Расход» (см. выше пример с таблицей «Приход»). Для создания SQL-инструкций используйте макеты таблиц «Клиенты» (для поля КодКлиента) и «Товары» (для поля КодТовара).

Не забудьте сохранить таблицы «Расход» и «Клиенты»!!! После сохранения закройте окна таблиц.

Рассмотрим второй вид подстановки. В таблице «Товары» есть два поля, для которых будет создана эта подстановка. Это поля КодТовара и ВидТовара.

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

• Откройте таблицу «Товары» в режиме Конструктора.
• Поставьте маркер на поле КодТовара.
• В столбце Тип данных откройте список типов полей и выберите Мастера подстановок…. Появится окно-диалог Создание подстановки.
• Выберите в нем опцию «будет введен фиксированный набор значений» и нажмите кнопку Далее>. Появится следующее окно-диалог Мастера.
• Здесь необходимо ввести нужные значения в каждую ячейку столбца-подстановки и нажать кнопку Готово (см. рисунок 11.)

Рисунок 11.

• Введите следующие значения: 101, 201, 301, 401, 501, 601, 701, 801, 901 и нажмите кнопку Готово.

Это коды девяти групп товаров. Для простоты мы взяли по одному виду в каждой группе товара. Если количество видов в группах возрастет, то эти группы можно увеличивать (например, 102, 103, 104, …; 202, 203, 204, … и т.д.).

• Аналогично сделайте для поля ВидТовара.
• Введите в ячейки столбца-подстановки следующие значения (см. таблицу 3.):

Таблица 3.

Не забудьте в конце каждой строки подстановки поставить код товара (см. таблицу 3.)!!!

• Кнопкой Готово завершите работу в окне.
• Сохраните макет таблицы «Товары» и закройте окно таблицы.

IV. Создание форм для ввода информации в таблицы. Ввод, редактирование и просмотр записей БД. Перемещение по записям БД.

Изучение этого параграфа вы начнете нестандартно – с форм. Обычно вторым этапом построения БД являются  запросы. Подобное отступление от «классического» изложения материала по Access объясняется ниже.

Итак, теперь наша база данных содержит пять таблиц: «Приход», «Расход», «Клиенты», «Поставщики» и «Товары». Эти таблицы необходимо заполнить данными. Вносить их можно непосредственно в поля таблиц или использовать формы для ввода информации в исходные таблицы. Последний вариант предпочтительнее. Обычно исходные таблицы содержат много полей, и все они не умещаются на экране монитора, поэтому ввод, редактирование и просмотр данных - неудобен. Специально созданные для этого формы позволяют отслеживать каждую запись от начала и до конца. Помимо этого, формы внешне привлекательнее таблиц, поэтому именно они (формы для ввода, меню, заставки и т.п.) формируют интерфейс разрабатываемого приложения. Оригинальность оформления БД зависит от ваших дизайнерских способностей.

Итак, приступим к созданию форм для ввода информации в наши таблицы. Как обычно начнем с таблиц «Приход», «Поставщики» и «Товары». Для быстрого создания подобных форм лучше использовать Мастер.

• Выберите вкладку Формы/Создать. Появится окно-диалог Новая форма.
• Задайте опцию Мастер форм и в качестве источника выберите таблицу «Приход» (см. рисунок 12.), а затем нажмите кнопку ОК.

Рисунок 12.

Появится окно-диалог Мастера - Создание форм.

• Щелкните по кнопке с двумя стрелками, смотрящими вправо, и выберите все поля таблицы «Приход».
• Затем нажмите кнопку Далее> (см. рисунок 13).

Рисунок 13.

После этого появится второе окно Мастера.

• Здесь выберите опцию «в один столбец» и нажмите кнопку Далее>.
• В третьем окне выберите стиль формы – камень.
• Щелкнув по кнопке Далее>, перейдите к последнему окну Мастера, и там задайте имя формы – «Приход».
• В завершение щелкните по кнопке Готово.

Итак, создана первая форма. Посмотрите на нее внимательно (см. рисунок 14).

Рисунок 14.

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

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

В нашем случае мы будем иметь дело с полями и надписями. Их можно редактировать и изменять. В полях непосредственно осуществляется ввод данных, а надписи являются элементом оформления формы, также надписи информируют пользователя о поле, в которое вводится информация (см. рисунок 14.: слева изображены надписи, справа – поля).

• Откройте форму «Приход» в режиме Конструктора (кнопка в окне БД – Конструктор или же кнопка  в панели инструментов).
• Удерживая клавишу Shift, мышкой выделите все поля, но не надписи, затем нажмите кнопку Ж на панели инструментов (см. рисунок 15).

Рисунок 15.

• Снимите выделение с полей, щелкнув левой кнопкой мыши в любом месте формы.

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

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

Теперь необходимо увеличить длину полей КодПоставщика и КодТовара.

• Зацепите мышкой их правую сторону, и растяните до нужной длины. Так как эти поля имеют подстановку, то их длину можно регулировать по строкам подстановки.

 После редактирования форма выглядит следующим образом (см. рисунок 16).

Рисунок 16.

• Самостоятельно по аналогии создайте формы «Поставщики» и «Товары».
• Отредактируйте формы: увеличьте длину полей Поставщик и ВидТовара, выделив все поля, выберите жирный шрифт (см. рисунок 17, 18).

Рисунок 17.

Рисунок 18.

Теперь можно приступить к заполнению таблиц «Поставщики», «Товары» и «Приход».

• Откройте форму «Поставщики» и внесите первую запись.
• Ввод начните с поля Поставщик, т.к. поле КодПоставщика заполняется автоматически.

В нижней части формы расположены навигационные кнопки. С их помощью можно перемещаться по записям: на следующую, на предыдущую, на первую, на последнюю, на новую (см. рисунок 17.).

• Внесите следующие три записи:

КодПоставщика                1

Поставщик                ОАО «Интера-2000» (1)

Российский                Да

КодПоставщика                2

Поставщик                ООО «Компьютер-Сити» (2)

Российский                Да

КодПоставщика                3

Поставщик                IBMLinks (3)

Российский                Нет

На рисунке 19 показана первая введенная запись (см. рисунок 19.).

Рисунок 19.

• Теперь заполните форму «Товары».
• Откройте ее.
• Ввод данных осуществляйте, используя подстановку.
• Введите все девять видов товаров, начиная с товара с кодом 101 и заканчивая товаром с кодом 901. Для поля КодТовара выберите из списка значение 101, а для поля ВидТовара – МатПл.Р.233-450 Tomato IX – 101 (см. рисунок 20.).

Рисунок 20.

• Перейдите на вторую запись и заполните поля формы для товара с кодом 201 и т.д. Всего должно получиться девять записей.

• Теперь заполните форму «Приход».
• Откройте ее.

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

• Используйте этот рисунок для заполнения формы «Приход» (см. рисунок 21).

Рисунок 21.

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

Практическое задание №2.

Создайте формы «Клиенты» и «Расход». Отредактируйте макеты этих форм (цвет шрифта, длина поля КодКлиента, КодТовара).

• Начните с создания формы «Клиенты», т.к. поля КодКлиента и Клиент таблицы «Клиенты» являются полями подстановки для поля КодКлиента таблицы «Расход».

Ниже приведена таблица, содержащая записи, которые необходимо ввести в форму «Клиенты» (см. рисунок 22).

Рисунок 22.

• Данные для формы «Расход» находятся ниже в таблице. Она условно разделена на левую и правую части (см. рисунок 23).

Левая часть таблицы.

Правая часть таблицы.

Рисунок 23.

Итак, вы создали пять форм, и заполнили данными пять таблиц. Формы не хранят информацию, они служат только для ее ввода. Вся информация передается в таблицы и там хранится. Убедитесь в этом. Выберите вкладку «Таблицы». Последовательно откройте таблицы «Приход», «Расход», «Поставщики», «Клиенты» и «Товары». Вся информация на месте.

V. Связывание таблиц. Виды связи.

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

1. удаление записей одной из таблиц, на которые ссылаются записи из другой таблицы;
2. осуществление изменений в одной таблице, которые приведут к появлению «осиротевших» записей в другой таблице;
3. добавление в одну таблицу записей, для которых отсутствует соответствующая запись в другой таблице.

Связывание таблиц – это установка отношения между полем первичного ключа (ключевым полем) одной таблицы  и полем внешнего ключа другой.

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

 Существуют четыре типа связи (отношения):

1. Отношение один-к-одному. При таком отношении каждой записи одной таблицы соответствует не более одной записи второй таблицы, и наоборот. Это отношение используют для разделения больших таблиц, для защиты части данных. Такая связь применяется не часто, т.к. данные могут быть помещены в одну таблицу.
2. Отношение многие-к-одному. Каждой записи исходной таблицы соответствует любое количество записей новой таблицы, но не наоборот.
3. Отношение один-ко-многим. Каждой записи таблицы может соответствовать несколько записей связанной таблицы. Это отношение точно такое же, как и отношение многие-к-одному, т.е. отношение многие-к-одному – это отношение один-ко-многим наоборот.
4. Отношение многие-ко-многим. Каждой записи одной из таблиц может соответствовать любое количество записей другой таблицы, и наоборот. Одной записи из таблицы А могут соответствовать несколько записей в таблице В, а одной записи в таблице В – несколько записей таблицы А. Такая схема реализуется только с помощью третьей (связующей) таблицы С, ключ которой должен состоять хотя бы из двух полей (можно больше), которые являются полями внешнего ключа в таблицах А и В.

А                В

С

При работе с нашей БД вы будете использовать отношение один-ко-многим.

Итак, приступите к созданию связей между таблицами «Поставщики», «Товары» и «Приход», а также между таблицами «Клиенты», «Товары» и «Расход».

• Выберите из основного меню команду Сервис/Схема данных. Появится окно-диалог Добавить таблицу (см. рисунок 6.).
• Выделите все таблицы в этом окне (клавиша Shift + клавиши со стрелками), а затем нажмите кнопку Добавить. Таким образом, все таблицы будут добавлены в специальное окно - Схема данных. В этом окне обычно устанавливаются и изменяются отношения между таблицами.
• Выделите в макете таблицы «Поставщики» ключевое поле - КодПоставщика (оно выделено жирным шрифтом).
• Перетащите его мышкой к полю внешнего ключа таблицы «Приход» - КодПоставщика (это поле написано обычным шрифтом). Появится окно-диалог Связи (см. рисунок 24.).

Рисунок 24.

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

Таким образом, было создано отношение между таблицами «Поставщики» и «Приход» типа один-ко-многим.

Практическое задание №3.

Установите связь между таблицами «Товары» и «Приход», «Клиенты» и «Расход», «Товары» и «Расход».

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

VI. Проектирование запросов. Запросы на выборку данных, запросы с вычисляемыми полями, запросы на создание таблиц, запросы с параметрами.

Как уже говорилось в предыдущих параграфах то, что запросы предназначены для отбора данных, удовлетворяющих заданным критериям. Критерием или условием отбора называются условные или логические выражения. Условия отбора бывают простыми и сложными. Простые критерии (условные) содержат операции отношения (см. таблицу 3.). Логическое выражение (сложное условие отбора) может состоять из простых, соединенных знаками логических операций (см. таблицу 4.).

Таблица 3.

Таблица 4.

1. Создание запросов на группировку данных.

Задание.

Создайте два запроса на группировку данных: «Итого приход» и «Итого расход». Начинайте с запроса «Итого приход».

• Перейдите на вкладку Запросы/Создать. Появится Окно-диалог Новый запрос.
• Выберите опцию Конструктор и нажмите кнопку ОК. Появится уже знакомое вам окно-диалог Добавление таблицы (см. рисунок 6.).
• Выберите таблицу «Приход» и закройте окно Добавление таблицы.

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

• Перетащите в первые два столбца бланка запроса два поля  из макета таблицы «Приход» – КодТовара и Количество. Используйте при этом мышь.

• Включите строку Групповая операция, используя кнопку  в панели инструментов (см. рисунок 25.).

Рисунок 25.

• В строке Групповая операция, в поле Количество из раскрывающегося списка выберите статистическую функцию – Sum.
• Через двоеточие задайте псевдоним полю Количество – Кол-воПР (см. рисунок 25.).

• Сохраните запрос под именем – «Итого приход».

• Аналогичным образом самостоятельно создайте запрос «Итого расход» на основе таблицы «Расход». Псевдоним поля Количество в запросе «Итого расход» – Кол-воРАС (см. рисунок 26.).

Рисунок 26.

• Сохраните созданный запрос под именем «Итого расход».

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

2. Создание запросов на выборку данных с вычисляемыми полями.

Задание.

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

• Перейдите на вкладку Запросы/Создать. Появится окно-диалог Новый запрос.
• Выберите опцию Конструктор и нажмите кнопку ОК. Появится уже знакомое вам окно-диалог Добавление таблицы (см. рисунок 6.)
• Выберите запросы «Итого приход», «Итого расход» и таблицу «Товары» и добавьте их в окно Конструктора запросов.

• Установите связь между полями запросов и таблицы КодТовара (выделив поле КодТовара запроса «Итого приход», протащите его мышкой к полю КодТовара запроса «Итого расход» и таблицы «Товары», и аналогично, выделив поле КодТовара запроса «Итого расход» протащите его мышкой к полю КодТовара таблицы «Товары»).

• Перетащите, используя мышь, поле ВидТовара из макета таблицы «Товары», поле Кол-воПР из макета запроса «Итого приход». А из макета запроса «Итого расход» - поле Кол-воРАС.

• Создайте вычисляемое поле в следующей свободной графе бланка Конструктора запроса. Назовите его Остаток (см. рисунок 27.).

Рисунок 27.

• Поставьте двоеточие после слова Остаток, затем нажмите кнопку  на панели инструментов. Появится окно-диалог Построитель выражений (см. рисунок 28.).

Рисунок 28.

Нижняя область этого окна разделена на три части. В левой части изображено дерево объектов БД – таблицы, запросы, формы и т.д.

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

• Щелкните по запросу «Итого приход». В средней графе диалога Построитель выражений появится список всех полей запроса «Итого приход».

• Выделите поле Кол-воПР и щелкните по нему дважды, тем самым занесите его в область для построения выражений (верхняя часть окна Построитель выражений).

• Затем щелкните по кнопке со знаком «минус».

• Теперь выделите запрос «Итого расход».

• Аналогично, выберите поле Кол-воРАС и щелкните по нему дважды.

• В завершение нажмите кнопку ОК.

• Сохраните запрос под именем «Остатки» (см. рисунок 29.)

Рисунок 29.

• Откройте вновь созданный запрос «Остатки» и посмотрите результаты его выполнения.

• Добавьте несколько записей в таблицы «Приход» и «Расход», используя формы для ввода.
• Откройте форму «Приход» и введите еще одну запись: 08.12.99; IBMLinks (3); Процессоры. Celeron 333A – 201; 12; 18650.
• Откройте форму «Расход» и введите еще одну запись: 09.12.99; Звягин А.А. (8); НГМД. 1.44 Mb Sony – 501$; 3; 364,5.
• Затем еще раз просмотрите результат выполнения запроса «Остатки».

Практическое задание №4.

1. Создайте запрос на выборку «Складская Цена» с вычисляемым полем СкладскаяЦена. Расчет складской цены осуществляется по формуле: СуммаУпл / Количество (см. рисунок 30.), используя таблицу «Приход».

Рисунок 30.

• Откройте вновь созданный запрос в режиме Конструктора. Значения поля СкладскаяЦена необходимо отформатировать.
• Поставьте курсор на это поле и нажмите кнопку  на панели инструментов. Появится диалог Свойства поля.

• Задайте свойству Формат поля значение Фиксированный, а свойству Число десятичных знаков – 2 (см. рисунок 31.).

Рисунок 31.

2. Создайте запрос «Средняя цена» на группировку данных с использованием статистической функции Avg ( Среднее значение) на основе запроса «Складская Цена». Этот запрос с помощью функции Avg вычислит средние цены на складе. Они определяются как среднее арифметическое складских цен каждого вида товара.

• Задайте псевдоним поля СкладскаяЦена запроса «Складская Цена»– СредняяЦена (см. рисунок 32.).

• Отформатируйте поле СредняяЦена запроса «Средняя Цена» (см. выше пункты задания 1.).

Рисунок 32.

• Сохраните созданный запрос под именем «Средняя Цена».
• Откройте вновь созданный запрос и просмотрите результат его выполнения.

3. Создание запроса с параметрами и запроса на создание таблицы.

Задание.

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

• Перейдите на вкладку Запросы/Создать.
• Добавьте в окно Конструктора запросов таблицы «Расход», «Клиенты», «Товары» и запрос «Средняя Цена».
• Установите связь между полями КодТовара таблицы «Расход» и запроса «Средняя Цена». Связи между полями других таблиц устанавливаются автоматически, т.к. вы позаботились об этом заранее (см. параграф «Связывание таблиц. ….».).
• Перенесите в графы Конструктора запроса следующие поля: ДатаОтпуска (т. «Расход»), Клиент (т. «Клиенты»), ВидТовара (т. «Товары»), Количество (т. «Расход»), ОтпускЦена (т. «Расход»), а из запроса «Средняя Цена» – поле СредняяЦена (см. рисунок 33.)

Рисунок 33.

• Создайте вычисляемые поля Выручка и ЗакупСтоимость в столбцах следующих за столбцом с полем СредняяЦена (см. рисунок 34.).

Рисунок 34.

Эти экономические величины рассчитываются по следующим формулам: Выручка = ОтпускнаяЦена * Количество проданного товара, ЗакупСтоимость = СкладскаяЦена * Количество проданного товара (см. рисунки 34, 35).

Рисунок 34.

Рисунок 35.

• Отформатируйте поля Выручка и ЗакупСтоимость точно так же, как вы форматировали поля СкладскаяЦена, СредняяЦена (см. предыдущий параграф).

• Теперь задайте тип запроса - нажмите кнопку  в панели инструментов.

• Выберите из списка тип запроса - Создание таблицы. Появится окно-диалог Создание таблицы.
• В поле ввода «имя таблицы:» задайте имя создаваемой таблицы – «Аналитика» (см. рисунок 36.).

Рисунок 36.

• После этого нажмите кнопку ОК.

• Сохраните запрос под именем «Расход Аналитика» и закройте его.

• Откройте созданный запрос и подтвердите создание таблицы.

• Перейдите на вкладку Таблицы и убедитесь в том, что таблица «Аналитика» существует.
• Откройте эту таблицу и просмотрите ее записи.

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

• Откройте запрос «Расход Аналитика» в режиме Конструктора.
• В строке «Условие отбора:», в столбце с полем ДатаОтпуска введите следующее выражение:  Between [Введите начальную дату] And [Введите конечную дату] (см. рисунок 37).

Рисунок 37.

• Сохраните запрос «Расход Аналитика».
• Выполните созданный запрос. После запуска появится первое окно для ввода параметра.
• Введите в него начальную дату временного диапазона – 06.12.99 (см. рисунок 38).

Рисунок 38.

• Затем появится второе окно для ввода параметра. Введите конечную дату – 07.12.99.

Итак, в результате выполнения запроса «Расход Аналитика» создается таблица, в которой содержатся данные из трех таблиц и одного запроса. Был создан информационный объект на основе нескольких связанных таблиц. Были рассчитаны выручка и закупочная стоимость за 6 – 7 декабря 1999г.

• Откройте вкладку Таблицы и просмотрите таблицу «Аналитика».

Форматировать поля этой таблицы не надо, т.к. она является промежуточным объектом, и на ее основе будет создаваться запрос, рассчитывающий доход фирмы. А уже этот запрос и запрос «Остатки» будут служить основой для создания отчетов. Форматировать числовые поля лучше в конечных объектах.

Практическое задание №5.

Создайте запрос «Доход» на основе таблицы «Аналитика» с вычисляемым полем Доход.

• Добавьте в бланк запроса три поля из макета таблицы «Аналитика» (см. рисунок 39.).

Рисунок 39.

Доход вычисляется по следующей формуле: Доход = Выручка – ЗакупСтоимость (см. рисунок 40.). В этом запросе отформатируйте поля Выручка, ЗакупСтоимость, Доход (см. предыдущие параграфы).

Рисунок 40.

• Сохраните запрос под именем «Доход».

• Запустите на исполнение.
• Посмотрите результаты выполнения запроса.

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

Задание.

Создайте запрос «Удаление Поставщика».

• Добавьте нового поставщика в таблицу «Поставщики» и несколько записей в таблицу «Приход» с этим поставщиком.
• Создайте новый запрос на основе таблицы «Поставщики».
• Добавьте одно поле в бланк запроса – КодПоставщика.
• В строке Условие отбора задайте параметр – [Введите код].

Рисунок 41.

• Затем задайте тип запроса – удаление (кнопка  в панели инструментов).
• Сохраните запрос под именем «Удаление поставщика».

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

5. Создание запроса на обновление записей.

Задание.

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

• Добавьте в бланк создаваемого запроса одно поле – ОтпускнаяЦена.
• Выберите тип запроса – обновление (кнопка  в панели инструментов).
• В строке Обновление введите следующее: [ОтпускнаяЦена]+[ОтпускнаяЦена]*0,2 (увеличение отпускной цены на 20%).

Можно также использовать строку Условие отбора (например, увеличение отпускной цены для цен меньших 1000 рублей.).

На рисунке 42 представлен бланк этого запроса в режиме Конструктора.

Рисунок 42.

• Сохраните запрос под именем «Обновление расхода».
• Откройте таблицу «Расход», найдите те записи, которые должны обновиться (отпускная цена увеличится на 20%), запишите значения цен <1000 в тетрадь.

• Запустите на исполнение.
• Посмотрите результаты выполнения запроса: откройте таблицу «Расход» и просмотрите обновленные записи и сравните их со значениями в тетради.

6. Установка внешней связи с таблицами другой БД. Создание запросов на добавление записей.

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

Задание.

• Выберите пункт меню Файл/Внешние данные/Связь с таблицами.
• По сети найдите БД соседа и в открывшемся окне-диалоге Связь с таблицами выделите две таблицы «Приход» и «Расход», затем нажмите кнопку ОК (имена новых присоединенных таблиц «Приход1» и «Расход1»).
• Создайте запрос «Добавление прихода» на основе таблицы «Приход1».
• В бланк создаваемого запроса добавьте следующие поля таблицы «Приход1»: ДатаПриема, КодПоставщика, КодТовара, Количество, СуммаУпл.
• В строке Условие отбора введите параметр – [Введите дату].
• Выберите тип запроса – добавление (кнопка  в панели инструментов). На рисунке 43. Представлен бланк этого запроса в режиме Конструктора.

Рисунок 43.

• Укажите имя таблицы, в которую будут добавлены записи из таблицы «Приход1» – «Приход» (см. рисунок 44.).

Рисунок 44.

• Сохраните запрос под именем «Добавление прихода».

• Запустите на исполнение.
• Посмотрите результаты выполнения запроса: откройте таблицу «Приход» и просмотрите ее записи. В таблицу добавились новые записи.

7. Создание перекрестных запросов.

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

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

Задание.

Создайте перекрестный запрос «Статистика прихода» на основе таблицы «Приход».

• Добавьте в бланк запроса в режиме Конструктора поля: КодПоставщика, КодТовара, Количество.
• Выберите тип запроса – перекрестный (кнопка  в панели инструментов).
• Для поля КодПоставщика в строке Перекрестная таблица выберите из раскрывающегося списка Заголовки строк, для поля КодТовара –Заголовки столбцов, для поля Количество – Значение.
• Для поля Количество в строке Групповая операция выберите статистическую функцию –Sum.
• Отсортируйте поля ВидТовара, Клиент по возрастанию.

Рисунок 45.

• Сохраните запрос под именем – «Статистика прихода».
• Запустите на исполнение.

• Посмотрите результаты выполнения запроса (см. рисунок 46.).

Рисунок 46.

Практическое задание №6.

1. Создайте запрос на удаление записей «Удаление Клиента» на основе таблицы «Клиенты» (см. выше задание). Обязательно добавьте в таблицу «Клиенты» нового клиента и несколько записей в таблицу «Расход» с этим клиентом. На рисунке 47 показан этот запрос в режиме Конструктора.

Рисунок 47.

2. Создайте запрос на обновление записей «Обновление прихода» на основе таблицы «Приход» для поля СуммаУпл (увеличение стоимости закупаемого товара на 10%. См. выше задание и рисунок 48.).

Рисунок 48.

3. Создайте запрос на добавление записей «Добавление расхода» на основе таблицы «Расход1». Внешние связи устанавливать не надо, они уже установлены. Записи из таблицы «Расход1» добавляются в таблицу «Расход» (см. задание выше и рисунок 49.).

Рисунок 49.

4. Создайте перекрестный запрос «Статистика расхода» на основе таблицы «Расход» (см. выше задание и рисунок 50.).

Рисунок 50.

8. QBE-запросы и SQL-запросы. Трансформация QBE-запросов в SQL-запросы.

В Access все запросы делятся на QBE-запросы (Query By Example – запрос по образцу), параметры которых устанавливаются в окне Конструктора запросов и SQL-запросы (Structured Query Language – структурированный язык запросов), при создании которых применяются операторы и функции языка SQL.

В Access легко можно преобразовать QBE в SQL-запросы, и наоборот.

Задание.

Трансформируйте все созданные вами виды запросов в SQL.

• Выберите запрос «Остатки».
• Откройте его в режиме Конструктора.
• Используйте кнопку  в панели инструментов.
• Из раскрывающегося списка выберите строку .

• Внимательно просмотрите операторы языка SQL (они выделены большими буквами). Ничего не менять, иначе запрос может перестать работать (см. рисунок 51).

Рисунок 51.

• В таком порядке откройте все созданные вами запросы и ознакомьтесь с операторами SQL.

VII. Отчеты. Создание итоговых документов.

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

Задание.

Создайте отчет «Остатки».

• Перейдите на вкладку Отчеты/Создать. Появится окно-диалог Новый отчет (см. рисунок 52).

Рисунок 52.

• Выберите опцию Мастер отчетов и чуть ниже из раскрывающегося списка запрос «Остатки», затем нажмите кнопку ОК. Появится первое окно-диалог Мастера - Создание отчета (см. рисунок 53).

Рисунок 53.

• Выберите все поля запроса: ВидТовара, Кол-воПР, Кол-воРАС, Остаток, затем нажмите кнопку Далее>. Появится второе окно Мастера (см. рисунок 54.).

Рисунок 54.

• Здесь добавьте уровни группировки: выделите поле ВидТовара и щелкните по кнопке со значком > (см. рисунок 54.). 
• После этого кнопкой Далее> перейдите к третьему окну Мастера (рисунок 55.).

Рисунок 55.

• В этом окне воспользуйтесь кнопкой Итоги…и перейдите к окну-диалогу Итоги (см. рисунок 56.).

Рисунок 56.

• Здесь поставьте «птички» в колонке Sum напротив полей Кол-воПР, Кол-воРАС, Остаток, затем нажмите кнопку ОК и вернитесь в третье окно Мастера (см. рисунок 55).
• Кнопкой Далее> откройте четвертое окно Мастера.
• Здесь выберите макет отчета – ступенчатый и ориентацию – книжная (см. рисунок 57.).

Рисунок 57.

• Перейдите к пятому окну Мастера.
• Здесь выберите стиль отчета – строгий (см. рисунок 58.).

Рисунок 58.

• Кнопкой Далее> откройте последнее окно Мастера.

• Задайте имя отчета – «Остатки» и нажмите кнопку Готово (см. рисунок 59.).

Рисунок 59.

• Откройте готовый отчет.

Многие элементы отчета требуют редактирования. Например, строка названия товара отражается не полностью, есть совершенно не нужные элементы, шрифт полей  - невыразителен. Чтобы отредактировать элементы отчета надо открыть его в режиме Конструктора (см. рисунок 60.).

Рисунок 60.

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

• Переместите вправо надписи Кол-воПР, Кол-воРАС, Остаток в области Верхний колонтитул и те же поля в области Область данных, а также поля =Sum([Кол-воПР]), =Sum([Кол-воРАС]), =Sum([Остаток]) в областях Примечание группы и Примечание отчета.
• Поле =”Итоги для ‘& ’’’ВидТовара …” сделайте невидимым. Для этого выделите его и нажмите кнопку  в панели инструментов. Появится окно-диалог Поле: (см. рисунок 61.).

Рисунок 61.

• Выберите вкладку Все, в строке Вывод на экран поставьте Нет.

• Теперь выделите поле ВидТовара в области Заголовок группы и растяните его мышкой вправо.
• Выделите поля Кол-воПР, Кол-воРАС, Остаток, =Sum([Кол-воПР]), =Sum([Кол-воРАС]), =Sum([Остаток]) в областях Область данных, Примечание группы, Примечание отчета и увеличьте размер шрифта, сделайте его жирным. Можно также изменить цвет шрифта в отдельных полях отчета.
• Сохраните макет отчета.
• Откройте отчет «Остатки» и просмотрите его (см. рисунок 62.).

Рисунок 62.

Практическое задание №7.

Создайте отчет «Доход» на основе запроса «Доход».

Выберите следующие поля для отчета: ВидТовара, Доход. Отредактируйте макет отчета (см. предыдущее задание).

На рисунке 63 показан фрагмент отчета «Доход».

Рисунок 63.

VIII. Защита базы данных. Связи с Office (Excel, Word).

Что же такое защита баз данных? Прежде всего, это защита информации и объектов, которые содержатся в БД.

Защита БД состоит из нескольких этапов:

• Первый этап – разделение базы данных на два файла. Один содержит одни только данные – файл с таблицами, второй – объекты (формы, запросы, отчеты, макросы, модули).
• Второй этап - задание пароля для этих файлов.
• Третий этап – установка защиты на системном уровне (создание файла рабочей группы).
• Четвертый этап – использование Мастера защиты и компиляция модулей БД.
• Пятый этап – хранение файла с данными на съемном носителе (например, на zip-диске, CD-R диске и т.п.). Этот способ защиты самый надежный. Нет данных, соответственно нет БД.

Вы познакомитесь на занятиях только с несколькими этапами защиты (1 и 2).

Задание.

Установите защиту для базы данных «Компьютеры».

• Разделите БД «Компьютеры» на два файла.
• Выберите Сервис/служебные программы/Разделение баз данных.
• В окне-диалоге Разделение БД нажмите кнопку «Разделить» (см. рисунок 64.)

Рисунок.64.

• В окне Создание базы данных с таблицами в строке имя файла задайте имя БД (Компьютеры_tb), задайте путь к месту на диске, где будет храниться ваша БД (например, папка Мои документы, см. рисунок 65.)

Рисунок 65.

• Затем нажмите кнопку Разделение. После этого Access выдаст сообщение, что БД успешно разделена (см. рисунок 66.).

Рисунок 66.

• Нажмите кнопку ОК.
• Теперь задайте пароль для БД «Компьютеры».
• Для этого закройте БД, и, используя кнопку  в панели инструментов, вновь откройте ее, но при этом установите опцию монопольно (см. рисунок 67.).

Рисунок 67.

• Выберите Сервис/Защита/Задать пароль базы данных…
• Появится окно-диалог Задание пароля базы данных (см. рисунок 68.).

Рисунок 68.

• В области Пароль задайте пароль БД – 1111 (четыре единицы).
• В области Подтверждение подтвердите заданный пароль, т.е. еще раз введите четыре единицы. Введенные цифры будут отображаться в виде звездочек - ****.

• Нажмите кнопку ОК.
• Удалите все связанные таблицы в БД «Компьютеры».
• Закройте окно БД.
• Откройте БД «Компьютеры_tb» монопольно.
• Задайте пароль и подтверждение для этой БД – 2222 (четыре двойки).
• Вновь откройте БД «Компьютеры» и установите связь с таблицами БД «Компьютеры_tb». Используйте меню Файл/Внешние данные/Связь с таблицами (см. рисунок 69.).

Рисунок 69.

• Нажмите кнопку Связь. Появится окно-диалог Необходимо ввести пароль…(см. рисунок 70.).

Рисунок 70.

• Введите пароль БД «Компьютеры_tb» – 2222.
• Затем появится окно-диалог Связь с таблицами (см. рисунок 71.)

Рисунок 71.

• Выделите все таблицы кнопкой Выделить все.
• Затем нажмите кнопку ОК. Связь с таблицами вновь будет установлена.

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

Помимо всех перечисленных выше возможностей, Access поддерживает связи с Office. Объекты Access (запросы, формы в табличной форме, таблицы, отчеты) могут быть трансформированы в файлы Excel и Word. Таким образом, можно создавать архивы с обработанными данными, отправлять эти документы по электронной почте, на базе офисных приложений могут быть созданы информационные комплексы (связанные между собой файлы Excel – результат информационной обработки Access).

Задание.

Установите связи с Office: сделайте архивные копии отчета «Доход» и запроса «Статистика прихода».

• Начните с запроса «Статистика прихода».

• Выберите вкладку Запросы. Откройте перекрестный запрос «Статистика прихода».
• Щелкните по кнопке  в панели инструментов и из раскрывающегося списка выберите пункт – Анализ в MS Excel. Откроется окно Excel, в которое автоматически сбрасывается преобразованный запрос Access - «Статистика прихода».
• Над строкой заголовков вставьте одну строку.
• В ячейку А1 впечатайте заголовок: «Статистика прихода товара» и выделите его жирным шрифтом.
• В ячейках В6, С6, …, J6 подсчитайте итоговое количество по каждому виду товара, при этом можно использовать кнопку автосуммы  в панели инструментов.
• Выделите ячейки A6,…, J6 и сделайте в них шрифт жирным.
• Затем задайте рамку для этих ячеек кнопкой  в панели инструментов.
• В ячейку А6 впечатайте слово «Итого:» (см. рисунок 72.).

Рисунок 72.

• Сохраните, внесенные изменения, щелкнув по кнопке в  панели инструментов.

Файл Excel автоматически записывается на диск в папку Мои Документы под именем «Статистика прихода» (имя запроса Access). При желании можно задать другое имя и указать другой путь к нему.

• Теперь создайте архивный файл для отчета «Доход».
• Перейдите на вкладку Отчеты и откройте отчет «Доход».
• Щелкните по кнопке  в панели инструментов и из раскрывающегося списка выберите пункт – Публикация в MS Word. Откроется окно Word, в которое автоматически сбрасывается преобразованный отчет Access - «Доход» (см. рисунок 73.).

Рисунок 73.

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

• Его можно отредактировать (например, выбрать более крупный шрифт и т.п.)

Практическое задание №8.

Установите связи с Office: сделайте архивные копии отчета «Остатки» и запроса «Статистика расхода».

На рисунках 74 и 75 показан конечный результат выполнения практического задания №8.

Рисунок 74.

Рисунок75.

Заключение.

Вы создали небольшую БД «Компьютеры». Если взять за основу пример, который здесь рассматривался, можно создавать более сложные приложения, использовать такие объекты как Макросы и Модули, запросы на объединение и управление (при создании этих запросов необходимо использовать язык SQL). Использовать формы не только для ввода информации, но и для создания меню, заставок, а также накладывать маски на табличные данные. Можно конструировать все, без исключения, объекты БД вручную тем самым, создавая свой собственный стиль приложения.

Совершенно очевидно, что этот курс не охватил всех тонкостей и нюансов Access 97, но мы постарались показать на конкретном, завершенном примере основные возможности этого инструмента. Хотелось бы кратко заметить, что создание гибких и мобильных приложений невозможно без знания языков программирования, таких как SQL и Access VBA.

Вопросы по теоретической подготовке (Access 97).

Вопросы (первый параграф).

1. Что такое база данных?
2. Что такое СУБД?
3. Какие модели баз данных вы знаете?
4. Охарактеризуйте каждую из них. Перечислите  главные черты реляционных БД.

Вопросы (второй параграф).

1. Назовите основные режимы работы Access.
2. Перечислите объекты БД. Какие из них являются базовыми, а какие – производными?
3. Дайте краткую характеристику объектов БД.

Вопросы (третий параграф).

1. Кого называют разработчиком БД?
2. Что называется проектом БД?
3. Какие бывают типы полей в Access 97? Что такое параметры  (свойства) поля?
4. Дайте определение ключевого поля.

Вопросы (пятый параграф).

1. Какие дополнительные преимущества дают связанные таблицы?
2. Как осуществляется процесс установки отношения (связи) между таблицами?
3. Дайте определение поля внешнего ключа?
4. Каких ошибок можно избежать с использованием опции «обеспечение целостности данных»?