Microsoft Access : Практическое задание по Информационное обеспечение, программирование

Рубрикатор

Информационное обеспечение, программирование

Практические задания

Microsoft Access

ЛАБОРАТОРНЫЙ ПРАКТИКУМ

MS ACCESS

СОДЕРЖАНИЕ

ВВЕДЕНИЕ В БАЗЫ ДАННЫХ

ОСНОВНЫЕ ПОНЯТИЯ БАЗЫ ДАННЫХ

ФУНКЦИИ СУБД

МОДЕЛИ ОРГАНИЗАЦИИ ДАННЫХ

РЕЛЯЦИОННАЯ МОДЕЛЬ ДАННЫХ

РЕЛЯЦИОННЫЕ СВЯЗИ МЕЖДУ ТАБЛИЦАМИ БАЗ ДАННЫХ

ЦЕЛОСТНОСТЬ ДАННЫХ

ПРОЕКТИРОВАНИЕ БАЗЫ ДАННЫХ

ЛАБОРАТОРНАЯ РАБОТА № 1. РАЗРАБОТКА И ОПИСАНИЕ СТРУКТУРЫ БАЗЫ ДАННЫХ

.1 ПРИМЕР ПРОЕКТИРОВАНИЯ БАЗЫ ДАННЫХ

.2 АРХИТЕКТУРА MICROSOFT ACCESS

.3 СОЗДАНИЕ БАЗЫ ДАННЫХ И ТАБЛИЦ

.4 ПРИМЕР СОЗДАНИЯ ТАБЛИЦ БАЗЫ ДАННЫХ

ЛАБОРАТОРНАЯ РАБОТА № 2. РАЗРАБОТКА ЭКРАННЫХ ФОРМ ВВОДА ВЫВОДА ДАННЫХ

.1 ПРОЕКТИРОВАНИЕ ФОРМ

.2 ОСНОВНЫЕ ЭЛЕМЕНТЫ ФОРМЫ

.3 ПРИМЕР СОЗДАНИЯ ФОРМЫ ДЛЯ ВВОДА ДАННЫХ

.4 СОЗДАНИЕ ПОЛЯ СО СПИСКОМ

ЛАБОРАТОРНАЯ РАБОТА № 3. РАЗРАБОТКА ЗАПРОСОВ К ТАБЛИЦАМ БАЗЫ ДАННЫХ

.1 ТИПЫ ЗАПРОСОВ

.2 СОЗДАНИЕ ЗАПРОСА-ВЫБОРКИ

.3 ПРИМЕРЫ СОЗДАНИЯ ЗАПРОСОВ ВЫБОРА

.4 СОЗДАНИЕ ВЫЧИСЛЯЕМЫХ ПОЛЕЙ

.5 СОЗДАНИЕ ИТОГОВЫХ ЗАПРОСОВ

.6 СОЗДАНИЕ ПАРАМЕТРИЧЕСКИХ ЗАПРОСОВ

.7 СОЗДАНИЕ ПЕРЕКРЕСТНЫХ ЗАПРОСОВ

.8 МОДИФИКАЦИЯ ДАННЫХ С ПОМОЩЬЮ ЗАПРОСОВ ДЕЙСТВИЙ

ЛАБОРАТОРНАЯ РАБОТА № 4. РАЗРАБОТКА СЛОЖНОЙ ФОРМЫ ДЛЯ ОБРАБОТКИ ДАННЫХ

ЛАБОРАТОРНАЯ РАБОТА № 5. РАЗРАБОТКА ОТЧЕТОВ ПО ДАННЫМ

ЛИТЕРАТУРА

Введение в базы данных

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

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

Основные понятия базы данных

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

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

БД создаются для хранения и доступа к данным, содержащим сведения о некотором предмете.

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

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

Характеристикой объекта является элемент данных или идентификатор, или атрибут, который определяется именем и совокупностью некоторых значений. Элементы данных часто называются полями.

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

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

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

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

Функции СУБД

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

определение (задание структуры и описание) данных,
обработка данных,
управление данными. Все эти функциональные возможности в полной мере реализованы в Microsoft Access.

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

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

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

Модели организации данных

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

Реляционная модель данных

Реляционные БД имеют мощный теоретический фундамент, основанный на математической теории отношений.

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

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

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

Реляционные связи между таблицами баз данных

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

Существует три разновидности связи между таблицами базы данных:

"один - ко - многим";
"один - к - одному";
"многие - ко - многим".

Отношение "один-ко-многим".

Отношение "один - ко - многим" имеет место, когда одной записи родительской таблицы может соответствовать несколько записей дочерней таблицы. Связь "один - ко - многим" иногда называют связью "многие - к - одному". И в том, и в другом случае сущность связи между таблицами остается неизменной.

