Базы данных Microsoft Access 2003
В предыдущих главах описывались методы
В предыдущих главах описывались методы создания и применения практически всех объектов Access. И хотя пройден уже немалый путь, до его конца очень далеко.
В настоящей главе мы поговорим о запросах, которые позволяют не только получать данные. По мере того, как объем данных и потребности в них возрастают, запросы становятся все более сложными. Со временем возможностей мастера простых запросов становится явно недостаточно. Когда такое время наступает, существует несколько возможных вариантов действий.
С помощью мастера нахождения повторяющихся записей можно создать запрос на выборку, обнаруживающий дублированные записи в одной таблице. Воспользовавшись мастером обнаружения записей без подчиненных можно создать запрос на выборку, способный обнаружить в одной таблице записи, у которых нет связанных записей в другой таблице. С помощью мастера создания перекрестных запросов можно создать разновидность запроса на выборку, суммирующего данные по категориям. Параметрические запросы требуют ввода соответствующего критерия при их запуске. В результате один и тот же запрос можно использовать многократно для получения различных результатов. Запросы действия позволяют добавлять новые и изменять уже существующие данные, а также удалять их.
В этой главе описано применение различных запросов, позволяющих решить задачи по обработке данных различной сложности.
Использование мастеров запросов
По сути, запрос представляет собой обычный вопрос. Пользователь задает вопрос, a Access возвращает ответ в виде определенных данных. Например, можно выяснить у Access, какие лечебные растения приобретены из тех или иных каталогов. Это весьма точный вопрос, и Access отобразит только растения, имеющие тип Лечебные и содержащиеся в указанном в запросе каталоге. Такой тип запроса называется запросом на выборку, так как Access получает и отображает только данные, соответствующие выбранным заранее критериям.
Мастер простых запросов, описанный в главе 7, «Получение данных с помощью запросов», не единственный. Кроме него, в нашем распоряжении есть еще три мастера запросов:
мастер нахождения повторяющихся записей; мастер нахождения записей без подчиненных; мастер перекрестных запросов.
Эти три мастера позволяют выполнять самые разнообразные, подчас довольно сложные, задачи.
Мастер нахождения повторяющихся записей
Прежде чем приступить к изучению мастера, выясним, что же собой представляет повторяющаяся запись. Дубликаты могут встречаться в одних полях, и в то же время их появление исключено в других полях. Например, как рассказывалось в главе 4, «Планирование базы данных», и в главе 6, «Использование взаимосвязей», уникальной должна быть каждая запись в ключевом поле. Более того, некоторым полям требуется уникальный индекс, и поля такого рода принимают только уникальные записи. Более подробно индексы описываются в главе 11, «Настройка таблиц».
С другой стороны, в некоторых полях допускается повторение записей. Например, в поле
Имя Каталога таблицы Растения по много раз повторяются имена одних и тех же каталогов, а в поле
Страна таблицы Каталоги неоднократно повторяется запись Россия. Более того, в таблицах нередко присутствует по нескольку повторяющихся значений.
Дублирование записей может стать причиной возникновения серьезных ошибок. Предположим, при создании таблицы заказов один из них был введен дважды. В результате клиент получит две квитанции или же два счета на оплату одной и той же суммы — ни к чему хорошему это, как вы сами понимаете, не приведет.
В дублированной записи при использовании типа данных Счетчик повторяются все поля, за исключением поля первичного ключа (более подробная информация об этом типе данных представлена в главе 11). Кроме того, повторяющиеся значения могут быть размещены лишь в нескольких важных полях. К примеру, база данных содержит записи о сотнях растениях, при этом довольно сложно определить, записи о каких растениях уже были введены, а о каких еще нет. Несколько сотен записей, в частности запись о синем васильке, вводятся повторно. При этом могут использоваться разные фотографии и даже разные торговые каталоги, но обычное и латинское имена будут одинаковыми. В результате получится дублированная запись, несмотря на то, что совпадают не все поля записи.
Пока что в созданной нами базе данных повторяющихся записей нет, поэтому придется создать такие записи самостоятельно, чтобы потренироваться в использовании мастера их нахождения. Откройте таблицу
Каталоги и введите новую запись в поле Имя, повторив все данные каталога
Огородник, за исключением его имени. В качестве имени каталога введите Огородники (во множественном числе). Это поле является первичным ключом, поэтому каждая его запись должна быть уникальной.
Теперь, выполнив перечисленные ниже действия, запустите мастер, который должен обнаружить дублированные записи.
1. Выберите команду Вставка>Запрос, затем дважды щелкните на опции Повторяющиеся записи в окне
Новый запрос (рис. 12.1). Или же выберите опцию одним щелчком и щелкните на кнопке
ОК.
Рис. 12.1. Окно мастера нахождения повторяющихся записей
2. На рис. 12.2 показана первая панель запроса, на которой выбрана таблица Каталоги (поскольку это первая таблица в базе данных). Это именно то, что нужно, поэтому щелкните на кнопке
Далее.
Рис. 12.2. Выбрана таблица Каталоги
3. Чтобы найти дублированные записи в поле Имя, дважды щелкните в списке Доступные поля на элементе
Имя, чтобы перенести его в список Поля с повторами, как показано на рис. 12.3. Щелкните на кнопке
Далее.
Рис. 12.3. Поиск дублированных записей в поле Имя
4. На следующей панели можно добавить второе поле, которое поможет идентифицировать любые обнаруженные записи. Но поскольку в данном случае поиск будет производиться по именам каталогов, никаких элементов выбирать не нужно. Щелкните на кнопке
Далее.
5. В последнем окне запросу будет предложено присвоить имя, используемое по умолчанию. Оставьте его таким как есть и щелкните на кнопке
Готово. Появится окно, показанное на рис. 12.4.
Рис. 12.4. Запрос демонстрирует, что в поле Имя не существует дублированных записей
Удивлены? Как видите, запрос не отобразил никаких записей. Дублированная запись была введена ранее и, тем не менее, мастер не ошибся. Дело в том, что вводимое во второй раз имя было изменено (с добавлением окончания «и»). Мастер совершенно справедливо считает имена
Огородник и Огородники разными, хотя на самом деле таковое одно, но дано в единственном и множественном числе. А так как сравнивались не все поля — поиск проводился только для поля
Имя — никаких дубликатов мастер не нашел.
Закройте окно запроса и попробуйте выполнить поиск еще раз. Теперь используем другое поле и увидим, что получится. Повторите действия, описанные в пп. 1-5, однако на этот раз сначала укажите поле
Адрес, а затем в п. 4, добавьте еще и поле Имя. Присвойте запросу на последней панели мастера имя
ДубликатыКаталога.
Результаты выполнения запроса приведены на рис. 12.5. На этот раз возвращено две записи, поскольку один и тот же адрес указан в таблице несколько раз. Дополнительное поле
Имя позволяет определить, для какого каталога запись была введена дважды. И хотя об этом нельзя судить по полученным результатам, но для определения повторов мастер применяет общий вид запроса (более подробно он описан далее в этой главе).
Рис. 12.5. На этот раз найдено несколько повторяющихся записей
Поиск не обязательно должен быть ограничен только одним полем, как в описанных выше примерах. При желании можно искать различные записи в нескольких полях. Допустим, вам понадобилось узнать, у каких сотрудников совпадают имя и фамилия. Для этого поиск нужно осуществлять по двум полям. Более того, его можно производить в любом поле таблицы, за исключением ключевого. Поскольку ключевое поле всегда содержит уникальное значение, мастер никогда не найдет в нем дублирующихся записей, даже если все другие значения записи полностью совпадают хотя бы с одной записью.
Попробуйте еще несколько раз выполнить поиск, например, одинаковых записей о городах или областях. По окончании эксперимента не забудьте удалить из таблицы
Каталоги запись Огородники.
Мастер нахождения записей без подчиненных
Этот мастер способен обнаружить записи, не связанные с другими таблицами в базе данных. С его помощью таким образом можно, например, узнать, какие клиенты не разместили свои заказы или же кто из сотрудников ни разу не болел. Запросы такого рода можно создавать самостоятельно, однако мастер предложит вам свой вариант и тем самым сэкономит ваше время.
Воспользуемся мастером для поиска каталогов без записей, т.е. таких, из которых растения еще не заказывались.
1. Запустите мастер, выбрав команду Вставка>Запрос, и дважды щелкните на опции
Записи без подчиненных (рис. 12.1).
2. На первом окне мастера выберите таблицу, содержащую записи первичного ключа. В данном случае речь идет о таблице Каталоги, но, поскольку таблица выбрана по умолчанию, достаточно щелкнуть на кнопке
Далее.
3. В следующем окне выберите таблицу, содержащую связанные записи или значения внешнего ключа; в нашем случае, это будет таблица
Растения. Щелкните на кнопке Далее.
4. Мастеру требуется указать поля, которые содержат связанные записи, при этом он попытается найти их самостоятельно. На рис. 12.6 показано, что мастер корректно определил нужные поля —
Имя и ИмяКаталога. Щелкните на кнопке Далее.
Рис. 12.6. Мастер определил поля, связанные между собой в двух таблицах
5. В следующем окне укажите поля, которые следует отображать в окне запроса Как видно из рис. 12.7, понадобится указать только поле
Имя. Щелкните на кнопке Далее.
Рис. 12.7. В запросе понадобится указать только поле Имя
6. В открывшемся окне примите имя запроса по умолчанию и щелкните на кнопке
Готово. Появится окно запроса, показанное на рис. 12.8.
Рис. 12.8. Мастер обнаружил два каталога, растения из которых еще не заказывались
Итак, мастер обнаружил два каталога, для которых нет связанных записей - Сад и
Посевная. Достаточно открыть таблицу Растения, чтобы убедиться, что это соответствует истине. С другой стороны, при необходимости просмотреть записи нескольких растении открывать таблицу не нужно - мастер позволяет это сделать намного быстрее.
Мастер перекрестных запросов
Перекрестный запрос отличается от обычных запросов, и его создание может быть сопряжено с определенными сложностями. Именно здесь на помощь придет мастер создания таких запросов.
Перекрестный запрос распределяет данные по категориям. Основная задача этого запроса состоит в отображении большого объема данных в сравнительно маленькой области просмотра, но в читабельном формате. Например, с его помощью можно определить количество заказов, размещаемых каждый день, или вычислить количество отгулов, взятых каждым служащим за месяц. В обоих случаях перекрестный запрос позволит добиться требуемого результата.
Каждый перекрестный запрос должен включать в себя следующие компоненты:
заголовок столбца; итоговое поле; заголовок строки.
Перечисленные выше компоненты будут использоваться при создании запроса с помощью мастера.
Если перекрестный запрос нужно создать на базе нескольких таблиц, следует сначала создать обычный запрос. Учитывая получаемые результаты, это довольно удобно, достаточно лишь не забывать о данном условии.
Создание запроса
Первый шаг в создании перекрестного запроса заключается в формировании простого запроса, содержащего все необходимые данные. Предположим, нужно узнать, сколько типов растений было заказано из каждого каталога. В этом случае без перекрестного запроса не обойтись. Требуется подсчитать количество типов на основе имени каталога. Для создания простого запроса, на основании которого затем будет создан перекрестный запрос, выполните такие действия.
1. В окне Растения: база данных выберите таблицу Растения.
2. Выполните команду Вставка>Запрос и дважды щелкните на опции Конструктор в диалоговом окне
Новый запрос.
3. Добавьте в поле конструктора запроса таблицу Типы, щелкнув на кнопке
Отобразить таблицу. Затем дважды щелкните на имени Типы и на кнопке
Закрыть.
4. Перетащите на таблицу конструктора поля Имя Каталога и Номер
типа из таблицы Растения, а также поле Описание из таблицы
Типы (рис. 12.9).
Рис. 12.9. Добавьте к запросу поля ИмяКаталога, Номер типа и Описание
5. Сохраните запрос под именем Перекрестный Запрос и закройте окно конструктора. Если в процессе создания запроса у вас возникли какие-то затруднения, еще раз просмотрите главу 7.
Теперь вы можете запустить мастер перекрестных запросов — для этого достаточно выполнить такие действия.
1. Выберите команду Вставка>Запрос. Дважды щелкните на опции Перекрестный запрос в диалоговом окне
Новый запрос.
2. На первой панели мастера укажите источник данных запроса. В этом качестве
теперь будет выступать уже созданный простой запрос, поэтому выберите на панели
Показать переключатель Запросы, чтобы обновить содержимое списка элементов управления. Затем выберите значение
ПерекрестныйЗапрос (рис. 12.10).
Рис. 12.10. Выбор запроса, содержащего данные дм перекрестного запроса
3. Обратите внимание на то, как изменился образец запроса в нижней области экрана, отображающий порядок расположения данных в будущем результате выполнения запроса. Вы можете периодически обращаться к этому образцу, как при выполнении последующих шагов данного примера, так и в процессе самостоятельной работы с мастером. Образец поможет в выборе требуемых параметров. Щелкните на кнопке
Далее.
4. На следующей панели укажите поле для заголовка строки. Поскольку растения объединяются в соответствии с их типом, выберите поле Описание (рис. 12.11). В результате в запросе будет представлен текст, описывающий тип, а не ключевое значение последнего. Щелкните на кнопке
Далее.
Рис. 12.11. Поле Описание указано в качестве поля заголовка строки для перекрестного запроса
5. На следующей панели выбирается заголовок столбца. Из рис. 12.12 следует, что в этом качестве используется поле
ИмяКаталога. Щелкните на кнопке Далее.
6. Теперь необходимо указать поле, содержимое которого будет суммироваться. Выберите поле
НомерТипа. Кроме того, следует определить метод обобщения данных этого поля. В списке
Функции представлен перечень всех возможных методов обобщения, от метода подсчета максимального (или минимального) значения до метода, основанного на применении сложных статистических функций, вроде функции подсчета среднеквадратического отклонения. Нам требуется подсчитать количество записей, поэтому выберите значение
Число. На рис. 12.13 показана обновленная панель Образец, в которой представлено выражение
Число (Номер типа). Щелкните на кнопке Далее.
Рис. 12.12. Поле Имя Каталога выбрано в качестве поля заголовка столбца для перекрестного запроса
Рис. 12.13. Суммирование значений поля Номер типа путем подсчета количества записей для каждого каталога
7. В последнем окне мастера запросу присваивается имя. Изменять его нет необходимости. Чтобы просмотреть полученные результаты, выберите переключатель
Просмотреть результаты запроса. Для открытия запроса в режиме конструктора и внесения требуемых изменений выберите переключатель
Изменить структуру запроса. Если вы установите флажок Вывести справку по работе с перекрестной таблицей, откроется окно справочной системы с информацией по перекрестным запросам. Но сейчас не стоит устанавливать этот флажок; щелкните на кнопке
Готово, и вы получите результат, показанный на рис. 12.14. В первой записи указывается, что обнаружено шесть декоративных растений, из них три из каталога
Огородник, одно из каталога Фермер и два из каталога Цветоводство. Во второй записи аналогичным образом анализируются лечебные растения. Всего их три, по одному из каждого каталога. Хотя в данном случае информации для анализа совсем немного, совершенно очевидно, что такой метод идеально подходит для анализа большого количества записей.
Рис. 12.14. Результат подсчета итоговых значений
Режим конструктора
Мастер позволяет быстро и эффективно создать нужный запрос, однако всегда найдется вопрос, на который у него не окажется ответа. В главе 7 рассказывается, как можно создать запрос на выборку в режиме конструктора. В частности, речь идет о привязке запроса к нескольким таблицам и об ограничении отображаемых результатов за счет представления только нужных полей. В этом разделе режим конструктора также будет применяться для решения разнообразных задач, связанных с запросами, в частности таких:
использование активных запросов для изменения и удаления данных; применение различных параметров для создания более эффективных и интерактивных запросов; отображение результатов суммирования с помощью групповых операций.
Активные запросы
Активными называются запросы, которые позволяют изменять данные. В этой главе рассматриваются активные запросы двух типов.
Запрос обновления изменяет ранее указанные данные на основании указанных критериев. Например, можно на 15% уменьшить цену всех товаров стоимостью ниже 200 рублей на ограниченный период распродажи. С помощью еще одного запроса (удаления) записи удаляются, также на основании указанных критериев. К примеру, можно удалить записи о товарах, которые уже не выпускаются, вместо того чтобы отмечать их тем или иным образом.
Кроме описанных типов запросов, существуют запрос на дополнение (добавляющий записи в готовую таблицу) и запрос на создание новых таблиц. Эти запросы также считаются активными, однако в данной книге они не рассматриваются.
Изменение данных с помощью запроса обновления
Зачастую данные приходится изменять. Вначале осуществляется поиск нужной записи, а затем обновляется одно или несколько ее полей, причем изменения не всегда ограничиваются лишь одной записью. В последнем случае запрос на обновление
может служить отличным способом изменения записей, так как позволяет модифицировать их большое количество с помощью одного простого запроса.
Предположим, нам необходимо в таблице Каталоги заменить все записи Россия в поле
Страна записью Российская Федерация. Когда в базе данных записей немного, сделать это несложно. Однако мы сейчас г"-пытаемся, воспользовавшись запросом на обновление, одновременно обновить большое количество записей.
1. Создайте копию таблицы Каталоги.
2. Выберите созданную копию в окне Растения: база данных, а затем выполните команду
Вставка>Запрос. В диалоговом окне Новый запрос дважды щелкните на записи
Конструктор.
3. Добавьте в таблицу конструктора поле Страна.
4. Выполните команду Запрос>Обновление, в результате чего к таблице будет добавлена строка
Обновление.
5. Область обновления следует ограничить полями со значением Россия, поэтому введите имя
Россия в ячейку Условие отбора столбца Страна (при этом название поля автоматически будет заключено в кавычки).
6. Поскольку все поля Россия следует заменить полем Российская
Федерация, введите последнее название в ячейку Обновление столбца Россия. На данном этапе запрос должен напоминать тот, что показан на рис. 12.15 (для того чтобы избежать путаницы, запрос на выборку будет добавлен не сейчас, а немного позже).
Прежде чем создавать активные запросы, следует позаботиться о сохранности данных. Во-первых, сохраните копию изменяемой таблицы. Если запрос не вернет нужных результатов, останется копия первоначальных данных. Во-вторых, выполняйте активный запрос как запрос на выборку. Это позволит просматривать измененные записи без их модификации.
Рис. 12.15. В запросе обновления указываются записи и метод их изменения
1. Выполните запрос, щелкнув на кнопке Запуск (кнопка с изображением восклицательного знака на панели инструментов).
8. Появится сообщение Будет обновлено следующее количество записей: 3. После нажатия кнопки «Да» отмена изменений станет невозможна.
Подтвердите обновление записей. Щелкните на кнопке Да (для отмены запроса нужно щелкнуть на кнопке Нет).
9. Откройте таблицу Каталоги, дабы убедиться, что все требуемые изменения внесены с помощью всего лишь нескольких щелчков мышью. Измененная таблица
Каталоги показана на рис. 12.16.
Рис. 12.16. Запрос заменил все поля Россия полем Российская Федерация
10. Закройте запрос и удалите созданную копию таблицы. Запрос можно сохранить, но в этой книге он нам больше не понадобится.
В предыдущем примере для определения условия и метода изменения поля Россия использовалось специальное поле. В запросе обновления можно применить несколько полей. Как это делается, показано в следующем примере, где для любой записи, количество растений в которой не превышает 5, устанавливаются все флажки поля
На Будущее таблицы Насаждения (созданной в прошлой главе).
Как следует из рис. 12.17, только одна запись соответствует заданному условию. Обновить всего лишь одну запись можно и вручную, а запрос на обновления будет особенно эффективным при обработке таблицы с многочисленными записями.
Рис. 12.17. В таблице Насаждения есть только одна запись, отвечающая критерию обновления (5 растении или меньше)
Обновите таблицу Насаждения, выполнив следующие действия.
1. В окне Растения: база данных щелкните правой кнопкой мыши на таблице Насаждения и выберите команду Копировать. Затем щелкните в окне базы данных, снова правой кнопкой мыши, и выберите команду
Вставить. Введите любое имя для копии таблицы Насаждения в диалоговом окне
Вставка таблицы и щелкните на кнопке ОК для ее создания.
2. Выберите таблицу Насаждения, затем команду Вставка>Запрос и дважды щелкните в диалоговом окне Новый запрос на опции Конструктор.
3. Добавьте поля КоличествоПосадок и на Будущее, а также введите выражение
<=5 в ячейку Условие отбора столбца КоличествоПосадок (рис. 12.18).
Рис. 12.18. Методы и характер модификации полей указываются с помощью двух полей в режиме конструктора
4. Запустите запрос на выборку, чтобы проверить, найдены ли все записи, соответствующие указанному критерию. Будет показана только одна запись, та что для грядки «Под передним окном», поскольку только для этой записи в поле
Количество Посадок указано значение 5. Таким образом, данный критерий отбора записей оказался правильным. Запрос также отобразил запись со значением 1 — основным значением для любой новой записи. Тем не менее, запрос не изменял новую запись, так как ее еще только предстоит создать.
5. Вернитесь в режим конструктора и выберите команду Запрос>Обновление.
6. Теперь добавьте значение -1 в ячейку Обновление поля НаБудущее, как показано на рис. 12.19.
Для создания копии таблицы сначала выберите нужную таблицу в окне базы данных, а затем команду Правка> Копировать. После этого выполните команду Правка>Вставить, введите новое имя таблицы в открывшемся диалоговом окне и щелкните на кнопке ОК.
Рис. 12.19. Добавьте выражение обновления перед запуском измененного запроса
7. Запустите запрос и щелкните на кнопке Да, чтобы подтвердить необходимость его выполнения.
8. Откройте таблицу Насаждения, показанную на рис. 12.20, и просмотрите полученные результаты. Как видите, в поле
НаБудущее для первой записи установлен флажок, равнозначный значению Да, о котором уже упоминалось в п. 6.
9. Удалите копию таблицы Насаждения.
Рис. 12.20. Для поля НаБудущее в первой записи теперь установлен флажок
10. Закройте запрос на обновление. Сохраните его под любым именем, если хотите, однако в примерах он больше не будет использоваться.
Удаление данных с помощью запроса
Удалить данные с помощью запроса также просто, как и модифицировать. Процедура подобна использованию запроса на обновление, только данные не обновляются, а удаляются.
Для открытия запроса на выборку достаточно дважды щелкнуть на нем в окне базы данных. Точно так же открываются активные запросы на обновление и удаление данных. Однако активный запрос не только открывается — он еще и выполняется. Будьте осторожны, поскольку двойной щелчок может запустить активный запрос, который, возможно, приведет к нежелательным модификациям базы данных.
Чтобы продемонстрировать работу запроса, удалим все лечебные растения из таблицы
Растения, выполнив такие действия.
1. Сделайте копию таблицы Растения, назвав ее КопияРастений. На этот раз будет применяться именно копия, поскольку из основной таблицы удалять данные не рекомендуется.
2. Выберите команду Вставка>Запрос. Затем дважды щелкните на опции
Конструктор в диалоговом окне Новый запрос.
3. Добавьте в сетку конструктора поле НомерТипа; затем введите номер 3 в ячейку Условия отбора (рис. 12.21). Выполните запрос на просмотр полученных результатов — должно быть отображено только три записи, причем, каждая строка должна содержать надпись Лечебное. Поле
НомерТипа в таблице Растения представляет собой поле подстановки, поэтому в строке отображается одно
значение, хотя на самом деле содержится значение 3. Более подробно о полях подстановки рассказывается в главе 6.
Рис. 12.21. Добавьте значение 3, чтобы ограничить операцию удаления только лечебными растениями
4. Вернитесь в режим конструктора и выберите команду Запрос>Удаление.
5. Запустите запрос, после чего появится предупреждение Access об удалении трех записей. Щелкните на кнопке
Да. Откройте таблицу КопияРастений (рис. 12.22). Как видите, остались только записи о декоративных растениях.
6. Таблицу КопияРастений и созданный запрос можно удалить, поскольку в дальнейшем они не понадобятся.
Рис. 12.22. С помощью запроса из таблицы были удалены все записи о лечебных растениях
Взаимодействие с запросом путем добавления параметра
До сих пор запросы выполняли именно то, что от них требовалось. Если добавить параметр (разновидности выражения), перед выполнением запроса понадобится ответить на несколько вопросов. Это даст возможность изменить получаемые результаты с помощью лишь одного запроса.
Параметрический запрос на самом деле отличается от активных запросов или запросов на выборку. Параметр позволяет «взаимодействовать» с запросом непосредственно во время его выполнения.
Предположим, нужно просмотреть записи о растениях, однако часто отображаемые запросом растения следует отфильтровать по стране, где они были приобретены. Необходимо создать простой параметрический запрос, для которого будет указана страна, после чего запрос возвратит лишь записи, отвечающие поставленному критерию. Вот как это реализуется на практике.
1. Выберите таблицу Растения в окне базы данных и выполните команду Вставка>3апрос. Дважды щелкните на записи Конструктор в; меню
Новый запрос.
2. В режиме конструктора добавьте таблицу Каталоги на рабочее поле, щелкнув на кнопке
Отобразить таблицу, расположенной на панели инструментов, дважды щелкнув на опции
Каталоги, а затем на кнопке Закрыть.
3. Добавьте в таблицу конструктора все записи таблицы Растения. Затем добавьте поле
Страна из таблицы Каталоги.
4. Введите параметрическое выражение [Пожалуйста, добавьте страну] в ячейку
Условие отбора поля Страна, как показано на рис. 12.23. При вводе выражения не забудьте указать квадратные скобки.
5. Запустите запрос, и появится диалоговое окно, в котором нужно ввести название страны. Введите Россия, как показано на рис. 12.24, и щелкните на кнопке
ОК. Результат, который вы должны получить, представлен на рис. 12.25. Как видите, запрос отобразил только записи о растениях, приобретенных из российских каталогов (если в прошлых примерах записи
Россия не были заменены на Российская Федерация, введите именно параметр
Россия). Сохраните запрос (это делать не обязательно) как ПараметрСтрана.
При желании строку параметра за-? проса можно проигнорировать, но это не имеет особого смысла. В данном случае запрос должен отобразить все записи, поскольку никакой параметр фильтрации не указан, однако пустая строка параметра обрабатывается Access другим образом, и в результате можно остаться вообще без записей. Дело в том, что пустая строка воспринимается Access как условие для отображения пустых, незаполненных записей. Так как все поля Страна имеют определенное значение, условие запроса будет не выполнено, следовательно, никакие записи не будут выведены на экран.
Рис. 12.23. Ввод параметрического выражения в ячейку Условие отбора поля Страна
Отображение общих значений с помощью групповых операций
Групповые операции запросов представляют общие значения данных. В предыдущих примерах этой главы перекрестные запросы объединяли данные с учетом
категорий. Групповые операции можно представить в качестве упрошенного варианта перекрестных запросов, поскольку первые объединяют данные, однако не распределяют их по категориям.
Рис. 12.24. В строке параметрического запроса Access нужно ввести дополнительную информацию
Рис. 12.25. Запрос отображает только записи Россия
В одном и том же запросе может обрабатываться несколько параметрических выражений. Например, во пером запросе можно указать на необходимость отображать только лечебные растения, приобретенные из российских каталогов. Для этого нужно добавить в ячейку Условие отбора поля Номер типа второе параметрическое выражение— [Введите значение Номер типа].
Групповые операции, как понятно из названия, группируют данные, благодаря чему с каждой группой можно провести определенные вычисления. Например, групповые запросы дают возможность узнать, сколько записей о растениях каждого типа содержится в таблице. Выясним это, выполнив следующие действия.
1. Выберите таблицу Растения в окне базы данных, а затем команду Вставка
>Запрос. Дважды щелкните на записи Конструктор в окна Новый запрос.
2. Добавьте два поля Номер типа в таблицу конструктора (именно два). Других полей добавлять не нужно, поскольку это изменит характеристики создаваемых групп.
3. Выберите команду Вид>Групповые операции для добавления соответствующей строки в таблицу. В каждой ячейке
Групповая операция будет по умолчанию отображаться название обобщенной функции
Группировка. Измените вторую функцию на Count, щелкнув на ячейке и выбрав имя Count в раскрывающемся списке, как показано на рис. 12.26.
4. Запустите запрос для отображения обобщенных результатов, представленных на рис. 12.27. Итак, на данный момент насчитывается шесть декоративных растений и три лечебных.
Рис. 12.26. Выберите обобщенную функцию Count из раскрывающегося списка строки Групповая операция
Рис. 12.27. Запрос определит количество записей в каждой категории
Обобщенная функция применяется с группой записей. Всего в Access применяются 12 типов групповых операций, 9 из которых — обобщенные функции. К числу обобщенных функций относятся:
Sum — сумма значений в каждой группе; Avg — среднее значение для группы; Min — наименьшее значение в группе; Мах — наибольшее значение в группе; Count — количество элементов в каждой группе (за исключением пустых и незаполненных элементов); StDev — стандартное отклонение для каждой группы; Var — вариации для каждой группы; First — первое значение в каждой группе; Last — последнее значение в каждой группе.
Ниже перечислены типы групповых операций:
Group By — определение группы путем уменьшения записей до уникальных значений; Expression — вычисление на основе обобщенной функции; where — указание условия, ограничивающего значения в каждой группе.
На втором шаге предыдущего упражнения было выведено предостережение относительно невозможности добавлять в таблицу новые поля, не относящиеся к группе. Это привело бы к изменению группы, и запрос отобразил бы другой набор записей. Предположим, необходимо просмотреть имя каждого растения, а также подсчитать количество растений в каждой группе (как в прошлом упражнении). Вы, наверное, уже догадываетесь, в чем здесь загвоздка, даже без запуска запроса? Рассмотрим такую ситуацию.
Предыдущий запрос выводит подсчитанное количество типов растений. К каждому типу группы невозможно добавить ни единого растения. При попытке сделать это будет создано несколько групп — по одной для каждого растения. К примеру, в случае добавления в таблицу конструктора поля
Имя результат будет таким, как показано на рис. 12.28. Обратите внимание на значение 1 функции
count для каждой записи. Дело в том, что каждая группа основана на полях
Имя и номер типа, а не исключительно на втором поле, как было ранее. Будьте осторожны при создании групповых операций и добавляйте только те поля, которые относятся к группе.
Рис. 12.28. Запрос воспринял запись о каждом растении как новую группу
Подведем итоги...
Запросы относятся к разряду одних из самых эффективных инструментов Access. Существует несколько мастеров и типов запросов, которые помогут решить самые сложные задачи. В этой главе описывалось выполнение различных действий с помощью запросов, а именно:
нахождение дублированных и несовпадающих записей, а также объединение данных по категориям с помощью нескольких мастеров; изменение и удаление групп записей посредством активных запросов; взаимодействие с запросом путем добавления параметра; объединение групп данных с помощью обобщенных функций.
