Access 2007

два раза. Если вы пользуетесь примером Boutique Fudge, обязательно выберите, по крайней мере, поля ID, DatePlaced и CustomerID.

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

Примечание

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

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

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

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

211

6.Если вы хотите скрыть один или несколько столбцов, сбросьте у них флажок Вывод на экран (Show).

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

7.Выберите порядок сортировки.

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

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

Подсказка

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

8 Задайте условие фильтрации или отбора.

Фильтрация (см. разд. "Фильтрация" главы 3) — это средство, позволяющее акцентировать внимание только на интересующих вас записях и игнорировать все остальные.

212

Фильтрация или отбор урезает большой пласт данных до нужной вам информации и является сутью множества запросов. (Вы узнаете больше о создании условий фильтрации в следующем разделе.)

Если вы сформировали нужное условие фильтрации, поместите его в поле Условие отбора (Criteria) соответствующего поля (рис. 6.5). В данном примере можно поместить это условие в Условие отбора поля DatePlaced таблицы для того, чтобы выбрать заказы, сделанные в течение первых трех месяцев года: >=#1/1/2007# And <=#3/31/2007#

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

Рис. 6.5. Здесь показан фильтр, определяющий заказы, сделанные в заданном диапазоне дат (с 1 января по 1 марта в 2007 году). Учтите, что когда вы используете реальную жестко закодированную дату как часть условия (например, 1 января 2007 г. в данном примере), ее следует обрамлять символами #

9, Выберите Работа с запросами | Конструктор → Результаты → Выполнить (Query Tools | Design → Results → Run).

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

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

213

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

Примечание

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

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

Вы можете сохранить ваш запрос в любое время с помощью сочетания клавиш <Ctrl>+<S>. Если вы этого не сделаете, программа Access автоматически сохранит его, когда вы закроете вкладку запроса (или всю вашу БД). Конечно, вы не обязаны сохранять ваш запрос. Иногда запрос создается для конкретной решаемой один раз задачи. Если вы не планируете повторно использовать запрос, нет смысла загромождать вашу БД лишними объектами.

При первом сохранении запроса программа Access запрашивает его имя. Применяйте те же правила именования, которым вы следуете при задании имен таблиц — воздержитесь от использования пробелов и специальных символов и делайте заглавной первую букву каждого слова. Удачное имя запроса описывает представление данных, которое он формирует. Хороший выбор FirstQuarterOrders_2007 (заказы первого квартала 2007) показан на рис. 6.6.

Примечание

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

После создания запроса вы увидите его в области переходов вашей БД (рис. 6.7). Если использовать стандартный режим отображения Все таблицы (All Tables), запрос появится под таблицей, которую он использует. Если запрос использует несколько таблиц, он появится в нескольких группах области переходов.

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

214

продукты из таблицы Products (с помощью условия фильтра >50 в поле Price). Если нужно просмотреть, отредактировать или напечатать информацию о дорогих товарах, вы выполняете запрос TopProducts. Для тонкой настройки параметров запроса щелкните его правой кнопкой мыши в области переходов и затем выберите режим Конструктор.

Рис. 6.7. По умолчанию в области переходов ваши запросы выводятся сразу под таблицами, которые они используют. Например, запрос TopProducts (показанный здесь) появляется под таблицей Products

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

Если вы добавляете в таблицу записи, когда открыт запрос, новые записи не будут автоматически появляться в запросе. Вам придется повторно выполнить ваш запрос. Самый быстрый способ — выбрать последовательность Главная → Записи → Обновить → Обновить все (Home → Records → Refresh → Refresh All). Можно также закрывать запрос и снова открывать его, поскольку Access каждый раз выполняет запрос, когда вы открываете его в Ре-

жиме таблицы.

Примечание

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

Построение условий отбора

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

215

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

=1032

Для того чтобы заставить это условие действовать, необходимо поместить его в поле Условие отбора (Criteria) под полем CustomerID.

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

Примечание

Если вы используете многозначное поле (см. разд. "Многозначные поля" главы 5), программа Access включает в результаты запроса запись, хотя бы одно значение которой соответствует условию отбора. Представьте себе, что таблица Classes содержит многозначное поле InstructorID (указывающее на то, что несколько преподавателей могут объединиться для ведения одного и того же учебного курса). Если написать условие =1032 в поле InstructorID, Access включает в результат любую запись, в которой преподаватель 1032 ведет класс независимо от того, назначены ли для ведения этого класса другие преподаватели.

Для тех, кто понимает. Не бойтесь подстановок

Как вы знаете, подстановки изменяют способ отображения значений на листе данных. Если добавить подстановку к полю CustomerID в таблице Orders, вы не увидите зашифрованные числа, такие как 1032. Вместо этого на экран выводятся информативные данные, например фамилия и имя Hancock, John (Хэнкок Джои).

Но при создании условия отбора или фильтрации следует помнить, какие данные на самом деле хранятся в поле. Условие отбора -1032 для поля CustomerID действует корректно, а условие =Hancock, John — нет, потому что имя и фамилия хранятся отдельно. (Они содержатся в таблице Customers, а не в таблице Orders.)

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

•найти нужное значение кода (ID) в таблице Customers заранее. После этого вы можете его использовать при построении запроса для таблицы Orders;

•применить запрос на объединение для получения имени и фамилии из таблицы Customers и вывести их рядом с остальными подробностями заказа. Как воспользоваться этим приемом, вы узнаете в разд. "Запросы и связанные таблицы" далее в этой главе.

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

="Harrington Red"

216

Вместо поиска точного совпадения можно использовать диапазон. Добавьте следующее условие отбора в поле OrderTotal для поиска всех заказов, стоящих больше 10 и меньше 50 долларов:

<50 And >10