Связь "один - ко - многим" является самой распространенной для реляционных баз данных. Она позволяет моделировать также и иерархические структуры данных.

Отношение "один-к-одному".

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

Отношение "многие-ко-многим".

Отношение "многие - ко - многим" имеет место, когда

одной записи в родительской таблице может соответствовать более одной записи в дочерней таблице;
одной записи в дочерней таблице может соответствовать более одной записи в родительской таблице.

Всякая связь "многие - ко - многим" в реляционной базе данных должна быть заменена на одну или более связей "один - ко - многим".

Целостность данных

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

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

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

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

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

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

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

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

По определенным ключам СУБД автоматически строит индексы. Механизм индексов основан на понятии методов доступа. Индексы представляют собой механизм быстрого доступа к данным в таблицах БД. Сущность индексов состоит в том, что они хранят значения индексных полей (т.е. полей, по которым построены индексы) и указатель на данные в таблицах.

Проектирование базы данных

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

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

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

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

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

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

В общем виде процесс проектирования базы данных можно представить в виде следующих этапов:

Этап 1. Определение типов сущностей.

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

Этап 2. Определение типов связей.

Определение важнейших типов связей, существующих между сущностями, выделенными на предыдущем этапе.

Этап 3. Определение атрибутов и связывание их с типами сущностей и связей.

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

Этап 4. Определение доменов атрибутов.

Определение доменов (типов данных или допустимых значений) для всех атрибутов в модели данных.

Этап 5. Определение атрибутов, являющихся потенциальными и первичными ключами.

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

Лабораторная работа № 1. Разработка и описание структуры базы данных

.1 Пример проектирования базы данных

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

Этап 1. Определение типов сущностей.

Исходя из анализа предметной области можно выделить два типа сущностей ТОВАР и ПОКУПАТЕЛЬ, связанные между собой отношением «много - ко - многим», т.к. каждый покупатель может купить много наименований товара, и каждый товар может быть куплен многими покупателями. Однако, реляционная модель данных требует заменить отношение «много - ко - многим» на несколько отношений «один - ко - многим». Добавим еще один тип сущностей, отображающий процесс продажи товаров, ОТПУСК.

Этап 2. Определение типов связей.

Установим связи между объектами. Один покупатель может неоднократно покупать товары. Поэтому между объектами ПОКУПАТЕЛЬ и ОТПУСК имеется связь "один - ко - многим". Каждый покупатель может приобрести несколько различных товаров. Поэтому между объектами ТОВАР и ОТПУСК имеется связь "один - ко - многим".

Этап 3. Определение атрибутов и связывание их с типами сущностей и связей.

Распределим признаки, указанные в задании, по выделенным типам сущностей. К объекту ТОВАР относятся такие характеристика как название, тип, цена. К объекту ПОКУПАТЕЛЬ - имя, фирма, город, телефон. Тип сущности ОТПУСК может быть охарактеризован такими признаками как дата и количество проданного товара.

Этап 4. Определение доменов (допустимых значений) атрибутов.

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

Этап 5. Определение атрибутов, являющихся потенциальными и первичными ключами.

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

Для таблицы ОТПУСК первичным ключом является поле НомерПокупки, т.к. однозначно определяет дату, покупателя и другие элементы данных.

Для таблицы ТОВАР Название не может служить первичным ключом, т.к. товары разных типов могут иметь одинаковые названия, поэтому введем первичный ключ КодТовара. Также и в таблице ПОКУПАТЕЛЬ ни Имя, ни Фирма, ни Город не могут служить первичным ключом. Введем первичный ключ КодПокупателя

Установим связи между таблицами. Один покупатель может неоднократно покупать товары. Поэтому между таблицами ПОКУПАТЕЛЬ и ОТПУСК имеется связь "один - ко - многим" по полю КодПокупателя.

Каждый покупатель может приобрести несколько различных товаров. Поэтому между таблицами ТОВАР и ОТПУСК имеется связь "один - ко -многим" по полю КодТовара.

Теперь нужно создать связи между таблицами базы данных. Для этого поместим копии первичных ключей из таблицы со стороны "один" в таблицу со стороны "много". Для организации связи между таблицами ТОВАР и ОТПУСК поместим копию поля КодТовара из таблицы ТОВАР в таблицу ОТПУСК. Для организации связи между таблицами ПОКУПАТЕЛЬ и ОТПУСК поместим копию поля КодПокупателя из таблицы ПОКУПАТЕЛЬ в таблицу ОТПУСК. Для таблицы ОТПУСК поля КодПокупателя и КодТовара являются внешними (чужими) ключами. Нормализация таблиц БД призвана устранить из них избыточную информацию. Как видно из приведенного примера, таблицы нормализованной БД содержат только один элемент избыточных данных - это поле связи, присутствующее одновременно в родительской и дочерней таблицах. В результате получим следующую структуру базы данных

