Отображение записей подчиненных таблиц в главной таблице

В Access имеется возможность при просмотре главной таблицы отображать записи подчиненных таблиц. Это позволяет пользователю при добавлении, удалении и редактировании записей осуществлять контроль за корректностью связей. Например, если отобразить в таблице договор подчиненную таблицу поставка_план, связанных по полю ном_дог, то при заполнении таблицы поставка_план невозможно допустить ошибку в поле ном_дог, т. к. оно даже не отображается в подчиненной таблице (Рисунок 5.10).

Достаточно щелкнуть на знаке "+" в строке записи, чтобы отобразились записи подчиненной таблицы, связанные с текущей записью. При этом "+" преобразуется в (минус). Щелчком на "–" подчиненные записи закрываются.

Открыть или закрыть все подчиненные записи можно, воспользовавшись командой меню Формат®Подтаблица®Развернуть все или Свернуть все.

В таблице базы данных Access одновременно можно просматривать данные подтаблиц восьми уровней.

Рисунок 5.9.. Отображение подчиненных подтаблицы в главной таблице

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

Рисунок 5.10. Окно для выбора подчиненной таблицы и поля связи с ней

Самостоятельная работа

Контрольные вопросы

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

2. Для чего используется поле со списком?

3. Какое кол-во полей можно вставлять в поле со списком?

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

5. Для чего создается схема данных базы Access?

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

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

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

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

10. Что произойдет при изменении значения ключевого поля в главной таблице, если для ее связи с подчиненной установлен параметр Каскадное обновление связанных полей?

11. Для чего предназначен знак "+" в левом столбце открытой таблицы?

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

13. Какое свойство таблицы определяет подтаблицу, задающую вывод связанных записей при щелчке на знаке "+"?

Лабораторная работа№ 2. Запросы

2.1 Назначение и виды запросов

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

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

В Access может быть создано несколько видов запроса:

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

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

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

2.2 Конструирование запросов

Для создания запроса нужно выбрать в окне база данных «Склад» объекты Запросы и нажать кнопку Создать. В открывшемся окне Новый запрос (Рисунок 6.1) выбрать Конструктор.

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

Рисунок 6.1. Окно для выбора способа создания нового запроса

Далее в открывшемся окне Добавление таблицы (Рисунок 6.2) нужно выбрать таблицу например Товар и нажать кнопку Добавить. Для выхода из окна следует нажать кнопку Закрыть.

Рисунок 6.2. Окно для добавления таблиц в запрос

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

Допустим, надо выбрать товары, цена которых не более 1000 руб. и НДС не более 10%, а также товары, цена которых более 2500 руб. Результат должен содержать наименование товара, его цену и НДС.

Рисунок 6.3. Окно для конструктора запросов

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

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

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

- в строке Сортировка выбирается порядок сортировки записей. Например в поле ЦЕНА с помощью кнопки выбрать сортировку по «возрастанию»;

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

- в строке Условие отбора задаются условия отбора записей. Например в столбце ЦЕНА условие <1000 (Рисунок 6.4.);

- в строке или задаются альтернативные условия отбора записей (рис 6.4)

Рисунок 6.4. Бланк запроса

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

Условия отбора оформим, как показано в бланке запроса на Рисунок 6.4. Между условиями, записанными в одной строке, выполняется логическая операция and. Между условиями, записанными в разных строках, выполняется логическая операция or.

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

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

3.9.1 Вычисляемые поля.

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

Выражение вводится в бланк запроса в пустую ячейку строки Поле. Затем, после нажатия клавиши < Enter > или перевода курсора в другую ячейку, перед выражением в этой ячейке добавляется имя поля Выражение1. Имя поля можно заменить на более информативное. Имя отделяется от выражения двоеточием.

Например: Выражение!: [Цена]*[Количество],

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

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

– Функции даты и времени. Используются при обработке дат и времени. Возвращают дату и время полностью или частично (год, месяц, день); функция Date () возвращает системную дату, функция Month (дата) выделяет месяц из значения поля, содержащего дату, и возвращает целое число из интервала от 1 до 12, обозначающее месяц года. Функция MonthName (месяц[;имя]) преобразует числовое обозначение месяца в имя, указывающее полное (имя=Ложь) или сокращенное (имя=Истина) название месяца. MonthName ([СРОК_ПОСТ]; Истина) возвращает сокращенное название месяца. По умолчанию берется значение ложь (название не сокращается).

– Функции обработки текста. Используются при работе с символьными строками. К этой группе функций относится, Например, функция Format, которая служит для преобразования любого допустимого выражения (в том числе числового значения, даты) в символьную строку. Format (выражение [; формат [; первый_день_недели[; первая_неделя_года]]]). Если аргумент выражение указан в формате даты, то аргумент формат задает форматирование даты. Символы формата даты приведены в табл. 6.1.

Символы формата даты Таблица 6.1.