В этом выражении на самом деле два условия (меньше 50 и больше 10), которые объединены могущественным ключевым словом And (см. разд. "Комбинирование условий на значения" главы 4). Как альтернативу можно применять ключевое слово Or, если нужны результаты, которые удовлетворяют одному из заданных вами условий. В главе 7 вы рассмотрите более мощные инструменты для построения выражений.

Особенно полезны условия для дат. Но не забывайте обрамлять жестко фиксированные даты знаками # (см. разд. "Проверка допустимости дат" главы 4). Если поместить следующее условие отбора в поле DatePlaced, будут найдены асе заказы, сделанные в 2007 г.:

<#1/1/2008# And >#12/31/2006#

Это выражение отбирает все даты до 1 января 2008 г., но после 31 декабря 2006 г.

Подсказка

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

На профессиональном уровне. Синтаксис фильтра

Если фильтры кажутся хорошо знакомыми, для этого есть основания. У них тот же синтаксис, что и у правил верификации или условий на значение, которые использовались для защиты от некорректных данных (см, разд. "Правила верификации или условия па значения" главы 4). Единственное отличие — способ интерпретации условия программой Access. Например, условие на значение <50 And >10 сообщает Access о том, что значение не должно приниматься, если оно не попадает в заданный диапазон (от 10 до 50). Если же такое условие помещается в поле отбора, оно уведомляет программу Access о том, что вас не интересует отображение записей, не попавших в заданный диапазон. Благодаря такому подобию вы можете использовать все условия на значение, представленные в разд. "Запись условия па значение поля" главы 4.

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

Получение заданного количества первых записей

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

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

217

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

1.Откройте запрос в Конструкторе (или создайте новый запрос и добавьте поля, которые хотите использовать).

В данном примере используется таблица Products и добавляются поля ProductName и

Price.

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

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

3.В поле Работа с запросами | Конструктор → Настройка запроса → Возврат (Query Tools | Design → Query Setup → Return) выберите другой вариант (рис. 6.8). Стандартный вариант для этого поля — Все (All), получение всех соответствующих условию записей. Но можно выбрать 5, 25 или 100 для получения 5, 25 или 100 первых записей соответственно. Можно также задать значение в процентах, например, 25% для получения первой четверти всех отобранных записей.

Рис. 6.8. Если нужного вам количества записей нет в списке, просто введите его в поле Возврат собственноручно. Ничего не помешает вам отобрать 27 наиболее дорогостоящих продуктов

218

Примечание

Для того чтобы поле Работа с запросами | Конструктор → Настройка запроса → Возврат

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

4. Выполните ваш запрос для отображения результатов (рис. 6.9).

Рис. 6.9. Пять самых дорогостоящих продуктов

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

В главе 4 вы познакомились с табличными индексами и научились создавать их. (Индекс — это перечень всех значений в одном поле в отсортированном порядке. Рядом с каждым значением хранится указатель на полную запись в таблице.) У индексов два назначения. Вопервых, они препятствуют возникновению дублирующихся значений (см. разд. "Предотвращение дублирования значений с помощью индексов" главы 4). Во-вторых, они помогают программе Access выполнять поиск с более высокой скоростью. Зачастую Access может искать, пользуясь индексом, быстрее, чем просматривая целую таблицу. Не только потому, что индекс меньше физически (поскольку он содержит значения только одного поля), но и потому, что он отсортирован, и программа может быстрее перейти в нужное место.

Для того чтобы понять разницу, предположим, что вы с помощью программы Access хотите найти запись "Bavarian Tart" в таблице Products. Если у вас есть индекс для поля ProductName, Access может просматривать раздел для буквы "В", дока не найдет нужное значение, и затем перейти к полному набору деталей. Если же индекса нет, программе придется просмотреть всю таблицу, запись за записью. Таблица не отсортирована, поэтому нельзя сказать, сколько пройдет времени до того, как Access случайно натолкнется на нужную запись.

219

На первый взгляд индексы кажутся невероятно полезными, и вы готовы попытаться создать их для всех полей вашей таблицы. Но у индексов есть и недостатки. Чем больше индексов создано, тем больше работы приходится выполнять программе Access при добавлении и обновлении записей. Кроме того, каждый индекс занимает какое-то место. В действительности индексы расходуют ресурсы, как бы они не повышали производительность поиска.

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

•У вас БД большого объема. Если у вас несколько сотен записей, Access почти всегда благодаря принципам работы жесткого диска может просмотреть быстрее всю таблицу, чем применять индекс. Даже если у вас тысячи записей, программа Access часто может загрузить весь набор в оперативную память вашего компьютера, поэтому ей не придется ждать отклика жесткого диска, и все ваши запросы становятся молниеносными.

•Ваш поиск выполняется медленно. Нет смысла улучшать запрос, если он и так работает с максимальной скоростью. Большинство приверженцев программы Access могут искать в гигантских БД день за днем, не тратя времени на ожидание.

•Поле, которое вы хотите индексировать, используется в поиске. Не индексируйте поле, если вы не применяете его в условии отбора. Если вы часто ищете отдельного конкретного клиента, применяя подстановку его фамилии, добавьте индекс в поле LastName (фамилия).

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

Создание простого запроса с помощью Мастера запросов

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

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

Далее описаны действия, необходимые для запуска и выполнения Мастера запроса.

1.Выберите Создание → Другие → Мастер запроса (Create → Other → Query Wizard). Программа Access позволяет выбрать из нескольких разных мастеров (рис. 6.10).

2.Выберите тип запроса. Сейчас лучше всего выбрать мастер Простой запрос (Simple Query).

ВМастер запроса включено несколько распространенных типов запросов. За исключением перекрестного запроса у всех остальных нет ничего необычного.

220