1.2 Архитектура Microsoft Access

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

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

Запрос - объект, который позволяет пользователю получить нужные данные из одной или нескольких таблиц. Для создания запроса можно использовать QBE (запрос по образцу) или инструкцию SQL. Можно создать запрос на выбор, обновление, удаление или добавление данных. С помощью запросов можно создавать новые таблицы, используя данные одной или нескольких таблиц, которые уже существуют.

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

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

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

Модуль - объект, содержащий программы на Microsoft Access Basic, которые позволяют разбить процесс на более мелкие действия и обнаружить ошибки, которые не удается найти с использованием макросов. Модули могут быть независимыми объектами, содержащими функции, которые можно вызывать из любого места приложения, но они могут быть и непосредственно "привязаны" к отдельным формам или отчетам для реакции на те или иные происходящие в них изменения.

В Microsoft Access база данных включает в себя все объекты, связанные с хранением данных, в том числе и те, которые определяет пользователь для автоматизации работы с ними.

2.3 Создание базы данных и таблиц

Для создания новой базы после запуска Microsoft Access нужно выбрать пункт: Создание базы данных / Новая база данных / ОК

и указать имя файла новой базы данных. Access автоматически добавит к имени расширение .mdb. Файл с расширением mdb Access использует для хранения всех объектов базы данных, включая таблицы, запросы, формы, отчеты, макросы и модули.

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

Определение полей

В режиме конструктора в столбце Поле указывается имя поля. Несмотря на то, что в Microsoft Access при задании имен можно использовать символ "пробел", лучше все-таки использовать имена полей без пробелов. Большинство баз данных SQL, к которым Microsoft Access может присоединять свои таблицы, не поддерживают использование пробелов при задании имен полей. В следующем столбце конструктора структуры таблицы нужно указать тип данных.

Типы данных.поддерживает восемь типов данных:

Текстовый - для хранения алфавитно-цифровых данных объемом не более 255 байт;- для хранения алфавитно- цифровых данных большого объема ( до 64 000 байт), например, предложения, абзацы, тексты;

Числовой - для хранения числовых значений длиной 1, 2, 4 или 8 байт ( байт, целое, длинное целое, с плавающей точкой);

Дата/время - используется для хранения календарных дат или значений времени и позволяет выполнять вычисления в единицах измерения времени: минутах, секундах, часах, днях, месяцах, годах;

Денежный - для хранения данных о денежных суммах;

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

Логический - используется для хранения значений Истина и Ложь;

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

Свойства полей.

Размер поля - это свойство задает максимальный размер данных, для хранения которых предназначено данное поле. Формат поля используется для задания формата представления данных при выводе на экран или печать.

Число десятичных знаков применяется для задания фиксированного числа десятичных знаков от 0 до 15.

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

Подпись поля используется для определения более описательного имени поля (например, без пробелов), которое Access будет выводить в элементах управления - подпись форм и в заголовках отчетов.

Значение по умолчанию используется для определения значения, которое появится в поле, если пользователь не ввел значение для этого поля.

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

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

Индексированное поле - можно задать построение индекса для ускоренного доступа к данным.

Задание условия на значение для полей.

Условие на значение задается выражением, которое состоит из операторов сравнения и операндов. Несколько условий могут быть связаны логическими связками OR (ИЛИ) и AND(И). В качестве операторов используются следующие:

< меньше, чем; <= меньше или равно; > больше, чем;

>= больше или равно;

= равно;

<> не равно;

Оператор IN обеспечивает проверку на равенство любому значению из списка, заключенного в круглые скобки.

Оператор BETWEEN проверяет, что значение поля находится внутри заданного диапазона. Верхняя и нижняя границы диапазона разделяются логическим оператором AND.

Оператор LIKE проверяет соответствие текстового или Мemo поля заданному шаблону символов. В качестве символов шаблона используются:

? - для замены одного произвольного символа;

* - для замены любой последовательности символов;

# - для замены одной любой цифры.

Можно задать, чтобы определенная позиция текстового или Memo поля содержала только символы, указанные в списке допустимых символов, заключенном в квадратные скобки. Можно включить в список диапазон символов, указав первый символ, знак дефиса и последний символ. Если нужно указать, что определенная позиция может содержать любой не входящий в список символ, следует начать список с восклицательного знака.Access позволяет задать условия не только на значение поля, но также и на значения для таблицы. Эти условия Microsoft Access будет использовать для проверки каждой записываемой в таблицу строки.

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

Задание маски ввода.

Чтобы облегчить ввод форматированных данных, Access позволяет задать маску ввода. Определить маску ввода можно при помощи следующих символов:

- в данной позиции может быть введена цифра или пробел,

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

