Access 2007

Рис. 5.20. Каждый назначенный курс приводит к появлению платежа в таблице TeacherPayments (вверху слева). Каждый прием студента в курс формирует запись об оплате в таблице StudentCharges (вверху справа). Несмотря на то, что на первый взгляд эта картинка слегка устрашающая, вы должны уметь прокладывать путь последовательно через все таблицы и связи. Начинать создание БД легче всего с нескольких таблиц, постепенно добавляя новые

Часто задаваемый вопрос. Печать ваших отношений

Почему последовательность Office → Печать (Office button → Print.) становится недоступной, когда я просматриваю вкладку Схема данных?

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

части III.)

Для создания отчета о связях ваших таблиц сначала расположите все выбранные по нашему вкусу таблицы на вкладке Схема данных. Затем выберите Работа со связями | Конструктор → Сервис → Отчет по схеме данных (Relationship Tools | Design → Tools → Relationship Report). На экран выводится окно предварительного просмотра, которое похоже в той или иной степени на текущее содержимое вкладки Схема данных. Для того чтобы вывести его на печатающее устройство, можно выбрать последовательность Office →

Печать.

После закрытия отчета со связями программа Access предложит сохранить его в вашей БД. Обычно вы с этим не связываетесь, потому что можете легко восстановить его в любое время. Но если у вас сложная БД и вы хотите напечатать несколько разных схем (на каждой из которых представлены разные группы связей), у вас может возникнуть желание сохранить ваш отчет о связях для последующего применения. Вы узнаете больше об отчетах в главе 10.

201

Магазин шоколадных изделий

БД о продажах, в которой хранятся товары, клиенты и заказы компании, торгующей чем-либо,

— наиболее распространенная разновидность БД. На самом деле этот шаблон появляется так часто, что стоит рассмотреть его в кратком примере. Как будет видно, существует несколько основных правил, применяемых к любому зависящему от продаж (торговому) предприятию, будь то продажа коллекционируемых книг или уцененных фармацевтических изделий.

В данном примере вы встретитесь с принимающей заказы по почте компанией Boutique Fudge, которая удовлетворяет декадентские изыски большой аудитории помешанных на шоколаде клиентов. Ее бесстрашные руководители склонны к постоянным инновациям и хотят как можно лучше управлять постоянно растущим каталогом своих высококачественных изделий. Они также нуждаются в способе учета клиентов и сделанных ими заказов.

Каталог изделий и список клиентов

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

■Таблица Products, в которой перечислены соблазнительные шоколадные деликатесы, предлагаемые для продажи. В таблице записаны: название, описание и цена каждого предлагаемого изделия. Имеет смысл включить в нее несколько необязательных подробностей — например, почему бы не учесть текущий запас с помощью двух числовых полей (UnitsInStock (количество единиц на складе) и UnitsOnOrder (количество заказанных единиц)), а также логическое поле (названное Discontinued (больше не продающиеся)) для обозначения изделий, которые больше не поступают в продажу.

Примечание

Во многих БД нельзя удалять старые данные. Компания вроде Boutique Fudge не может просто исключить старые изделия из своих каталогов, поскольку они могут быть связаны со старыми заказами. Кроме того, есть смысл хранить исторические сведения для возможного анализа данных. (Boutique Fudge могла бы применить запрос для поиска самых хорошо продаваемых товаров в 1999 г. и выяснить, есть ли связь между снижением содержания какао и сокращением продаж.) Именно поэтому вам нужно поле Discontinued. Когда перечисляются изделия для продажи, все больше не поступающие

впродажу изделия можно исключить с помощью средств фильтрации, с которыми вы познакомились

вразд. "Фильтрация" главы 3.

