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

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

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

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

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

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

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

· Повышается вероятность появления ошибки (при вводе и обработке).

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

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

Заботясь об уменьшении объема базы, не следует забывать о ее надежности. Access напомнит об этом. Если, к примеру, в файле наименований товаров дается ссылка на поставщика с номером 3, Access не позволит удалить запись с номером 3 в файле поставщиков.

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

Работа с таблицами

Теоретические сведения

Создание базы данных с помощью мастера

1. При запуске Microsoft Access открывается диалоговое окно, в котором предлагается создать новую базу данных или открыть существующую. Если появляется это диалоговое окно, выберите параметр Запуск мастера, а затем нажмите кнопку ОК.

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

2. Выберите необходимый шаблон базы данных и дважды нажмите кнопку мыши.

3. Укажите имя и каталог для создаваемой базы данных.

4. Чтобы начать создание новой базы данных, нажмите кнопку OK.

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

1. На вкладке Таблица нажмите кнопку Создать.

2. Дважды щелкните элемент «Мастер таблиц».

3. Следуйте инструкциям, выдаваемым в диалоговых окнах мастера таблиц.

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

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

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

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

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

Типы данных, используемые в поле таблицы

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

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

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

Ключевое поле называют также первичным ключом, а внешний ключ – вторичным ключом.

Существует три типа связей:

· один-ко-многим;

· один-к-одному;

· много-ко-многим.

Связь типа «один-ко-многим» является наиболее часто используемым типом связи между таблицами. В такой связи каждой записи в таблице A могут соответствовать несколько записей в таблице B, а запись в таблице B не может иметь более одной соответствующей ей записи в таблице A.

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

При отношении «один-к-одному» запись в таблице A может иметь не более одной связанной записи в таблице B и наоборот. Этот тип связи используют не очень часто, поскольку такие данные могут быть помещены в одну таблицу. Связь с отношением «один-к-одному» используют для разделения очень широких таблиц, для отделения части таблицы по соображениям защиты, а также для сохранения сведений, относящихся к подмножеству записей в главной таблице.

При отношении «многие-ко-многим» одной записи в таблице A могут соответствовать несколько записей в таблице B, а одной записи в таблице B несколько записей в таблице A. Такая схема в Microsoft Access реализуется только с помощью третьей (связующей) таблицы, ключ которой состоит из по крайней мере двух полей, которые являются полями внешнего ключа в таблицах A и B.

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

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

2. Переключитесь в окно базы данных и нажмите кнопку Схема данных на панели инструментов.

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

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

5. Для связывания полей выберите поле в одной таблице и перетащите его на соответствующее поле во второй таблице.

Для связывания сразу нескольких полей переместите их при нажатой клавише CTRL.

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

· поле счетчика можно связывать с числовым полем, если в последнем в свойстве Размер поля задано значение «Длинное целое»;

· поле счетчика можно связать с числовым, если для обоих полей в свойстве Размер поля задано значение «Код репликации».

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

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

7. Для создания связи нажмите кнопку Создать.

Целостность данных и каскадные операции

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

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

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

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

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

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

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

Создание новой таблицы путем импортирования из другой базы данных

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

Проектирование таблицы Модели

Структура таблицы Модели

Характеристики полей таблицы

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

Данные для заполнения таблицы Модели

Проектирование таблицы Клиенты

Структура таблицы Клиенты

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

Структура таблицы Купля автомобилей

Поле Номер заказа

Тип данных Счетчик

Поле Номер клиента

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

Организация таблиц в режиме таблицы

Проектирование таблицы Мастерские

Организация таблиц при помощи мастера создания таблиц

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

Связывание таблиц базы данных

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

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

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

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

Теоретические сведения

Способы создания форм

Механизм создания формы

Изменение макета формы в режиме конструктора

Изменение вида формы

Проектирование формы на основе таблицы Клиенты

Автоматическое создание формы

1. В окне базы данных выберите закладку Формы и нажмите кнопку Создать. В появившемся окне Новая форма выберите строку Автоформа (в столбец). В качестве источника данных выберите таблицу Клиенты. Нажмите кнопку ОК.

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

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

Доработка макета формы

