Текст книги "Office 2007: самоучитель"

Контрольное упражнение

Пришло время самостоятельно попрактиковаться в работе с конструктором таблиц.

Совет

Access автоматически обновляет файл базы данных. Чтобы сохранить его исходный вариант, в проводнике создайте копию файла Контакты. accdb – файл Контакты_копия. ассс1Ь. Закончив контрольное упражнение, переименуйте файл Контакты_копия. accdb обратно в Контакты. accdb.

1. Откройте таблицу Контакт в режиме конструктора.

2. Измените тип данных поля Адрес на Текстовый (Text).

3. Ограничьте текстовому полю Имя длину 30 символов.

Какой параметр определяет длину поля?

4. Запретите Access добавлять в таблицу Контакт записи с пустым полем Фамилия.

Как запретить добавление пустого поля?

5. Закройте конструктор и сохраните структуру таблицы.

6. Создайте новую таблицу с именем Метод.

7. В конструкторе добавьте в нее поле МетодШ с типом данных Счетчик (Auto-Number) и поле Вариант с типом Текстовый (Text). Первое из них сделайте ключом таблицы.

8. Переключитесь в режим редактирования и введите в таблицу три записи, в поле Метод которых укажите слова Телефон, Письмо, Факс. Эта таблица будет хранить возможные способы контакта.

9. С помощью конструктора добавьте в таблицу Список поле МетодШ с числовым типом.

10. С помощью мастера подстановки назначьте этому полю подстановку, состоящую из полей МетодШ и Вариант таблицы Метод.

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

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

12. Закройте базу данных.

Какие операции закрывают базу данных?

Подведение итогов

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

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

Занятие 17
Выборка данных

Тема занятия

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

• мастер запросов;

• конструктор запросов;

• условие отбора;

• итоговый запрос;

• запрос действия;

• фильтры.

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

Запросы

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

Упражнение 1. Мастер запросов

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

1. Перейдите на вкладку Создание (Create).

2. В группе Другие (Other) щелкните на значке Мастер запросов (Query Wizard).

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

4. В раскрывающемся списке Таблицы и запросы (Tables/Queries) первого окна мастера выберите таблицу Список (рис. 17.1).

Рис. 17.1. Мастер запросов

5. В списке Доступные поля (Available Fields) щелкните на строке Дата.

6. Щелкните на кнопке >, чтобы переместить выделенное поле в список Выбранные поля (Selected Fields).

7. Повторяя шаги 5–6, добавьте в список Выбранные поля (Selected Fields) поле Описание таблицы Список, а также поле Фамилия таблицы Контакт.

8. Щелкните на кнопке Далее (Next).

9. Введите имя запроса Перечень контактов и щелкните на кнопке Готово (Finish).

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

Примечание

Чтобы база данных выглядела интереснее, в таблицы было добавлено несколько новых записей.

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

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

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

Упражнение 2. Конструктор запросов

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

1. Чтобы переключиться в режим конструктора, щелкните на кнопке Режим (View) вкладки Главная (Home). Окно конструктора показано на рис. 17.3. В его верхней части отображаются списки полей таблиц, к которым обращается запрос, и связи между таблицами. Нижняя область содержит бланк выбора полей таблиц, условий отбора и режимов сортировки. Добавим в запрос еще одно поле.

2. Наведите указатель на пункт Имя таблицы Контакт.

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

4. В раскрывающемся списке третьей ячейки третьего столбца бланка выберите пункт По возрастанию (Ascending), как показано на рис. 17.4.

Рис. 17.3. Конструктор запроса

Рис. 17.4. Параметры объединения

Совет

Чтобы добавить в запрос сразу все поля таблицы, перетаскивайте верхний пункт (со значком*). Если нужная таблица отсутствует в верхней части окна запроса, щелкните на кнопке Отобразить таблицу (Show Table) вкладки Конструктор (Design), выделите нужную таблицу или запрос в открывшемся диалоговом окне и щелкните сначала на кнопке Добавить (Add), а затем – на кнопке Закрыть (Close).

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

6. Дважды щелкните на линии связи.

7. В открывшемся диалоговом окне Параметры объединения (Join Properties) установите переключатель Объединение всех записей из «Контакт» и только тех записей из «Список», в которых связанные поля совпадают (Include All records from «Контакт» and only those records from «Список» where the joined fields are equal).

