Microsoft Access, шаг второй

Как уже упоминалось ранее, одной из основополагающих частей любой СУБД является запрос. Запрос следовало бы даже признать самой важной ее частью, так как именно благодаря ему СУБД вообще существует и приносит практическую пользу. Обычно любая база данных напоминает классическую школьную задачу про бассейн, в котором есть две трубы. В одну вода втекает, а через другую - вытекает. Так вот, в качестве бассейна в СУБД используются таблицы, которые заполняются данными через пользовательские формы. А вот труба, через которую информация вытекает, это и есть те самые запросы. Когда, например, владелец местного автосервиса задает вопрос своему сотруднику - а сколько вчера мы заправили "шестисотых" мерседесов с шести утра и до обеда - то на самом деле он инициирует следующую нехитрую процедуру. Сотрудник, которому по штату положено, открывает журнал регистрации, пробегает по записям, если потребуется - делает кое-какие записи, и выдает полученный результат. Запрос в СУБД Microsoft Access действует совершенно аналогичным образом. Только вместо человека выборку производит компьютер.

По принципу своего действия все запросы Microsoft Access делятся на несколько видов. Наиболее часто применяются запросы, производящие простую выборку некоторой информации из одной или нескольких взаимосвязанных таблиц. Результатом работы такого запроса является набор фактических данных, удовлетворяющих определенным, заранее заданным, условиям. Полученные данные представляются в виде обычной таблицы, что позволяет создать на ее основе пользовательскую форму, представляющую результат на экране в удобном для восприятия виде. Кроме того, в отличие от обычной таблицы, в запросе, кроме условий выборки, можно задавать дополнительные поля, в которых указывать формулы, оперирующие содержимым других полей. Практически в точности как в Microsoft Excel. В этом случае вместе с отобранными данными во временной таблице появятся и результаты обсчета формул. Таким образом, механизм запросов компенсирует отсутствие в таблице возможности автоматически произвести какие бы то ни было расчеты. На самом деле в этом таится глубокий смысл. Как показывает практика, реально пользователю практически повсеместно все результаты базы данных не нужны, однако программа не умеет делить все формулы на первоочередные и второстепенные. Как только пользователь, как это имеет место в Microsoft Excel, обращается к файлу, содержащему формулу, компьютер производит все вычисления, чем в значительной степени расходует машинный ресурс. Решение, реализованное в СУБД вообще, и в Microsoft Access в частности, позволяет вычислять лишь те данные, которые необходимы в данный конкретный момент времени данному конкретному пользователю. Вся база данных может содержать множество самых разных запросов, в том числе и с формулами, но так как они разбиты между разными отдельными модулями, то это позволяет управлять процессом вычислений, чем в значительной степени экономит машинное время.

Вторым большим классом запросов является запрос-действие, в результате которого получается уже действительно самая обычная таблица. Упомянутый выше запрос-выборка тоже генерирует таблицу, однако она существует только в оперативной памяти компьютера и исчезает сразу после прекращения работы запроса. В большинстве случаев этого оказывается вполне достаточно. Однако периодически возникает необходимость создать новую таблицу на основании данных из других таблиц. Например, по итогам хозяйственной деятельности месяца бухгалтерия желает получить одну удобную таблицу, включающую в себя как обычные, так и расчетные данные. Теоретически, ее можно соорудить вручную, что займет достаточно много времени и потребует приличных усилий от оператора. Практически же, можно сконструировать запрос-действие, который все возьмет на себя и уже через считанные секунды справится с задачей.

Третьей разновидностью запросов является запрос-удаление. Следует помнить, что Microsoft Access обычным образом не позволяет удалять данные, занесенные в таблицу. Вообще-то, в ручном режиме пользователь может удалить одну или несколько строк, но это будет топорная работа. Хотя бы потому, что поле типа "счетчик" при таком удалении не обновляется, а значит по окончании процедуры последовательность будет нарушена. Следовательно, потом номер последней строки вовсе не свидетельствует о реальном количестве записей в таблице. Подобное затруднение относительно легко разрешается простой процедурой поверки количества строк в таблице, однако это отнимет некоторую часть машинных ресурсов. Таким образом, как только из таблицы требуется что-либо удалить, следует воспользоваться запросом-удалением. В отличие от ручного режима, автоматическое удаление счетчик как раз обновляет...

Теперь, когда общие теоретические основы назначения и областей применения запросов рассмотрены, настала очередь практической работы по построению запроса в среде Microsoft Access 97. Как известно, любой запрос опирается на одну или несколько таблиц. Поэтому воспользуемся заранее подготовленной тестовой таблицей (см. рис. 1), в которой хранятся данные по продажам автомобилей. В столбце " Счетчик" автоматически поставляется номер продажи, совпадающий с порядковым номером строки в таблице. Столбец " Марка автомобиля", соответственно, содержит марку проданной машины. В таблице также учитываются: дата продажи и сумма, за которую автомобиль был продан клиенту. Между ними расположен столбец " Вид платежа", в котором содержатся данные с типом " логический". Будем считать, что часть покупателей расплачиваются наличными, а часть - кредитной карточкой. Предположим, что большинство предпочитает не носить с собой большие суммы, а значит, по умолчанию, таблица настроена таким образом, что в графу " Вид платежа" все время заносится значение "Истина". И лишь когда клиент все же выкладывает наличность, содержимое ячейки " Вид платежа" конкретной продажи вручную изменяется оператором на "Ложь".

