Основы работы СУБД MS Access: основные технологии работы с формами (на примере).

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

o во-первых, структура таблицы должна строиться на основе логики задач хранения информации, которая, вообще говоря, может существенно отличаться от логики ее накопления и ввода;

o во-вторых, важным показателем качества автоматизированной системы является организация ее системы ввода/вывода в виде, максимально приближенном к традиционным формам представления информации на немашинных носителях. Такие формы, как правило, делают программное обеспечение привлекательным для конечного пользователя, уменьшают период его адаптации ко вновь внедряемой системе и позволяют быстро сосредоточиться на решении основных профессиональных задач;

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

Для решения как этих, так и многих других проблем организации интерфейса ввода/ вывода данных в Access служит механизм электронных форм. Выберем вкладку Формы главного окна базы данных и нажмем кнопку Создать. Появляющееся диалоговое окно позволяет выбрать как таблицу или запрос, для работы с данными которых составляется форма, так и режим ее создания. В зависимости от квалификации пользователя и, естественно, сложности разрабатываемой формы можно либо воспользоваться встроенными программными надстройками-мастерами, либо сразу начать ее создание с нуля в режиме Конструктора. Весьма плодотворным также оказывается комбинированный подход: сначала используется соответствующий мастер, а затем полученная форма дополнительно дорабатывается в "ручном режиме". Проиллюстрируем сказанное на примере. Создадим форму для работы с таблицей Бумаги, воспользовавшись надстройкой Автоформа: в столбец. В результате получим окно следующего вида.

 

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

На рис. 7.18 показана та же форма в режиме Конструктор. Технология процесса проектирования форм в среде Access сводится к добавлению управляющих элементов и изменению их свойств. В связи с этим при переходе в режим Конструктор >;>Ш Экране по умолчанию появляются два дополнительных окна:

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

Окно Свойств текущего элемента управления, предназначенное для изменения его атрибутов и настроек, например, цвета, шрифта, размера и т. п.

 

 

Рис. 7.18. Форма Бумаги в режиме конструктора

 

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

В качестве иллюстрации возможностей конструктора по изменению интерфейса ввода/вывода проведем следующие манипуляции над формой Бумаги:

L Удалим фоновый рисунок: очистим свойство Рисунок, когда текущим выбранном элементом является вся форма.

2. Изменим цвет фона: выберем элемент ОбластьДанных и изменим у нее атрибут Цвет фона (рис. 7.19).

3. Изменим внешний вид полей: выделим группу полей (поля выбираются с помощью мыши при нажатой клавише Shift) и в окне свойств изменим значение атрибута Оформление на Утопленное.

4. Отредактируем подписи полей и несколько изменим их расположение друг относительно друга: для этого достаточно воспользоваться возможностями визуального редактирования элементов.

5. Добавим разделительную линию после поля НаимБум (наименование бумаги): для этого следует воспользоваться элементом Линия.

6. Добавим кнопку завершения работы с формой: в большинстве ситуаций эту и подобные операции проще и удобнее делать в режиме мастера (нажата соответствующая кнопка на панели Элементы управления). В этом случае от пользователя требуется лишь ввести минимальное количество параметров для добавляемого программного компонента. Добавленную кнопку поместим в область Примечания формы.

 

 

Рис. 7.19. Окно свойств элемента управления

 

В результате отредактированная форма Бумаги примет вид, показанный на рис 7.20.

 

 

Рис. 7.20. Форма Бумаги после редактирования

 

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

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

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

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

- создать новую форму на базе некоторой таблицы или запроса;

- воспользоваться уже существующей формой, сделав ее подчиненной.

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

o во вспомогательном запросе достаточно просто обработать условие, идентифицирующее тип заявки (если объем заявки меньше нуля, то это заявка на продажу). Более того, для конечного пользователя в качестве объема заявки вместо отрицательных величин выводятся выглядящие более естественно положительные значения -1*[0бъем3аявки];

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

 

 

Рис. 7.22. Структура запроса ЗаявПрод (заявки на продажу)

 

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

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

 