- в данной позиции может быть введена цифра,- в данной позиции может быть введена произвольная буква,

# - в данной позиции может быть введена цифра или буква,

. - определяется место для десятичной точки,

, - определяется место для разделителя тысяч.

Создание первичного ключа

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

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

.Выполнить команду Правка/Определить ключ. В подтверждение того, что ключ задан, Access выведет в области маркировки слева от указанного поля символ ключа.

Определение связи между таблицами

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

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

Для удаления связи достаточно установить курсор мыши на связи в окне схемы данных и нажать клавишу Del.

таблица база данный архитектура запрос

2.4 Пример создания таблиц базы данных

Таблица ПОКУПАТЕЛЬ (родительская таблица)

Имя поляТип данныхСвойства поляКодПокупателяСчетчикПервичный ключИмяТекстовыйРазмер поля 20ФирмаТекстовыйРазмер поля 20ГородТекстовыйРазмер поля 20ТелефонТекстовыйРазмер поля 8 Маска ввода 99-99-99

Таблица ТОВАР (родительская таблица)

Имя поляТип данныхСвойства поляКодТовараСчетчикПервичные ключНазваниеТекстовыйРазмер поля 30ТипТекстовыйРазмер поля 20ЦенаДенежный

Таблица ОТПУСК (дочерняя таблица)

Имя поляТип данныхСвойства поляНомерПокупкиСчетчикПервичный ключКодПокупателяЧисловойКодТовараЧисловойКоличествоЧисловойУсловие на значение >10ДатаДата/ВремяУсловие на значение > #15.10.2002#

Связи между таблицами

Вид связиОдин - ко - многимОдин - ко - многимСвязываемые таблицыПОКУПАТЕЛЬ - ОТПУСКТОВАР - ОТПУСКСвязываемые поляКодПокупателя-КодПокупателяКодТовара-КодТовара

Создание таблицы в режиме конструктора показано на рис.

Процесс связывания таблиц показан на рис.

Лабораторная работа № 2. Разработка экранных форм ввода/вывода данных

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

2.1 Проектирование форм

Формы можно спроектировать на базе существующей Access -таблицы или запроса. Имена полей берутся из спецификации таблицы, а расположением полей на экране занимается пользователь - в соответствии со своими вкусами и потребностями. При этом он определяет, какие поля в какой последовательности появляются в форме, объединяет логически взаимосвязанные поля в группы, вносит в формы элементы оформления.
Формы являются основным средством организации интерфейса пользователя в приложении Microsoft Access. Можно создавать формы для различных целей:
Вывод и редактирование данных. Это наиболее распространенный способ использования форм. Формы обеспечивают вывод на экран данных в выбранном представлении. С их помощью значительно упрощается внесение изменений, добавление и удаление данных из базы. В форме можно сделать доступными только для чтения все записи или некоторую их часть, автоматически выбирать информацию из других связанных таблиц и помещать ее в форму, вычислять выводимые в форме значения, скрывать (делать невидимыми) или наоборот выводить некоторые данные в зависимости от значений других полей записи либо от выбранных пользователем параметров.
Управление ходом выполнения приложения. Чтобы автоматизировать вывод определенных данных или выполнения некоторой последовательности действий, можно создать формы для работы с макросами и функциями Microsoft Access Basic. Для запуска макросов или процедур Access Basic можно создать специальные элементы управления - командные кнопки). С помощью макросов и процедур можно открывать другие формы, выполнять запросы или команды меню, фильтровать выводимые на экран значения, устанавливать значения в записях или формах, выводить меню на экран, распечатывать отчеты и выполнять ряд других действий. Можно спроектировать форму таким образом, чтобы макросы или функции Access Basic запускались в ответ на определенные события:открытие формы, активизация определенных элементов управления или на изменение данных в форме.
Ввод данных. Можно создать форму, предназначенную только для ввода в базу данных новых данных или значений, помогающих автоматизировать выполнение приложения.
Вывод сообщений. Формы могут предоставлять вспомогательную информацию о работе приложений или о выполняемых действиях. С помощью макрокоманд или функций Microsoft Access Basic можно вывести на экран информацию, предупреждающую или сообщающую об ошибках.
Печать информации. Несмотря на то, что для печати информации используются отчеты, можно распечатать информацию ,содержащуюся в форме.

2.2 Основные элементы формы

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

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

2.3 Пример создания формы для ввода данных

Создадим форму Мастером для таблицы ТОВАР, взяв все поля, кроме КодТовара. Укажем внешний вид формы - как ленточный. Пример формы показан на рис.

Аналогичным образом создадим форму для таблицы ПОКУПАТЕЛЬ, взяв все поля, кроме поля КодПокупателя. Поля КодТовара и КодПокупателя имеют тип Счетчик и заполняются автоматически не зависимо от пользователя.