Создание диаграммной формы по таблице Модели

Проектирование составной формы

Создание составной формы с использованием мастера

Оформление составной формы

Встраивание в форму кнопок

Изменение свойств формы

1. Находясь в режиме конструктора, в меню «Правка» выберите команду «Выделить форму».

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

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

4. Выведите диалоговое окно изменения свойств подчиненной формы и в строке «Блокировка» установите Да. Тем самым Вы запретите пользователю вносить изменения в подчиненную форму. Перейдите в режим формы и убедитесь в этом.

Теоретические сведения о запросах

Запросы на выборку и их использование

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

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

1. В окне базы данных выберите вкладку Запросы и нажмите кнопку Создать.

2. В окне диалога Новый запрос выберите в списке Конструктор и нажмите кнопку OK.

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

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

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

Не начинайте построение запроса, если хотя бы одна таблица не связана ни с одной из остальных таблиц, участвующих в запросе!

6. Добавьте поля в запрос путем переноса имен полей с помощью мыши из списка полей в бланк запроса.

7. При необходимости выполните следующие действия:

· введите в запрос условия отбора;

· задайте порядок сортировки;

· создайте вычисляемые поля;

· создайте итоговые поля и т.д.

8. Сохраните запрос.

9. Просмотрите результат с помощью открытия или запуска запроса.

Открытие и запуск запроса

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

Отображение или скрытие поля в результатах запроса

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

Сортировка записей в запросах

1. Откройте запрос в режиме конструктора.

2. Для того чтобы провести сортировку по нескольким полям, сначала разместите поля в бланке в порядке выполнения сортировки. Помните, что Access начинает сортировку с самого левого поля в строке Сортировка, затем переходит к следующему, расположенному справа от него, и т. д. Например, чтобы выполнить сортировку сначала по полю "Фамилия", а затем по полю "Имя", поле "Фамилия" должно стоять в бланке слева от поля "Имя".

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

4. Для сортировки по дополнительным полям повторите шаги 2 и 3.

Построение запросов по базе данных «автомобили»

Построение запросов с помощью конструктора по одной таблице

Задание 1: вывести список всех мастерских высшей категории, отсортированный в порядке убывания цены ремонта.

1. Откройте базу данных Автомобили и выберите вкладку «Запросы».

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

3. Выберите таблицу Мастерские и закройте окно добавления таблиц.

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

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

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

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

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

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

