По результатам проектирования базы данных в электронной базе данных Товары созданы таблицы Товары, Поставщики, Менеджеры по поставкам, Поставка товара, Клиенты, Менеджеры по продажам, Продажа товара, Счет-фактура выданный и Счет-фактура полученный. Всего девять таблиц. Необходимо проверить связанные поля. Они должны быть одинакового типа (кроме поля Счетчик),одинакового размера и иметь отметки в индексированном поле в разделе Свойствополя.
Для того чтобы было удобно просматривать, создавать, удалять и модифицировать связи между таблицами, в Microsoft Access используется схема данных.
Чтобы открыть схему данных, необходимо выполнить команду Сервис, Схема данных. По умолчанию схема будет содержать все таблицы со связями (рис. 8).
Рис. 8. Схема данных
Чтобы убрать какую-либо таблицу из схемы данных, необходимо щелкнуть правой кнопкой мыши на любом месте этой таблицы и из контекстного меню выбрать команду Скрыть таблицу.
Чтобы вновь добавить в эту схему свернутую таблицу или таблицу, у которой связи еще не установлены, необходимо:
1. Щелкнуть правой кнопкой мыши на свободном пространстве схемы данных и из контекстного меню выбрать команду Добавить таблицу.
2. В диалоговом окне Добавление таблицы раскрыть вкладку Таблицы, выбрать из списка необходимую таблицу, и нажать кнопку Добавить (рис. 9).
3. Нажать кнопку Закрыть, чтобы закрыть диалоговое окно Добавление таблицы.
В данном случае связь между таблицами Access строит автоматически. Это происходит тогда, когда две таблицы имеют одинаковые названия связанных полей и согласованные типы данных, причем хотя бы в одной из таблиц связанное поле является ключевым.
|
Рис. 9. Диалоговое окно Добавление таблицы
Если нужная связь автоматически не создана, ее можно создать двумя путями. Первый путь — графический. Следует выбрать в главной таблице поле для связи, нажать левую кнопку мыши и перетащить поле во вторую таблицу. Отпустить левую кнопку мыши нал тем полем подчиненной таблицы, с которым устанавливается связь. После этого появится диалоговое окно Изменение связей (рис. 10). Альтернативный вариант - выбрать команду Связи, Изменить связь из главного меню Access. Эта команда появляется в меню, когда открыто окно Схема данных.
Рис. 10. Окно Изменение связей
В диалоговом окне Изменение связей можно выбрать из списков названии связанных таблиц и полей для связывания. Если отношение между таблицами «один-ко-многим», то слева из списка Таблица/запрос выбирается главная таблица и поле в этой таблице, а справа из списка Связанная таблица/запрос — подчиненная и соответственно поле в ней. Если отношение «один-к-одному», то порядок таблиц значения не имеет. Если вы устанавливали связь графически, то все поля в списках уже выбраны, и нужно только определить правила ссылочной целостности. Для этого устанавливают флажок Обеспечение целостности данных и один или оба флажка: каскадное обновление связанных полей и каскадное удаление связанных записей. При необходимости можно изменить параметры объединения, для чего требуется нажать кнопку Объединение и выбрать один из переключателей (рис. 11):
• 1. Объединение только тех записей, в которых связанные поля обеих таблиц совпадают;
• 2. Объединение ВСЕХ записей из <имя первой таблицы> и только тех записей из <имя второй таблицы>, в которых связанные поля совпадают;
• 3. Объединение ВСЕХ записей из <имя второй таблицы> и только тех записей из <имя первой таблицы>, в которых связанные поля совпадают.
По умолчанию устанавливается первый переключатель. Нажать кнопку ОК.
Рис. 11. Окно Параметры объединения
Запросы в СУБД Access
Одним из семи стандартных объектов Microsoft Access является запрос. Запросы используются для просмотра, анализа и изменения данных в одной или нескольких таблицах. Например, можно использовать запрос для отображения данных из одной или нескольких таблиц и отсортировать их в определенном порядке, выполнить вычисления над группой записей, осуществить выборку из таблицы по определенным условиям. Запросы могут служить источником данных для форм и отчетов Microsoft Access. Сам запрос не содержит данных, но позволяет выбирать данные из таблиц и выполнять над ними ряд операций. В Microsoft Access существует несколько видов запросов:
· запросы к серверу, которые используются для выборки данных с сервера;
· запросы на автоподстановку, автоматически заполняющие поля для новой записи;
· запросы на выборку, выполняющие выборку данных из таблиц;
· запросы на изменение, которые дают возможность модифицировать данные в таблицах (в том числе удалять, обновлять и добавлять записи);
· запросы на создание таблицы, создающие новую таблицу на основе данных одной или нескольких существующих таблиц,
· другие типы запросов.
Запросы и фильтры
Запрос на выборку содержит условия отбора данных и возвращает выборку, соответствующую указанным условиям, без изменения возвращаемых данных. В Microsoft Access существует также понятие фильтра, который в свою очередь является набором условий, позволяющих отбирать подмножество записей или сортировать их. Сходство между запросами на выборку и фильтрами заключается в том, что и в тех и в других производится извлечение подмножества записей из базовой таблицы или запроса. Однако между ними существуют различия, которые нужно понимать, чтобы правильно сделать выбор, в каком случае использовать запрос, а в каком — фильтр.
Основные отличия запросов и фильтров заключаются в следующем.
· Фильтры не позволяют в одной строке отображать данные из нескольких таблиц, т. е. объединять таблицы.
· Фильтры не дают возможности указывать поля, которые должны отображаться в результирующем наборе записей, они всегда отображают все поля базовой таблицы.
· Фильтры не могут быть сохранены как отдельный объект в окне базы данных (они сохраняются только в виде запроса).
· Фильтры не позволяют вычислять суммы, средние значения, подсчитывать количество записей и находить другие итоговые значения.
Запросы могут использоваться только с закрытой таблицей или запросом. Фильтры обычно применяются при работе в режиме Формы или в режиме Таблицы для просмотра или изменения подмножества записей. Запрос можно использовать:
· для просмотра подмножества записей таблицы без предварительного открытия этой таблицы или формы;
· для того чтобы объединить в виде одной таблицы на экране данные из нескольких таблиц;
· для просмотра отдельных полей таблицы;
· для выполнения вычислений над значениями полей.
Типы запросов
Запросы на выборку
Запрос на выборку, является наиболее часто используемым типом запроса. Запросы этого типа возвращают данные из одной или нескольких таблиц и отображают их в виде таблицы, записи в которой можно обновлять (с некоторыми- ограничениями). Запросы на выборку можно также использовать для группировки записей и вычисления сумм, средних значений, подсчета записей и нахождения других типов итоговых значений.
Существует два варианта построения: в режиме Мастера и в режиме Конструктора.
1. Создание запроса в режиме Мастера.
Выбрать информацию, которая необходима, можно из одной или нескольких таблиц. Так как таблицы связаны друг с другом, это не представляет большой сложности, особенно если подключить к работе мастер запросов. Мастер запросов вызывается двумя способами:
· в основном окне базы данных необходимо выбрать объект Запросы, затем на панели инструментов основного окна нажать кнопку Создать и в открывшемся диалоговом окне Новый запрос (рис. 12) выбрать строку Простой запрос:
Рис. 12. Окно Новый запрос
· мастер запросов можно вызвать сразу, если в объекте Запросы дважды щелкнуть по надписи Создание запроса с помощью мастера.
Вобоих случаях будет открыто диалоговое окно Создание простых запросов для выбора полей (рис. 13)
Рис. 13. Окно Создание простых запросов. Шаг первый
В окне списка Таблицы и запросы надо выбрать таблицу, из которой будут отбираться данные, после чего в окне Доступные поля автоматически появится список полей выбранной таблицы. Нужные поля отбираются в окно Выбранные поля с помощью стрелок, расположенных между окнами (одинарная стрелка переносит одно выделенное поле в одну или другую сторону, двойная - переносит все). Кнопка Далее позволит перейти на следующий шаг работы мастера (рис. 14).
Рис. 14. Окно Создание простых запросов. Шаг второй
На втором шаге требуется поставить точку в строке Подробный (вывод каждого поля каждой записи). Кнопка Далее откроет окно третьего шага (рис. 15).
Рис. 15. Окно Создание простых запросов. Шаг третий
В последнем окне необходимо задать имя запроса. Кнопка Готово выведет на экран запрос в режиме таблицы (рис. 16)
Рис. 16. Новая таблица, созданная мастером запросов
2. Создание запроса в режиме Конструктора (основной режим построения)
Режим Конструктора в запросах является основным. Даже запрос, выполненный в режиме Мастера, может потребовать доработки: выполнить вычисления, сгруппировать данные или произвести еще какие-либо действия. Выполнение всех дополнительных условий производится в режиме Конструктора - здесь можно сделать практически все.
Так же как ив мастере запросов, открытие запроса в режиме Конструктора происходит двумя способами:
· в основном окне базы данных необходимо выбрать объект Запросы, на панели инструментов основного окна нажать кнопку Создать и в окне Новый запрос выбрать строку Конструктор. Кнопка ОК в этом случае откроет два окна: Запрос 1: запрос на выборку и Добавление таблицы;
· тот же результат даст двойной щелчок по надписи Создание запроса в режиме конструктора в окне объекта Запросы.
В Окне Добавление таблицы необходимо выбрать требуемую таблицу и нажать кнопку Добавить. Выбранная 
таблица появится в зоне отбора таблиц окна Запрос на выборку в верхнем (сером) поле. После вывода необходимых таблиц или запросов окно Добавление таблицынадо закрыть.
К выбору таблиц нужно отнестись внимательно, так как вместо данных в них могут находиться коды. Лучше всего ориентироваться на таблицы, в которые информация вносится впервые. Например, нужно знать название товара и его количество. Эти данные находятся в таблице Поставка товара, однако название товара здесь представлено в виде кода, поэтому для отбора данных потребуется две таблицы: Товар и Поставка товара.
Теперь из таблиц нужно выбрать необходимые поля. Это можно сделать тремя способами:
· перетащить поле из окна выбранной таблицы в нижнюю часть окна Запросна выборку в свободную (белую) строку Поле, удерживая нажатой левую кнопку мыши. В результате в строке Поле появится имя отобранного поля, а в строку Имя таблицы автоматически будет внесено название таблицы, из которой это поле было выбрано;
· в нижней части окна построителя запроса в строке Имя таблицы выбрать из списка нужную таблицу, после чего в строке Поле появится список полей данной таблицы, из которого выбирается нужное поле;
· поле выбирается при помощи двойного щелчка мышью в зоне выбранной таблицы, находящейся в верхней части построителя запроса.
При отборе таблиц связи полей устанавливаются автоматически в соответствии со схемой данных, но при удалений или добавлении полей они могут быть потеряны. В этом случае их связь восстанавливается вручную, аналогично созданию связи в схеме данных.
В результате всех действий запрос в режиме Конструкторапримет вид, представленный на рис. 17
Рис. 17. Создание запроса в режиме Конструктора
Проверить запрос можно с помощью кнопки, на которой изображен восклицательный знак, на панели инструментов. Запрос переходит в режим таблицы, и, если он не выполняется, следует доработать его, вернувшись в режим Конструктора.
После проверки запроса окно построителя закрывается. При этом появится сообщение о сохранении запроса. Если содержащиеся в нем данные будут использованы в дальнейшем, запрос надо сохранить, присвоив ему индивидуальное имя. В основном окне базы данных в объекте Запросыпоявится новый запрос.
Открытие запроса производится двойным щелчком мыши по названию. Запрос открывается в режиме таблицы.
Запросы с параметрами
Запрос с параметрами — это запрос, при выполнении отображающий в собственном диалоговом окне приглашение ввести данные, например условие для возвращения записей или значение, которое требуется вставить в поле. Можно разработатьзапрос, выводящий приглашение на ввод нескольких единиц данных, например двух дат. Затем Microsoft Access может вернуть все записи, приходящиеся на интервал времени между этими датами.
Запросы с параметрами работают по двум вариантам:
· отбор данных происходит в отдельном диалоговом окне. Такие диалоговые окна существуют в любой справочной системе, где вводится вопрос, и программа ищет ответ;
· отбор данных ведется непосредственно в таблице запроса в режиме Конструктора. В строку Условие отборазаписываются критерии отбора, и только после этого на экран выводится табличная форма запроса.
1. Диалоговое окно для запроса
Диалоговые окна нужны не только для справки. Они выводят на экран только запрашиваемые данные и скрывают всю остальную информацию. Такой порядок позволяет разделить работу по нескольким направлениям и скрыть коммерческий характер информации.
Любой запрос начинается с создания простого запроса, а так как предстоит дополнительная работа, то создавать его лучше в режиме Конструктора.
После отбора таблиц и полей в том же режиме в строке Условие отборав поле, где необходимо произвести отбор, надо указать критерии отбора. Эти критерии записываются в квадратных скобках в виде формулы:
[введите:КодТовара].
После слова «введите» указывается название поля (рис. 18).
Рис. 18. Создание запроса с параметром
При проверке или запуске запроса открывается диалоговое окно Введите значение параметра (рис. 19), в котором пользователю предлагают ввести параметры. Какой параметр внести, указано на сером фоне окна, а в текстовом окне мигает курсор, дожидаясь ввода данных с клавиатуры.
Рис. 19. Диалоговое окно Введите значение параметра
После ввода параметра в диалоговом окне Введите значение параметра нажмите ОК, и на экране появится таблица с выбранными параметрами (рис. 20).
Рис. 20. Результат действия запроса с параметром
При выполнении запроса по двум и более параметрам окна Введите значение параметра будут появляться после введения каждого параметра друг за другом до тех пор, пока не будут заполнены все требуемые значения. Только после этого откроется таблица запроса.
2. Отбор данных в таблице запроса
Запросы с отбором данных считаются рабочими. Все начинается с простого запроса. В режиме Конструкторав строке Условие отбора в столбце, где необходимо произвести отбор, указываются критерии отбора. В этом случае они записываются в кавычках с указанием данных из записи или с помощью математических знаков (например, "тетради", <5)..
Итоговые запросы
В данном виде запросов выполняются математические итоговые вычисления или статистические расчеты.
При выполнении итогового запроса записи следует сгруппировать по какому-либо признаку (название товара, дата продажи и т.д.).
Для каждой из групп можно провести итоговое вычисление {например, определить общее количество поставленных тетрадей или карандашей).
Запрос начинается с составления простого запроса, то есть отбираются необходимые поля для проведения итоговой операции.
Группировка данных происходит в режиме Конструктора нажатием на кнопку 
(Групповые операции) на панели инструментов или выполнением команды меню Вид =>Групповые операции. В нижней части окна построения запроса появится новая строка Групповые операции, а в зоне каждого поля автоматически будет установлено значение Группировка.
В тех полях, где нужно сгруппировать данные, необходимо оставить запись Группировка. В этом случае одинаковые данные будут выведены на экран единой записью без повторения. В остальных полях, по которым следует провести итоговые вычисления, в строке Группировка нужно раскрыть список и выбрать одну из итоговых функций (рис. 21)
Рис. 21. Создание итогового запроса
Функции в раскрывающемся списке имеют краткое обозначение (табл. 11).
Таблица 11 Функции групповых операций.
| Элемент | Результат | Тип поля |
| Sum | Сумма значений поля. | «Числовой». «Дата/время». «Денежный» и «Счетчик» |
| Avg | Среднее от значений поля. | «Числовой». «Дата/время», «Денежный» и «Счетчик» |
| Min | Наименьшее значение поля. | «Текстовый», «Числовой», «Дата/время», «Денежный» |
| Мах | Наибольшее значение поля. | «Текстовый», «Числовой», «Дата/время»,«Денежный» |
| Count | Число значений поля без учета пустых значений. | «Текстовый», «Числовой», «Дата/время». |
| StDev | Среднеквадратичное отклонение от среднего значения поля. | «Числовой», «Дата/время», «Денежный» и «Счетчик» |
| Var | Дисперсия значений поля. | «Числовой», «Дата/время», «Денежный» и «Счетчик» |
| First | Значение первой записи поля | «Текстовый», «Числовой», «Дата/время», «Денежный» |
| Last | Значение последней записи поля | «Текстовый», «Числовой», «Дата/время», «Денежный» |
Перекрестные запросы
Перекрестный запросявляется специальным запросом итогового типа. Он позволяет увидеть вычисляемые значения в виде перекрестной таблицы, напоминающей электронную (рис. 22).
Рис. 22. Изменение представления данных в перекрестном запросе.
В перекрестной таблице левый столбец образует заголовки строк из значений одного поля. Верхняя строка новой таблицы образует заголовки столбцов из значений другого поля, а на пересечении строк и столбцов размещаются итоговые значения, вычисленные по значениям третьего поля. Для получения итоговых значений записи группируются по полям, используемым в качестве заголовков строк и столбцов, и для значений третьего поля в полученных группах записей применяется одна из выбранных статистических функций.
Рассмотрим создание перекрестного запроса в режиме Конструктора (рис. 23).
1. Из выбранных таблиц отбираются поля, которые в новой таблице примут значения заголовков столбцов и строк. В этот же запрос отбирается поле, значение которого можно использовать в вычислениях.
2. На панели инструментов необходимо нажать кнопку 
(Тип запроса) и выбрать пункт Перекрестный или выполнить команду меню Запрос => Перекрестный. В нижней части запроса появятся разделы Групповая операция и Перекрестная таблица.
Рис. 23. Перекрестный запрос в режиме Конструктора.
3. В разделе Групповая операция нужно выбрать функцию напротив поля, значение которого будет вычисляться, а в остальных полях оставить значение Группировка.
4. Чтобы значения поля появились в заголовке строк, в разделе Перекрестная таблица необходимо щелкнуть по строке и из раскрывшегося списка выбрать пункт Заголовки строк.
5. Если значение поля выбирается в качестве заголовка, то в разделе Перекрестная таблица следует выбрать пункт Заголовки столбцов.
6. Для поля, в котором выбрана функция вычисления, в разделе Перекрестная таблица нужно выбрать параметр Значение.
7. Проверить и сохранить запрос (рис. 24)
Рис. 24. Перекрестный запрос в режиме Таблицы.
Запросы на изменение
Запросом на изменение называют запрос, который за одну операцию изменяет или перемещает несколько записей. Существует четыре типа запросов на изменение.
На удаление записи
Запрос на удаление удаляет группу записей из одной или нескольких таблиц. Например, запрос на удаление позволяет удалить записи о товарах, поставки которых прекращены или на которые нет заказов. С помощью запроса на удаление можно удалять только всю запись, а не отдельные поля внутри нее.
На обновление записи.
Запрос на обновление вносит общие изменения в группу записей одной или нескольких таблиц. Например, на 10 процентов поднимаются цены па все молочные продукты или на 5 процентов увеличивается зарплата сотрудников определенной категории. Запрос на обновление записей позволяет изменять данные в существующих таблицах.
На добавление записей.
Запрос на добавление добавляет группу записей из одной или нескольких таблиц в конец одной или нескольких таблиц. Например, появилось несколько новых клиентов, а также база данных, содержащая сведения о них. Чтобы не вводить все данные вручную, их можно добавить в таблицу «Клиенты».
На создание таблицы.
Запрос на создание таблицы создает новую таблицу на основе всех или части данных из одной или нескольких таблиц. Запрос на создание таблицы полезен при создании таблицы для экспорта в другие базы данных Microsoft Access или при создания архивной таблицы, содержащей старые записи.