Также с помощью Мастера создадим форму для таблицы ОТПУСК, исключив поле НомерПокупки, т.к. это поле формируется как счетчик автоматически. В этой форме присутствуют поля КодТовара и КодПокупателя, что потребует от пользователя при вводе данных о покупках указывать коды товара и покупателя. Что является весьма неудобным. Заменим в форме ОТПУСК в режиме конструктора поля КодТовара и КодПокупателя на поля со списком, чтобы обеспечить пользователю при вводе информации о покупке выбор товаров и покупателей из списков.

Покажем процесс построения поля со списком. В режиме конструктора формы ОТПУСК выберем из панели инструментов элемент управления ПолеСоСписком. При этом кнопка Мастера должна быть включена. Далее необходимо ответить на вопросы Мастера: поле со списком использует значения из таблицы или запроса; следует указать таблицу ТОВАР; взять поля КодТовара для связи и Название для выбора; скрыть ключевой столбец; сохранить в поле КодТовара.

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

2.4.Создание поля со списком

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

Свойство Данные показывает, с каким полем связано поле со списком.
Свойство Тип источника строк указывает, что данные поступают в поле со списком из таблицы или запроса.
Ячейка свойства Источник строк содержит инструкцию SQL, которую нужно создать для запроса полей, например, КодТовара и Наименование из таблицы ТОВАРЫ. В бланке QBE следует установить сортировку по возрастанию для поля Наименование.
Свойство Число столбцов следует установить в значение 2, что указывает на то, что поле со списком использует два столбца запроса.
Свойство Заглавия столбцов устанавливается в значение Нет. это означает, что при раскрытии списка заголовки не выводятся.
Свойство Ширина столбцов. Для первого столбца ширина столбца равна 0. Это значение указывает на то, что первый столбец не выводится в поле со списком (на экране не надо показывать КодТовара). Для второго столбца (Наименование) устанавливается ширина столбца 1,323 дюйма. Свойство Ширина столбцом имеет следующие значения: 0";1,323"
Значение следующего свойства Связанный столбец 1 указывает на то, что спрятанный первый столбец является столбцом, значения которого используются в качестве значения поля со списком, т.е. определяют значения связанного поля базовой таблицы (поля, указанного в свойстве Данные).
Свойство Ограничиться списком разрешает выбор значений только из списка. Чтобы Access выбирал значения из списка по первым введенным символам, следует установить свойство Автоподстановка в значение Да.

Лабораторная работа № 3. Разработка запросов к таблицам базы данных.

3.1 Типы запросов

Выделяют два типа запросов: QBE - запросы (QBE - Query By Example запрос по образцу), которые пользователь формирует, определяя отдельные параметры в окне проектирования с использованием подсказок (образцов), и SQL - запросы (SQL - Structured Query Language - структурированный язык запросов), при создании которых пользователь применяет инструкции и функции, выстраивая некоторое описание. QBE - запрос Access легко транслирует в соответствующий SQL - запрос. Обратная операция тоже не составляет труда. Для Access безразлично, с какими типами запросов работает пользователь.

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

Запрос - выборка используется для выбора из базы данных информации, которая интересует пользователя в данный момент. Результат обработки программой Access такого запроса представляет cобой таблицу (последовательность записей) и называется Dynaset (динамический, временный набор данных). В эту таблицу включены выбранные из основной таблицы блоки данных, которые удовлетворяют критериям запроса.- динамический набор данных, т.е. при каждом выполнении запроса он строится вновь на основе "свежих" табличных данных. В большинстве случаев с этими наборами можно работать точно также как с таблицами: просматривать и выбирать информацию, печатать и даже обновлять записи, однако в отличие от реальной таблицы, этот набор данных физически не существует в базе данных.соэдает набор данных из данных таблиц и других запросов. Если изменяются данные в наборе данных, Access вносит соответствующие изменения в таблицы, на базе которых построен запрос. Набор записей, который создает Access при выполнении запросов, выглядит и ведет себя во многом точно так же, как реально содержащая данные таблица. Во многих случаях можно вставлять строки, удалять их и обновлять данные в наборе записей. при этом Access выполнит необходимые изменения в соответствующих таблицах. Однако при использовании запросов - выборки существуют ограничения для обновления данных. Так, не подлежат изменению:

* вычисляемые и итоговые поля;

* данные в перекрестном запросе;

* значения ключевых полей в таблице со стороны "один".

3.2 Создание запроса-выборки

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