Задание 2: построить запрос «Авто с ручной коробкой передач» по таблице Модели (поля Модель (сортировка по возрастанию), Цвет, Количество дверей, Обивка, Коробка передач (ручная и поле скрыть).

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

Построение запросов по нескольким таблицам

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

1. Откройте базу данных Автомобили и выберите вкладку «Запросы».

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

3. Нужные сведения можно получить, только лишь используя две таблицы. Выберите последовательно таблицы Модели, Клиенты и Купля автомобилей, затем закройте окно добавления таблиц. Обратите внимание на то, что таблицы автоматически связались (так как связывание таблиц было предварительно осуществлено).

4. Необходимо поместить в бланк запроса поля Фамилия, Имя из таблицы Клиенты и Модель, Цвет из таблицы Модели.

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

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

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

Задания

1. Вывести список всех клиентов с указанием марки купленных ими автомобилей.

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

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

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

5. Вывести список клиентов из Киева или из Харькова с указанием марки и заводской цены купленных ими автомобилей.

6. Вывести список заказов с указанием фамилии клиента, со скидкой от 5% до 20% в порядке убывания транспортных расходов.

7. Вывести список белых или черных автомобилей с кожаной обивкой, проданных всем клиентам, кроме харьковчан.

Добавление вычисляемых полей в запросы

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

6. Чтобы не создавать запрос заново, воспользуемся результатами уже имеющегося. Откройте созданный в предыдущем задании запрос Покупатели авто (учет цвета) и, выбрав команду «Сохранить как/Экспорт» из меню «Файл», сохранить его под новым именем Итоговые цены автомобилей.

7. Удалите из бланка запроса поле Цвет и добавьте поля Заводская цена, Транспортные расходы, Предпродажная подготовка и Скидка.

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

([Заводская цена] + [Транспортные расходы] +

[Предпродажная подготовка])*(1 – [Скидка])

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

10.Обратите внимание на заголовок вычисляемого поля. Чтобы дать полю более понятное название, выделите его в режиме конструктора, вызовите контекстное меню правой кнопкой мыши, выберите пункт «Свойства» и в появившемся диалоговом окне в строке подпись напишите «Продажная цена». Здесь же можно установить формат поля (в данном случае денежный) и требуемое число десятичных знаков.

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

Задание 2: построить запрос «Срок ремонта», позволяющий для каждого клиента посчитать число дней, в течение которых его автомобиль находился в ремонте.

На основе данного запроса построить ленточную автоформу. Установить тонкую нерастяжимую границу и скрыть кнопки сворачивания и разворачивания формы (использовать диалоговое окно «Свойства формы»).

Группировка данных в запросах

Запросы по базе данных борей

Импорт таблиц из базы данных Борей

Схема данных базы данных Борей

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

Простые запросы по базе данных Борей (А)

1. Вывести список всех товаров (поля Марка и Цена) с ценой не менее 80000.

2. Получить список всех товаров (указать марку, описание, количество и код заказа), заказы на которые исполнены со скидкой 10%.

3. Найти всех клиентов из Берлина и Мадрида.

4. Получить список всех товаров с ценой от 100000 до 120000. включительно.

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

6. Получить список всех заказов, исполненных во второй половине 1997 г.

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

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

9. Найти всех клиентов, находящихся не в Бразилии и не в США, для которых не указана область проживания. Список упорядочить в алфавитном порядке городов проживания клиентов.

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

11. Вывести список марок товаров, минимальный запас которых равен нулю, а поставки их не прекращены. Поля с минимальным запасом и прекращением поставок на экран не выводить.

12. Вывести список всех клиентов, работающих в должности бухгалтера, заказы которых еще не исполнены.

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

14. Вывести список кодов заказов с расчетом числа дней выполнения каждого заказа (Дата исполнения-Дата размещения).

15. Для каждого сотрудника вывести список просроченных заказов (построить вычисляемое поле Дата Исполнения-Дата Назначения) и в строке Условие отбора ввести условие >0.

16. Для каждого заказа рассчитать его стоимость Цена*Количество*(1-Скидка). Найти самый дорогой заказ (Подсказка: для этого отсортируйте данные по убыванию и установите значение 1 в свойстве).

17. Аналогично предыдущему запросу рассчитать стоимость только тех заказов, которые были исполнены в 1997 году.

18. Вывести список всех поставщиков из Франции, поставивших товары (указать марку), цена которых от 80 000 до 400 000.

19. Вывести список всех товаров, поставки которых не прекращены, заказы на которые размещены не позднее 1 января 1998 г.

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

21. Вывести список всех товаров, доставка которых производилась не по почте, а страна получателя заказа – Швеция или Великобритания.

22. Вывести список товаров, по которым ожидаются поступления из Канады или Японии, и рассчитать ожидаемую стоимость товаров каждой марки (Цена*Ожидается).

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

Параметрические запросы (Б)

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

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

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

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

5. Организуем такой запрос, который позволил бы выводить список клиентов, работающих в той должности, которая будет указываться пользователем перед выполнением запроса. Для этого в строке Условие отбора поля Должность впишите фразу в квадратных скобках [Укажите должность клиента]. Говорят, что для поля Должность такими действиями Вы задали параметр.

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

7.Иногда требуется ввести не точное значение параметра, а интервал значений. Пусть, например, следует для каждого клиента вывести коды заказов, которые были размещены позже вводимой пользователем даты. После добавления в запрос необходимых таблиц и полей в строке Условие отбора поля Дата размещения следует вписать фразу
>[Позже какой даты был размещен заказ?]. Просмотрите в режиме таблицы результат.

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

[Введите первую страну] OR [Введите вторую страну]

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

Задания (Б)

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

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

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

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

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

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

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

Использование шаблонов
при организации запросов (В)

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

* любое количество символов;

? один любой символ;

# любая одиночная цифра от 0 до 9;

[список] любой одиночный символ, входящий в список (например, [A-Д] это буква А,Б,В,Г или Д); используется в сочетании с шаблонами * или ?;

[!список] любой одиночный символ, не входящий в список (например [!A-Б] – это любая буква, только не А и не Б); также используется в сочетании с шаблонами * или ?.

Задания

1. Вывести список всех поставщиков, страна которых начинается на букву Н, а минимальный запас поставляемых ими товаров больше нуля. После перехода в режим таблицы обратите внимание на то, что в строке Условие отбора перед построенном Вами шаблоном появился оператор Like. Этот оператор необходим при конструировании шаблонов, но в данном случае Access добавил его в Ваш запрос самостоятельно.

2. Вывести список марок товаров, количество которых на складе выражается трехзначным числом.

3. Вывести список клиентов, название которых начинается буквами A, B, C, D, E. Выполнить задание двумя способами, вначале используя логическую функцию OR, а затем – шаблон Like "[A-E]*".

4. Вывести список клиентов, в телефоне которых отсутствует код (т.е. номер телефона не начинается открывающей скобкой). Используйте шаблон Like "[!(]*".

5. Используя шаблоны, вывести список клиентов, которые разместили свои заказы в декабре 1997 года.

6. Аналогично предыдущему запросу, вывести список клиентов, которые разместили свои заказы в апреле 1998 года, но их заказы до сих пор не исполнены.

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

Запросы с группировкой данных
(итоговые запросы) (Г)

1. Посчитать число заказов для каждой категории товаров.

2. Посчитать число товаров в каждой категории.

3. Вывести список всех марок товаров, средняя стоимость доставки которых не менее 100 руб.

4. Посчитать число всех заказов, оформленных для клиентов из Мехико.

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

6. Для каждого сотрудника старше 40 лет посчитать число клиентов.

Указание. Для вывода сотрудников старше 40 лет создать вычисляемое поле (Now()-Сотрудники![ДатаРождения])/365 и в качестве условия отбора ввести условие >40.

Итоговые запросы с использованием условия (Д)

1. Вначале организовать обычный итоговый запрос «Число заказов у клиентов»:
Для каждого клиента посчитать количество оформленных им заказов. Список упорядочить по убыванию числа заказов.
Исполнение. Добавить в запрос две таблицы Клиенты и Заказы, в бланк запроса добавить поле Название из первой таблицы и поле Код Заказа – из второй таблицы. Добавить строку Групповые операции, в первом поле оставить значение «группировка», а во втором установить значение Count. Просмотреть результат, переключившись в режим таблицы.

2. Доработаем данный запрос. Следующая задача поставлена так:
Вывести список клиентов, которые оформили более 10 заказов.
Исполнение. В полученном в предыдущем задании запросе в поле Код заказа в строке Условие отбора добавить условие: >10.

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

Исполнение. Помимо двух имеющихся полей, добавить поле Дата Исполнения из таблицы Заказы. В строке Групповая операция в данном поле появится значение «группировка». Переключиться в режим таблицы и убедиться, что ограничение на дату не выполняется.
В строке Групповая операция изменить значение «группировка» на значение «условие». В строке Условие отбора вписать условие: >=1/06/97. Переключиться в режим таблицы и убедиться в правильности выполнения запроса.

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

Используя данную технику, организовать следующие запросы:

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

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

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

4. Для каждого сотрудника посчитать среднюю стоимость доставки товаров, отосланных в США или в Канаду.

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

6. Указать, сколько раз каждая марка товаров попала в заказы, которые были размещены в первой половине 1994 года.

7. Для каждого клиента посчитать сумму, уплаченную за каждый заказ (организовать вычисляемое поле Цена*Количество*(1-Скидка)), а затем вывести список клиентов с указанием суммарной стоимости всех произведенных заказов. Список упорядочить в порядке убывания уплаченной суммы. Ввести условие на страну получателя – не Швеция и не Италия.

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

9. Для каждого сотрудника посчитать общую сумму выручки от всех заказов (вычисляемое поле Цена*Количество*(1-Скидка)) +Стоимость доставки), страна получателя которых – Франция или Италия.

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