8. Щелкните на кнопке ОК. На одном конце линии связи появится стрелка, указывающая на смену режима объединения.

Примечание

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

9. Щелчком на кнопке Выполнить (Run) вкладки Конструктор (Design) выполните запрос повторно. Результат выполнения запроса с учетом сортировки и нового режима объединения показан на рис. 17.5.

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

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

11. В ответ на запрос о необходимости сохранения новой структуры запроса щелкните на кнопке Да (Yes).

Упражнение 3. Условие отбора

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

1. Выделите в области переходов (рис. 17.6) строку запроса Перечень контактов.

Рис. 17.6. Окно базы данных

2. Щелкните правой кнопкой мыши и в контекстном меню выберите команду Конструктор (Design View).

3. На бланке запроса щелкните в ячейке Условие отбора (Criteria) первого столбца правой кнопкой мыши и выберите в контекстном меню команду Построить (Build). Откроется окно построителя выражений (рис. 17.7).

Рис. 17.7. Построитель выражений

4. В левом списке построителя щелкните на папке Операторы (Operators).

5. В среднем списке выберите категорию Сравнения (Comparison).

6. В правом списке дважды щелкните на пункте Between, чтобы добавить этот оператор в поле формулы.

7. Щелчком выделите в поле формулы первый местозаполнитель Выражение (Ехрг).

8. В левом списке построителя выражений двойным щелчком откройте папку Функции (Functions).

9. Щелкните на папке Встроенные функции (Built-in Functions), содержащей стандартные функции Access.

10. В среднем списке построителя выражений щелкните на пункте Дата/время (Data/Time).

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

12. Нажмите два раза клавишу —>, выделив местозаполнитель «stringexpr».

13. Введите текст «1.02.2007».

14. Повторив шаги 7-13, замените второй местозаполнитель Выражение на выражение DateValue («10.02.2007»).

У вас должна получиться следующая формула: Between DateValue («1.02.2007») And DateValue («10.02.2007»)

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

Совет

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

15. Щелкните на кнопке ОК. Построенная формула появится в ячейке Условие отбора (Criteria) первого столбца бланка запроса. При выборе записей, относящихся к определенному интервалу, точная дата контакта может оказаться несущественной. Совсем выбросить это поле из бланка запроса нельзя, так как оно необходимо для реализации условия отбора. Однако любое поле можно скрыть, то есть не включать в результат запроса.

16. Сбросьте флажок Вывод на экран (Show) первого столбца запроса (рис. 17.8).

Рис. 17.8. Скрытие поля

17. Щелчком на кнопке Выполнить (Run) вкладки Конструктор (Design) выполните запрос. Новый результат показан на рис. 17.9.

Рис. 17.9. Контакты за первую декаду февраля 2007 г.

Упражнение 4. Итоговый запрос

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

1. Перейдите на вкладку Создание (Create).

2. Щелкните на кнопке Конструктор запросов (Query Design).

3. В открывшемся диалоговом окне (рис. 17.10) выделите строку Контакт.

Рис. 17.10. Добавление таблицы

4. Щелчком на кнопке Добавить (Add) добавьте выбранную таблицу в верхнюю область конструктора запроса.

5. Выделите пункт Список и снова щелкните на кнопке Добавить (Add).

6. Щелчком на кнопке Закрыть (Close) закройте диалоговое окно Добавление таблицы (Show Table). Списки полей двух таблиц, соединенные линией связи, появятся в окне конструктора (рис. 17.11).

7. Щелкните на кнопке Итоги (Totals) вкладки Конструктор (Design). В бланке запроса появится дополнительная строка Групповая операция (Total), позволяющая выполнять статистические операции со значениями конкретных полей.

8. Перетащите поле Фамилия в ячейку Поле (Field) первого столбца конструктора.

9. В ту же ячейку второго столбца перетащите поле Имя таблицы Контакт.

10. В третий, четвертый и пятый столбцы бланка запроса перетащите поле Дата таблицы Список.

11. В раскрывающемся списке ячейки Групповая операция (Total) третьего столбца бланка запроса выберите пункт Min.

12. В той же ячейке четвертого столбца выберите пункт Мах.

Рис. 17.11. Запрос с групповыми операциями