Для выбора полей используется первая строка бланка QBE. Это могут быть поля, которые должны присутствовать в наборе записей запроса, поля, по которым нужно провести сортировку, или поля, значения которых нужно проверить. Включение полей в запрос может быть осуществлено двойным щелчком на имени поля в области верхней панели. Во второй строке бланка QBE Access выводит на экран имена таблиц, из которых выбраны поля. В третьей строке бланка можно задать выполнение сортировки по выбранному или вычисляемому полю. Четвертую строку бланка Вывод на экран можно использовать для указания полей, которые должны быть выведены в наборе данных. Строки Условие отбора используются для ввода условий в качестве фильтра. Несколько условий отбора для нескольких полей, связанных логической связкой И, записываются в одну строку, а связанные логической связкой ИЛИ - в разные строки условий отбора. Несколько условий для одного поля связываются логическим оператором И (AND), если необходимо выполнение всех приведенных условий, и логическим оператором ИЛИ (OR), если достаточно выполнение хотя бы одного условия. Кроме обычных операторов сравнения

> - больше, чем,

>= - больше или равно,

< - меньше, чем,

<= - меньше или равно,

= - равно,

<> - не равно,

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

3.3 Примеры создания запросов выбора

Пример 1. Показать названия и цену товаров, проданных в объеме более 10 единиц

ПолеНазваниеЦенаКоличествоИмя таблицыТОВАРТОВАРОТПУСКУсловие отбора>20

Пример 2. Показать фамилии покупателей Москвы или Ульяновска

ПолеФамилияГородИмя таблицыПОКУПАТЕЛЬПОКУПАТЕЛЬУсловие отбораIN("Москва";"Ульяновск")

или

ПолеФамилияГородИмя таблицыПОКУПАТЕЛЬПОКУПАТЕЛЬУсловие отбора"Москва" Or "Ульяновск"

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

ПолеНазваниеЦенаИмя таблицыТОВАРТОВАРУсловие отбораBETWEEN 100 And 200

или

ПолеНазваниеЦенаИмя таблицыТОВАРТОВАРУсловие отбора>= 100 And <=200

Пример 4. Найти фирмы, название которых начинается с буквы А или Д

ПолеФирмаИмя таблицыПОКУПАТЕЛЬУсловие отбораLike "А*" Or Like "Д*"

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

ПолеФирмаИмя таблицыПОКУПАТЕЛЬУсловие отбораLike "?[а-к]д[0-9]*"

Отдельно рассмотрим условия отбора для даты и времени.Access хранит значения дат и времени как числа с плавающей точкой двойной точности. Значение целой части соответствует дате, а дробной части - времени суток. В Access значения даты и времени заключаются между символами #. Для ввода условий отбора для даты и времени используются следующие функции:

Day(дата) - возвращает значение дня от 1 до 31;(дата) - возвращает значение месяца от 1 до 12;(дата) - возвращает значение года от 100 до 9999;(дата) - возвращает целое число от 1 до 7, соответствующее дню недели;(дата) - возвращает число от 0 до 23 - значение часа;() - возвращает системную дату.

Чтобы воспользоваться этими функциями, сначала следует создать в запросе вычисляемое поле, а затем записать для него условие отбора.

Пример 6. Вывести список товаров, проданных 15 мая 2002 года.

ПолеНазваниеДатаИмя таблицыТОВАРОТПУСКУсловие отбора#15.05.02#Пример 7. Вывести список товаров, проданных в мае 2002 года. В запросе создадим вычисляемые поля по формулам Месяц:Month([ОТПУСК]![Дата]) и Год:Year([ОТПУСК]![Дата]).

ПолеНазваниеМесяц: Month([ОТПУСК]![Дата])Год:Month([ОТПУСК]![Дата])Имя таблицыТОВАРУсловие отбора52002

3.4 Создание вычисляемых полей

Можно задать вычисления над любым полем таблицы и сделать вычисляемые значения новым полем в наборе данных. В выражении можно использовать следующие операторы:

& - конкатенация (сцепление, склеивание) текстовых полей или символьных констант;

+, -, *, /, \, ^, mod - соответственно сложение, вычитание, умножение, деление, целочисленное деление, возведение в степень и остаток от деления арифметических выражений.

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

Пример 8. Рассчитать общую стоимость покупок.

ПолеНазваниеСтоимость: [ТОВАРЫ]![Цена]*[ОТПУСК]![Количество]Имя таблицыТОВАР

3.5 Создание итоговых запросов

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

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

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

Стоимость:[ТОВАРЫ]![Цена]*[ОТПУСК]![Количество]

ПолеДатаКоличествоСтоимостьИмя таблицыОТПУСКОТПУСКГрупповая операциягруппировкаSumSum

Чтобы в группы включать только определенные записи, можно добавить в бланке QBE поля, которые будут использоваться в фильтре. Для создания фильтра следует выбрать установку Условие в строке Групповая операция, удалить флажок Вывод на экран для этого поля и ввести условие отбора.