11. Посчитать общую стоимость каждой марки товаров, поставленных в Германию не по почте (учесть цену единицы товара, количество, скидку и стоимость доставки). Вывести первые 25% самых прибыльных марок.

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

Использование параметров и шаблонов
в итоговых запросах (Е)

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

2. Для каждого сотрудника, фамилия которого начинается на букву “Б”, а город проживания вводится пользователем, указать число оформленных заказов.

3. Вывести список всех товаров, количество которых на складе превышает 30, а скидка при продаже вводится пользователем перед выполнением запроса.

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

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

6. Вывести список поставщиков с указанием числа клиентов, сделавших заказы на поставленные товары той или иной категории. Категория товаров вводится пользователем перед выполнением запроса.

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

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

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

Примечание. При создании параметрических запросов Access по умолчанию присваивает полю с параметром текстовый тип. Если организовывать параметр для числового поля, запрос работает неправильно. Например, при выполнении таких запросов 2>10, 33<100 и т.д. Чтобы избежать появления таких ошибок, воспользуйтесь одним из следующих методов:

a) Перед параметром вставьте функцию Val, например, Val([Введите цену]).

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

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

Построение запросов на основании запросов (Ж)

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

Указание: Если создать обычный итоговый запрос с подсчетом числа марок товаров для каждого поставщика, то полученные числа окажутся больше реальных, поскольку одинаковые марки товаров будут посчитаны многократно. Поэтому сначала надо построить запрос, в котором эти одинаковые марки товаров будут сгруппированы в одну строку (Поля Поставщики.Название и Товары.Марка и группировка по обоим полям). Полученный запрос сохранить и закрыть. Затем организовать новый запрос и в качестве источника данных добавить не таблицу, как мы это делали ранее, а запрос.