13. В пятом столбце задайте групповую операцию Count. Групповые операции построенного запроса обработают все записи таблицы Список, соответствующие конкретному человеку из таблицы Контакт, и вместо самих данных таблицы Список выведут в соответствующее поле результата запроса только значение величины, вычисляемой по определенной формуле. Доступные групповые операции перечислены в табл. 17.1.

Таблица 17.1. Групповые операции

Примечание

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

14. Щелкните на кнопке Выполнить (Run), чтобы выполнить запрос. Появится таблица с пятью столбцами. Два первых столбца будут содержать фамилии и имена людей. По ним выполняется группировка, то есть расчет значений остальных полей запроса выполняется для записей таблицы Список, сопоставляемых с одним человеком. Как уже отмечалось, соответствие контакта таблицы Список и человека из таблицы Контакт определяется полями Код и Контакт, с помощью которых осуществляется связь этих двух таблиц. Третий и четвертый столбцы запроса выводят соответственно дату первого (функция Min) и последнего (функция Мах) контактов с данным человеком. Пятый столбец содержит количество записей в таблице Список (функция Count), соответствующих данному человеку, то есть число контактов с ним. Единственный недостаток построенного запроса – это непонятные имена столбцов. Давайте скорректируем их.

15. Щелчком на кнопке Режим (View) вернитесь в окно конструктора запроса.

16. В ячейке Поле (Field) третьего столбца замените имя Дата текстом Дата первого контакта: Дата. Правая часть этого выражения, расположенная правее двоеточия, по-прежнему задает имя поля, а левая определяет название столбца результата запроса. Таким образом, любому столбцу запроса можно назначить произвольное имя.

17. В ячейке Поле (Field) четвертого столбца введите Дата последнего контакта: Дата.

18. В первой строке пятого столбца бланка запроса введите Число контактов: Дата.

19. Снова щелкните на кнопке Режим (View). Окончательный результат запроса показан на рис. 17.12.

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

21. Для сохранения изменений структуры щелкните на кнопке Да (Yes).

22. В диалоговом окне Сохранение (Save As) введите имя Итоговый запрос и щелкните на кнопке ОК.

Рис. 17.12. Запрос с итоговыми значениями

Упражнение 5. Запрос действия

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

1. Перейдите на вкладку Создание (Create).

2. Щелкните на кнопке Конструктор запросов (Query Design).

3. В открывшемся диалоговом окне Добавление таблицы (Show Table) выделите строку Список.

4. Щелчком на кнопке Добавить (Add) добавьте выбранную таблицу в верхнюю область конструктора запроса.

5. Щелчком на кнопке Закрыть (Close) закройте диалоговое окно Добавление таблицы (Show Table).

Новое в Office

Во время работы с конструктором запросов на ленте появляется новая вкладка Конструктор (Design) с контекстными инструментам для работы с запросами, представленная на рис. 17.13. Щелкнув на кнопке в группе Тип запроса (Query Туре), вы можете выбрать соответствующий тип запроса. Описание всех возможных вариантов запросов, предлагаемых программой Access, приведено в табл. 17.2.

Рис. 17.13. Инструменты вкладки Конструктор

6. На вкладке Конструктор (Design) в группе Тип запроса (Query Туре) щелкните на кнопке Обновление (Update). Структура бланка запроса изменится в соответствии с типом запроса. В бланке выбранного варианта запроса на обновление появится поле Обновление (Update То), в которое нужно ввести новое значение поля. Чтобы изменить месяц даты с февраля на март, достаточно прибавить к дате 28 дней.

Таблица 17.2. Варианты запросов

7. Перетащите в бланк запроса поле Дата, значение которого нужно обновлять (рис. 17.14).

Рис. 17.14. Запрос на обновление

8. Введите в ячейку Обновление (Update То) формулу [Дата]+28.

Совет

Название поля, заключенное в квадратные скобки, является ссылкой на значение поля. Чтобы сослаться на поле другой таблицы, нужно в начале указать имя таблицы, а затем имя поля (оба имени в квадратных скобках) и разделить их восклицательным знаком, например: [Контакт]![Фамилия].