Пример 10. Показать общую сумму продаж каждого товара с 1 мая 2002 года

ПолеДатаКоличествоСтоимостьИмя таблицыОТПУСКОТПУСКГрупповая операциягруппировкаSumSumУсловие отбора>#01.05.02#

Пример 11. Определить товары, общая сумма продажи которых превысила 10000 рублей

ПолеНазваниеКоличествоСтоимостьИмя таблицыНазваниеОТПУСКГрупповая операциягруппировкаSumSumУсловие отбора>10 000

3.6 Создание параметрических запросов

В запросах выбора условия отбора вводятся непосредственно в бланках QBE окна запроса в режиме конструктора. Однако не всегда на этапе создания запроса можно решить, какие именно значения должен отыскивать Access. Вместо этого можно включить в запрос параметр и в этом случае перед выполнением запроса Access каждый раз будет запрашивать у пользователя конкретные условия отбора. Чтобы установить параметр, вместо конкретного значения в строку Условия отбора в бланке QBE нужно ввести имя или фразу, заключенную в квадратные скобки. То, что заключено внутри квадратных скобок, Access рассматривает как имя параметра. Это имя выводится в диалоговом окне при выполнении запроса, поэтому в качестве имени параметра следует использовать содержательную фразу. В одном запросе можно задать несколько параметров. При этом имя каждого параметра должно быть уникальным и информативным.

Пример 12. Определить общую сумму продаж за определенный день

ПолеДатаСтоимостьИмя таблицыОТПУСКГрупповая операциягруппировкаSumУсловие отбора[Введите дату]

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

Access поддерживает специальный тип итоговых запросов, называемый перекрестным запросом или запросом кросс-таблиц. Такой запрос позволяет увидеть вычисляемые значения в виде перекрестной таблицы, напоминающей электронную таблицу. Если необходимо объединить данные в формате строк-колонок (двумерная таблица), то следует создать запрос кросс-таблицы. При проектировании запроса кросс-таблицы можно указать в качестве заголовков для колонок значения некоторых полей или выражений. Запросы кросс-таблиц позволяют более компактно, чем обычные запросы, индицировать данные, объединяя однотипную информацию. При создании перекрестного запроса необходимо в режиме конструктора запроса выполнить команду Запрос/ Перекрестный. Access добавляет в бланк QBE строку Перекрестная таблица. В этой строке для каждого поля перекрестного запроса может быть выбрана одна из четырех установок: Заголовки строк, Заголовки столбцов, Значение (выводимое в сетке перекрестного запроса) и Не выводить. Для перекрестного запроса нужно определить по крайней мере одно поле в качестве заголовка строки, одно поле в качестве заголовка столбца и одно поле значений. Каждое поле, являющееся заголовком строки или столбца, должно иметь в строке групповых операций установку Группировка. Для поля, которое в бланке запроса имеет установку Значение, нужно выбрать одну из групповых функций или ввести выражение, содержащее групповые функции. Как и в других типах итоговых запросов, для отбора данных, включаемых в набор записей, можно использовать дополнительные поля. Для таких полей необходимо выбрать установку Условия в строке групповых операций и Не выводить в строке Перекрестная таблица, а затем ввести Условия отбора. Условия отбора и сортировки можно указать для любого поля, используемого в качестве заголовка.

Пример 13. Определить итоговые суммы продаж по месяцам (столбцы таблицы) для каждого наименования товара (строки)

ПолеНазваниеМесяц:Month([Дата])СтоимостьИмя таблицыТОВАРГрупповая операцияГруппировкаГруппировкаSumПерекрестная таблицаЗаголовки строкЗаголовки столбцовЗначениеСортировкаПо возрастаниюПо возрастанию

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

3.8 Модификация данных с помощью запросов действий

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

Различают четыре типа запросов действия: запросы добавления, запросы удаления, запросы обновления и запросы создания таблицы.

Запрос добавления

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

Запрос удаления

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

Пример 14. Удалить сведения о продаже товаров за определенный месяц.

ПолеMonth([ОТПУСК]![Дата])Year(ОТПУСК[]![Дата])Имя таблицыУсловие отбора[Месяц удаления][Год удаления]

Запрос обновления

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

Пример 15. Увеличить цену товаров заданного типа на 20%

ПолеТипЦенаИмя таблицыТОВАРТОВАРОбновление[Цена]*1.2Условие отбора[Введите тип]

Запрос создания таблицы

Из динамического набора Dynaset, сформированного при выполнении запроса, можно создать таблицу, например для архивирования старых блоков данных, сохранения резервных копий таблиц или накапливания итоговой информации. Сначала следует создать запрос выбора и получить динамический набор данных Dynaset, заодно проверив при этом корректность запроса. Если Dynaset соответствует представлениям пользователя, следует превратить запрос-выборку в запрос на создание таблицы, выполнив команду Запрос/Создание таблицы в режиме конструктора запроса. В диалоговом окне Свойства запроса следует указать имя для создаваемой таблицы.