В целом, представленная таблица вполне корректно описывает операцию продажи и может быть использована для иллюстрации работы запроса-выборки. Предположим, нам понадобилось выяснить, какие конкретно автомобили были проданы фирмой за наличные. Этим и займемся.

Для создания нового запроса следует сначала перейти на вкладку "ЗАПРОСЫ" и нажать на экранную кнопку " СОЗДАТЬ". Это приведет к появлению уточняющего вопроса: "какой конкретно запрос вы желаете" (см. рис. 2). Всего предложенное меню Microsoft Access 97 содержит пять возможных вариантов. Профессионалы, хорошо освоившие данную СУБД или пользующиеся непосредственным программированием на языке запросов (SQL), выбирают режим " Конструктор", в котором все операции производятся вручную. Это имеет как плюсы, так и минусы. Слишком много рутинных операций, зато можно сконструировать какой угодно запрос.

Вариант " Перекрестный запрос" применяется в тех случаях, когда требуется найти нечто общее в двух связанных таблицах. Например, отсортировать всех покупателей из Сор-бонны (первая таблица) и выбрать все их покупки (вторая таблица).

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

Вариант " Записи без подчиненных" в подавляющем большинстве случаев применяется в таблицах со множеством связей. О самих связях речь пойдет позднее, а пока предположите, что на каждого покупателя некоторый магазин заводит реестр покупок. Покупки могут быть разные. Они также могут по-разному оплачиваться (наличные, кредитная карточка или что-нибудь другое) и доставляться (самостоятельно или "с доставкой на дом"). Чтобы каждый раз не задавать старому клиенту массу излишних вопросов, удобно завести отдельную таблицу со всеми необходимыми данными, а в реестре покупок указывать только порядковый номер конкретного клиента в списке. Однако может так получиться, что какой-либо человек перестал пользоваться услугами данного магазина. Вскорости в системе учета возникнет своего рода дыра. Запись о клиенте есть, а упоминаний о его покупках нет. Стало быть имеет смысл периодически просматривать систему и удалять из нее лишние записи ради экономии машинных ресурсов. Правда, вручную подобная задача далеко не всегда может быть решена успешно, так как человеку свойственно ошибаться. Поэтому куда удобнее, да и надежнее, воспользоваться специально сконструированным запросом, который буквально перелопатит всю базу данных, сличит все записи о покупках с перечнем зарегистрированных покупателей и безошибочно выявит перечень тех, кто не объявлялся сверх положенного срока, а значит, записи о которых можно совершенно безболезненно из реестра удалить.

Таким образом, из всего перечня в нашем случае подходит только пункт " Простой запрос", которым и воспользуемся. Это активизирует первую часть мастера построения запроса (см. рис. 3). По умолчанию программа подразумевает, что все действия будут проделаны над таблицей, которая была активизирована последней и в данный момент развернута на экране. В противном случае следует выбрать нужное в поле с выпадающим списком " Таблицы/Запросы". Как только конкретная таблица или запрос выбраны, все их поля тут же отражаются в окне " Доступные поля" в левой нижней части окна мастера. Рядом с ним располагается окно " Выбранные поля", заполняемое теми наименованиями, которые пользователь выделяет в левом окне и при помощи кнопок со стрелками, что между указанными окнами, перебрасывает в правое. Смысл этой процедуры заключается в том, чтобы указать мастеру, какие поля следует в запросе просматривать. Как говорилось ранее, гипотетическому начальнику требуется узнать, сколько покупателей расплатились наличными, поэтому выделяем наименование поля " Вид платежа" и нажимаем экранную кнопку со стрелкой, обращенной вправо. Это наименование исчезает в левом окне и появляется в правом. В дополнение к нему можно перебросить поля: " Марка автомобиля", " Дата продажи" и " Сумма". Как вы уже поняли, конструируя запрос, пользователь волен оперировать полями, как ему вздумается: переставлять, тасовать словно карты, выстраивать в любом желаемом порядке или выбирать только некоторую часть из них. Когда все готово, наступает очередь экранной кнопки " ДАЛЕЕ".

Мастер задаст еще один уточняющий вопрос - какого рода ответ вы желаете (см. рис. 4). Ответов предусмотрено два: подробный список, отсортированный согласно вашим пожеланиям, или итоговый подсчет в стиле - всего столько-то. Для наглядности воспользуемся первым вариантом и нажмем " ДАЛЕЕ".

