Запросы , не изменяющие файл БД

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

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

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

· выбрать записи, удовлетворяющие условиям отбора;

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

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

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

· произвести обновление полей в выбранном подмножестве записей;

· создать новую таблицу базы данных, используя данные из существующих таблиц;

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

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

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

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

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

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

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

Создание запроса

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

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

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

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

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

Таблица в режиме таблицы

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

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

Схема данных запроса

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

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

Запросы, изменяющие файл БД

(запрос на создание таблицы,запрос на обновление, запрос на добавление, запрос на удаление)

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

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

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

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

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

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

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

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

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

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

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

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

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

Создайте свободный элемент управления типа Поле (Text Box) в нужном месте формы. Прямо в поле введите выражение =Date (). Знак равенства обязателен. Откройте окно Свойства (Properties) для этого элемента и задайте для свойства Формат поля (Format) желаемый формат даты, например Длинный формат даты (Long Date). Установите длину поля таким образом, чтобы в нем помещалось необходимое количество символов. Измените название метки этого текстового поля.

Обычно для создания вычисляемых полей выбирают именно элемент управления Текстовое поле, хотя это и необязательно. Допускается использовать для этого любые элементы управления, имеющие свойство Данные (Control Source). Именно в свойстве Данные (Control Source) должно быть задано выражение, которое вычисляет значение этого элемента. Ввод выражения допускается прямо в элемент управления, как мы и сделали это в примере. Однако, если выражение достаточно длинное, его неудобно вводить прямо в поле. Можно ввести выражение в ячейку свойства Данные (Control Source) в окне Свойства (Properties), а если выражение не помещается в ячейке свойства, просто нажать комбинацию клавиш <Shiftl>+<F2> и открыть окно Область ввода (Zoom). Еще можно воспользоваться Построителем выражений.

Вычисляемые поля создаются чаще всего в табличных или ленточных формах для отображения столбца с какими-нибудь вычисленными значениями. Например, стоимость товара рассчитывается как значение выражения Цена * Количество. Для отображения таких расчетных данных используют два способа:

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

Во втором случае форма может строиться прямо на базе таблицы. В ней создается текстовое поле, в котором в качестве источника данных указывается выражение, например = [Цена за единицу] * Количество. При этом в выражении необязательно указываются те поля, которые включены в форму, а любые поля базовой таблицы.

Ввод данных с помощью форм

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

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

" Тип данных, заданный для источника данных.

" Свойства, заданные для поля.

" Свойства, заданные для элемента управления.

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

Ввод пустых значений

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

" Чтобы ввести пустую строку, откройте таблицу в режиме таблицы или формы.

" Выделите нужное поле и введите пару двойных кавычек без пробела между ними ( "" ).

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

Ввод данных с помощью списка

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

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

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

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

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

Подчиненные формы.

Подчиненная форма – это форма, находящаяся внутри другой формы. Такой вариант называется иерархической или дочерней формой. Первичная форма называется главной, а форма внутри формы – подчиненной. Комбинацию «форма/подчиненная форма» иногда называют комбинацией «родительской» и «дочерней» форм.

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

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

Подчиненная форма выводится в Режиме таблицы как простая или ленточная форма. Главная форма может быть выведена только как простая.

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

Существует три способа создания подчиненных форм.

1. Первый способ:

a. Вкладка Создание – блок Формы – другие формы – Мастер форм

b. Выбрать таблицу/запрос; выбрать поля, которые нужно включить в форму

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

d. Выбрать вид представления данных –в положение Подчиненные формы

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

f.Выбрать стиль форматирования

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

2. Второй способ:

a. Открыть главную форму в режиме конструктора , в ней активировать элемент управления подчиненной формой и вызвать его свойства (контекстное меню или F4).

b. Вкладка Данные – рядом со свойством Подчиненные поля нажать кнопку Построителя (многоточие), открыв диалоговое окно Связь с полями подчиненной формы

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

d. Сохранить главную форму и проверить правильность её работы.

3. Третий способ:

a. Открыть главную форму в режиме конструктора

b. В области переходов на объекте Форма выберите подчиненную форму и перетащить её в главнуюформу.

c. Просмотреть подчиненную форму в режиме Формы.

Экспорт отчетов в MS WORD.

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

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

Шаги экспорта данных Access в Word:

1. Откройте исходную базу данных.

2. В области навигации выделите объект, содержащий данные, которые необходимо экспортировать.

3. Экспортировать можно таблицу, запрос, форму или отчет.

4. Просмотрите исходные данные, чтобы убедиться в отсутствии индикаторов ошибок (треугольники зеленого цвета) или значений ошибок, таких как #Число!.

5. ВАЖНО. Любая неразрешенная ошибка в источнике данных будет заменена в документе Word значением null.

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

7. На вкладке Внешние данные в группе Экспорт выберите пункт Дополнительно, а затем — Word (Команды Экспорт доступны только в том случае, если база данных открыта, а объект выбран).

8. В мастере экспорта укажите имя целевого файла.

9. Мастер всегда экспортирует форматированные данные. Если требуется просмотреть документ Word после завершения операции экспорта, установите флажок Открыть целевой файл после завершения операции экспорта.

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

11. Нажмите кнопку ОК.

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

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

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

· выбрать записи, удовлетворяющие условиям отбора;

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

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

· произвести обновление полей в выбранном подмножестве записей;

· создать новую таблицу базы данных, используя данные из существующих таблиц;

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

Создание запроса

Таблица в режиме таблицы

Схема данных запроса

Запросы , не изменяющие файл БД

(это запросы на выборку, параметрические, итоговые, перекрестные запросы)

§ Параметрические запросы

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

Для этого в строку «Условие отбора» вводится фраза в квадратных скобках, которая будет выводиться в качестве «подсказки»-комментария в процессе диалога, например [Введите фамилию]. Таких параметров в одном запросе может быть несколько, каждый – для своего поля. При задании нескольких параметров имя каждого должно быть уникальным.

Для каждого параметра можно указать тип данных, который корректируется командой Работа с запросами – Конструктор – блок Показать или Скрыть – Параметры .

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

§ Итоговые запросы

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

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

Возможные операции в строке «Групповые операции»:

SUM - сложение

AVG – среднее значение

MIN – минимальное значение

MAX – максимальное значение

COUNT – количество записей со значениями (без пустых значений)

STDEV – стандартное отклонение

VAR - дисперсия

FIRST – значение в первой записи

LAST – значение в последней записи

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

§ Перекрестные запросы

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

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

Запросы, изменяющие файл БД

(запрос на создание таблицы,запрос на обновление, запрос на добавление, запрос на удаление)

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

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

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

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