Лабораторная работа № 4. Разработка сложной формы для обработки данных

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

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

1.Создать запрос для подчиненной формы. Запрос должен включать следующие поля:

НомерПокупки из таблицы ОТПУСК - Поле первичного ключа дочерней таблицы;
КодПокупателя из таблицы ОТПУСК - Поле внешнего ключа дочерней таблицы;
КодТовара из таблицы ОТПУСК - Поле дочерней таблицы;
Количество из таблицы ОТПУСК - Поле дочерней таблицы;
Цена из таблицы ОТПУСК - Поле дочерней таблицы;
Стоимость: [Сделка]![Количество]*[Товар]![Цена] - вычисляемое поле.

В режиме SQL запрос Подчиненый выглядит следующим образом:

SELECT ОТПУСК.КодПокупателя, ОТПУСК.КодТовара,

ОТПУСК.Количестиво, ОТПУСК.Дата, ТОВАР.Цена,

[ТОВАР]![Цена]*[ОТПУСК]![Количестиво] AS Стоимость

FROM ТОВАР INNER JOIN ОТПУСК ТОВАР.КодТовара = ОТПУСК.КодТовара;

3.Создать форму для запроса Подчиненный.

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

4.Создать главную форму для таблицы ПОКУПАТЕЛЬ с помощью Конструктора.

Установить элемент ПолеСоСписком с помощью мастера., выбрав в первом окне диалога пункт Поиск записи в форме на основе значения, которое содержит ПолеСоСписком, выбрав КодПокупателя для связи и Имя для выбора.
Установить элемент Подчиненная форма/отчет, указав в свойствах элемента Данные свойство Объект -источник имя формы, созданной для подчиненного запроса. Тогда установятся значения для свойства Подчиненные поля - КодПокупателя и для свойства Основные поля -КодПокупателя
Установить элемент поле с именем Поле5 для указания общего количества заказанного товара и элемент поле с именем Поле7 для общей стоимости заказа. Установим свойства элементов Блокировка в значение Да и Доступ в значение Нет.
Добавим поле с именем Поле9, установим его свойство Данные в значение КодПокупателя и сделаем это поле невидимым (свойство Макет - Вывод на экран в значение Нет). Это поле потребуется для синхронизации перемещения по записям таблицы ПОКУПАТЕЛЬ.

Внешний вид формы показан на рис.

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

Для подсчета общего количество товара в заказе используем формулу:

Forms![Главная]![Поле5] = DSum("[Количестиво]", "[Подчиненный]", "Forms![Главная]![ПолеСоСписком0] = [Подчиненный]![КодПокупателя]")

Для подсчета общей стоимости заказа испаользуем формулу:

![Главная]![Поле7] = DSum("[Стоимость]", "[Подчиненный]", "Forms![Главная]![ПолеСоСписком0] = [Подчиненный]![КодПокупателя]")

Обработаем события главной формы: при открытии и текущая запись

При открытии формы установим в выпадающем списке имя первого клиента из таблицы ПОКУПАТЕЛЬ и пересчитаем общее количество и общую стоимость заказа.

Sub Form_Open(Cancel As Integer)

[ПолеСоСписком0] = 1![Главная]![Поле5] = DSum("[Количестиво]", "[Подчиненный]",

"Forms![Главная]![ПолеСоСписком0] = [Подчиненный]![КодПокупателя]")![Главная]![Поле7] = DSum("[Стоимость]", "[Подчиненный]",

"Forms![Главная]![ПолеСоСписком0] = [Подчиненный]![КодПокупателя]")

End Sub

При переходе на следующую запись в таблице ПОКУПАТЕЛЬ необходимо синхронно установить имя следующего клиента и также пересчитать общее количество и общую стоимость заказа.

Private Sub Form_Current()

[ПолеСоСписком0] = [Поле9]![Главная]![Поле5] = DSum("[Количестиво]", "[Подчиненный]",

"Forms![Главная]![ПолеСоСписком0] = [Подчиненный]![КодПокупателя]")![Главная]![Поле7] = DSum("[Стоимость]", "[Подчиненный]",

"Forms![Главная]![ПолеСоСписком0] = [Подчиненный]![КодПокупателя]")

End Sub

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

Sub ПолеСоСписком0_AfterUpdate()

' Поиск записи, соответствующей этому элементу управления..RecordsetClone.FindFirst "[КодПокупателя] = " &

Me![ПолеСоСписком0]

Me.Bookmark = Me.RecordsetClone.Bookmark![Главная]![Поле5] = DSum("[Количестиво]", "[Подчиненный]",