27Основы работы СУБД MS Access: основные технологии организации многотабличных баз данных (на примере).

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

 

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

 

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

 

3. Отношение «многие ко многим» возникает между двумя таблицами в тех случаях, когда:

 

• одна запись из первой таблицы может быть связана более чем с одной записью из второй таблицы;

 

• одна запись из второй таблицы может быть связана более чем с одной записью из первой таблицы.

 

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

 

В большинстве случаев любые две таблицы связаны отношением «один – ко многим».

Создание связей между таблицами

 

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

 

1. Таблица тАбитуриенты:

 

КодАб является ключевым полем.

 

 

2. Таблица пересечения тСвязьАбВУЗ:

 

Первые два поля являются ключевыми, причем для таблицы тСвязь-Абвузы они являются чужими ключами.

 

Рисунок 12.6. Схема данных

 

 

Чтобы определить связи, необходимо использовать команду Сервис->Схема данных (кнопка

 

). Если связь определяется впервые в базе данных, то Access откроет пустое окно Схема данных, а затем выведет на экран окно диалога Добавление таблицы. В этом окне необходимо выделить нужные таблицы и нажать кнопку Добавить. В нашем случае в окно схемы данных надо добавить все три таблицы. Кнопка Закрыть закрывает окно диалога Добавление таблицы (данное окно можно вызвать по нажатию кнопки

 

). После этого окно Схемы данных должно выглядеть примерно так, как показано на рис. 3 (кроме линий соединения). Чтобы установить связь между таблицами твузы и тСвязьАбвуз, надо выделить поле Кодвуза в таблице твузы (щелкнуть левой кнопкой «мыши»), перетащить и опустить его на поле Кодвуза в таблице тСвязьАбвуз. Аналогично устанавливается связь между двумя другими таблицами.

 

Когда вы отпустите кнопку мыши, Microsoft Access откроет окно диалога Связи, представленное на рис. 12.7.

 

Рисунок 12.7. Настройка связей между полями таблиц

 

 

После установки флажка Обеспечение целостности данных, Access делает доступными еще два флажка: Каскадное обновление связанных полей и Каскадное удаление связанных записей. При установленном флажке Каскадное обновление связанных полей Microsoft Access обновляет все значения чужих ключей в дочерних таблицах (то есть в таблицах на стороне отношения «многие» при связи «один ко многим») при изменении значения первичного ключа в родительской таблице, которая находится на стороне отношения «один» при связи «один ко многим». Если же вы установите флажок Каскадное удаление связанных записей, Microsoft Access удалит дочерние строки (связанные строки в дочерних таблицах) при удалении родительской строки (связанная строка в родительской таблице). Установите флажок Каскадное обновление связанных полей, чтобы при изменении кода вуза Access автоматически обновлял записи в таблице тСвязьАбвуз.

 

После нажатия кнопки Создать Access создаст связь и нарисует линию между двумя таблицами, указывающую на наличие связи между ними. Заметьте: когда вы просите обеспечить целостность данных, Access изображает на конце линии у таблицы, находящейся на стороне отношения «один», цифру 1, а на другом конце у таблицы со стороны отношения «многие» – символ бесконечности. Чтобы удалить связь, щелкните по ее линии и нажмите клавишу Del. Если вы хотите отредактировать или изменить существующую связь, дважды щелкните по соответствующей линии, чтобы снова открыть окно диалога Связи.

28Основы работы СУБД MS Access: основные технологии работы с запросами (на примере).

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

o поиск записи по условию (см. функцию меню Правка > Найти);

o сортировка записей в требуемом порядке (см. функцию меню Записи > Сортировка);

o получение выборки записей таблицы, удовлетворяющей заданному условию, или, как еще говорят, задание фильтра для таблицы (Записи > Фильтр).

 

 

Рис. 7.12. Контекстное меню работы с данными в таблице

 

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

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

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

- Наименование бумаги;

- Наименование агента;

- Тип бумаги;

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

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

o описание структуры запроса (то есть указание того, какая информация должна выводиться в колонках таблицы запроса);

o задание порядка, в котором данные должны выводиться при выполнении запроса;

o задание условий вывода записей в запросе.