Символ форматирования	Возвращаемое значение
dddd	Название дня недели
ddd	Сокращенное название дня недели
dd	Номер дня месяца
ww	Номер недели года
mmmm	Полное название месяца
mm	Номер месяца
mmm	Краткое название месяца
уу	Две последние цифры года
уууу	Четыре цифры года

Format ([НАКЛАДНАЯ]![ДАТА_ОТГР];"mmmm") – возвратит полное название месяца; Format ([накладная]![дата_отгр];"mm") — возвратит номер месяца.

Если необходимо выделить не один элемент из даты, запишите в функции Format несколько символов форматирования, разделяя их знаком "\". Например, Format ([НАКЛАДНАЯ]![ДАТА_ОТТР]; "mmmm\ yyyy") Возвратит полное название месяца и год. После знака "\" ставится пробел.

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

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

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

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

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

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

Построим запрос, который отберет накладные для заданного месяца, через вычисляемое поле с выражением месяц: Month(накладная!дата_отг). Название месяца указывается в условии отбора образец на Рисунок 6.5. Выполним запрос - Запуск Рисунок 6.6. Сохраните полученный результат под именемПример (накладная).

Рисунок 6.5. Запрос на выборку накладной по номеру месяца

Рисунок 6.6. Результат выполнения запроса

Создадим запрос, на выборку накладных выписанных в январе месяце. В конструкторе выберем поле НОМ_НАКЛ и КОД_СК в третье поле введем вычисляемое поле через «Построитель выражения» (Рисунок 6.7.) Введем в верхнее поле название столбца месяц: для построения вычисляемого поля воспользуемся встроенными функциями. Функции ® Встроенные функции ® Дата/время ® MonthName ® Вставить ® ( ® Month ® Вставить ® ( ® Таблицы ® Накладная®ДАТА_ОТГ®Вставить®))®ОК В результате получим:

Рисунок 6.7. Выбор функции в построителе выражений

Замечание При появлении дополнительных выражений «Выражение» MonthName («month»; «abbreviate»), все что подчеркнуто можно выделить и удалить кнопкой Delete расположенной на клавиатуре.

Рисунок 6.8 Запрос с функцией выделения из даты полного названия месяца.

В третьем столбце в строке « условие отбора»: внесем название месяца «январь». Нажмите .В результате получит таблицу рисунок 6.9.

Рисунок 6.9. Результат отбора

3.9.2 Использование групповых операций в запросах

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

Sum — сумма значений некоторого поля для группы;

Avg — среднее от всех значений поля в группе;

Mах, Min — максимальное, минимальное значение поля в группе;

Count — число значений поля в группе без учета пустых значений;

StDev — среднеквадратичное отклонение от среднего значения поля в группе;

Var — дисперсия значений поля в группе;

First и Last — значение поля из первой или последней записи в группе.

Результат запроса будет, содержать по одной записи для каждой группы.

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

Откроем мастер запроса выберем таблицу поставка_план; выберем поле код_тов и поле кол_пост, нажмем (Рисунок 6.10.); Нажмем Далее в следующем окне подключим подробный (Рисунок 6.11.) и нажать кнопку Далее; В строке «Задайте имя запроса» впишите - Заказано товаров и нажмите Готово (Рисунок 6.12);

Рисунок 6.10. Мастер создания запросов для таблицы поставка_план

Рисунок 6.11. Мастер создания запросов для таблицы поставка_план

Рисунок 6.12. Мастер создания запросов для таблицы поставка_план

После создания запроса в мастере откорректируем его в конструкторе, для этого нажмем кнопку на панели инструментов; подключим Групповые операции и заменим слово группировка в столбце кол_пост на функцию Sum. выбрав ее из списка. Бланк запроса примет вид, показанный на рисунке 6.13. Нажмем . получим таблицу представленную на рисунке 6.14.

Рисунок 6.13. Запрос с группировкой по коду

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

Подпись поля Sum - кол_пост можно - заменить на Заказано товаров. В режим конструктора , в бланке запроса установим курсор мыши на поле кол_пост и нажмем правую кнопку. В контекстном меню выберем Свойства. В окне Свойства поля наберем в строке Подпись — Заказано товаров. Таблица результата после доработки запроса показана на Рисунок 6.15.

Рисунок 6.15. Таблица результата с измененной подписью поля

Сохраним запрос-выборку под именем " Заказано товаров ".

3.9.3 Запрос с функцией Count

Определим, сколько раз отгружался товар по каждому из договоров. Факт отгрузки фиксируется документом "Накладная".

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

Откроем Вид - Групповые операции. Заменим слово группировка в одном из столбцов с именем ном_дог на функцию Count. Бланк запроса, показанный на Рисунок 6.16. Сохраним запрос под именем "Число отгрузок по договорам".

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

2.3 Самостоятельная работа

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

2. Выбрать количество товаров, заказанных в заданном месяце через параметрический запрос. (Рисунок 6.17.).

Рисунок 6.17 Запрос с группировкой по двум полям

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

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

После этого, введем в строку Условие отбора выражение: >10000.

Дата добавления: 2015-10-24