2. Для каждого клиента определить, каким количеством различных фирм по доставке он пользовался. Кто реже всего менял способ доставки заказов?

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

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

5. Для каждого сотрудника посчитать число обслуженных им клиентов.

Указание: Создать запрос с группировкой по полям Фамилия сотрудника и Название клиента. На основании этого запроса создать еще один запрос с группировкой по полю Фамилия сотрудника и функцией Count по полю Название клиента.

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

7. Определить, кто из сотрудников чаще всего оформлял заказы без скидки (в % к общему числу оформленных каждым сотрудником заказов).

Построение запросов с записями без подчиненных (З)

1. Найти клиентов, не сделавших ни одного заказа.
Исполнение:

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

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

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

· Попробовать в качестве условия отбора в столбце Код заказа записать Nullили IsNull. Просмотреть результат.

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

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

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

4. Вывести список клиентов, которым ни разу не просрочили заказ.
Указание:

· Сначала построить запрос, позволяющий вывести всех клиентов, которым хоть раз просрочили заказ.

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

Вывести список сотрудников, у которых время просрочки заказа ни разу не превышало 10 дней.

Вывести список клиентов, которые не размещали свои заказы позднее 1 января 1997 года.

7. Вывести список клиентов, которые получали свои заказы только по почте.

Указание:

· Сначала сделать запрос с выводом клиентов, хоть раз получивших свои заказы не по почте. Этих клиентов надо будет из итогового списка убрать.

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

· Соединив полученный запрос с первым запросом по данной теме, вычленить только тех клиентов, которые заказы производили, но получали их только по почте.

Вывести список поставщиков, поставляющих только напитки.

Модифицирующие запросы (Е)

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

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

* запросы удаления,

* запросы добавления,

* запросы обновления.

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

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

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

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

Группировку не вводить!

Создание отчетов

Теоретические сведения

Создание ленточного автоотчета

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

Исполнение. Щелкнуть по вкладке «Отчеты», кнопка «Создать», в качестве источника данных выберите запрос «Товары с ценой выше средней». Последовательно выберите сначала опцию «Автоотчет: в столбец» , а затем «Автоотчет: ленточный». Дайте отчетам подходящие имена. Проанализируйте, чем отличаются эти два типа отчетов, в чем их удобства и неудобства.

Доработка отчета в режиме конструктора

Работа с панелью элементов при модернизации отчетов

Группировка данных в отчетах

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

Создание базы данных на основе имеющихся образцов