База данных представляет собой хранилище информации, которое может вмещать от нескольких записей в одной таблице до тысяч записей во множестве связанных таблиц. Независимо от объема информации база данных полезна лишь в том случае, если в ней можно найти то, что нужно, и когда нужно. В небольшой базе данных достаточно прокрутить таблицу, чтобы появилась нужная запись. Но по мере развития и усложнения базы данных, поиск информации затрудняется.
Microsoft Access включает ряд инструментов, позволяющих найти в базе данных нужные сведения и представить их в различных видах. Так, например, можно извлечь из таблицы записи, отвечающие определенным условиям, и отсортировать их по одному или нескольким полям. Приложив некоторые усилия, можно создать запрос, отображающий данные из нескольких таблиц. При желании запрос можно сохранить и использовать в дальнейшем.
Возможности запросов не ограничиваются извлечением данных из таблиц. Используя встроенные функции, можно производить вычисления над содержимым полей для получения суммы, количества, среднего арифметического и других значений.
В этой лекции вы узнаете, как найти и отобразить нужную информацию с помощью сортировки, фильтрации и запросов. В процессе выполнения упражнений вам понадобятся файлы базы данных GardenCo, которые хранятся в папках Sort, FilterDS, FilterForm, AdvFilter, QueryDs, QueryWiz и Aggregate, вложенных в папку Office XP SBS\Access\Chap12.
Сортировка данных
Информация в таблицах может быть отсортирована в порядке убывания или возрастания по одному или нескольким полям. Например, можно отсортировать таблицу клиентов по алфавиту, воспользовавшись фамилией в качестве первого поля сортировки и именем - в качестве второго. Получится список, подобный тому, который встречается в телефонных справочниках.
| Фамилия | Имя |
|---|
| Кембелл |
Девид |
| Кембелл |
Мишель |
| Кенеди |
Кевин |
| Керни |
Нил |
| Ким |
Шейн |
В некоторых случаях сортировка применяется для того, чтобы сгруппировать табличные данные по определенному принципу. Например, если, прежде чем печатать почтовые наклейки, отсортировать адресную информацию по почтовому индексу, наклейки будут сгруппированы в соответствии с требованиями почтовой службы.
GardenCo
В этом упражнении вы воспользуетесь различными способами сортировки данных в таблице и форме. В качестве рабочей будет использоваться папка Office XP SBS\Access\Chap12\Sort. Выполните следующие шаги.
Откройте базу данных GardenCo, расположенную в рабочей папке.
Откройте таблицу Клиенты в режиме таблицы.
Чтобы отсортировать таблицу по полю Штат, щелкните в любом месте столбца Штат, а затем щелкните на кнопке . Записи будут упорядочены по регионам.Совет. Можно также воспользоваться командами Сортировка по возрастанию (Sort Ascending) и Сортировка по убыванию (Sort Descending) в меню Записи (Records) или щелкнуть правой кнопкой мыши на столбце, а затем щелкнуть на нужной команде в контекстном меню.
Чтобы изменить порядок сортировки на обратный (при условии, что курсор все еще находится в столбце . Записи для штата Орегон находятся теперь в начале списка. В обоих случаях сортировка осуществлялась по алфавиту, но данные в поле Город располагаются беспорядочно. Очевидно, что список выиграет, если упорядочить города внутри регионов.Принципы сортировки в Access
Хотя суть сортировки кажется вполне очевидной, для компьютера все не так просто. В Access цифровые данные могут трактоваться и как числа, и как текст. Поля, содержащие, помимо цифр, пробелы, тире или знаки препинания (как, например, в почтовом индексе или телефонных номерах), трактуются как текстовые. В отличие от них поля, содержащие такие данные, как цена или количество, считаются числовыми. При сортировке текстовых полей, все записи упорядочиваются вначале по первому символу, затем по второму и так далее - вплоть до последнего. Сортировка числовых данных осуществляется по их значениям в порядке возрастания или убывания. Такой подход может привести к неожиданным результатам. В следующей таблице представлены результаты сортировки первого столбца в зависимости от того, является его содержимое текстовым или числовым.
| Исходный список | Отсортированный как текст | Отсортированный как числа |
|---|
| 1 |
1 |
1 |
| 1234 |
11 |
2 |
| 23 |
12 |
3 |
| 3 |
1234 |
4 |
| 11 |
2 |
5 |
| 22 |
22 |
11 |
| 12 |
23 |
12 |
| 4 |
3 |
22 |
| 2 |
4 |
23 |
| 5 |
5 |
1234 |
Чтобы отсортировать текстовое поле, содержащее цифры, в порядке возрастания или убывания значений, нужно дополнить их ведущими нулями до одинаковой длины. Например, данные 001, 011, 101 будут отсортированы правильно, даже если они определены как текстовые.
Совет. При сортировке записей по нескольким полям сортировка осуществляется последовательно, слева направо. Поэтому поля сортировки должны быть смежными и располагаться в нужной последовательности.
Чтобы разместить столбец Штат слева от поля Город, щелкните на заголовке столбца и перетащите его влево, пока между полями Адрес и Город не появится темная линия.
Так как столбец Штат уже выделен, нажмите на клавишу (Shift) и щелкните в заголовке столбца Город, чтобы распространить выделение на оба столбца.
Щелкните на кнопке Сортировка по возрастанию (Sort Ascending), чтобы упорядочить записи по регионам и городам в порядке возрастания (города будут отсортированы внутри регионов).Совет. Просматривая записи в режиме формы, их можно отсортировать, но только по одному полю. Для этого щелкните в нужном поле, а затем щелкните на одной из кнопок Сортировка (Sort).
Хотя порядок столбцов в таблице Клиенты не имеет особого значения, все же верните столбец Штат на прежнее место.
Сохраните и закройте таблицу Клиенты.
Закройте базу данных.
Фильтрация записей в таблице
В отличие от сортировки, которая упорядочивает информацию, фильтрация позволяет извлечь из таблицы записи, отвечающие определенным условиям. Например, можно создать фильтр, который находит в таблице Клиенты всех клиентов, проживающих в Сиэтле, или клиентов, разместивших заказы после 18 января.
Работая с таблицами и формами, можно задавать простые фильтры. Обычно эти фильтры относятся к выделенному полю, но, если к результатам отбора применить дополнительные фильтры, область просмотра сузится еще больше.
Совет. Фильтрация записей осуществляется с помощью команды Фильтр (Filter) в меню Записи (Records), кнопок на панели инструментов и команд в контекстном меню. Доступ к этим командам зависит от конкретной ситуации.
GardenCo
В этом упражнении вы воспользуетесь несколькими способами фильтрации записей. В качестве рабочей будет использоваться папка Office XP SBS\Access\Chap12\FilterDS. Выполните следующие шаги.
Откройте базу данных GardenCo, расположенную в рабочей папке.
Откройте таблицу Клиенты в режиме таблицы.
Щелкните на значении "Бойсе" в поле . В таблице из 109 записей останутся 2, поскольку в Бойсе живут только два клиента.Важно. При фильтрации таблицы, записи, не отвечающие условиям отбора, не удаляются, а просто перестают отображаться.
Щелкните на кнопке , чтобы снова отобразить все записи таблицы.
Отобразите всех клиентов, имеющих почтовый индекс, начинающийся с символов "V3F". Для этого найдите в таблице запись с таким индексом, выделите символы "V3F", а затем щелкните на кнопке Фильтр по выделенному (Filter By Selection). В таблице останутся только две записи, имеющие почтовые индексы, начинающиеся с "V3F".
Щелкните на кнопке Удалить фильтр (Remove Filter).
Если таблица слишком велика, чтобы искать в ней запись, содержащую "V3F" в почтовом индексе, можно воспользоваться контекстным меню. Щелкните правой кнопкой мыши на любом почтовом индексе, в контекстном меню щелкните в поле Фильтр для (Filter For), введите V3F* и нажмите на клавишу (Enter).
Звездочка (*) является заменителем символов и означает, что нужно найти все вхождения почтового индекса, начинающие с "V3F".
Чтобы отобразить клиентов, живущих вне Соединенных Штатов, удалите текущий фильтр, щелкните правой кнопкой мыши в поле Страна, которое содержит значение "США", и щелкните на команде Исключить выделенное (Filter Excluding Selection) в контекстном меню. В таблице отобразятся клиенты из других стран (в данном случае из Канады).Заменитель символов
Если точное значение критерия отбора неизвестно, можно воспользоваться заменителями символов (wildcard characters). Наиболее распространенные из них представлены в следующей таблице:
| Символ | Назначение | Пример |
|---|
| * |
Заменяет любое число символов |
Lname = Co* возвращает значения типа Colman и Conroy |
| ? |
Заменяет один символ |
Fname = eri? возвращает значения типа Eric и Erik |
| # |
Заменяет одну цифру |
ID = 1## возвращает все значения от 100 до 199 |
Чтобы поэкспериментировать с еще одним способом фильтрации, удалите фильтр, сохраните и закройте таблицу Клиенты, а затем откройте таблицу Заказы в режиме таблицы.
Чтобы отобразить все заказы, принятые Майклом Эмони 23 января, в поле Код сотрудника щелкните правой кнопкой мыши на 6 (код Эмони Майкла) и щелкните на команде Фильтр по выделенному (Filter By Selection) в контекстном меню.
Щелкните правой кнопкой мыши в поле ДатаРазмещения, содержащем значение 1/23/01, и снова щелкните на команде Фильтр по выделенному (Filter By Selection) в контекстном меню. В таблице останутся только заказы, сделанные Майклом 23 января. Можно усложнить фильтр, добавив условие отбора по другому полю, или отсортировать результаты фильтрации. Для этого щелкните в поле сортировки, а затем щелкните на одной из кнопок Сортировка (Sort).Совет. Отобрав и упорядочив нужную информацию, можно отобразить результаты в виде формы или запроса. Щелкните на кнопке Новый объект (New Object) на панели инструментов и следуйте указаниям.
Удалите фильтры, щелкнув на кнопке Удалить фильтр (Remove Filter).
Сохраните и закройте таблицу Заказы.
Закройте базу данных.
Совет. Команды фильтрации позволяют фильтровать записи таблицы и при работе с формой. Особенно полезной может оказаться команда Фильтр для (Filter For), поскольку не требует отображения выделяемого значения в поле формы.
Использование функции Изменить фильтр (Filter By Form)
С помощью функции Изменить фильтр (Filter By Form) можно легко и быстро создать сложный фильтр, основанный на содержимом полей таблицы. Если открыть таблицу, а затем щелкнуть на кнопке Изменить фильтр (Filter By Form) вместо записей таблицы отобразится пустая строка, ячейки которой представляют собой поля со списками, содержащими все значения, встречающиеся в данном столбце таблицы.
Выбрав элемент из списка, можно щелкнуть на кнопке Применить фильтр (Apply Filter), чтобы отобразить только те записи, которые содержат выбранное значение.
Если таблица содержит десятки полей, использование функции Изменить фильтр (Filter By Form) усложняется, поскольку приходится прокручивать таблицу по горизонтали. В этом случае лучше воспользоваться кнопкой Изменить фильтр (Filter By Form) в режиме формы. Вместо исходной формы отобразится пустая форма, предназначенная для создания фильтра. Если щелкнуть в любом поле, а затем щелкнуть на стрелке, появится список всех значений поля, как показано ниже.
Выбрав значение и щелкнув на кнопке Применить фильтр (Apply Filter), щелкните на кнопке Следующая запись (Next), чтобы отобразить первую из отобранных записей. Воспользуйтесь кнопками перехода, чтобы просмотреть остальные записи.
Совет. Функция Изменить фильтр (Filter By Form) применима как в режиме таблицы, так и формы. В тех случаях, когда проще задать фильтр в форме, а просматривать результаты удобнее в таблице, можно быстро создать форму, воспользовавшись функцией Автоформа (Autoform). Затем, определив фильтр в форме, можно переключиться в режим таблицы для просмотра результатов.
GardenCo
В этом упражнении вы воспользуетесь функцией Изменить фильтр (Filter By Form), чтобы найти клиента, фамилию которого забыли, но уверены, что он живет либо в Калифорнии, либо в Вашингтоне, а фамилия начинается с буквы "С". В качестве рабочей будет использоваться папка Office XP SBS\Access\Chap12\FilterForm. Выполните следующие шаги.
Откройте базу данных GardenCo, расположенную в рабочей папке.
На панели объектов щелкните на пункте Формы (Forms), а затем щелкните дважды на форме Клиенты, чтобы открыть ее в режиме формы.
Щелкните на кнопке на панели инструментов. Вместо формы Клиенты, в которой отображалась запись таблицы, появится аналогичная форма с пустыми полями и вкладками Найти (Look for) и Или (Or), указанными внизу.
Щелкните во втором поле Имя, введите с* и нажмите на клавишу (Enter) (указав тем самым, что нужно отобразить всех клиентов, фамилии которых начинаются с "С"). Access преобразует введенное значение в выражение: Like "с*".
Щелкните в поле Штат и выделите в списке Калифорния.
Щелкните на кнопке , чтобы отобразить клиентов из Калифорнии, фамилии которых начинаются с буквы "С". Окно фильтра заменится формой Клиенты. На панели перехода внизу формы указано, что найдено 3 записи.
Щелкните на кнопке Изменить фильтр (Filter By Form), чтобы вернуться к фильтру. Поля формы все еще содержат заданные значения. При создании фильтра условия отбора сохраняются до тех пор, пока их не заменят другими.
Чтобы добавить в условия отбора клиентов из другого штата, щелкните на вкладке Или (Or). Эта вкладка содержит те же пустые поля, что и вкладка Найти (Look for).Совет. При переходе на вкладку Или (Or) появляется вторая вкладка Или (Or), позволяющая задать третье условие.
В поле Фамилия, введите с*, в поле Штат выделите из списка или введите значение Вашингтон, а затем щелкните на кнопке Применить фильтр (Apply Filter). Прокрутите форму, чтобы просмотреть шесть отфильтрованных записей.
Закройте форму Клиенты, а затем закройте базу данных.
Отбор информации по нескольким критериям
Команды Фильтр по выделенному (Filter By Selection), Фильтр для (Filter For) и Изменить фильтр (Filter By Form) позволяют легко и быстро извлечь из базы данных нужную информацию, пока фильтры остаются относительно простыми. Но предположим, что требуется найти сведения о заказах, отправленных в западные штаты в определенный период времени одной из двух фирм, занимающихся доставкой. В подобных ситуациях, когда нужно создать сложный фильтр, содержащий несколько условий отбора или выражений, можно воспользоваться командой Расширенный фильтр (Advanced Filter/Sort).
Построение расширенного фильтра осуществляется с помощью бланка, представленного на следующем рисунке.
Бланк построения может использоваться только для одной таблицы.
Совет. Если простой запрос, созданный в окне фильтра, предполагается использовать в дальнейшем, сохраните его в виде запроса. Это можно сделать с помощью команды Сохранить как запрос (Save As Query) в меню Файл (File) или кнопки Сохранить как запрос (Save As Query) на панели инструментов. Можно также щелкнуть правой кнопкой мыши в окне фильтра, а затем щелкнуть на команде Сохранить как запрос (Save As Query) в контекстном меню.
GardenCo
В этом упражнении вы воспользуетесь командой Расширенный фильтр (Advanced Filter/Sort), чтобы создать фильтр для отображения клиентов из двух штатов, а затем поэкспериментируете с бланком построения фильтра, чтобы оценить его возможности. В качестве рабочей будет использоваться папка Office XP SBS\Access\Chap12\AdvFilter. Выполните следующие шаги:
Откройте базу данных GardenCo, расположенную в рабочей папке.
На панели объектов щелкните на Таблицы (Tables), а затем щелкните дважды на таблице Клиенты, чтобы открыть ее в режиме таблицы.
В меню Записи (Records) укажите на команду Фильтр (Filter), а затем щелкните на Расширенный фильтр (Advanced Filter/Sort). Откроется окно фильтра со списком полей таблицы Клиенты в верхней части.
Если бланк построения не пустой, щелкните на команде Очистить бланк (Clear Grid) в меню Правка (Edit).
Щелкните дважды на поле Фамилия, чтобы скопировать его в ячейку Поле (Field) первого столбца бланка.
Щелкните в ячейке Условие отбора (Criteria) в столбце Фамилия, введите c* и нажмите на клавишу (Enter). Введенное значение будет преобразовано к виду: Like "c*".
Прокрутите список полей таблицы Клиенты до конца и щелкните дважды на поле Штат, чтобы скопировать его в следующий свободный столбец бланка.
Щелкните в ячейке Условие отбора определяет теперь клиентов из штатов Калифорния и Вашингтон, фамилии которых начинаются с буквы "С".
В меню Совет. Уменьшив размеры окон, можно одновременно видеть фильтр и таблицу.
В меню Записи (Records) укажите на команду Фильтр (Filter), а затем щелкните на Расширенный фильтр (Advanced Filter/Sort), чтобы вернуться в окно фильтра.
Щелкните в ячейке
В меню Фильтр (Filter) щелкните на команде Применить фильтр (Apply Filter/Sort). При построении фильтра условия, заданные в одной строке, объединяются оператором И (And), а условия, заданные в разных строках - оператором Или (Or). Поэтому результаты отбора включают клиентов, проживающих в Калифорнии или Вашингтоне, фамилии которых начинаются с буквы "С", и клиентов с фамилиями, начинающимися с буквы "В", независимо от их местожительства.
Вернитесь в окно фильтра, введите Калифорния or Вашингтон в ячейку Или (Or) в столбце Штат и нажмите на клавишу (Enter).
Снова примените фильтр, чтобы отобразить клиентов только из штатов Калифорния и Вашингтон.
Закройте таблицу Клиенты без сохранения изменений, а затем закройте базу данных.
Выражения
В Access выражение (expression) представляет собой сочетание операторов, констант, функций и свойств, определяющих конечный результат. Собственно, это часть формулы, распложенная справа от знака равенства. Выражения используются при определении свойств таблиц и форм, для вычисления значений в формах или отчетах, при построении запросов и во многих других ситуациях.
Используя выражения, можно создавать сложные критерии, которые включают несколько условий отбора, объединенных логическими, арифметическими операторами и операторами сравнения.
Наиболее распространенными логическими операторами являются И (And), Или (Or) и Нет (Not). Если условия отбора объединены оператором И (And), будут отобраны записи, удовлетворяющие всем условиям. При использовании оператора Или (Or) искомые записи должны соответствовать хотя бы одному из заданных условий, а оператор Нет (Not) отберет только те записи, которые не отвечают ни одному из условий отбора.
Операторы сравнения включают следующие символы: > (меньше чем), < (больше чем) и = (равно). Кроме того, часто используются комбинации основных операторов: <= (меньше или равно), >= (больше или равно) и <> (не равно). Операторы сравнения иногда используются в сочетании с оператором Like, который проверяет значения полей на соответствие образцу.
Арифметические операторы (сложение (+), вычитание (-), умножение (*) и деление (/)) используются для действий над числами. Близкий к ним оператор используется для сцепления (текстовый аналог сложения) двух текстовых строк.
