Базы данных Microsoft Access 2003
         

Режим конструктора



Мастер позволяет быстро и эффективно создать нужный запрос, однако всегда найдется вопрос, на который у него не окажется ответа. В главе 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. Запрос воспринял запись о каждом растении как новую группу


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