Работа в Microsoft Access

Размещено на http://www.allbest.ru/

1. MICROSOFT ACCESS

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

Средствами Access проводятся следующие операции:

* Проектирование базовых объектов информационных систем (ИС) - двумерных таблиц, с разными типами данных, включая поля объектов OLE. Например, прежде чем заполнить данными таблицу, надо создать её макет.

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

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

* Создание, модификация и использование производных объектов (форм, запросов и отчётов).

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

Объектом обработки MS-Access является файл базы данных, смеющий произвольное имя и расширение .mdb. В этот файл входят основные объекты MS-Access: таблицы, формы, запросы, отчёты, макросы и модули.



Рис. 3.4. Окно приложения Access

Загрузка Access обычно производится нажатой левой кнопкой мыши на значке Б. Д. на рабочем столе или из главного меню Пуск - Программы - Microsoft Access

После загрузки Access можно либо Создать новую базу данных (т. е. файл с расширением .accdb.), либо Открыть существующую базу данных в окошке справа. (рис.3.4).

Открыть существующую Б.Д. можно двойным щелчком на имени файла в любой папке, в которой содержится Б.Д и это, в свою очередь, вызовет автоматическое открытие приложения MS Access.

1.1 Окно базы данных

При создании новой базы данных или открытии существующей приложение Access открывает окно базы данных (рис. 3.5), в котором создаются или редактируются объекты текущей базы данных.

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

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

Назначение каждой кнопки объектов:

* Таблица - это базовый элемент хранения информации в базе данных Access;

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

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

* Отчеты -это инструмент предназначенный для представления данных из таблиц или запросов в удобной для восприятия форме или в определенном формате;

* Страницы - это специальный тип приложений Access, предназначенных для публикации в Internet данных, которые хранятся в базах данных Access;

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

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

Любой объект (таблицу, запрос, форму, отчёт) можно создавать либо вручную (в режиме конструктора), либо с помощью мастера.

Рекомендация для начинающих пользователей MS-Access:

* таблицы и запросы создавать вручную;

* формы и отчёты создавать с помощью мастера.

Рис. 3.5. Окно базы данных Access

1.2 Создание таблиц

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

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

Создание базы начинается с создания первой таблицы.

Для создания таблицы необходимо выбрать Создание- Таблицa в горизонтальном меню



Возможные режимы создания новой таблицы:

· Режим таблицы

· Конструктор таблиц

· Шаблоны таблиц

· Списки SharePoint

· Импорт (меню Внешние данные- Импорт)

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

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

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

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

Второй способ создания таблиц - это один из режимов базы данных:

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

Создание таблицы с помощью списка SharePoint;

Создание таблицы путем ввода данных;

Использование шаблона.

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

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

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