На рис. 7.13 показано окно конструктора запроса.

 

 

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

 

Отметим, что колонки таблицы запроса на рис. 7.13 содержа? как поля таблиц, так и выражения, построенные на основе полей. В частности, последняя колонка (ей присвоено имя НоминСтоим) содержит выражение [Номинал]*[СуммОбъем], при этом записи будут выводиться отсортированными по типу бумаг.

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

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

 

 

Рис. 7.14. Вывод данных по запросу СтруктураПортфелей

 

Рассмотрим еще один случай применения запросов для решения задач обработки данных. Достаточно типичной (в том числе для приложений финансово-экономического характера) является проблема группировки данных по тому или иному признаку. Например, в рамках построенной нами базы данных может быть поставлена задача определения суммарного (или среднего) спроса и предложения по ценным бумагам, циркулирующим на рынке. Решить ее можно, построив запрос, содержащий групповые операции. Для активизации возможности их задания в окне Конструктора запросов необходимо включить функцию меню Вид > Групповые операции.

 

 

Рис. 7.15. Создание запроса с групповыми операциями

 

На рис. 7.15 показано окно конструктора в процессе создания запроса, выводящего информацию по суммарному спросу и предложению на ценные бумаги. Операция свертки нескольких записей из таблицы Заявки в одну результирующую запись, осуществляемая для каждого наименования бумаги, определяется командой Группировка, расположенной в строке Групповая операция. Для двух последующих колонок запроса (СуммСпрос и СуммПредл) определены операции суммирования по группе (Sum), расположенные в той же строке, а в строке Поле находятся производные выражения, суммы которых мы хотим получить в запросе. В соответствии с ранее принятыми соглашениями объем суммарного спроса определяется совокупностью всех записей по данной бумаге, имеющих положительное значение в поле ОбъемЗаявки, а объем суммарного предложения - записями, содержащими в данном поле отрицательную величину. Таким образом, для вычисления СуммСпрос необходимо просуммировать If[0бъем3аявки]>=0; [Цена3аявки]*[0бъем3аявки];0), а для вычисления СуммПредл - If[ОбъемЗаявки]<=0;-1* [Цена3аявки]*[0бъем3аявки];0).

 

ПРИМЕЧАНИЕ

Встроенная функция lf(bArg; Arg1; Arg2) возвращает значение аргумента Arg1, если значение аргумента bArg, который может содержать только логическую величину, является истинным (bArg = ИСТИНА), и значение Агд2, если bArg = ЛОЖЬ.

 

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

- задание параметров, запрашиваемых при открытии запроса;

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

В завершение обзора средств построения запросов в СУБД Access следует указать также и на то, что в нее помимо мощного и эффективного визуального конструктора встроен также и режим непосредственного ввода SQL-выражений, определяющего запрос. Данный режим существует параллельно и доступен из меню Вид > Режим SQL (а также из пиктограммы Вид на панели инструментов). Перейдя в него, в частности, можно просмотреть SQL-выражение, соответствующее ранее построенному запросу СводнСпросПредл. Оно выглядит так:

 

SELECT Бумаги.НаимБум,

Sum(IIf([ОбъемЗаявки]>=0,[Цена3аявки]*[0бъем3аявки],0))

 

AS СуммСпрос,

Sum (IIf ([ОбъемЗаявки]<=0,-[ЦенаЗаявки]*[0бъемЗаявки],0))

AS СуммПредл

 

FROM Бумаги INNER JOIN

 

(Агенты INNER JOIN Заявки ON Агенты.КодАг = Заявки.КодАг)

ON Бумаги.КодБум = Заявки.КодБум

 

GROUP BY Бумаги.НаимБум

ORDER BY Бумаги.НаимБум;

 

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

Говоря о связи между режимом визуального конструктора запросов и режимом построения SQL-выражений, необходимо отметить, что существует естественная и логичная связь между типами запросов и реализующими их SQL-операторами. В частности, запросу на выборку соответствует оператор SELECT, запросу на создание - CREATE, запросу на обновление.- UPDATE, запросу на удаление - DELETE и т. д.