Урок 7. построение запросов

Урок 7. построение запросов

Вы научитесь:

создавать запрос на основе таблицы или другого запроса;

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

сортировать данные и «прятать» поле в запросе;

создавать запрос, включающий связанные данные;

объединять в запросе таблицы;

связывать таблицы в запросе.

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

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

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

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

Вы уже имели дело с запросами в уроке 4 «Расширение базы данных». В этом уроке вы научитесь создавать и совершенствовать запросы для разных видов данных.

Что такое запросы?

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

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

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

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

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

Работа с фильтрами рассматривается в уроке 3 «Использование фильтров для отбора нужной информации»

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

Как создать запрос

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

Начало урока

Запустите программу Microsoft Access, если она еще не загружена. Откройте базу данных Сластена Лили. Разверните окно базы данных до размеров полного экрана.

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

Создайте запрос

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

1. В окне базы данных щелкните на вкладке Запросы (Queries).

2. Щелкните на кнопке Создать (New).

Появится диалоговое окно Новый запрос (New Query).

3. В диалоговом окне Новый запрос (New Query) щелкните дважды на пункте Простой запрос (Simple Query Wizard).

На экране появится первое диалоговое окно мастера Создание простых запросов (Simple Query Wizard).

4. Прокрутите список в окне Таблицы/Запросы (Tables/Queries) и выделите пункт Таблица:3аказчики.

Поля таблицы отобразятся в окне Доступные поля (Available Fields). Выберите из списка поля, которые вам нужны в запросе.

5. Дважды щелкните на поле Код заказчика. Поле перемесится в список окна Выбранные поля (Selected Fields).

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

6. Добавьте к выбранным поля Имя, Фамилия, Штат/Провинция, Телефон, поочередно выделяя их и щелкая на кнопке выбора.

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

Присвойте запросу имя и сохраните его

1. В окне Создание простых запросов (Simple Query Wizard) щелкните на кнопке Далее (Next). Появится следующее диалоговое окно мастера, в котором можно задать имя запроса.

2. В поле Задайте имя запроса (What Title Do you want For Your Query) введите Заказчики в Нью-Йорке и щелкните на кнопке Готово (Finish). Результаты запроса отобразятся в режиме таблицы, так что можно увидеть, насколько правильно вы его определили. В строке заголовка отобразится имя запроса. Microsoft Access автоматически сохраняет запрос и вносит его имя в список запросов в окне базы данных.

Модификация запроса в режиме Конструктора

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

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

Можно создать запрос с помощью окна конструктора, но Мастер простых запросов делает это быстрее.

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

Как задать условие отбора данных

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

Условие отбора определяется с помощью выражения, по которому Microsoft Access выбирает записи из таблиц. Если, например, нужны поля

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

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

Задайте условия отбора

1. Щелкните на стрелке кнопки Вид (View) и затем щелкните на пункте Конструктор (Design View).

2. В столбце Штат/Провинция в бланке запроса щелкните в строке Условие отбора (Criteria).

3. Напечатайте NY и нажмите клавишу Enter.

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

Запустите запрос

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

Программа Microsoft Access выдает список клиентов из штата Нью-Йорк и их телефонные номера, то есть те поля, которые вы определили с помощью Мастера простых запросов.

Введите дополнительное условие отбора

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

1. Щелкните на стрелке кнопки Вид (View) и затем щелкните на пункте Конструктор (Design View).

2. В строке Условие отбора (Criteria) в столбце Код заказчика введите выражение >200 и затем нажмите клавишу Enter.

Тем самым вы сообщаете Microsoft Access следующее: «Найти заказчиков, имеющих код больше 200 и проживающих в штате Нью-Йорк». Ваш запрос должен выглядеть как на следующем рисунке.

3. Щелкните на стрелке у кнопки Вид (View) и затем щелкните S на пункте Режим таблицы (Datasheet View). Посмотрите, как теперь будет выглядеть список заказчиков.

Как улучшить отображение результатов запроса

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

Выполните сортировку записей

Примеры сортировки приведены, в уроке 3 «Использование фильтров для отбора данных».

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

1. Щелкните в столбце Фамилия.

2. На панели инструментов щелкните на кнопке Сортировка по возрастанию (Sort Ascending).

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

Уберите с экрана поле

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

1. Щелкните на стрелке кнопки Вид (View) и щелкните на пункте Конструктор (Design View). '»

2. В строке Вывод на экран (Show) столбца Штат/провинция щелкните на флажке, чтобы его убрать. Запрос должен выглядеть как на следующем рисунке.

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

3. Щелкните на стрелке кнопки Вид (View) и затем щелкните на пункте Режим таблицы (Datasheet View).

Как видите, столбца Штат/провинция нет на экране.

4. Закройте окно запроса Заказчики в Нью-Йорке. При появлении; сообщения с вопросом, сохранить ли изменения, щелкните на кнопке Да (Yes).

Как получить данные из разных таблиц

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

Создание связей между таблицами было рассмотрено в уроке 4 «Расширение базы данных».

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

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

1. В окне базы данных щелкните на вкладке Запросы (Queries), a затем на кнопке Создать (New).

Откроется диалоговое окно Новый запрос (New Query).

2. В диалоговом окне Новый запрос (New Query) дважды щелкните на пункте Конструктор (Design View).

Откроется окно Запрос (Query) и появится диалоговое окно Добавление таблицы (Show Table).

3. На вкладке Таблицы (Tables) диалогового окна Добавление таблицы (Show Table) поочередно дважды щелкните на таблицах Заказы и Заказчики.

4. Закройте диалоговое окно Добавление таблицы (Show Table), щелкнув на кнопке Закрыть(С1озе).

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