9. Чтобы обновлялись только даты, относящиеся к февралю 2007 года, введите в ячейку Условие отбора (Criteria) следующую формулу (эта формула подробно обсуждалась в третьем упражнении этого занятия): Between DateValue («1.02.2007») And DateValue («28.02.2007»)

10. Закройте запрос, сохранив его под именем Обновление.

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

Внимание!

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

12. Дважды щелкните на значке Обновление.

13. Щелкните на кнопке Да (Yes) в ответ на запрос о необходимости запуска запроса действия.

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

14. Щелкните на кнопке Да (Yes) и изучите изменения, произошедшие с данными таблицы Список.

Фильтры

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

Упражнение 6. Установка фильтра

Установка фильтра – простейший способ отбора части записей в таблице, запросе или форме. В этом упражнении с помощью фильтра будет выделено несколько записей таблицы Контакт.

1. В области переходов щелкните дважды на значке Контакт (рис. 17.15).

2. В столбце Фамилия щелкните в ячейке с фамилией Петров.

3. Перейдите на вкладку Главная (Home). В группе Сортировка и фильтр (Sort & Filter) щелкните на кнопке Выделение (Selection) и выберите в раскрывшемся списке команду Равно «Петров» (Equals «Петров»). В результате видимыми останутся только те записи, в поле Фамилия которых присутствует значение Петров.

Рис. 17.15. Фильтрация записей

4. Чтобы отменить действие фильтра, щелкните на кнопке Применить фильтр (Toggle Filter).

Совет

Access запоминает последний применявшийся фильтр. Его можно снова назначить повторным щелчком на кнопке Применить фильтр (Toggle Filter).

Фильтр может быть и более сложным. Например, можно оставить все записи с фамилией Петров, имеющие непустое поле номера телефона.

5. Щелкните на кнопке Дополнительно (Advanced) и выберите в раскрывающемся списке команду Расширенный фильтр (Advanced Filter/Sort). Откроется окно, похожее на окно конструктора запроса (рис. 17.16). Критерий равенства поля Фамилия значению Петров уже будет присутствовать в бланке фильтра.

Рис. 17.16. Окно расширенного фильтра

6. Перетащите поле Рабочий телефон во второй столбец бланка.

7. В ячейку Условие отбора (Criteria) введите формулу <>Null.

Значение любого пустого поля равно величине Nul 1. Условие <>Nul 1 позволяет проверить поле на неравенство величине Null, то есть отбирает все непустые поля.

8. Выберите команду Применить фильтр (Toggle Filter). Настроенный фильтр будет применен к таблице Контакт. В результате в ней останутся только две записи.

9. С помощью команды Дополнительно → Очистить все фильтры (Advanced → Clear All Filters) отмените фильтрацию.

Упражнение 7. Преобразование фильтра в запрос

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

1. Командой Дополнительно → Расширенный фильтр (Advanced → Advanced Filter/Sort) активизируйте окно сложного фильтра, созданного в предыдущем упражнении.

2. Щелкните на кнопке Сохранить (Save), расположенной на панели быстрого доступа.

3. В открывшемся диалоговом окне введите имя Фильтр и щелкните на кнопке ОК.

4. Закройте окно фильтра.

5. В области переходов в группе Контакт появился новый запрос с названием Фильтр. Дважды щелкните на строке этого запроса.

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

6. Щелчком активизируйте окно таблицы Контакт (если таблица закрыта, откройте ее).

7. Чтобы назначить новый фильтр (и стереть предыдущий), щелкните в ячейке с фамилией Петров правой кнопкой мыши и выберите в контекстном меню команду Не равно «Петров» (Does Not Equal «Петров»). В таблице останутся только те записи, в поле Фамилия которых нет слова Петров.

8. Щелчком на кнопке Применить фильтр (Toggle Filter) отмените фильтрацию данных. Теперь давайте вернем прежний сложный фильтр.

9. Выберите команду Дополнительно → Расширенный фильтр (Advanced → Advanced Filter/Sort).

10. Щелкните правой кнопкой мыши в верхней части конструктора фильтра и выберите команду Загрузить из запроса (Load from Query).

11. В открывшемся диалоговом окне выделите запрос Фильтр и щелкните на кнопке ОК.

Старые условия фильтрации появятся в бланке окна фильтра. После этого для применения сохраненного фильтра достаточно щелкнуть на кнопке Применить фильтр (Toggle Filter).