■Таблица ProductCategories делит изделия на несколько описательных групп. Это позволит клиентам просматривать изделия только в той категории, которая им нужна (будь то напитки (Beverages), конфеты (Candies), шоколад (Chocolate) или сделанная на заказ одежда с надписями, подчеркивающими пристрастие хозяина к шоколаду (Personalized Choco-wear).

■Таблица Customers содержит список шокоголиков, подписавшихся на регулярные заказы. В этой таблице вам нужна вся информация о клиентах, например, имена и фамилии, сведения, необходимые для доставки, информация о счетах.

Примечание

Многие компании разрешают своим клиентам предоставлять несколько адресов доставки и кредитных карт. Если вы согласитесь на такое разнообразие, вам понадобится (не удивляйтесь)

202

больше таблиц. Можно создать таблицу CustomerCreditCards. Затем каждую запись из таблицы Customers можно связать с одной или несколькими записями в таблице CustomerCreditCards. BoutiqueFudge выбрала легкий путь и хранит номер кредитной карты клиента и его адрес непосредственно в таблице Customers.

Пока существует одна действующая связь — связь типа "один-ко-многим" между таблицами ProductCategories и Products. Этот проект показан на рис. 5.21.

Рис. 5.21. Товар (например, Chocolate Jasmine Tea (шоколадный жасминовый чай)) можно поместить в одну категорию (скажем, напитки), но в отдельную категорию входит много товаров

Заказ товаров

Как бы искусно не была спроектирована ваша БД о продажах, если клиенты не смогут заказывать интересующие их товары, компания Boutique Fudge быстро разорится.

Новички в разработке БД часто допускают ошибку, считая, что сведения о заказах можно хранить в одной таблице. На самом деле вам нужны две.

■В таблицу Orders записывается каждый заказ, сделанный клиентом. Она связана с клиентом, сделавшим заказ, и включает информацию, такую как дата размещения заказа.

■В таблице OrderDetails перечислены отдельные элементы заказа. Каждая запись в таблице OrderDetails включает код (ID) заказанного товара, количество единиц товара в заказе и цену, по которой они заказаны.

Поскольку в среднем заказ содержит несколько видов изделий, отдельная запись в таблице Orders обычно связана с несколькими записями таблицы OrderDetails (как показано на рис. 5.22). Это утверждение может показаться нелепым (т. к. оно означает, что вам требуется создать группу новых записей для всего лишь одного заказа), но процесс не потребует от вас больших усилий. У программы Access есть два средства, которые выручат: подтаблицы (рис. 5.23) и формы (см. главу 12).

Обратите внимание на то, что запись OrderDetails хранит цену каждого заказанного вида товара. Может показаться, что такая система порождает избыточность данных по отношению

203

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

Рис. 5.22. У каждого заказа может быть неограниченное количество заказанных видов товаров. Такая возможность неизменно радует компанию Boutique Fudge

Рис. 5.23. Благодаря наличию подтаблицы можно добавлять в одном месте запись о заказе и связанные с ним виды товаров

Примечание

Профессиональные разработчики БД называют информацию такого сорта сиюминутными или текущими данными (point-in-time data), поскольку они меняются со временем.

204

Следует отметить, что запись таблицы Order не хранит общую стоимость заказа, т. к. общая стоимость — это просто сумма стоимостей всех заказанных товаров. Если хранить общую стоимость, то возникает возможность появления противоречивых данных — иными словами, у вас появится проблема, если сохраняемая общая стоимость не совпадет со стоимостью всех товаров, включенных в заказ.

Вам придется еще как следует поработать, прежде чем Boutique Fudge превратится в компанию, которая управляется БД. Например, возможно, придется создать таблицу Shipments (доставки), в которой учтены заказы, отправленные по почте, и таблицу Payments (платежи), чтобы быть уверенными в том, что клиенты расплатились полностью. Концептуально в этом нет ничего нового, но чем больше таблиц добавляется, тем сложнее становятся БД. Теперь, зная основы отношений и правила создания таблиц хорошей структуры, вы сумеете сохранять хладнокровие в стрессовой ситуации.

205

Часть II

Обработка данных с помощью запросов

Глава 6. Запросы, выбирающие записи Глава 7. Основные хитрости, применяемые в запросах Глава 8. Запросы, обновляющие записи

Глава 9. Анализ данных с помощью перекрестных запросов и сводных таблиц

206

Глава 6

Запросы, выбирающие записи

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

Для того чтобы понять, о чем идет речь, представьте себе, что вы создаете БД в программе Access для компании Boutique Fudge, принимающей по почте заказы на пищевые продукты. С помощью фильтрации, сортировки и скрытия столбцов можно сократить таблицу Orders, так чтобы в ней отображались только самые дорогие заказы, сделанные в прошлом месяце. (Эти сведения хороши для выявления транжир или ведения кампании маркетинга самых кодовых товаров (hot marketing).) Затем можно применить другой набор параметров, чтобы определить клиентов, заказывающих более пяти фунтов (2 кг) сливочной помадки каждую субботу. (Вы могли бы использовать эти данные для более детального исследования рынка сбыта или для передачи Министерству здравоохранения.) Но каждый раз, применяя на листе данных новый набор параметров, вы теряете предыдущие результаты. Если нужно перейти от одной выборки к другой, придется скрупулезно переопределять все ваши параметры. Если на создание удачного представления ваших данных было затрачено какое-то время, этот процесс добавит вам много ненужной дополнительной работы.

Решить описанную проблему можно с помощью запросов: заранее подготовленных процедур поиска, которые хранятся в вашей БД. Несмотря на то, что у компании Boutique Fudge только одна таблица Orders, у нее могут быть десятки (и больше) запросов с разными параметрами фильтрации и сортировки каждый. Если вы ищете самые дорогостоящие заказы, вам не нужно применять фильтрацию и сортировку вручную — вместо этого вы можете просто запустить запрос MostExpensive Orders LastMonth (самые дорогостоящие заказы за последний месяц) и он извлечет только нужную вам информацию. Аналогичным образом, если нужно найти страстных любителей сливочной помадки, можно выполнить запрос LargeRepeatFudgeOrders (большие, повторяющиеся заказы сливочной помадки).

Запросы — основной элемент проекта БД. В данной главе вы узнаете все, что нужно для разработки и тонкой настройки базовых запросов.

Основные сведения о запросах

Как следует из названия, запросы позволяют сформировать вопросы о ваших данных, например, какие продукты приносят больше всего денег, где живет большинство клиентов и

207

кто заказал разукрашенную зубную щетку? Программа Access сохраняет каждый запрос в вашей БД, как любой другой ее объект (см. разд. "Что такое базы данных Access" главы 1).

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

Основное достоинство запросов заключается в их способности многократно выполнять тяжелую работу за вас. Кроме того, запросы открывают новые функциональные возможности, которых вы лишены при использовании только листа данных.

■Запросы могут объединять связанные таблицы. Такая возможность невероятно полезна, т. к. позволяет при поиске принимать в расчет связанные данные. В примере с компанией Boutique Fudge благодаря этой способности можно создать запросы, которые находят заказы конкретных продуктов или заказы клиентов, живущих в определенных городах. Оба эти поиска должны использовать связи, т. к. они выходят за пределы таблицы Orders и включают данные из других таблиц (например, Products (товары) и Customers (клиенты)). Как действуют такие запросы, вы узнаете в разд. "Запросы и связанные таблицы "далее в этой главе.

■Запросы могут выполнять вычисления. В таблице Products БД Boutique Fudge приведены сведения о ценах наряду с данными о количестве товаров на складе. Запрос может перемножить эти данные, а затем вставить столбец, в котором представлена вычисленная стоимость товара, находящегося у вас под рукой. В главе 7 вы попробуете выполнить такой подсчет.

■Запросы могут подсчитывать итоги. Для анализа больших массивов данных вы можете сгруппировать строки с подобными данными. Можно сгруппировать вместе заказы одного клиента, чтобы узнать его максимальные затраты. Или вы можете сгруппировать заказы по продуктам, чтобы на лету построчно сравнить объем продаж товара ThermoNutcular Fudge с объемом продаж продукта Vanilla Bean Dream. С этим методом вы познакомитесь в главе 7.

Запросы могут автоматизировать внесение изменений. Если нужно найти все заказы, сделанные определенным человеком, и снизить стоимость каждого на 10%, запрос можно применить сразу к группе записей. Это действие требует применения запроса другого типа, запроса на изменение (action query), с которым вы познакомитесь в главе 8.

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

Создание запросов

Программа Access предлагает три способа создания запросов.

■Мастер запросов предоставляет самый легкий способ построения простого запроса. Но этот метод обладает минимальным набором средств управления.

Примечание

Если вы решите использовать Мастер запросов для формирования вашего запроса, возможно, впоследствии вам придется переопределить этот запрос с помощью Конструктора.

208

■Конструктор предлагает самый общий метод построения запросов. Он обладает удобным графическим инструментом, который можно применять для улучшения вашего запроса.

■В Режиме SQL вы можете увидеть скрытую команду запроса, представляющую собой текстовый фрагмент (состоящий из одной строки или десятка строк), который задает конкретные действия программе Access. Многие профессиональные разработчики творят именно в Режиме SQL, и хотя на первый взгляд он кажется мудреным, на самом деле в нем не так трудно разобраться.

Создание запроса в Конструкторе

Лучшая стартовая точка для создания запроса — режим Конструктора. Далее перечислены необходимые действия. (Для того чтобы самостоятельно попробовать создать запрос, можно использовать базу данных BoutiqueFudge.accdb, включенную в примеры к данной главе, загружаемые из Интернета.) Окончательный результат — запрос, получающий данные за 2007 г. — показан на рис. 6.6.

Рис. 6.1. Вы уже видели окно Добавление таблицы — с его помощью вы вставляли таблицы в схему данных в главе 5

209

Далее описано, что следует сделать.

1.Выберите Создание → Другие → Конструктор запросов (Create → Other → Query Design).

Па экране появится новое окно Конструктора, в котором вы сможете создать вага запрос. Но сначала программа Access распахивает диалоговое окно Добавление таблицы (Show Table), в котором можно выбрать таблицы для обработки (рис. 6.1).

2.Выберите таблицу, содержащую нужные вам данные, и щелкните мышью кнопку Добавить (Add) (или дважды щелкните таблицу кнопкой мыши).

В примере с БД Boutique Fudge вам нужна таблица Orders.

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

3.Щелкните мышью кнопку Закрыть (Close).

Диалоговое окно Добавление таблицы исчезает, открывая доступ в Конструктор для формирования запроса.

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

4. Выберите поля, которые хотите включить в ваш запрос.

Для выбора поля в прямоугольнике таблицы щелкните поле дважды кнопкой мыши (рис. 6.2). Не включайте одно и то же поле дважды, иначе столбец будет отображаться

210