5. В списке полей таблицы Заказы поочередно дважды щелкните на полях Код заказа, Код заказчика и Дата заказа. В столбцах бланка запроса появятся эти три поля.

6. В списке полей таблицы Заказчики щелкните дважды на поле Фамилия. Поле Фамилия появится на бланке запроса.

7. В меню Файл (File) щелкните на команде Сохранить (Save).

8. Назовите запрос Информация о заказах и щелкните на кнопке ОК.

Задайте условия отбора

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

1. В строке Условие отбора (Criteria) в столбце поля Дата заказа введите выражение Between 1-ноя-96 And ЗО-ноя-96 и затем нажмите клавишу |Enter |.

Формат даты изменится, и символ номера (#) автоматически появится с обеих сторон обозначения дат.

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

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

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

4. Снова сохраните запрос, после чего закройте окно запроса Информация о заказчиках.

Как объединить в запросе таблицы

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

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

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

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

Объедините в запросе две таблицы

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

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

Таблица Поставщики была создана в уроке 4 «Расширение базы. данных».

1. В окне базы данных щелкните на вкладке Запросы (Queries) и затем на кнопке Создать(New).

2. Дважды щелкните на пункте Конструктор (Design View). Включите в запрос таблицы Ингредиенты и Поставщики, поочередно выделив их и щелкнув на кнопке Добавить (Аdd) в диалоговом окне

Добавление таблицы (Show Table). Закройте диалоговое окно Добавление таблицы.

3. Из таблицы Ингредиенты перетащите поле Категория на поле КодПоставщика в таблицу Поставщики.

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

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

4. Щелкните на линии между таблицами Ингредиенты и Поставщики в окне Запроса (или как его еще называют сетке QBE (Query By Example)), вследствие чего линия станет толще. Нажмите клавишу Delete. Соединительная линия исчезнет.

5. Перетащите поле Код источника из таблицы Ингредиенты на поле КодПоставщика в таблице Поставщики. Соединительная линия свяжет эти поля.

6. В таблице Ингредиенты поочередно дважды щелкните на полях Категория и Тип, чтобы добавить их в бланк запроса.

7. В таблице Поставщики дважды щелкните на поле ОбращатьсяК, чтобы добавить его в бланк запроса.

8. В таблице Ингредиенты дважды щелкните на поле Код источника, чтобы добавить его в бланк запроса.

9. В столбце Код источника щелкните в ячейке Сортировка (Sort), щелкните на стрелке и выберите пункт По возрастанию.(А8сепатд).

Теперь результаты запроса будут содержать отсортированные данные.

Просмотрите запрос в режиме таблицы

Щелкните на стрелке кнопки Вид (View) и затем щелкните на пункте Режим таблицы (Datasheet View), чтобы просмотреть результаты запроса.

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

1. Прокрутите записи и посмотрите, как меняется содержимое поля ОбращатьсяК а зависимости от поставщика.

2. Закройте запрос. При появлении диалогового окна с вопросом, хотите ли вы сохранить изменения, щелкните на кнопке Да (Yes). Появится диалоговое окно Сохранение (Save as).

3. В поле Имя запроса (File Name) введите Поставщики ингредиентов. Щелкните на кнопке ОК.

Напечатайте запрос

Если вам нужна копия результатов запроса на бумаге, напечатайте его.

1. В окне базы данных щелкните на вкладке Запросы (Queries), щелкните дважды на запросе Поставщики ингредиентов.

На экране появятся результаты запроса в виде таблицы.

2. В меню Файл (File) выберите команду Печать (Print).

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

3. Щелкните на кнопке OK, чтобы напечатать таблицу результатов запроса.

4.Закройте запрос.

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

Шаг вперед: совершенствование запросов

Запрос должен быть организован таким образом, чтобы выдавать специфическую информацию по конкретному вопросу и обладать в то же время достаточной гибкостью для использования в различных ситуациях. Два простых способа позволяют сделать запрос более наглядным. Это добавление подписи поля (заголовка столбца), чтобы точнее отобразить его содержание, и вывод на экран значения фильтра, показывающего критерий отбора данных. Если на основании запроса строится форма, то подписи полей в запросе используются в качестве подписей к полям данных формы. При отсутствии подписи поля в качестве заголовков используется имя поля. Рассмотрим это на примере запроса Заказчики в Нью-Йорке. Хотя в результатах запроса будут представлены заказчики только одного штата, в соответствующей форме будет подпись Код заказчика, так как это поле запроса не имеет подписи.

Задайте свойства поля

Чтобы содержание запроса было очевидно, измените заголовок столбца Код заказчика. Тогда в любой форме, созданной на основе этого запроса, подпись поля будет точно описывать его содержание. В следующем упражнении задайте значение свойства Подпись для запроса Заказчики в Нью-Йорке.

1. Откройте запрос Заказчики в Нью-Йорке в режиме Конструктора (Design View).

2. Выделите поле Код заказчика, щелкнув в строке Поле (Field) в первом столбце.

3. На панели инструментов щелкните на кнопке Свойства (Properties), чтобы открыть окно свойств.

4. Чтобы добавить заголовок для поля Код заказчика, щелкните в поле свойства Подпись (Caption).

5. Введите Заказчики в Нью-Йорке.

6. Закройте окно Свойства поля (Field Properties).

Выведите на экран только пять первых значений

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

Щелкните на стрелке поля Набор значений (Top Values) и затем щелкните на значении 5.

2. Закройте запрос и щелкните на кнопке Да (Yes), чтобы подтвердить сохранение изменений.

Окончание урока

1. Чтобы перейти к следующему уроку, щелкните на команде Закрыть (Close) в меню Файл (File).

2. Если вы завершили работу с программой Microsoft Access, щелкните на команде Выйти (Exit) в меню Файл (File).

Краткое содержание урока