Использование мастеров запросов

По сути, запрос представляет собой обычный вопрос. Пользователь задает вопрос, 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. Результат подсчета итоговых значений

Содержание раздела