Программа пойдет далее и опять "спросит" - как вы желаете новый запрос назвать (см. рис. 5). Его следует набрать в строке " Задайте имя запроса". По умолчанию, Microsoft Access 97 сама генерирует имя по достаточно простой методике: к имени таблицы, на основании которой работал запрос, добавляется слово "Запрос". Можно оставить и так, а можно придумать и более выразительное название. Тут следует учитывать тот простой факт, что через некоторое время вы можете забыть тонкости проекта и некогда вполне знакомые наименования могут стать совершенно бессмысленными.

Авторы СУБД Microsoft Access 97 предусмотрели также такой вариант, когда услуги мастера для построения запросов используются исключительно для выполнения большей части черновой работы. Если вы хотите добраться до внутренней механики полученного запроса - выберите вариант " изменение структуры запроса", иначе программа автоматически решит показать получившийся результат. Можно также "попросить" Microsoft Access вывести подсказку по работе с запросами. Это делается путем выставления галочки против записи " Вывести справку по работе с запросом".

Все, запрос готов. Можно смело нажимать экранную кнопку " ГОТОВО" и посмотреть на результат. Как видно из итога, запрос сработал не совсем так, как ожидалось. Программа выбрала все строки, не различая между кредитными карточками и наличными. Таким образом, не остается иного выбора, как подкорректировать исходный код запроса. Для этого нужно перейти в режим конструктора (экранная кнопка с угольником и карандашом на панели инструментов). Запрос примет иной вид (см. рис. 6). Наименования выбранных полей стали наименованиями столбцов, а над этим бланком появилось изображение таблицы, из которой указанные поля берутся. Как вы видите, все поля, которыми оперирует запрос, специально помечены галочками, проставленными в строке " Вывод на экран". Это означает, что далеко не все поля, которыми оперирует запрос, должны непременно выводиться на экран компьютера. Допускается сортировать данные по одному признаку, а на экран выводить лишь то, что связано с полученным результатом.

В данном случае, чтобы запрос не путался с выборкой, ему требуется дополнительно указать, что конкретно требуется извлечь из опрашиваемой таблицы. Условие задается в строке " Условие отбора". Это может быть простое условие, набранное с клавиатуры и представляющее собой одну из возможных величин. Это также может быть более сложное выражение, опять же как набранное с клавиатуры, так и сконструированное при помощи конструктора выражений. Выделите ячейку на пересечении строки " Условие отбора" и столбца " Вид платежа". Потом нажмите на правую клавишу мыши и выберите в контекстном меню режим "ПОСТРОИТЬ". Это "вызовет к жизни" еще один мастер.

Это очень интересный и "могучий" мастер. Он позволяет строить сколько угодно сложные выражения, многие из которых иным путем вообще созданы быть не могут. Вам надлежит отыскать в левом нижнем окне поле "Вид платежа" (принцип поиска аналогичен поиску папки в модуле Проводник операционной системы Microsoft Windows 95), кликнуть по нему (в верхнем окне появится запись, состоящая из наименования таблицы и имени поля). Потом - нажать мышью на экранную кнопку со знаком равно, а потом опять отыскать в левом нижнем окне, в папке " Константы", значение "ложь" и перенести его в верхнее окно посредством экранной кнопки " ВСТАВИТЬ". Получится выражение [Продажи]![Вид платежа] = Ложь. Это означает, что запрос должен выбрать из таблицы ПРОДАЖИ только те строки, ячейка ВИД ПЛАТЕЖА которых содержит значение "Ложь". Как вы, несомненно, помните, именно это означает, что данная продажа была сделана за наличные деньги. Теперь нажмите "ОК", и построитель выражений исчезнет, а само выражение попадет в ячейку " Условие отбора" столбца " Вид платежа" нашего запроса. Не беда, что вы увидите лишь небольшую часть сгенерированной текстовой строки. На самом деле она там присутствует полностью, лишь недостаточная ширина столбца не позволяет увидеть ее целиком. Если не верите - можете "взять" мышью правую границу столбца " Вид платежа" и перетащить ее вправо на достаточное расстояние.

Теперь самое время проверить, все ли в порядке. Переведите запрос в режим таблицы. Это делается кликом по инструменту " Режим таблицы", который сейчас находится там же, где ранее располагалась кнопка с треугольником и карандашом. В появившейся результирующей таблице теперь всего две строки, из которых данные содержит только одна, именно та, в которой поле "Вид платежа" содержало значение "Ложь", внешне выраженное в виде квадратика без галочки. На этот раз запрос "сработал" как надо. Его можно сохранить и использовать в дальнейшем.

Несомненно, это далеко не все возможности запросов. На самом деле с их помощью в СУБД выполняется львиная доля задач, но об этом мы поговорим позднее. А пока - главное уяснить, что такое есть запрос и для чего он существует.

Александр Запольскис