Имя поля должно быть уникальным, содержать не более 64 символов, включая пробелы, причем имя поля не должно начинаться с пробела. Имена полей могут содержать любые символы, за исключением «.»; «!»; «`»; «[»; «]».

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

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

* Числовой - тип данных для хранения числовых значений, используемых в математических расчетах. Данные этого типа могут содержаться в 1, 2, 4, 8 байтах в зависимости от значения поля Размер поля;

* Поле MEMO - специальный тип данных для хранения больших объемов информации, по объему превышающей 255 символов. Это поле может содержать до 65535 символов;

* Дата/время - тип данных для хранения календарных дат и текущего времени;

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

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

* Поле объекта OLE - содержит ссылку на объект, например документы Microsoft Word, Microsof Excel, звук, изображение и другие виды объектов.

Ограничено дисковым пространством.

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

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

Наиболее важными являются следующие свойства:

* размер поля - определяет максимальную длину данных, которые могут содержаться в данном поле;

* формат поля - определяет способ формирования данных в данном поле таблицы;

* число десятичных знаков -определяет количество знаков после запятой для полей числового и денежного типа;

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

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

* значение по умолчанию - указывает значение, автоматически вводимое в

поле при создании новой записи;

* условие на значение - ограничение. накладываемое на вводимые в данное поле данные;

* сообщение об ошибке - это текст сообщения, которое будет выводиться в случае нарушения условия на значения;

* обязательное поле - определяет, является ли ввод данных в этом поле обязательным;

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

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

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

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

Наличие индекса помогает Access быстрее находить и сортировать записи.

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

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

Рис. 3.6. Окно создания структуры таблиц в режиме Конструктора

1.3 Ввод и редактирование данных

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

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

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

Закончив ввод или модификацию данных в конкретном поле, нажмите ТАВ или ENTER (или щёлкните мышью в другой ячейке таблицы).

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

Щелчок на этом маркере выделяет всю запись и готовит её к копированию, перемещению, удалению (рис. 3.7).



Рис. 3.7. Окно таблицы для ввода данных

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

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

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

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

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

В табл. 3.1 приведены способы редактирования данных.

Способы редактирования данных Таблица 3.1

Перемещение внутри поля	Нажатиями клавиш -> и <
Вставка значения в поле	Выбрать позицию вставки и ввести зна-чение
Выделение содержимого поля целиком	Нажать F2 или двойной клик
Замена значения новым	Выделить поле целиком и ввести новое
Замена из предыдущего поля	Ctrl+'(апостроф
Замена значением по умолчанию	Ctrl+Alt + пробел
Вставка разрыва строки (текст, Мемо)	Ctrl+Enter
Сохранение текущей строки	Shift+Enter или переход к другой строке
Вставка текущей даты	Ctrl+;(точка с запятой)
Вставка текущего времени	Ctrl+:(двоеточие )
Добавление новой записи	Ctrl++(плюс)
Удаление записи	Ctrl+-(минус)
Переключение кнопок опций	Пробел
Отмена изменений тек записи	Esc или кнопка отмены

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

Не редактируемые поля Таблица 3.2

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

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

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

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

Создание связей выполняется следующим образом:

С помощью меню: Работа с базами данных- Показать или скрыть- Схема данных.

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

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

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

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

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

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

Если вы включили механизм поддержки целостности, он должен одновременно указать тип связи: «один к одному» или «один ко многим».

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

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

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

Для того чтобы обеспечить целостность данных надо включить флажки:

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

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

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

Ограничения для баз данных:

Таблица может содержать не более 255 полей.

Таблица не может иметь более 32 индексов.

Составные индексы не могут включать более 10 полей, причём общая их длина не должна превышать 255 байтов.

Общая длина строка таблицы без учёта полей MEMO и объектов OLE не может превышать примерно 2 кб.

Размер поля MEMO ограничивается 1 Гб, но вы не можете вывести поле MEMO размером >64 кб в форме или режиме таблицы

Размер объекта OLE не должен превышать 1 Гб.

На число записей в таблице не существует ограничений, но размер файла базы данных Access не может превышать 1 Гб.

1.5 Изменение внешнего вида таблицы

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

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

Операции над таблицами:

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

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

Поиск и замена данных предусмотрена в режиме Таблицы. Она дает возможность поиска и замены данных по образцу либо в контекстном меню, либо во всей таблице. Для этого нужно выполнить команду Главная- Найти. Появиться диалоговое окно Поиск и Замена, которое имеет соответствующие вкладки для поиска и замены данных.. Затем нужно ввести в поля соответствующие значения и выполнить указанные действия.

Фильтрация - это способ быстрого отбора записей в режиме Таблицы в соответствии с заданным критерием. После фильтрации информация отображается в результирующей таблице.

Виды фильтров: Фильтр по выделенному; Обычный фильтр; поле Фильтр для; Расширенный фильтр.

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

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

Чтобы задать более подробное условие для фильтрации используется Расширенный фильтр (рис.3.8).

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

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



Рис. 3.8. Диалоговое окно создания фильтра

Порядок действий:

* выберите в меню Главная команду Сортировка и Фильтр- Дополнительно- Расширенный фильтр;

* перенесите названия полей в нижнюю область окна в строку Поле,

* в строках Сортировка и Условие отбора задайте нужные параметры;

* выполните команду Применить фильтр или др. способом.

(Применить фильтр на панели инструментов).

1.6 Обработка данных, содержащихся в таблице с помощью запросов

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

Access позволяет создавать запросы двух типов: QBE-запросы, SQL- запросы.

QBE-запросы (Query By Example) - запросы, строящиеся с помощью конструктора запросов, представляющего собой графический инструмент для создания запросов по образцу.

SQL-запросы - запросы, строящиеся при помощи унифицированного набора инструкций SQL(Structurred Query Language - структурированный язык запросов).

Все запросы делятся на две группы: запросы на выборку, запросы- действия.

Запросы-выборки осуществляют выборку данных из таблиц в соответствии с заданными условиями. К этой группе запросов относятся следующие.

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

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

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

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

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

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

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

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

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

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

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

В Access можно создавать запросы с помощью при помощи Мастера запросов и с помощью Конструктора.

Для создания запросов при помощи Мастера имеются следующие мастера:

· Простой запрос;

· Перекрестный запрос;

· Повторяющиеся записи;

· Записи без подчиненных;

При создании запроса с помощью мастера производится пошаговое выполнение действий в диалоговом окне Мастера запросов.

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

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

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

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

Рис. 3.9 Бланк заполнения запросов по образцу



Назначение строк в бланке Конструктора запросов:

1-я строка Поле предназначена для ввода имени поля запроса и обозначения поля источника данных или вычисляемого выражения;

2-я строка Имя таблицы предназначена для задания имени таблицы источника данных;

3-я строка Сортировка предназначена для указания порядка сортировки в данном поле. Может принимать следующие значения: по возрастанию, по убыванию, отсутствует;

4-я строка Вывод на экран содержит флажок, указывающий на то, будет ли выводиться соответствующее значение поля;

5-я строка Условие отбора содержит критерий, по которому будет происходить отбор записей в результирующую таблицу.

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

При создании макета-запроса, в общем случае, необходимо выполнить четыре базовые операции:

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

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

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

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

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

Сначала надо выполнить стандартные операции:

В горизонтальном меню выбрать Создание выбрать Другие- Конструктор Запросов, откроется диалоговое окно Добавление таблицы (рис 3.10).

В окне Добавление таблицы выделите нужную таблицу, и нажмите кнопку Добавить.

Рис. 3.10. Диалоговое окно для добавления таблиц при создании запросов

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

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

Строка Имя таблицы заполняется автоматически при перетаскивании поля левой кнопкой мыши.

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

Условие отбора. По каждому полю можно создать своё условие отбора.

Для добавления в строку Условие отбора значения вы можете использовать любое выражение, которое способно принимать значение True или False. При создании условия используют операции сравнения: больше (>), меньше (<), больше либо равно (>=), меньше либо равно (<=), не равно (<>). Условие отбора может осуществляться по одному полю или по несколько полям одновременно. Если отбор осуществляется по нескольким полям и условия для этих полей записаны в донной строке, то между этими условиями выполняется логическая операция AND, между условиями, записанными в разных строках, выполняется логическая операция OR. Может быть также использована еще одна логическая операция отрицания NOT. Если выражения связаны оператором AND, результат будет принимать значение True лишь тогда, когда истины все выражения, связанные этим оператором. В отличие от этого, операция OR истинна, когда истинно хотя бы одно или сразу оба выражения. Операция NOT выдает значение, противоположное обрабатываемому выражению, - истину, если выражение ложно, и ложь, если выражение истинно. Кроме обычных опера торов сравнения, MS Access предоставляет четыре специальных оператора, полезных для отбора данных: BETWEEN, IS, IN, LIKE. Between - определяет, находится ли числовое значение в определенном диапазоне значений (например Between (100) AND (100)). Is - при использовании вместе с нулем (Null) определяет, является ли значение нулем или нет (например Is Null или Is Not Null). In - определяет, является ли строковое значение элементом списка значений (например, In(“Москва”, ”Киев”,”Санткт-Петербург”). Like - определяет, начинается ли строковое значение с указанных символов (например, Like “Ив*” или Like “db??”).Где * - соответствует любому количеству символов , ? -- соответствует одному символу, # - соответствует одной цифре. Кроме того константы даты и времени выделяются символами # (например, #26/04/2005#).

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

Сохранение запроса осуществляется закрытием окна бланка запроса по образцу .При закрытии запроса введите его имя.

Запрос с параметром - это специальный тип запросов позволяет пользователю самому ввести критерии отбора данных на этапе запуска запроса. Этим приёмом обеспечивается гибкость работы с базой. Запрос с параметром отличается от обычного запроса тем, что в параметр запроса вводится в структуре в то поле, по которому осуществляется отбор. Для этого параметр запроса, заключается в квадратные скобки и записывается в строке Условие отбора. По умолчанию Access назначает параметром запроса текстовый тип данных. Если нужно изменить тип данных параметра, то необходимо выбрать Конструктор, введите название параметра и его тип. Название параметра должно точно соответствовать названию

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

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

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

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

Например

сумма: [цена]*[количество]

где: сумма: - название нового поля,

[цена] - название первого поля, участвующего в вычислении,

* - знак операции умножения,

[количество] - название второго поля, участвующего в вычислении.

Для определения вычисляемых столбцов введите выражение в строку Поле. Выражение вводится непосредственно в ячейку, заметьте, что имена полей заключаются в квадратные скобки. Можно использовать построитель выражений для создания вычисляемого поля, для этого необходимо щелкнуть на кнопке Построить или использовать кнопки Shift+F2- и в диалоговом окне вводят формулу, при нажатии кнопки Ок формула переносится в бланк запроса.

Новое вычислительное поле может быть отображено в результирующей таблице отсортированное.

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

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

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

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



Рис. 3.11. Бланк запроса с использованием операций группировки

Чтобы запустить запрос для предварительно просмотра нажмите на кнопку Выполнить, которая выдаёт результирующую таблицу с итоговыми данными.

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

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

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

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

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

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

Логика использования запросов на изменение такая:

* создаётся запрос на выборку, который отбирает данные из разных таблиц или сам создаёт новые данные путём вычисления;

* после запуска образуется временная результирующая таблица;

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

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

1. Создаётся запрос на выборку, в который войдут все поля базовой таблицы плюс вычисляемое поле.

2. При нажатии на кнопку Вид - можно убедиться, что запрос работает, как положено и создаёт результирующую таблицу, более полную, чем базовая.

3. Далее использование пункта меню Запрос, который доступен только в режиме конструктора, и выбор команды Создание таблицы из списка.

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

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

1.7 Создание формы и работа с ней

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

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

Работа с формами может происходить в трех режимах: в режиме Формы, в режиме Таблицы, в режиме Конструктора. Выбрать режим работы можно при помощи кнопки Вид панели инструментов Конструктор форм либо с помощью команды меню Создание.

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

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

Создание формы:

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

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

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

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

Разделенная форма отображает данные в режиме таблицы.

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

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

Рис. 3.12. Окно создания формы

Этапы создания формы с помощью мастера:

1. кнопка Создание;

2. выбрать Таблицу - источник данных;

3. выбрать режим Мастер форм (Создание- Другие формы- Мастер форм)

4. выбор полей, данные для которых можно будет вводить в форме

(кнопки > или >>);

5. выбор внешнего вида формы (один из 4-х): в один столбец, ленточный, табличный, выровненный или в один столбец;

6. выбор фонового рисунка;

7. задание имени формы.

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

Конструктор форм - позволяет конструировать форму пользователем в окне конструктора форм (рис. 3.13).

Структуру формы составляют её разделы с элементами управления:

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

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

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

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

* примечание формы - отображает внизу последней экранной страницы формы.

Рис. 3.13. Окно создания формы в режиме конструктора форм

Форма может содержать все разделы или некоторые из них.

Линии, разделяющие разделы в форме, перетаскиваются по вертикали мышкой.

Разделы: область заголовка и примечания имеют чисто оформительское назначение.

Всё, что содержится в области данных, является элементами управления. Все элементы управления находятся на панели Элементы управления.

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

Для создания заголовков существует элемент Надпись . Щелкнув на этом элементе, а потом на форме - получается текстовая рамка, в которую можно вводить произвольный текст и затем нажать клавишу ENTER.

Элемент Поле -, при его использовании в нём отображается содержимое одного из полей таблицы, на которой основана форма.

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

Для этого служит инструмент Выбор объектов . Выделенный объект управления выделяется 8 маркерами. При наведении на маркер указатель мыши меняет форму - изображение открытой ладони. В этот момент рамку можно перемещать. Когда объект выделен, можно изменить шрифт, метод выравнивания текста и другие элементы форматирования.

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

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

Присоединённая надпись - называется так, поскольку перемещается вместе со всеми элементами управления.

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

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

1.8. Создание и печать отчетов

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

* представить данные в удобной для чтения и анализа форме,

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

* включить в отчет и напечатать графические объекты (например, диаграммы).

Ниже приведены виды отчетов:

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

* Многоколонный отчет - создается из отчета в одну колонку и позволяет вывести данные отчета в несколько колонок.

* Табличный отчет - отчет, имеющий табличную форму.

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

* Перекрестный отчет - строится на основе перекрестных запросов и содержит итоговые данные.

* Составной отчет - отчет, имеющий сложную структуру, включающий один или несколько отчетов.

* Отчет, полученный слиянием документов с Word (составной документ).

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

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

Отчёты состоят из разделов, а разделы могут содержать элементы управления. Но, в отличие от форм, разделов в отчётах больше, а элементов управления, наоборот, меньше.

Структура отчета

Отчет может состоять из следующих разделов (рис 3.14).

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

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

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

* Область данных - предназначена для отображения записей источника данных отчета.

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

* Нижний колонтитул - выводится внизу каждой страницы; содержит, например, номер страницы отчета, дату печати отчета и т. д.

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



Рис. 3.14. Структура отчета в окне Конструктора

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

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

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

Этапы создания отчетов:

* открыть окно базы данных;

* в окне базы данных перейти на вкладку Создание;

* в окне диалога Отчеты выбрать из предложенного списка необходимый вариант.

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

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

microsoft access таблица запрос

,

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

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

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

...