Книги, научные публикации
Pages: | 1 | 2 | 3 | 4 | 5 | ... | 7 | Ирина Харитонова САМОУЧИТЕЛЬ OFFICE ACCESS 2003 Прочитав эту книгу, вы научитесь: ...
-- [ Страница 3 ] --2. Перетащите это поле мышью в бланк запроса на поле Н а з в а н и е. Новый стол бец окажется вставленным перед столбцом Н а з в а н и е.
3. Удалите из строки У с л о в и я з а п р о с а (Criteria) ранее введенное условие Л и хачев.
4. Нажмите кнопку Вид (View) Щ!^. Появится таблица, аналогичная представ ленной на рис. 6.14.
"- :- -: ШтШ " Х?
Р Книги по авторам : запрос на выборку ' Х-Х' Щ. Х...
Х.' Философия :
Боэций '"/течение философией" и друг Большая комната ??
Художественная литература ^Африка Франческо Петрарка Большая комната i'.'J Художественная литература | Белая гвардия, Мастер и Марг;
Большая комната Михаил 'Булгаков J Даниил Х Гранин История ;
Вечера с Петром Великим Маленькая комнат з'Х Драматургия i Драмы Ма/енькая комнат Софокл _ Александр Пушкин Художественная литература ;
Евгений Онегин Хчая комната * Адам Мицкевич Поэзия Избранная гоэз^.я Художественная литература Александр Пушкин ! Медный всадник омнат Андрей ;
Митрошенкое Детская литература -эта ! Мы ждем ребенка Ольга ;
Еремеева Детская литература ! Мы ждем ребенка Психология :
Франциск Вейс 1 Нравственные основы жизни Бол=шая комната ;
-|.
;
0риген 'Философия |0 началах Большая комната Эмануэль Сведенборг Х Философия |0 небесах, о мире духов и об ;
Бшыиая комната :
Хорхе Луис Борхес 'Фантастика ;
0прэвдание вечности Большая комната ::
Аркадий Стругацкий Фантастика :Понедельн,1к начинается в с > с Бол=шая комната -Ш ;
i ":
-'-'1 :\,..
' |' !
Ш ','. Рис. 6.14. Запрос К н и г и по авторам, раскрытый в режиме таблицы 5. Нажмите кнопку Вид (View), чтобы вернуться в режим конструктора 1 56 Занятие 6. Создание и выполнение запросов на выборку Перемещение столбцов в бланке запроса Можно выполнять сортировку в запросе не по одному полю, а по нескольким, например, сначала отсортировать книги по разделам, а в разделах по фамилии авторов. Access выполняет сортировку в порядке расположения полей слева на право. Поэтому столбец Раздел должен быть левее в бланке, чем столбец Фами л и я. Итак, нам предстоит поменять порядок полей в запросе. Первым поставим столбец Раздел, затем Ф а м и л и я, И м я, Название и т. д.
1. Подведите указатель к верхней границе столбца Раздел так, чтобы он превра тился в вертикальную стрелку, и щелкните. Столбец будет выделен.
2. Установив курсор на верхнюю границу выделенного столбца, перетащите столбец к левой границе бланка запроса. Столбец станет первым (рис. 6.15), $& Книги па авторам : запрос на выборку ч _..J КодАвтсра ЧJ КодРаэдела КодАвтора Имя Раздел КодИздэмя Фамилия 4 Заметки.. Х Фамилия Х Х - -. Издания Авторе Издания по возрастанию.. :Х " ХХХ Рис. 6.15. Перемещение столбца Раздел 0 Книги по авторам : запрос нэ выборку ДатаПокупьй!
КодАвтор.1 КодРаздепа ЧислоСтрЭ! КодАвтора Раздел Тип Из дани;
- =j т щ Заметки Рис. 6.16. Перемещение столбиа Имя Редактирование запроса 1 3. Аналогично выделите столбец Имя и перетащите его вправо за столбец Фами л и я (отпустите кнопку мыши, когда черная жирная линия окажется между столбцами Ф а м и л и я и Название) (рис. 6.1G).
4. В ячейке Сортировка (Sort) столбца Раздел выберите порядок сортировки По возрастанию (Ascending).
5. Аналогично в ячейке Сортировка (Sort) столбца Фамилия выберите порядок сортировки По в о з р а с т а н и ю (Ascending).
6. В ячейке Сортировка (Sort) столбца Н а з в а н и е выберите порядок сортировки отсутствует (not-sorted).
7. Переключитесь в режим таблицы, нажав кнопку Вид (View). Записи в ре зультирующей таблице отсортированы по разделу, а в разделе по авторам (рис. 6.17).
0 <ниги по авторам ;
запрос на выборка РТ ТГ Х < Х,:
Ольга Детская литература | Еремеева Мы ждем ребенка Большая комната ;
.fi :
Митрошенков Андрей Детская литература _Мы ждем ребенка Большая комната -:f :Спок Детская литература Бенджамин Разговор с матерью Большая комната | :;
Драматургия Софокл Драмы Маленькая комнат -Щ Искусство Дмитрий Поэзия садов Большая комната I"1-" ! Гранин Даниил История Вечера с Петром Великим Маленькая комнат Х*"Х Дмитрий История ^Лихачев Раздумья о России Большая комната Jii.
История Соловьев Сергей Чтения и рассказы по истории Маленькая комнат J Поэзия : Мицкевич Дцам Избранная поэзия Большая комната j.
.Хайям Омар Рубай Поэзия Маленькая комнат ' ХВейс Франциск Психология Нравственные основы жизни Большая комната \ :
: Максим Исп'ов Религия Творения преподобного Макси Богьшая комната ;
-!
i Максим Испов Религия Творения преподобного Макси Большая комната ";
: Фантастика : Борхес Хорхе Луис Оправдание вечности Большзя комната : Стругацкий Фантастика Аркадий Понедельник начинается в суб Большая комната ^ Фантастика.Стругацкий Борис Понедельник начинается в суб Большая комната -" ;
Боэций Философия "Утешение философией" и друг Большая комната :' Философия |0риген 0 началах Богьшая комната *-.
1., l 1 Х= >"Х-' " 1. Снова переключитесь в режим конструктора, нажав кнопку Вид (View). 2. Введите в строку Условия отбора (Criteria) столбца Раздел слово История, а стро кой ниже Философия (рис. 6.18). (Кавычки можно не вводить, они будут под ставлены автоматически.) 3. Выполните запрос, нажав кнопку View (Вид). Результат запроса представлен на рис. 6.19. Отбираются все книги, которые входят в раздел История ИЛИ Философия. 1 58 Занятие 6. Создание и выполнение запросов на выборку (jjji Книги ло йвтирам : запрос на выборк * \i КодАвтора -ХХХ ^- - ^ ч "ХХ Х ТипИздзнм * Г'юдсл КодИэдашя ФэМИТИЙ 5*j f,, Д : | |. | *.: 1 ^ 1. ; |1 |' i ' Ж. ЧЧЧ| Х я Название КодМеста " Раздел Фамилия Издания Авторь Авторы Издания Разделы по возрастанию по возрастанию а а В И '..... "история Wei!' "филосо( эия" >г ^ : Рис. 6.18. Добавление условии отбора в запрос Гранин Да-шил !Вечера с Петром Великим Маленькая коинат Ли/а'-.ев Дмитрий !Раздумья о России Большая комната ^Чтения л рассказы по иста^ии I Маленькая комнат Соловьев Сезгнй Боэций ; "Утешенне философией" и друг Большая комната Оригвн ; 0 началах Большая комната Платон "Федр Большая комната СведеьБор г Эиэьуэль Ю небесак, о мире дулов и об ; Большая комната Рис. 6.19. Результат выполнения запроса на выборку 4. Переключитесь снова в режим конструктора. 5. Теперь отберем книги по истории ИЛИ по философии И христианству. Вве дите в ту же строку, где введено условие Философия, но в столбце Заметки, выражение * х р и с т и а н с * и нажмите клавишу Enter или Tab. Выражение будет преобразовано следующим образом: Like " * х р и с т и а н с * " (рис. 6.20). .. Имя Х Х Раздел Фамилия Название КодМестэ - Разделы Авторы Издания Издания Издания * АЕТСрЫ., по еоэрэстан!*э по возраст-а-мю г И. 0 0 В "ист ори ч" 1 "философия" Like "'хаисгиачс* " |. Ж !У Рис. 6.20. Установка нескольких условий отбора, связанных по ИЛИ и по И Редактирование запроса 1 6. Выполните запрос, нажав кнопку Вид (View). Результат выполнения запроса представлен на рис. 6.21. Отобраны 3 книги по истории и одна по филосо фии и христианству. Кинги по авторам : запрос на выбор История Транин ; Даннм JВечера с Петром Велики История Лихачев i Дмитрий Раздумья о России Большая ко :В сборник включены наиболее популярные п|: История ^Соловьев : Сергей 'Чтения и рассказы по истор Маленькая Философия :0ригеи ]_| _; О началах.Большая ко ; Трактат одного из основателей христианского Рис. 6.21. Отбор записей по нескольким условиям, связанным по ИЛИ и по И Итак, запомните. Если нужно установить несколько условий отбора, причем должны выполняться условие! Йусловие2 ЯусловиеЗ и т. д., все эти условия нужно писать в одной строке. Если нужно, чтобы выполнялись условие! ИЛИ условие2 ЯЛЯусловиеЗ, записывайте каждое из них в отдельной строке. Окно SQL Режим конструктора позволяет в удобной и простой графической форме скон струировать инструкцию SQL (языка структурированных запросов), которая и выполняется процессором базы данных Access. Вы можете даже увидеть эту инструкцию. 1. Переключитесь в режим конструктора. 2. Щелкните на стрелке кнопки Вид (View) и выберите в списке Режим SQL (SQL View) (рис. 6.22). Появится диалоговое окно с инструкцией SQL, выра жающей созданный вами запрос. Это инструкция SELECT, означающая запрос выборки. После слова SELECT пе речисляются все поля, которые включены в запрос. Далее идет слово FROM, И Книги по авторам : запрос на выбери: SELECT Разделы, Раздел, Авторы Фамилия, Авторы.Имя, Издания.Название, Издания.КодМеста, Издания.Заметки FROM Разделы INNER JOIN (Издания INNER JOIN (Автора INNER JOIN АвторИздание ON Авторы, Код Автора = АвтсрИздание.КодАвтора) ON Издания. Ко дИэ дани я = АвторИздание,КодИздания) ON еаздел=1 КодРзздела = Издания.КодРаэдепа MHERE (((разделы,Раздел)="история")) OR (((Paзделы.Раздел)="философия") AND ((Издания,Эа^етж) Liks ""'христиане*")) ORDER 6V Разделы Раздел, Авторы.Фамилия; Рис. 6.22. Режим SQL 1 60 Занятие 6. Создание и выполнение запросов на выборку и после него указываются таблицы, из которых извлекаются данные. Эти таб лицы объединяются в запросе с помощью связанных полей. Следующая часть инструкции начинается со слова W H E R E и содержит условия отбора. Последняя часть ORDER BY определяет порядок сортировки записей в резуль тате запроса. Именно это предложение сохраняется, когда вы сохраняете запрос. Данные при этом остаются в своих таблицах. При выполнении запроса они собира ются из всех таблиц, являющихся источниками данных для запроса, и ото бражаются на экране. Поэтому результаты многократного выполнения од ного и того же запроса могут быть различны, если между запусками этого запроса изменялись данные в таблицах-источниках. В предыдущих версиях Access размер шрифта в окне SQ.L был очень маленький (8), и текст запроса был трудночитаемым. Теперь появилась возможность настраивать размер шрифта и гарнитуру в окне настроек Access. 3. Выполните команду Сервис > П а р а м е т р ы (Tools > Options). Появится диалого вое окно настройки параметров Параметры (Options). 4. Раскройте вкладку Таблицы и запросы (Tables/Queries) с параметрами режимов создания таблиц и запросов. В поле Размер (Size) группы Ш р и ф т е конструкто ре запросов (Query Design Font) (рис. 6.23) установите значение 12 и нажмите КНОПКУ ОК. !UM ! ilSpi ; П; .-IK-, Х>; ".:-Х ' им'МцЫ ?.а6пшы й запросы. Apyrte- Мемшунаредные ' Разм?р*,= тп; гчэ умот-йннге текстовое; ( рге ихсйдашт; 1 ; лиспоесе: дпинноецелое Idi ЩщШМ -.^n. Х.-; ,, /ске предаст ащляются орава. k' i-JL Ш..'ЛИМег*, ; v Д -V : ; .-: ; -.' '=:Х.елыа i" i.!,.-. д !, (, ' -. ". : P Аб-пматическое о^ъедирекие. . ХХ -Х - Х,> i - Х llahoma Рис. 6.23. Установка параметров режима конструктора таблии и запросов Использование выражении в запросах 1 Размер шрифта изменится только после того, как вы закроете и вновь от кроете запрос. Откройте еще раз запрос Книги по авторам в режиме конструктора и отбери ЗАДАНИЕ те все книги по истории, которые находятся в маленькой комнате. Исключение столбцов из запроса Если вы хотите временно исключить какой-нибудь столбец из результата за проса, снимите флажок в строке Вывод на экран (Show) этого столбца. 1. Откройте запрос Книги по авторам в режиме конструктора и снимите флажки в столбцах Место хранения и Заметки (рис. 6.24). 1 ч -^ /~ * ? ДатаПокуш-ЛЗ _; . Ч КодРаздаги КодАвгора~- -..-- КодАшора ЧиспоСтра!-. Иия ТипИздани; . КидИздания оамипия $ | Замет км j^J Ш т^ Название КодМеста |Эа 1етки Х Имя Фамилия ?~~ Из анил Авторы Издания Издания. Авторы по возрастанию Е 0 D ^Щй KJ л-*л-*> или; " :Lik ^ Ф^стианс Рис. 6.24. Исключение столбиов из результирующего набора запроса 2. Переключитесь в режим таблицы, чтобы увидеть результат выполнения за проса. 3. Вновь переключитесь в режим конструктора и установите флажки. Если вы сохраните запрос, в бланке которого у некоторых столбцов флажок Вывод на экран (Show) будет сброшен, эти поля будут автоматически удале ны из запроса. Поэтому, если вы исключали их только временно, перед со хранением запроса флажки следует вновь установить. Использование выражений в запросах При создании запроса, как, впрочем, и других объектов Access, часто использу ются выражения. Выражением является любая комбинация операторов, кон 6 Зак. 1 62 Занятие 6. Создание и выполнение запросов на выборку стант, значений текстовых констант, имен полей, элементов управления или свойств, результатом вычисления которой может быть конкретное значение. Например, когда мы определяли условия отбора записей в запросе, мы создали выражение История, являющееся строковой константой. В этом случае прове ряется точное совпадение значения поля с указанной константой. Однако, мож но было использовать оператор отрицания Not и написать Not("HcropHfl"), тогда, наоборот, были бы отобраны все записи, у которых значение поля Раздел не сов падает с История. Выражение Like "'христиане* ' содержит оператор Like Ч оператор сравнения с образцом. Он сравнивает значение поля, по которому ведется отбор записей, с образцом Ч текстовой строкой, указанной в кавычках. Символы звездочка (*) слева и справа обозначают любое количество символов слева и справа от ука занной строки. Оператор Like определяет наличие указанной строки "христиане" в любом месте поля Ч в начале, конце или в середине, и возвращает значение Истина (True), если находит эту строку, и Ложь (False), если не находит. Значение Истина (True) приводит к включению записи в результирующий набор, а значе ние Л о ж ь (False) Ч нет. Если бы мы включили в выражение строку "христиане*", то эта строка искалась бы только в начале поля, а строка "*христианс" искалась бы только в конце поля. Другим часто используемым оператором сравнения с образцом является опера тор Between... And. Его обычно используют при отборе записей по полю типа Date/Time, чтобы указать диапазон отбора дат. Например, выражение Between #01.01.031 And #01.02.03# задает диапазон дат между 1 января 2003 года и 1 февраля 2003 года. В этом вы ражении используются константы даты/времени, которые в Access выделяются символами #. Сама дата должна указываться в формате, определенном в регио нальных настройках Windows на вашем компьютере. Причем символы # при вводе выражения можно не вводить. Если дата введена правильно, Access доба вит их автоматически. Даты, указанные в выражении (концы диапазона), вклю чаются в выборку, то есть это выражение эквивалентно следующему: >= (больше или равно) #01.01.03# And <= (меньше или равно) #01.02.03# Чтобы увидеть региональные настройки Windows, откройте панель управле ния (например. Пуск > Панель управления). На панели управления дважды щелкните на ярлыке Язык и региональные стандарты. В появившемся диа логовом окне Язык и региональные стандарты откройте вкладку Дата. Еще один вид оператора сравнения In позволяет задать список значений. При отборе записей проверяется, совпадает ли значение поля с одним из элементов списка. Если совпадает, возвращается значение Истина (True), и запись включа ется в результирующий набор, в противном случае Ч не включается. Например, нужно отобрать книги нескольких авторов. Это можно сделать дву мя способами. Один мы уже знаем. Все фамилии нужно перечислить в столбце Фамилия в строках Условия отбора (Criteria). В каждой строке должно быть по одной Использование выражений в запросах 1 фамилии. Тогда будут отобраны записи, удовлетворяющие условию! ИЛИ условию2 ИЛИ условиюЗ и т. д. А можно написать выражение In ("Пушкин"; "Петрарка"; "Мицкевич") 1. Удалите все установленные раньше условия в запросе (клавишей Delete) и введите в столбец Фамилия это выражение (рис. 6.25). 2. Переключитесь в режим таблицы. Результат выполнения запроса представ лен на рис. 6.26. 3. Закройте запрос, сохранив его. шншшш Хi Х | Книги по лавторам : запрос на выборку S щ :i Х -.. Я! И Х м,,., tl ДатаПокупк! КодАвтара.2J Ч -~-...ч. КодРаодела -. ЧислоСтр ; КодАвтора Имя Раздел ш -' Фамилия |Vj: JLLJ".ч..-я. ; Х.' г. -.-.V Х-Х ', - Х Х : -... :,.,., Х.. .., Раздел Ко,1^ Название ГЪП& ! г 1.I1!. ' i ИМЯ Автоды Авторы Разделы Издания Из,"/ по возрастанию. ср ровка по возрастанию В Я И Б] Ус товие 3tD3pa ; 1п ("Пушкш"; "Петрарна"; "МицкбЕ1Ич") ":. - - ИЛИ "Т Рис. 6.25. Использование оператора сравнения In (р Книги по авторам : запрос па еыборм Поэзия Мицкееич Лдам Избранная поэзия Художественная литература Петрарка Франческо Африка Художественная литература Пушкин Александр Медный всадник Художественная литература Пушкин Александр Евгений Онегин Рис. 6.26. Выборка записей по совпадению с элементом списка В табл. 6.1 представлены некоторые другие операторы, которые могут быть ис пользованы в условиях отбора запроса. Таблица 6.1. Примеры часто используемых операторов и функций Оператор Пример Описание Is Null Is Null позволяет выбрать записи, не имеющие значения в данном поле. Is Not Null позволяет Is Not Null выбрать записи, имеющие значение в данном поле (любое) Ч ХЧ Х Х _ продолжение Занятие 6. Создание и выполнение запросов из выборку 1 Таблица 6.1 (продолжение) Описание Оператор Пример Значение раино 10 (обычно этот оператор = опускают, так как он используется по умолчанию) Значение не равно =о <>,л5.08.99, Значение в моле типа дата больше 15 августа 1999 года >= >= 18 Значение больше или равно < Значение меньше 0 (отрицательное число) <= Значение в поле типа дата меньше или равно <- #31.12.99 31 декабря 1999 года "[Страна]" & "," & Оператор слияния двух строковых выражений. & "[Город]" В приведенном примере объединяет поля Страна п Город через запятую Like '"антич*" A n d Like And В результат запроса включаются записи, "*сосуд* м удовлетворяющие одновременно и одному, и другому условию. Позволяет отобрать записи, которые содержат в заданном поле подстроку ламтич //подстроку сосуд* (при поиске информации об а н т и ч н ы х сосудах) " П у ш к и н " Or Or В результат запроса включаются записи, которые "Достоевский" удовлетворяют хотя бы одному из указанных условий. Значение ноля должно быть либо Пушкин, /[ибо Достоевский Not "фольклор" В результат запроса включаются только записи, ' Not не содержащие указанное значение в ноле [Цена] * [Количество] Арифметические операторы умножения, деления, *,/,+. сложения и вычитания соответственно DateQ >= Date() Функция, которая возвращает текущую дату DateDiff() Оасе01'гг("у"; [ДатаВыда Функция, вычисляющая промежуток между чи]; [ДатаВозврата]} > двумя датами Trim{) Тпт([Название]) Функция возвращает строку, которая является ее аргументом, осз начальных и заключительных пробелов lif ([ДатаВыдачи]- Функция ветвления. Имеет три аргумента. Date(); 0; l) Первый аргумент Ч выражение, значение которого проверяется, и если это значение Истина (True), функция возвращает значение второго аргумента (в примере 0), если Л о ж ь (False), то возвращается значение третьего аргумента (и примере 1). Второй и третий аргументы функции также могут быть сложными выражениями Способы объединения таблии в запросах 1 Квадратные скобки используются для обозначения имен полей, таблиц и других объектов Access. Обязательным употребление скобок является только для имен, состоящих из нескольких слов, то есть включающих пробе лы, однако при обработке введенного вами условия Access автоматически добавляет скобки для всех имен объектов. Для получения более подробной информации об использовании выраже ний в запросах воспользуйтесь справочной системой Access. Для этого вы берите команду Справка > Справка: Microsoft Access (Help > Microsoft Access Help). В поле Искать (Search) введите строку выражения и нажмите кноп ку со стрелкой справа. Помните, что запрос в базе данных Access во многих случаях аналогичен са мой таблице, то есть можно рассматривать результат выполнения запроса как виртуальную таблицу,, и к н е й применимы все действия, касающиеся вы вода на печать, экспорта, копирования, рассылки, которые мы рассматрива ли применительно к обычной таблице. Создайте запрос, который позволил бы отобрать всех читателей, которые за ЗАДАНИЕ писались 8 библиотеку в октябре. (Они не должны и меть записи в формуляре с датой вы дачи меньше 1.10.2003.) Способы объединения таблиц в запросах Чаще всего запросы па выборку используются, чтобы объединить в одной табли це на экране данные, которые реально размещаются в разных таблицах базы дан ных. Источниками данных в таких запросах являются связанные таблицы. Как связываются таблицы в базе данных, вы уже знаете (см. занятие 3). Когда запрос создается на базе нескольких таблиц, говорят, что эти таблицы объединяются в запросе. При этом можно использовать несколько видов объединений. Наиболее распространенным является внутреннее объединение. При этом в ре зультирующий набор попадают те записи из двух связанных таблиц, у которых связанные поля совпадают. Если две таблицы связаны отношением лодин-ко многим, то в результат запроса включаются все записи из главной таблицы (таблицы лодин), для которых имеются соответствующие записи в подчинен ной таблице (таблице многие). Если запись в главной таблице не имеет соот ветствующих записей в подчиненной таблице, эта запись в результирующее множество не включается. Такое объединение таблиц в запросе Access создает автоматически, когда выполняется одно из следующих условий; Х соединение лодин-ко-многим было явно задано в окне Схема д а н н ы х (Rela tionships); Х в таблицах имеются поля с одинаковыми именами и согласованными типа ми, причем одно из полей является ключевым. 1 66 Занятие 6. Создание и выполнение запросов на выборку Согласованные типы Ч в большинстве случаев означают одинаковые типы. Исключением является поле типа Счетчик (AutoNumber), которое может иметь размер Длинное целое (Long Integer) и связываться должно с число выми полями, имеющими тот же размер Long Integer. Именно такое объединение таблиц использовалось в тех запросах, которые мы создавали до сих пор. Чтобы понять, как работает внутреннее объединение, предлагается сделать такое упражнение. 1. Откройте таблицу Издания в режиме таблицы и внесите в нее книгу, которая не имеет автора, например, Русские былины. Обязательно введите раздел Художественная литература. После ввода всех полей не забудьте перейти на следующую запись, чтобы введенные да иные сохранились в таблице. Убедитесь, что значок с каранда г=Ч/ шиком исчез из области выделения введенной записи. 2. Закройте таблицу И з д а н и я. 3. Откройте запрос К н и г и по авторам в режиме конструктора. 4. Удалите все введенные ранее условия выборки. 5. Выполните запрос, переключившись в режим таблицы. 6. Щелкните на любой записи в столбце Название и нажмите на панели инстру ментов кнопку Н а й т и (Find) [0|:. 7. В диалоговом окне Поиск и замена (Find and Replace) в поле Образец (Find What) введите слово былины (рис. 6.27). 8. В поле Совпадение (Match) установите значение С любой частью поля (Any Part of Field). i.Д.. Отибна Г с учетом 0ep*rpa.- P Рис. 6.27. Диалоговое окно поиска и замены 9. Нажмите кнопку Найти далее (Find Next). Вы получите сообщение о том, что запись не найдена. Действительно, новая запись в таблице И з д а н и я не имеет связанных записей в подчиненной таблице А в т о р И з д а н и е, а также записей в таблице Авторы. Поэтому Способы объединения таблиц, в запросах 1 она не попадает в результат запроса. Таким образом, наш запрос построен не совсем корректно. Именно эта ошибка может во многих случаях быть причиной исчезновения записей в ваших таблицах. Чтобы устранить ошибку, нужно ис пользовать так называемое внешнее объединение. Внешние объединения бывают левыми или правыми. Если таблицы в запросе объединяются левым внешним объединением (в инструкции SQL оно обознача ется LEFT JOIN), то выводятся все записи таблицы лодин с уникальным значе нием первичного ключа вне зависимости от того, имеются ли соответствую щие им записи в таблице многие. В тех строках результирующей таблицы, где отсутствуют записи в подчиненной таблице, соответствующие поля будут пустыми. Если же таблицы в запросе объединены правым внешним объедине нием (в инструкции SQL RIGHT JOIN), то выводятся все записи таблицы мно гие вне зависимости от того, имеются ли соответствующие им записи в табли це лодин. Теперь мы установим внешнее объединение между таблицами в запросе Книг/i по авторам. 1. Переключитесь в режим конструктора. 2. Выделите связь между таблицами И з д а н и я и А в т о р И з д а н и е и затем дважды щелкните на ней. 3. Появится диалоговое окно Параметры объединения (Join Properties) (рис. 6.28). Переключатель 1 задает обычное внутреннее объединение, переключатель 2 Ч левое внешнее объединение, а переключатель 3 Ч правое внешнее объе динение. Параметры объединения Лееая табгаща Плавая тэбгйи& '|из дэния V] |ДвторИздание ШдКодИэдания "j ]<адИздания г- к. f *I Гч из "Издания", е которых окшнные по-тсоаладаюТ' Рис. 6.28. Диалоговое окно параметров объединения 4. Задайте левое внешнее объединение, выбрав переключатель 2. Нажмите кноп ку ОК для закрытия диалогового окна. При этом на конце линии соединения появится стрелка в сторону таблицы многие (АвторИздание), что указыва ет на левое внешнее,соединение (рис. 6.29). В пашей базе данные таблиц Издания и Авторы связаны косвенно, через таб лицу связи А в т о р И з д а н и е. Поэтому, чтобы запрос был создан правильно, не обходимо установить внешнее объединение и между таблицами А в т о р ы и Ав 1 68 Занятие 6. Создание и выполнение запросов на выборку (Р Книги по авторам : запрос на датаПокупн числоСтра ТипИздан! Фзмили ц Раздел Название Имя Разделы Издания Авторы Авторы.-Х. ; , Ч по возрастанию по возрастанию ' ". Х ' 0 П -".:- 0. ..... Хщ Х >Г^ ЫЛ Рис. 6.29. Отображение саого внешнего объединения в запросе торИздание. Только теперь нужно, чтобы в запрос были включены вес записи из таблицы АвторИздание (лмногие*) и только те записи из таблицы Авторы, которые имеют совпадающие значения в связанных полях. Это будет правое внешнее соединение. 5. Щелкните дважды на линии, соединяющей таблицы Авторы и АвторИздание, открывая тем самым диалоговое окно Параметры объединения (Join Proper ties). 6. Выберите переключатель 3 и нажмите кнопку ОК. Стрелочка теперь будет указывать в сторону таблицы на стороне лодин (Авторы). Окончательный вид запроса должен быть таким, как представлен на рис. 6.30. 7. Выполните запрос и убедитесь, что книга Русские былины появилась в результирующем наборе запроса. & Книги по авторам: запрос на выборки КпдАвтсраЧJ Имя Фамилия : | * . Раздел Фа ми пия Название Имя Чf. Разделы Авторы Авторы Издания по возрастанию по возрастанию а 0 0 Условие собора; %. ч... Дг.Г П Рис. 6.30. Отображение внешнего объединения между таблииами, связанными косвенно Использование вычисляемых полей 1 Использование вычисляемых полей Кроме полей таблиц-источников данных, в запрос могут включаться поля, зна чения которых являются результатом вычисления выражения. Такие поля на зываются вычисляемыми. Как правило, эти поля отображают данные, рассчи танные на основе значений других полей этой же строки запроса. Простым примером вычисляемого ноля может быть поле Автор и запросе К н и г и по авто рам. Вместо двух полей Ф а м и л и я и Имя, можно включить в запрос одно поле Ав тор, значение которого должно содержать и фамилию, и имя автора. Чтобы соз дать такое поле; 1. Переключитесь в режим конструктора. 2. Выделите столбец Ф а м и л и я, щелкнув на области выделения в верхней частя столбца (серый узкий прямоугольник). 3. Нажмите клавишу Ins. Перед столбцом Ф а м и л и я будет вставлен пустой столбец. 4. Установите курсор в первую строку нового столбца и нажмите комбинацию клавиш SMft+F2. Появится диалоговое окно Область ввода (Zoom) (рис. 6.31). Вы можете настроить шрифт и этом окне, нажав кнопку Шрифт (Font). Автор: [Фамилия] а " " S [Имя] ХХ Рис. 6.31. Диалоговое окно области ввода Окно Область ввода (Zoom) удобно использовать для ввода и просмотра длинных выражений, которые не помещаются целиком в отведенные для них поля. Поэтому рекомендую запомнить сочетание клавиш Shift4-F2. Вы можете использовать его для просмотра полей, содержащих достаточно длинный текст (таких как Заметки). 5. Введите в область ввода строку Автор: [Фамилия] & " " & [Имя]. Первая часть до двоеточия будет именем нового поля. Вторая часть - выра жение, значением которого является слияние нолей Фамилия и Имя. Между ними нужно обязательно вставить пробел, иначе при выводе на экран имя I /0 Занятие 6. Создание и выполнение запросов на выборку и фамилия автора будут отображаться слитно. Квадратные скобки вокруг имен полей в данном случае можно и не вводить Ч Access автоматически до бавит их позже. 6. Нажмите кнопку ОК. Строка будет вставлена в бланк запроса. Нажмите кла вишу -I. При попытке завершить ввод в данную ячейку бланка запроса Access проверяет введенное выражение и добавляет квадратные скобки вокруг имен полей. Если вы ошибетесь в написании имени поля и Access не обнаружит такого поля ни водной из таблиц запроса, скобки не добавляются и выдается сооб щение об ошибке. Если указанное в выражении имя поля встречается в не скольких таблицах запроса, также будет выдано сообщение об ошибке, так как Access не может определить, в какой таблице брать значение поля для вычисления выражения. Тогда нужно точно указать имя таблицы, например, Авторы.Имя. 7. Установите в столбце Автор сортировку по возрастанию. 8. Выделите столбцы Фамилия и Имя и удалите их клавишей Delete. Запрос дол жен выглядеть, как на рис. 6.32. ДатаПокупкЛ КодАвтира КодРаздела ЧислоСтра-. КодАвтора Имя Раздел Тип Из дани :^> КодИздам-ш Фамилия jJ | Заметки ! Раздел % Заметки *g АВТСр: [Фамилия] 8. " " & [Имя] КодМеста название Разделы Издания Издания Издания по возрастанию по возраста ни >о а В И 0 ИЛИ' 1 з; ХХ[ 1 | Х- Х" " Х >Г~ Рис. 6.32. Вычисляемое поле в запросе 9. Переключитесь в режим таблицы. Она будет выглядеть как на рис. 6.33. 10. Сохраните запрос и закройте его. Вычисляемые поля часто используются для вычислений с датами. Создадим за прос, который покажет, сколько дней держат на руках книги наши читатели, и найдем тех, кто задерживает их больше месяца. Построим этот запрос немного иначе, чем делали до сих пор (Access позволяет делать это несколькими способами). 1. Нам потребуется только одна таблица Ч Ф о р м у л я р ы. Поэтому раскройте спи сок таблиц и выделите в нем таблицу Формуляры. Использование вычисляемых полей 1/ Sj3 Книги по автора : запрос на выборку |ЕЭ 'ji 'I ?; i,Mfr-.' I Детективы Современный французский детективный роман Большая коми; Детская литература Х.Счастливые родители Большая коми, Детская литература.Еремеееа Ольга Мы ждем ребенка Большая комн. Детская литература Х Митрошенков Андрей Мы ждем ребенка Большая коми; Детская литература.Спок Бенджамин Разговор с матерью Большая комм, : Драматургия Софокл Драмы Маленькая кол/ оэзня Искусство Лихачев Дмитрий П садов ; Большая коми; СТ ИЯ ан И.Р....1ГР...и)1. Даним Вечера с Петром Великим Маленькая KOW История ; ихачев Дмитрий Раздумья о России Большая коми; -Маленькая ком История -Соловьев Сергей Чтения и рассказы по истории России ; Поэзия Мицкевич Адам Избранная поэзия Большая коми; ям Поэзия :.Хай Омар Рубаи Маленькая кои Психология.Бейс Франциск -Нравственные основы жизни Большая коми, Религия Максим Исповедник Творения преподобного Максима Исповедник; большая коми: Религия Максим Исповедник Творения преподобного Максима Исповедника Большая коми; Фантастика Борхес Хорхе Луис Оправдание вечности Большая коын: Фантастика 'Стругацкий Аркадий Понедельник начинается в субботу Большая коми, Рис. 6.33. Отображение вычисляемого столбиа 2. Щелкните на стрелке кнопки Новый объект (New Object) панели инстру ментов и выберите в списке пункт Запрос (Query). Появится диалоговое окно Новый запрос (New Query) (рис. 6.34). Простой запрос Перекрестный 'этрис Повтостающиеся записи Записи 6в5 подчиненных Рис. 6.34. Диалоговое окно нового запроса 3. Выделите в списке элемент Конструктор (Design V i e w ) и нажмите кнопку ОК. Появится окно конструктора запросов, в верхнем поле которого уже нахо дится таблица Формуляры. 4. Перенесите в бланк запроса поля КодЧитателя, ДатаПолученил, ДатаВозвратаФакт (вспомните, что для этого достаточно дважды щелкнуть на соответствующем поле таблицы). Запрос должен выглядеть как на рис. 6.35. Такой способ создания запроса удобен, когда источником данных является одна таблица. Теперь нужно создать вычисляемое поле, в котором будет отображаться ко личество дней, которое читатели держали книги. Для создания выражения воспользуемся построителем выражений. 1 72 Занятие 6. Создание и выполнение запросов на выборку 5. Сначала сохраните запрос, нажав на панели инструментов кнопку Сохранить (Save) g]. В окне Сохранение (Save As) ниедите имя запроса Задерживающие книги. щ : Sз* Запрос! ; запрос на выборкц '- : ": s.-ЩШШ! ; | L?' iiiiiilMi.(LMLMViiiJMjij ДатаПо лучения !Ш ДатаВоэврата ДатаВозвратаФакт ?ХХХ* Примечание КодЧмтагеля ДатаПо лучения ДатаВозвратаФ. i Формуляры Фоомупяры Формуляры а 0 Усгшме г т fro pa; ! j Рис, 6.35. Создание запроса 6. Щелкните в бланке запроса на первой строке первого свободного столбца и нажмите на панели инструментов кнопку Построить (Build) Щч! Появится окно построителя выражений (рис. 6.36). ' ДатаПолужения ДатаВоэБратаОакт 1 Запросы Й И Forms И Report* ЕЙ Функции Q Константы СЗ Операторы С] Общие выражения Рис. 6.36. Окно построителя выражений Поле в верхней части окна предназначено для создания выражений. Кноп ки под ним представляют наиболее часто употребляемые операторы. В ниж ней части окна расположены списки, позволяющие выбирать константы, функции и имена объектов Access, которые должны присутствовать в выра жении. Использование вычисляемых полей 1 Сейчас в левом списке открыта папка текущего запроса, а второй список со держит все выбранные поля. Дважды щелкните в первом списке па папке Функции (Functions). Появятся две вложенных папки. Щелкните па папке Встроенные функции (Built-in Functions). Во втором списке будут отображены категории функций, а в третьем списке сами функции. По умолчанию список содержит все функции (рис. 6.37). [^Задерживающие книги Массивы (И Таблицы Преобразование (1 Запросы База данных SQ Forms Дэта.'время CBcol ИЗ Reports По подмножеству CEyte Обработка ошибок CCur Финансовые CDate [ ^ЗЗЕИШЗЗЗ Обшие CDbl IЧ СП Библиотека Данные Проверка Q Константы Математические Choose Ihr IQ общения С] Операторы Управление Q Общие выражения 'Inr Стлтигтичвгкие.... Abs(number) Рис. 6.37. Список встроенных функиий в окне построителя выражений Выделите в среднем списке категорию функций Дата/время (Date/Time). В пра вом списке появятся только функции работы с этим типом данных. Дважды щелкните на функции DateDiff. В верхнем поле появится шаблон для этой функции (рис. 6.38). Построитель выражений IDateWf (лintervals; edalel, tirstweeks) оси вы tB Таблиц Преобразование It)Запрось Ъаза дакных S3 Forms т ~~ Х ЙЗ Reports !По подмножестеу И Функции юбработка ошибок r- Финансовые OGuyie IЧ С] БибпиотекаДанмье |проверка СЭ Константы [Математические Q Операторы [Сообщения С] Общие выражения Oateotff(interval; dabel; dateZj flretweekday; flrstweek) Рис. 6.38. Шаблон функиий DateDiff 1 74 Занятие 6. Создание и выполнение запросов на выборку 9. Щелкните на элементе interval, он будет выделен. Введите "d". Символ "d" в первом аргументе функции DateDiff означает, что разница между датами будет вычисляться в днях. 10. Выделите следующий аргумент функции datel. 11. Снова откройте папку Задерживающие книги, чтобы получить на экране спи сок полей текущего запроса. 12. Дважды щелкните на поле ДатаПолучения в среднем списке. Шаблон в верх нем поле примет следующий вид; DateDiff ("d"; Выражение [ДатаПолучения] Выражение : date2; firstweekday; firstweek). 13. Выражения слева и справа мы создавать не будем, поэтому их нужно уда лить. Выделите первое слово л В ы р а ж е н и е и нажмите клавишу Delete. Анало гично удалите второе слово Выражение, 14. Выделите следующий аргумент функции date2. 15. Дважды щелкните в среднем списке на поле ДатаВозвратаФакт. Шаблон в верх нем поле примет следующий вид: DateDiff С"й"; [ДатаПолучения]; Ехрг [ДатаВозвратаФакт] Ехрг; firstweekday; firstweek), 16. Снова удалите лишние слова Ехрг, а также два последних аргумента, функции и разделители. Окончательный вид выражения должен быть сле дующим: DateDi ff("d"; [ДатаПолучения]; [ДатаВозвратаФакт]) 17. Нажмите кнопку ОК. Созданное вами выражение будет подставлено в ячейку Поле (Field) нового столбца. Нужно только дать этому полю название. 18. Нажмите клавишу Ноте и вставьте перед выражением имя поля Дни:. Не за будьте про разделитель л> между именем поля и выражением. Нажмите клавишу -I. Новое поле будет добавлено в запрос (установится флажок Вы вод на экран (Show)). 19. Переключитесь в режим таблицы. Результат запроса представлен на рис. 6.39. 20. Переключитесь в режим конструктора. Теперь добавим в запрос условие отбора. 21. Введите в строку Условие отбора (Criteria) вычисляемого столбца Д н и выраже ние >= 30 (рис. 6.40). 22. Выполните запрос и убедитесь, что отобраны будут только те формуляры, в которых книги были на руках у читателей более 30 дней. 23. Сохраните и закройте запрос. Создайте запрос, в котором будут показаны книги, выданные читателям и не ЗАДАНИЕ возвращенные. В запросе должно выводиться также, сколько дней книги уже находятся на руках. Создание запросов с параметрами 1 Ьушманрва Ирина Владимировна Файзрахманов Айрат Фатхерахманови1 Рис. 6.39. Результат запроса с вычисляемым полем Дни зща & задерживающие книги: запрос на выборку ДатаЛолучения ДатаВомратаФакт Примечание jj Х Пеле; Д а таПо пучения г ДатаОоэвратаФак- Дни: Оа1:еО^("^'; .ДатаПолучения]; [Дат.э&о!врат.эФ л Щ Формуляры Форм/лчры (.Ортир -ее*,!! : вывод *.афан: /споьпе отбора- >= или; '1J Х -! "- '- ' ^Г Рис. 6.40. Добавление условия отбора в запрос Создание запросов с параметрами В начале занятии мы создали запрос Издания&Авторы и отбирали книги Дмит рия Лихачева. При желании посмотреть книги любого другого автора нужно изменить условие отбора. Чтобы не менять условие отбора каждый раз, можно создать запрос, в котором фамилия автора будет параметром, запрашиваемым при выполнении. 1. Откройте запрос Иэдания&Авторы в режиме конструктора. 2. В строку У с л о в и я отбора (Criteria) для столбца Ф а м и л и я введите вместо конкретного значения приглашение к вводу параметра [Фамилия автора ]. 1 /О Занятие 6. Создание и выполнение запросов на выборку Приглашение должно быть обязательно заключено в квадратные скобки (рис. 6.41). 3. Запустите запрос, нажав кнопку Вид (View). Появится диалоговое окно с вве денным вами приглашением к вводу параметра (рис. 6.42). 1 КодАвтора ~J КодИздаи КодАвтора Имя : КодРаздел.= " КодИздаьмя Фамилия Л Название | Ы кг- Попе: ИМЯ Фамилия Название ГодИздани ч Х' ' И; Авторы Авторы Издания издания - -. 0 0... Ч Х - 1 [Фамилия автора:] |i j Х Рис. 6.41. Ввод параметра запроса Поедите значение параметра Ш Рис. 6.42. Диалоговое окно ввода параметра 4. Введите фамилию автора, например Пушкин, и нажмите кнопку ОК. В резуль тирующее множество запроса попадут все книги Пушкина, которые имеются в библиотеке. 5. Закройте и сохраните запрос Издания&Авторы. Запрос может содержать не один, а несколько параметров, и все они по очереди будут запрашиваться при его выполнении. В следующем упражнении вы создадите запрос на выборку книг, приобретен ных за заданный промежуток времени. Граничные даты диапазона определим в виде параметров. 1. Щелкните на ярлыке Создание запроса в режиме конструктора (Create Query in Design View) и выберите в списке три таблицы: Авторы, АвторИздание и Изда ния. Первые два шага по созданию запроса аналогичны созданию запроса Книги по авторам. Создание запросов с параметрами 2. Измените параметры объединения таблиц (внешнее объединение). 3. Сделайте первое поле запроса вычисляемым, введя в ячейку Поле {FieLd) строку: Автор: [Фамилия] & " " & [Имя] 4. Включите в запрос все поля таблицы Издания, начиная с поля Название и кончая полем Заметки. Проще всего это сделать так. Выделите в таблице Из д а н и я в верхней области запроса поле Название. Прокрутите ее до конца и, удерживая клавишу Shift, щелкните на поле Заметки. Окажутся выделенными все нужные поля. Теперь перетащите их мышью в первый свободный стол бец бланка запроса. Все поля будут вставлены одновременно. 5. Установите сортировку по возрастанию в столбце Автор. 6. В строк}' Условия отбора (Criteria) столбца ДатаПокупки введите выражение Bet ween [Начало периода:] And [Конец периода:] (рис. 6.43). ер Запрос! : запрос на выборку I КодАвтсраЧJ КодАвтсра КодИздания Имя Фамилия ТипОбложкм Чис; Цена ДатаПокупки Издания Издания Издания Издг И 0 В : Between [Начало периода:] And [Конец периода; ] ' Рис. 6.43. Параметры в поле ДатаПокупки При вводе значений параметров типа Дата желательно контролировать пра вильность ввода дат, для чего нужно указать тип данных для этого пара метра. 7. Щелкните правой кнопкой мыши на свободном поле верхней панели запро са и выберите в контекстном меню команду Параметры (Parameters) или вы берите команду Запрос > Параметры (Query > Parameters). Появится диалоговое окно Параметры запроса (Query Parameters), представленное на рис. 6.44. 8. В столбец Параметр (Parameter) пнедите значение параметра точно так, как он определен в бланке запроса (легче всего это сделать путем копирования че рез буфер обмена), только можно не вводить ква; фатные скобки. В столбце Т и п д а н н ы х (Data Type) выберите в раскрывающемся списке тип Дата/время (Date/Time). 9. Аналогично введите второй параметр. Затем нажмите кнопку ОК, 10. Нажмите кнопку С о х р а н и т ь (Save) панели инструментов и сохраните запрос, введя его имя Поступления за период. 1 /О Занятие 6. Создание и выполнение запросов на выборку цив данных.-; ...". [Начало периода] 1ЧНЫИ Текстоеъй Поле объекта OLE Поле MEMO Код репликации Действительное Значение Рис. 6.44. Диалоговое окно параметров запроса 11. Нажмите на панели инструментов кнопку Запуск (Run), чтобы выполнить за прос. Поочередно будут выведены два диалоговых окна Введите з н а ч е н и я па раметра (Input Parameter Value), в которые нужно ввести дату начала периода и дату конца. Если нужно, допустим, посмотреть книги, приобретенные за 1999 год, введите две даты: 01.01.1999 и 31.12.1999. Результат отбора пред ставлен на рис. 6.45. 12, Закройте запрос. йШ 3<цюс1: запрос на Оправдание вечности 1994 Ди-дик Москва Гранин Даниил Вечера с Петром Великим 2000 Эксмо-Прес СПб Лихачев Дмитрий Поэзия садов 1998 Согласие.Москва 0 небесах. о мире Д. у ков и об аде 1999 Амфора Рис. 6.45. Книги, поступившие за 1999 год Подведение итогов Итак, на данном занятии вы научились: Х создавать запросы с помощью мастера и с помощью конструктора; Х просматривать результат выполнения запроса; Х изменять запрос с помощью конструктора; Х устанавливать порядок сортировки и условия отбора записей в запросе; Х включать поля в запрос и исключать их из него; Подведение итогов 1> Х добавлять таблицы в запрос и вставлять новые поля; Х перемещать поля в запросе; Х создавать левое и правое внешние объединения таблиц в запросе; Х создавать запросы с параметрами; Х создавать вычисляемые поля в запросе. Вы познакомились с теми операторами и функциями, которые могут использо ваться при составлении выражений, являющихся условиями отбора записей или значениями вычисляемых полей. Знакомство с запросами мы продолжим на следующем занятии. ЗАНЯТИЕ Анализ и изменение данных с помощью запросов ТЕМА ЗАНЯТИЯ На этом занятии вы узнаете: Х как группировать записи в запросе и рассчитывать итоговые значения; Х какие существуют статистические функции и как они могут быть использованы для обработки данных в запросах; Х как анализировать данные с помощью сводных таблиц; Х как построить сводную диаграмму; Х в каких случаях данные в результирующем множестве запи сей можно изменять, а в каких нельзя; Х как с помощью запроса создать новую таблицу и включить в нее данные из результата запроса; Х как выполнить множественные изменения данных в таблицах; Х как добавить за один прием несколько записей в таблицу; Х как удалить из таблицы множество отобранных записей. Расчет итоговых значений 1 о! Для выполнения упражнений данного занятия вам потребуется файл Библиоте каДанные.тсШ 1. Откройте его перед началом занятия. Расчет итоговых значений Рассмотрим, как с помощью запросов можно выполнять подсчет итоговых зна чений. Расчет итоговых значений тесно связан с применением групповых опе раций в запросе. Групповые операции позволяют задать группы записей, для которых выполняются вычисления. Рассмотрим примеры таких расчетов как на всем множестве записей, так и на подмножествах, отобранных условиямг в запросе. Посмотрим, сколько в библиотеке книг по каждому разделу. В качестве источ ника данных используем уже существующий запрос К н и г и по авторам. (Запрос, как и таблица, может быть источником данных для другого запроса.) 1. Выделите в списке запрос К н и г и по авторам. Нажмите стрелку на кнопке Новый объект (New Object) и выберите в сги ске Запрос (Query). В диалоговом окне Новый запрос (New Query) выберите Конструктор (Design V i e w ) и нажмите кнопку ОК. 2. Включите в бланк запроса два поля: Раздел и Н а з в а н и е. Щелкните на панели инструментов па кнопке Групповые операции (Totals) |й. В бланк запроса добавляется строка Групповая операция (Total), содержащая по умолчанию в каждой ячейке значение Г р у п п и р о в к а (Group By). Для поля Раздел оставьте значение Группировка (Group By),-так как мы дей ствительно хотим сгруппировать все книги по разделам, для поля Н а з в а н и е выберите в списке функцию C o u n t (рис. 7.1), чтобы подсчитать количество записей в каждой группе. Нажмите на панели инструментов кнопку Запуск (Run) для просмотра резуль татов запроса. Вы получите таблицу со списком разделов, где для каждого раздела указывается количество относящихся к нему изданий (рис. 7.2). Нажмите на панели инструментов кнопку Сохранить (Save) и введите имя за проса Количество и з д а н и й по разделам. В данном примере расчеты производились над всеми имеющимися в таблр ц с? И з д а н и я записями. Однако иногда требуется выполнить вычисления только над отобранным набором записей. Для этого нужно добавить в запрос условия отбора. Изменим запрос таким образом, чтобы получить данные не по всем разделам, а только по одному, название которого введем в качестве параметра. Примеры вы можете найти на сайте издательства www.piter.com на странице, посвящен ной этой книге. Занятие 7. Анализ и изменение данных с помошью запросов 1 М 1C IО ! О A i; QF Я S Рис. 7.1. Использование в запросе статистической фуншии Count Рис. 7.2. Запрос, подсчитывающий количество изданий в каждом разделе 1. Переключитесь снова в режим конструктора. Введите в ячейку Условия отбора (Criteria) столбца Разделы строку [Раздел; (рис. 7.3). По умолчанию Access присваивает параметрам запроса тип данных Тексто вый, поэтому текстовые параметры в диалоговом окне Параметры запроса можно не перечислять, достаточно указать их в условии отбора. Расчет итоговых значений 1 оЗ j$S Количество изданий по разделам : запрос на выборку ХХ -. '- '.".--'.--.-.". Ч 4J '-Щ КодИздания Раздел Автор Название _*j iLJ ' il.l Раздел Название : -; ИЩ | * _.а Книги по авторам Книги по авторам f р'/гкючля сг'лфайия Группировка Count > а п :| 0 :. Вывод на экран [Раздел:] ] или иг: Рис. 7.3. Добавление параметра в запрос с итогами Нажмите кнопку Запуск (Run). Введите в окно Введите значение параметра (Input Parameter Value) название раздела, например, история (рис. 7.4). введите значение параметре щЩЗн [история Рис. 7.4. Ввод значения параметра в проиессе выполнения запроса Результатом запроса будет одна строка с числом книг по истории (рис. 7.5). 2. Закройте запрос, сохранив его. Функция Count которую мы использовали в примере, называется стати стической функцией. Статистические функции не обрабатывают при вычис лениях записи, имеющие значение Null в том поле, к которому они применя ются. Поэтому будьте осторожны при использовании функции Count. Она будет считать только записи, не содержащие пустые значения. Если требу ется сосчитать все записи в результирующем наборе или группе, нужно соз дать в запросе новое вычисляемое поле, значение которого должно быть равно Мг(<имя поля>), где аргументом функции Nz() должно быть поле, по которому мы считаем записи. А сама функция Nz() преобразует пустое зна чение своего аргумента в 0. В данном примере условие отбора добавлялось в столбец, по которому произво дится группировка записей (Разделы), но так бывает не всегда. Предположим, 1 84 Занятие 7. Анализ и изменение данных с помощью запросов требуется посчитать, сколько книг по каждому разделу находится в большой комнате (отбор ведется по полю КодМеста Ч Место х р а н е н и я ). В таком случае нужно добавить это поле в бланк запроса и установить для него в строке Груп повая операция (Total) значение Условие (Where). Количество изданий по разделен: запрос на Рис. 7.5. Результат запроса, подсчитывающего число изданий в разделе ЗАДАНИЕ Создайте самостоятельно запрос, позволяющий подсчитать количество книг раздела (название вводится в качестве параметра) в каждом из мест хранения. В табл. 7.1 перечислены статистические функции Access для применения в за просах с групповыми операциями. Вы видите их в раскрывающемся списке операций, в строке Групповая операция (Total) бланка запроса. Таблица 7.1. Статистические функиии Access Функция Описание Вычисляет арифметическое среднее набора чисел, содержащихся AvgQ в указанном тюле запроса Вычисляет кол и честно непустых записей, возвращаемых запросом CountQ Возвращает значение поля из первой записи результирующего набора FirstQ Возвращает значение поля из последней записи результирующего Last<) набора MaxQ Возвращает максимальное значение из набора, содержащегося в указанном поле Возвращает минимальное значение из набора, содержащегося MinQ в указанном поле, StDevQ Возвращают смещенное и несмещенное значения среднеквадратичного StDevPQ отклонения, вычисляемого по набору значений, содержащихся в указанном поле Возвращает сумму набора значений, содержащихся и заданном поле Sum() Var() Возвращают значения смещенной и несмещенной дисперсии, VarP() вычисляемой по набору значений, содержащихся в указанном поле Попробуем применить еще одну статистическую функцию: Avg{). Вспомним созданный ранее запрос Задерживающие книги. Сделаем запрос, считающий, сколь ко дней в среднем держит книги каждый читатель нашей библиотеки. Расчет итоговых значений 1 1. Откройте запрос Задерживающие книги в режиме конструктора и удалите из него все условия отбора. 2. Закройте запрос и сохраните его. Щелкните на ярлыке Создание запроса в режиме конструктора (Create Query in Design View). В диалоговом окне Добавление таблицы (Show Table) раскройте вкладку Queries (Запросы) и выберите запрос Задерживающие книги (рис. 7.6). Нажмите кноп ку Добавить (Add). Добавление таблицы.,:,,. lli.rj.4l it Ш.'.. г. Задерживающие гнигм д а ния&Авторы Книги по авторам Образец фильтра по форме Поступления за период Рис. 7.6. Выбор запроса в качестве источника данных Закройте окно Добавление таблицы (Show Table). 3. Включите в бланк запроса поля КодЧитателя и Д н и (вычисляемое поле, пока зывающее количество дней, которое книга была на руках у читателя). Ыр Запрос! : запрос на выборку -.-.-.' --..-.- : ...- КодЧитателя ДатаПо лучения \ ДатаВозвратаФакт Дни :-. - -, : Г. II *' I: Х-, _ _..,,, Х - | Ч Ч Ч -| Ч. ; Ч Т Г -~ КодЧитатвпя Дни Задерживающие книги Задерживающие книги ' "" lfc ШБ-ЭЧ >|; ; ; ,.1:л,ии Группировка Avg| ^ 0 0 П П - П., ; - F ' i. 'Т г |, *н ii Рис. 7.7, Использование функиии Avg() в запросе 1 86 Занятие 7. Анализ и изменение данных с помошью запросов Щелкните на панели инструментов на кнопке Г р у п п о в ы е о п е р а ц и и (Totals). В строке Групповая операция (Total) столбца Д н и выберите в списке функцию Avg() (рис. 7.7). Нажмите на панели инструментов кнопку Запуск (Run) и посмотрите на ре зультат запроса. Для каждого читателя библиотеки отображается, сколько дней в среднем он держит книги (рис. 7.8). 4. Закройте этот запрос и сохраните его под именем Средние сроки н а л и ч и я к н и г на руках. jP Запрос"! : запрос на выборку Бушманова Ирина Владимировна Файзвахманов Айват Фатхеоахманович Рис. 7.8. Результат использования статистической функиии AvgO в запросе Анализ данных с помощью сводной таблицы Пока мы делали только достаточно простые операции с группировкой данных в запросе и расчетом итоговых значений. Но еще в версии Access 2002 появился новый мощный аппарат анализа данных Ч Сводные таблицы (Pivot Tables), аналог сводных таблиц, давно существующих в Excel. Умелое использование этих таблиц позволит просматривать данные в различных разрезах и с разной степенью детализации. Переходим к рассмотрению сводных таблиц. В Access всегда существовал и сохранился в версии Microsoft Office Ac cess 2003 перекрестный тип запроса (Crosstab Query), упрощенный вариант сводных таблиц. Теперь этот запрос не актуален, поэтому рассматривать его мы не будем. Если вы все-таки захотите узнать, как он строится, можете вос пользоваться мастером перекрестного запроса, вызываемым из диалогово го окна Новый запрос (New Query). Это окно вызывается кнопкой Создать (New) в окне базы данных. Создание сводной таблицы Применение сводных таблиц удобно, когда в базе данных есть таблицы с боль шим количеством записей, которые просто невозможно проанализировать без специальных средств. Поэтому, чтобы научиться создавать такие таблицы, мы будем использовать учебную базу данных Борей.mdb (Northwind.mdb). Б ней есть достаточно большие таблицы Заказы (Orders) и Заказано (Order Details) с данными о заказах, которые поступали на фирму Борей (Northwind). 1. Откройте учебную базу данных Борей.mdb (Northwind.mdb). 2. Щелкните на ярлычке Запросы (Queries), чтобы открыть список запросов. Анализ данных с помошью сводной таблииы 1 о/ 3. Найдите и выполните запрос Счета (Invoices), дважды щелкнув на нем. Вы увидите таблицу, содержащую более двух тысяч записей и много полей с разнообразной информацией о заказах клиентов: данные о клиенте, время заказа, кто принял заказ, какие продукты были заказаны и т. д. Эти данные мы и попробуем проанализировать. Пусть вас не смущает, что записи в ней относятся к 1996, 1997, 1998 годам Ч видимо, с тех пор как эта база данных была создана, данные в таблицах не изменялись. 4. Щелкните на стрелке на кнопке Вид (View) и выберите в раскрывшемся спи ске элемент Сводная таблица (PivotTable). На экране появится окно с макетом сводной таблицы с четырьмя областями: П самая верхняя горизонтальная полоса называется областью фильтра, здесь будут находиться поля, по которым вы сможете фильтровать данные; П вторая горизонтальная полоса Ч прообраз заголовков столбцов таблицы; П вертикальная полоса слева будет содержать заголовки строк; П средняя большая область Ч область данных, будет содержать собственно данные. Справа вы должны увидеть отдельное окно Ч Список полей сводной таблицы (Pivot Table Field List) с перечнем полей запроса (рис. 7.9). Получатель Адрес получателя Город получателя Область получателя Индекс получателя С т сана получателя КодКпиентв Клиенты. На юани е Адрес Город Область Индекс Страна Продавец Рис. 7.9. Макет сводной таблицы Для отображения окна Список полей сводной таблицы нажмите на панели инструментов кнопку Поля (Field List) Щ 1 88 Занятие 7. Анализ и изменение данных с помошью запросов В верхней части диалогового окна Access появилась специальная панель ин струментов Сводные таблицы (PivotTable) с кнопками выполнения действий со сводной таблицей. Мы познакомимся с ними по мере освоения этих дей ствий. Итак, мы хотим видеть в таблице суммы всех размещенных на фирме зака зов клиентов, причем в строках мы будем группировать данные по продукту, а в столбцах Ч по годам. 5. Сначала определим строки таблицы. Найдите в списке полей запроса в окне Список полей сводной таблицы (Pivot Table Field List) поле Марка (ProductName) и перетащите его мышью в облает]) заголовков строк Ч вертикальную об ласть у левого края формы (рис. 7.10). В этой области появится столбец Мар ка (ProductName) со списком всех продуктов. Определим столбцы таблицы. Найдите в списке в окне Список полей сводной таблицы поле Дата размещения по месяцам (OrderDate by Month) и перетащите его в область заголовков столбцов (рис. 7.10). Появятся три столбца, которые будут содержать сгруппированные по годам данные и один столбец итого вый по строке. . : запрос на выборкч I.... I.*'- - ^, rr -.' '. ; j.,,, | : , -._Х[ Х"Х:-.' ; ,s. *! }-t\ Caniarvofi.Tlgfets Chanrj -...,; ^. щ Х i,i ; ; Х-,..!Х,* >.. Х, Рис. 7.10. Определение строк и столбиов сводной таблицы 6. Строки и столбцы определены, нужно определить, что будет отображаться в самой таблице. Найдите в окне С п и с о к полей сводной т а б л и ц ы поле Цена (Extended Price) и переместите в центральную область (рис. 7.11). Все заказы сгруппированы, и вы видите, что, например, продукт Aniseed Syrup был в трех заказах в 1996 г., в шести заказах Ч в 1997 г. и в одном в 1998 г. В этой таблице не хватает итоговых значений Ч их легко получить. 7. Щелкните на заголовке любого столбца с надписью Цена. Все столбцы, кроме последнего, итогового, окажутся выделенными. Нажмите на панели Анализ данных с помошью сводной таблииы (Р Счета : запрос на выборку F.?vnn f^T HE-PS гатите сед л пг; л фидыиа Годы l; j : !% 1 ]и:.,- | ГЩ '"'Гцциг iiru'-ii Je|a - Hefw|^ Х. Х.., 100,00р.; Щ! fiHirMi 345.30р., ЮО.ООр ; ao.oop.j юо.оор. i 100,00р.; loo.oop.; 80,00p.! 80,00p.; 100,00p.j ХХ 100,00р.! ibd Дор. i I i ll 100,00р.! AniSfefed Зушр jS 55.30р. 136.60р | 170,00 p.; 0,00р. 170,00р.! i i j 136,00р.. 136.00p; 170,00р.! 170,00р.; : 136,00p.i : Beaton i ^ 96,50 p. i. 96.50 p.; : [ I 77,00р. 77.00 p.! 96,50 p.! 77,00p. 96,50 p.! ; 96,50p.i -j | |_ ; _.,_^ ^.Jj^JZffi. Рис. 7.11. Сводная таблица для запроса Счета Счета : запрос на выборку 425,30р. 660,00р.' 'ЮОДОр.; 1 465,30р. ""19Т,ЗОр: 918.CiOpV 170,00р; 1279,30р. йзэдар; 2"487,орр!: 2 iza'rjpp"' 5 i49,oop. ernbefl f-i,- Х,_: 3420,00р.: 345,00р. '4779,оЬр.; 854400р. 1414.50р." '4968,50р.; 1100,00р.; 7 433.00р! .li; -I'villl T|j-.(.-, 10В4,ООр.: 'Э923,50р.: 4322,50р.. 9310.00р. 1008,00р.' 4389.00р.Г 1 684,00р.. 7 081,00р. 917,30р. 4157,00р.: ' 2024ДОр/ 7Ь98,ЗОр. ""Ш.ООр. 420,00р.; ...-:.; '. : i |. i.,..r.iir; " 700Щ 1 "725]Ьрр/ О^ЩР-1 3880 00р. "В40",СОр. 1ЖЖ?Р'! i"poe",gbp. 2"i96,opp,- i "ворлор!": 5004,gop. 8 800,00р.": ЗЭБО'.Шр 14'850,00р.' 27 610.ООр. 1046,00р.' 5376,00р, 2624,00р/' 904800р. '336,00р." 75600р.! 'Х] 26000р.; " 235200р. 448,00р. 1"03600р.Г 980,00р.:" 2464.00р. " 8 6 4. 0 0 5 ^ " 2 772.ООр.; "2вК^^.'- " б 516 ООр. Рис. 7.12. Сводная таблииа Ч итоговые данные инструментов кнопку Автовычисления (AutoCalc)pPii выберите в раскрываю щемся списке функцию Сумма (Sum). Появятся по одной дополнительной строке для каждого продукта, в которой будут суммироваться данные по 1 90 Занятие 7. Анализ и изменение данных с помощью запросов всем заказам, и в итоговом столбце Ч итоговая сумма по всем заказам за все годы по каждому продукту. Теперь в таблице одновременно отображаются и детальные, и итоговые данные. Можно скрыть детальные данные, оставив только итоговые. 8. Для этого на панели инструментов нажмите кнопку Скрыть подробности (Hide Details) Щ. Итоговая таблица представлена на рис. 7.12. Получившаяся таблица Ч двухмерная, так как показывает данные в двух разрезах: продукты и время (годы). Можно добавить еще одно измерение, например, посмотреть, как продажи продуктов распределялись по странам. 9. Найдите в списке полей в окне поле Страна получателя (Country) и перене сите его в область фильтра Ч в самый верх таблицы, в область, которая пока не определена. Теперь вы сможете просматривать и анализировать заказы по странам, отображая в таблице данные только по выбранной стране. Для этого вы должны научиться фильтровать данные в сводной таблице. Сортировка и фильтрация данных в сводной таблице Начнем с фильтрации данных по странам. По умолчанию в таблице отобража ются данные сразу по всем странам. 1. Щелкните на стрелке справа от названия фильтра Страна получателя (Country). Появится окно со списком всех стран, в которых находятся заказчики (рис. 7.13). Слева в каждой строке стоит флажок. Х^Австрия №! Аргентина 5П Бельгия j/i Б раз ипия ^Великобритания 425,30р 660,00р. 400,00р. 1 485,30р. 191,30р 913,00р. 1 279,30р. ^Германия " 539 ",00 р 2487.DOp. 2 123,00р. 5149,00р Ц^ЗДания "Э" 420,00р. 345,00 р 4 779,00р. 3 544,00р. "" ^Ирландия 1 4/14,ЕОр. 49Б8,50р. '7483,00р. " "J 064Д]р; "1 ррб.СЮр.' 1684,00р. ' '' 4157.рОр 917 ЗОр 112.ООр..... 840, ООр. 420,00р. 1 372,00р. 1 725,00р. 'l"500',00p. 700.ООр 3925,00р. ' 640,00р. 1 440,ООр 1 800.00 р. 3 680,00р. 1 008,ООр 1 800,00 р. 2 196,ООр 5 004,00р. а 800,00р. 3960,ООр 14850,00р. 27Б10,ООр. 1 048,0рр ""5376JJOpV 2624,00р. ' 9046,00^' ""'"' "1 260 Д]р. 756,00р. Шдзр...... 980'Щр ДЖЛ9Р Рис. 7.13. Установка фильтра в сводной таблиие Анализ данных с помошью сводной таблииы 2. Сбросьте флажок Все (АИ) в первой строке, будут сброшены все флажки. Ус тановите их только у одной страны, например Франции, и нажмите кнопку ОК. Данные в сводной таблице изменятся, а под наименованием фильтра появилось имя выбранной страны. Окно со списком стран позволяет сделать множественный выбор, то есть выбрать сразу несколько стран. В области фильтра можно поместить несколько полей, и каждая такая опе рация добавляет еще одно измерение в нашу таблицу. Все эти поля будут ра ботать как независимые фильтры. ЗАДАНИЕ Добавьте в область фильтра поле Продавец (Salesperson) и посмотрите, как будут работать эти два фильтра. 3. Чтобы сбросить все установленные фильтры, щелкните на панели инстру ментов на кнопке Автофильтр (AutoFilter) |^П. Вы увидите, что фильтр исчез, но его можно восстановить снова, если щелк нуть на этой кнопке еще раз. Хотя верхняя область таблицы называется областью фильтров, фильтры мож но устанавливать не только описанным способом. Вы могли заметить, что стрелки рядом с названием поля находятся и в области заголовка строк, и в области заголовка столбцов. 4. Щелкните на стрелке справа от заголовка столбца Марка (Product Name). По.-i вится окно, аналогичное использовавшемуся в предыдущем примере. Сбросьте флажок у тех товаров, которые вы не хотите видеть в таблице. Пример применения фильтра по товарам приведен на рис. 7.14. Счета : запрос на выборку 235,00р., 1 430.00р.; 1 932,00р. Рис. 7.14. Сводная таблица с фильтром по товарам и странам 5. В область строк можно внести несколько полей, Перенесите туда поле Полу чатель (Company Name), и вы получите данные о продажах в разрезе не только товаров, но и клиентов (рис. 7.15). Данные сводной таблицы сортируются так же легко, как данные обычной. Сей час они отсортированы по наименованию клиента, а затем по наименованию продукта. Но если вы выделите область данных таблицы, щелкнув на заголовке Сумма Цена (Sum of Extended Price), а затем нажмете знакомые кнопки панели 1 92 Занятие 7. Анализ и изменение данных с помошью запросов инструментов Ч По возрастанию (Ascending) и По убыванию (Descending), таблица бу дет отсортирована в пределах каждого клиента по суммам продаж, а не по товарам, \. KM, - ':+-..':...'.." \*,-z. | ; ' г'.чзз ХХ-Х:: Х|. Х-!'.Х< |aa | | л|Х Х Х ' '| | | | i ; Х Х < "joo'.cop*' ^P-pOp " '"1эо.с)ОрГ"". З^ШР 4SBJ3up. 456.Шр.; Х- 215,00р.: 215,ЬЬр.'...,. : 132,50р. 132,50р. 250,00р.; 250,00р. :,. 280,00р.' 1 053,50р.: " "1 333,50р. 345",30р. " " 345,30р. ' 55,30р. 5530р. 534,50р. 534,50(5. '"' 320 ДОр. | 320"фр! 238'.Шр""'" 288ДОр.' '.. '' : Х 140,00р.' 140.00р. 1 " 92,00р.:" " 92ДОр." "Э48ДОр.Е " 346ДОр! 1 1-C.ith- Uif, iHufi-jpa Nue'S'tiiuiigaf-C iW"1 210,00р.' 210,00р. 340ДОр/ 340,00р. I Рис. 7.15. Сводная таблица продаж в разрезе клиентов и товаров Есть и другие способы фильтровать данные в таблице. 1. Удалите столбец с наименованиями клиентов. Выделите этот столбец и в кон текстном меню выберите команду Удалить (Remove). 2. Выделите столбец Марка (ProductName), щелкнув на его заголовке. На панели инструментов нажмите кнопку Показать верхние и нижние элементы (Show Top/Bottom Item) Затем выберите в списке элемент Показать только в е р х н и й элемент (Show Only the Top) и далее 5 (рис. 7.16). В результате будет отобрано только пять про дуктов с наибольшим значением в столбце Общие итоги (Grand Total). В заго ловке столбца Марка (ProductName) появился значок фильтра (рис. 7.17). 3. Снимается такой фильтр той же кнопкой, но выбрать в списке нужно коман ду Показать все (Show All). Изменение структуры сводной таблицы Преимущество использования сводных таблиц в простоте трансформации. Простым перетаскиванием полей из области в область можно получать разные виды таблиц, меняя местами столбцы и строки, фильтры и столбцы, добавляя и удаляя поля любой области. При этом вы будете получать различные виды представления данных, что и обеспечивает их всесторонний анализ. 1. Переместите поле Продавец (Salesperson) из области фильтра в область столбцов. Анализ данных с помошью сводной таблицы 1 Рис, 7.16, Фильтрация данных по максимальным значениям Я Счета : запрос на выборку R! i',, Mt; '[tin; ,' CvMiWo. "((,,,Х Х566,00p ; 90iO,OOp. 19292,QQp - 3546,OQp.| 9365,ODp:" " "э; 367Д1р." 22278,00р т 3 960,00p.l 14"850,QOpi: 8 600,00р.; 27 610pOp. 8432,COp.i 21 6р7,Шр.: 28985,ODp.: 59024,[X)p Щ 23574,Юр ; Вв"271'; Юр" 73492,60р.Г 165337.SDp Рис. 7,17. Установка фильтра по максимальному значению объемов продаж 2. Переместите поле Годы (Year) в область фильтра. 3. Добавьте поле Получатель (Company Name) в область строк. 4. Удалите поле М а р к а (ProductName) из области строк, перетащив этот столбец за пределы сводной таблицы (при этом рядом с указателем появится изобра жение креста). Столбец исчезнет. 5. Установите фильтр по продавцам, выбрав из общего списка пар}' фамилий. 6. Установите фильтр по годам, отобрав данные только за 1998 год. Вы должны получить таблицу, которая будет выглядеть как на рис. 7.18. При перетаскивании полей обращайте внимание на форму указателя, изме няющуюся над разным и областям и таблицы. Указатель над элементом, кото рый можно перетащить, приобретает форму крестика. При перетаскивании элемента указатель выглядит как миниатюрный макет сводной таблицы, и над той областью таблицы, где можно отпустить перемещаемый элементна этом значке подкрашивается соответствующая область. 7 Зак 1 94 Занятие 7. Анализ и изменение данных с помошью запросов ш Счета: запрос на выборку т Стрднд получателя :Дята jia шещеи ня по иесяцам l952.Cpp.952,00p 762"J3Qp 762,80p!; i-.-fiv: Hanrjel 781,50р. 781.50р.: 77,50p ' ri5S,oop. Х i- r:-,, ^ f ! y : : 1 586,OQp. ""1586Д)р.': . i i i i ' in. 1:1 ХХ. '3SB8'flpp" : 3568,0pp."; .. i! L. i i ' л v 435 №_ 435'Sbp" Рис. 7.18. Трансформированная структура сводной таблицы Изменение уровня детализации данных Чтобы посмотреть, как легко менять уровень детализации отображаемых в свод ной таблице данных, нужно снова поменять местами поля Годы (Years) и Прода вец (Salesperson) и снять все фильтры (нажмите на панели инструментов кнопку Автофильтр (AutoFilter)). В заголовке столбцов слева от числа, обозначающего год, находится значок плюса в квадратике. Это маркер развертывания. Он показывает, что сейчас ото бражаются данные только самого верхнего уровня детализации. 1. Щелкните на маркере, например, 1998 года. Появятся четыре столбца, в ко торых будут отображаться данные за квартал и один итоговый столбец за год. 2. Щелкните маркер развертывания в столбце Кв1 (Qrtl), и таблица развернет ся по месяцам (рис. 7.19). 3. Чтобы раскрыть данные сразу по всем кварталам, выделите заголовок Квар талы (Quarters) (он находится рядом с заголовком Годы). При этом окажутся выделенными заголовки сразу всех кварталов 1998 года (см. рис. 7.19). Те перь на панели инструментов нажмите кнопку Развернуть (Expand) Обратите внимание, что кроме данных по каждому месяцу, выводятся также промежуточные итоги за квартал и за год. 4. Чтобы снова свернуть таблицу, щелкните на заголовке Годы (Years). Будут выделены все заголовки, содержащие год. Нажмите кнопку Свернуть (Collapse) слева от кнопки Развернуть |ccfl Анализ данных с помощью сводной таблицы 1 т Счета : запрос на выборки jiua "Цека" С^мыэ 'Цлнл" С/кма "L(fens' С/ннч 706,00р Х 706 flUp И 71С,ООр Ц. 1 343,10р. 71б,00р'| з"1э"4,зОр.:" ззо",оор 168,00р. Э30,00| i *.'i,'. i :.. : : i ":'.,: от 1 097, 50 р 3 34В,50р. 320.00р. 1 417,5Г1|. 843,50р' 3893, 2276,00р. "_6983.50р !169,20р 88Q,9Qp.; 725,00р " i'350",DO|r 625, ббр! 2424,40р. " Д 5 3 5 Шр 7 273,00р " '| 0"ООДО|. ; "1 62^,до р "7о",оор"; ' 70,'бОр'"" 599|00p'i 5 231,00р. 490flOp.": 267,00pj" " 1 453JDGp.i" 3210,00|. ; 954,00р ; 2679,00р. 3985,00р.; 3965,00р ij. Рис. 7.19, Детализаиия данных по столбиам сводной таблииы Детализировать данные по любому столбцу или строке таблицы можно с по мощью кнопочек со значками л->> и л+>>. 5. Щелкните на кнопке Х+->> справа от названия любой компании-клиента. Во всех столбцах для этого клиента будут показаны суммы по всем счетам и одна итоговая строка (но всем счетам данного клиента) (рис. 7.20). Рис. 7.20. Раскрытие детальной информации по строке 1 96 Занятие 7. Анализ и изменение данных с помошью запросов 6. Снова сверните строку (скройте детальные данные), щелкнув на кнопке л- в той же строке. 7. Аналогично можно развернуть любой столбец. Щелкните на значке л+ в за головке 1997 года. Будут показаны детальные данные этого столбца (все сче та за 1997 год) для всех клиентов. Еще две кнопки панели инструментов позволяют манипулировать уровнем де тализации данных в таблице: {Wj Показать подробности (Show Details) | Скрыть подробности (Hide Details). Щ Их можно использовать, когда требуется развернуть или свернуть данные сразу по всем строкам или столбцам. Нужно выделить соответствующий заголовок (для столбца) или столбец в области строк и щелкнуть на одной из этих кно пок. Попробуйте менять уровни детализации отображаемых данных в таблице, ис ЗАДАНИЕ пользуя все описанные выше приемы и кнопки. Мы создавали итоговые значения командой Автовычисления (AutoCalc). При этом могут быть использованы разные статистические функции. Перечень этих функ ций вы можете увидеть, щелкнув на стрелке этой кнопки. 1. Выделите столбец Получатель и щелкните на этой стрелке. Выберите един ственную доступную функцию раскрывшегося списка Ч Ч и с л о (Count). Б об ласти данных таблицы для каждого года будут отображаться два столбца (рис. 7.21): й$ Счета : запрос на выборку '!Х' 1: ~ ' Г 2 276 flOp. "2424.40р. I: I 4 535 ДОр. 7273.00р.; ' Гб27",90р.; 5 231 ДОр: 2Б79ДОр: 34бДОр"...... 2...... 294Б,Оар "....^.. I ХХ<,. -'. I Х ; . ' ""'9'1: 79а,ООр 2713р Щ -.Х ; .! "з^. 1 424,00р.! " 569,00р; '718, ООр; tl Рис. 7.21. Сводная таблица с двумя итоговыми полями Анализ данных с помощью сводной таблииы 1 П Сумма Цена (Sum of Extended Price) Ч сумма, вырученная от продажи то варов каждому клиенту; П Количество з н а ч е н и й Получатель (Count of Company Name) Ч количество счетов, выписанных клиенту. Вам не нравятся стандартные наименования итоговых столбцов в таблице? Это легко поправить. 2. Выделите нужный столбец и нажмите на панели инструментов кнопку Свой ства (Properties) [Ц* Появится диалоговое окно свойств выделенного поля. Раскройте вкладку З а г о л о в к и (Captions) и в поле Заголовок (Caption) введите значение, которое вы хотели бы видеть в заголовке столбца (рис. 7.22). Формат. Заголовки I Отчет т Ун ик ant) мое имя. итог а [Me asures]. [Ит or I ] Итог] Имя итога Цена. Попе источника итога Тип итога Вычисляемый Рис. 7,22. Диалоговое окно Свойства сводной таблииы, вкладка Заголовки 3. Раскройте вкладку Отчет (Report) с флажками различных параметров табли цы. Например, сейчас в группе Отображать итоги в (Display Total As) установлен флажок заголовках столбцов (Column Headings). 4. Установите второй флажок Ч заголовках строк (Row Headings). Таблица транс понируется, принимая вид, представленный на рис. 7.23. В сводной таблице можно создавать и вычисляемые поля. Суть этих полей та же, что и в запросе Ч значение поля рассчитывается, и требуется задать выражение для этого расчета. 5. Нажмите на панели инструментов кнопку Итоги и вычисляемые поля (Calculated Total and Fields) HI В раскрывающемся списке выберите Создание вычисляемого итога (Create Cal culated Total). Снова появится окно Свойства (Properties), но раскрытой будгт вкладка Вычисление (Calculation) (рис. 7.24). 6. В поле И м я (Name) введите имя нового поля. В большое поле под ним нужно ввести выражение. Причем, если в выражение надо вставить название како I 98 Занятие 7. Анализ и изменение данных с помошью запросов го-то ноля из запроса, то это поле можно выбрать в раскрывающемся списке в нижней части окна и затем нажать кнопку Добавить ссылку на (Insert Refe rence to). Счета : запрос на Х Х Х',. Х V |h id 13153.10р.: SI Biauar See Oelikstess 4499.40)).! Рис. 7.23. Транспонирование таблииы ча | | Вырученная сумма (Итог) Рис. 7.24. Диалоговое окно Свойства сводной таблииы, вкладка Вычисление 7. Закройте окно Свойства. В таблице окажутся лишние строки, уже созданные для нового итогового поля. Их можно легко удалить, выделив (щелкнув на заголовке поля) и перетащив за пределы сводной таблицы. Сводные диаграммы I Аналогично можно создать вычисляемое поле и и детальных данных таблицы (выбрав вторую строку списка Итоги и в ы ч и с л я е м ы е поля (Calculated Total and Fields), но если это нужно, лучше создать это поле н исходном запросе. Сводные диаграммы Готовую сводную таблицу можно легко преобразовать и диаграмму. В нашем примере для наглядности диаграммы нужно отфильтровать клиентов, выбрав, например, только одну страну. 1. Разверните фильтр Страны (Countries) в области фильтра и выберите в списке только одну страну Ч Германию (Germany). 2. Щелкните на стрелке кнопки Вид (View) и выберите пункт Сводная д и а г р а м м а (PivotChart View) Ч получите диаграмму (рис. 7.25). ' '- -:- -'ИОШО Х,. -. j$S Счета : запрос на выборку : -Страну получатели ~* П|м>дд Вец е|>вдния Х.,Все. ^Зьшученная суииа[Коя-цо счетов = 12500,00р. о X П 1""Д1-' ' П п _. ! и 1 Ш IПТГЖТ! 1 _.._Х fl 1 II 1 1 иП OP UC Л ^С ТЗ С -к ии з ^ш с -Е2 сл&1 v J2d л и Jt, И С il i 61 &J и з "И Ь 2* m~ с с а о "О С 5 е. с, Название оси,-Ч.... ~.~~.~,_.Д...,,.,_. : Цодучатель " Рис, 7.25. Сводная диаграмма Как и в сводной таблице, в верхней части поля диаграммы находится область фильтра, в которой размещены два поля: Страна получателя (Countries) и Прода вец (Salespersons). Вы видите, что и данный момент установлен фильтр по стра нам. По горизонтали располагаются получатели, а по вертикали Ч суммы про даж. Для каждой страны отображается три столбика Ч каждый соответствует году (1996, 1997, 1998). Поля Получатель (Company N a m e ) и Годы (Years) стали тоже фильтрами. Область справа, где находится поле Годы, называется обла стью рядов, область внизу, и которой находится поле Получатель, называется. 200 Занятие 7. Анализ и изменение данных с помошью запросов областью категорий. Раскрыв соответствующий список, можно выбрать от дельные фирмы или только один год и быстро получить требуемые данные. Как и сводную таблицу, диаграмму можно трансформировать, перемещая поля из области в область. Изменяйте диаграмму, перемещая поля из области в область и устанавливая ЗАДАНИЕ различные фильтры. Сводная диаграмма тесно связана со сводной таблицей Ч это просто две формы представления одних и тех же данных. Переключитесь в режим сводной табли цы, и вы увидите, что она тоже изменила вид в соответствии с диаграммой. Создание сводной диаграммы Сводную диаграмму можно построить сразу на основе запроса или таблицы. 1. Выберите в списке запрос Продажи по сотрудникам и странам (Employee Sales by Country) и откройте его в режиме конструктора. 2. Этот запрос имеет параметры, которые в данном случае нам не нужны. По этому сначала удалите условие в столбце ДатаИсполнения (ShippedDate), а за тем нажмите кнопку Parameters (Параметры) на панели инструментов, открой те окно Параметры (Parameters) и удалите два параметра запроса. Закройте окно Параметры. 3. Щелкните на стрелке кнопки Вид (View) и выберите пункт Сводная диаграмма (PivotChart View). Появится окно макета сводной диаграммы (рис. 7.26). :ёр Продажи по сотрудникам и странам : запрос на выйоикч о 0,75 Й O.S 0.25 Рис. 7.26. Макет сводной диаграммы 4. Поместите в область категорий (в нижней части макета) поля Ф а м и л и я (Last Name) и Имя (First Name). Значения этих полей будут размещаться по гори зонтальной оси диаграммы. Сводные диаграммы 5. В область данных в центр макета поместите поле СуммаПродаж (SaleAmount). Значения этого поля будут откладываться по вертикальной оси диаграммы. Диаграмма построена. Добавим надписи осей. 6. Щелкните на надписи Н а з в а н и е оси (Axis Title) под горизонтальной осью, она будет выделена. 7. Если окно Свойства не отображается на экране, щелкните на панели инстру ментов на кнопке Свойства |Si В окне Свойства раскройте вкладку Формат (Format). Введите в поле Заголовок (Caption) значение Продавцы. 8. Аналогично введите название вертикальной оси Объем продаж. 9. Если мы хотим, чтобы на одной диаграмме отображались данные в разбивке по годам, необходимо перенести поле Дата исполнения по месяцам (Shipped Date by Month) в область рядов в правой части макета. Теперь каждому продавцу компании будет соответствовать серия столбцов диаграммы Ч каждый стол бец для одного года. У столбцов будет разная окраска: каждому году будет соответствовать свой цвет. 10. Чтобы видеть, какой цвет какому году соответствует, можно вывести легенду. Нажмите на панели инструментов кнопку Добавить легенду (Show Legend) [Ш| Пояиится табличка с легендой Годы (Years). 11. Переместите поле Страна (Country) в область фильтра, чтобы можно было фильтровать данные по странам. Диаграмма приобрела законченный вид (рис. 7.27). JP Продажи по сотрудникам и странам i запрос на выборку 1 500000.00р. 1 000000,00р. 500000.00р. Иван Ольга Мария Дарья Петр Андрей Анна Павел Инна ' Продавцы Рис. 7.27. Сводная диаграмма Продажи по сотрудникам и странам 12. Переключитесь в режим сводной таблицы, и вы увидите те же данные в виде сводной таблицы. Занятие 7. Анализ и изменение данных с помощью запросов Изменение вида диаграммы Получившийся тип диаграммы называется гистограммой. Его легко изменить, 1. Щелчком выделите область данных диаграммы. Нажмите на панели инстру ментов ставшую доступной кнопку Тип д и а г р а м м ы (Chart Type) Щ. 2. В диалоговом окне Свойства (Properties) раскрыта вкладка Т и п (Туре) с воз можными типами диаграмм (рис. 7.28). Общие ] Границы и заливка 'Х\$. ' - Х ХХ'ХХ'ХХ'Х Линейчатая Граф>к \d Гладкий графи Круговая I.-.-" Точечная ?! Пузырьковая А С областями Э Кольцевая lf Лепестковая ьС Биржевая Полярная Рис. 7.28. Типы сводной диаграммы вр Продажи по сотрудникам и странам : запрос на выборЩ rjj гид ; ^ зоооога.оор. та ч g_ 2000000,00р. с i 1 оооооо,оор. j 0,00р Рис. 7.29. Сводная диаграмма Ч график Изменение данных с помошью запросов 3. Выберите любой понравившийся тип, например, График (Line), а затем под тип справа. Внешний вид диаграммы изменится (рис. 7.29). 4. Закройте запрос, сохранив все изменения. Итак, мы рассмотрели, как извлекать данные из таблиц базы данных и клк представить эти данные в удобном для анализа виде. Рассмотрим, как с помо щью запроса можно изменять данные. Изменение данных с помощью запросов Все запросы, которые мы строили до сих пор, помогали извлекать данные из таблиц и анализировать их с помощью разного рода вычислений. Однако, клк уже говорилось выше, можно создать запросы, изменяющие сами данные. Изменение данных в результирующем множестве запроса Когда результат запроса отображается в виде таблицы, возникает желание не только просматривать, но и изменять данные в этой таблице. Поскольку так; ш таблица является виртуальной,, редактирование полей запроса на самом дс-лс означает редактирование полей и таблицах-источниках. Однако такое редакти рование не всегда возможно, и при попытках изменить значение некоторого поля вы можете получить сообщение, что данные в запросе не являются обнов ляем ыми. Является ли запрос обновляемым, достаточно легко обнаружить: при отображе нии результатов запроса в конце записей должна быть пустая строка, помечен ная значком л* в области выделения записи. Тогда в эту строку можно вво дить данные, которые создадут новую запись. Если такая строка отсутствует, добавлять записи в запрос и изменять поля запроса нельзя. В частности, нельзя добавлять или изменять записи, если; Х в запросе применяются статистические функции; Х две таблицы запроса связаны отношением лодин-ко-многим, и при этом в 'i a блице лодин не задано полей первичного ключа. Условия, при выполнении которых можно обновлять и добавлять записи в за просе: Х таблица является единственной в запросе; Х таблицы в запросе связаны отношением лодин-к-одному>>; Х если таблицы связаны отношением лодин-ко-многим>>, можно изменять поля только в таблице многие. В таблице лодин, связанной с другой таблицей отношением лодин-ко-мно гим, можно изменять пол я только втом случае, если ни одно поле таблицы многие не включено в результат запроса, а используется для отбора за писей. Занятие 7. Анализ и изменение данных с помошью запросов Кроме того, любой запрос имеет два свойства; Уникальные значения (Unique Values) и Уникальные записи (Unique Rows). Большинство запросов, свойство Уникальные з н а ч е н и я ( U n i q u e Values) которых имеет значение Нет, может использоваться для обновления данных, а запросы, свойство У н и к а л ь н ы е з н а ч е н и я которых имеет значение Да, не допускают ни обновления имеющихся в н и х записей, ни добав ления новых. Чтобы открыть диалоговое окно свойств запроса, щелкните правой кнопкой мыши на свободном поле в верхней панели окна конструктора запроса и вы берите в контекстном меню команду Свойства (Properties). ЗАДАНИЕ Откройте созданные вами запросы в режиме таблицы и посмотрите, какие из них являются обновляемыми, а какие нет. Попробуйте изменять поля в результирующем наборе и убедитесь в действии правил, описанных выше. Запрос обновления данных Рассмотрим новые виды запросов, которые позволяют выполнять массовые из менения данных, то есть изменения сразу во многих записях в соответствии с опре деленным правилом. Перед выполнением следующих упражнений откройте базу данных БиблиотекаДан Hbie.mdb и измените таблицу Издания: в нее нужно добавить еще одно поле Ч Списано. ' тип даннь Х? :"' c.-Tur-iK-t.'... Издание Числовом КодМвста Числовой : Цена Двнвжшй. ТипОбложки Текстовый | J " ДатаПокупки Дата/время ЧислрСтраниц Ч меловой ТипИзданий Текстовый Заметки ПолеМЕМО ! V.* -! J -: Х:Х : Х -.- :- '-. ХХ..,- :- '. Рис. 7.30. Добавление поля Списано 1. Раскройте список таблиц базы данных БиблиотекаДанные.тс1Ь. 2. Откройте в режиме конструктора таблицу И з д а н и я. Изменение данных с помощью запросов Добавьте в конец таблицы новое поле. Введите имя поля Списано, выберите в списке тип данных Логический (Yes/No) (рис. 7.30). Введите в ячейку свойства Значение по умолчанию (Default Value): Нет (No). 3. Переключитесь в режим таблицы и убедитесь, что логическое поле отобра жается в виде флажка (рис. 7.31). Греческий м русский тексты, стать! В сборник включены 1333 четверс; П Российская энциклопедия для пег О ! а" жесткая, з свержу мягкая 21.D9.1999 550! книга жесткая 11.09.1998 480: книга Швейцарский философисследует: П жесткая 21.09.1999; 80:*ннга "О" жесткая 23.09.2000] 6ЭО:очиГ5 П жесткая I 15.12 1999! 356 книга |К семантике садово-парковых сти: жесткая : 15.092000: 592 книга ! Библиотека славянской литератур: 20.091999: \ жесткая 55В. книга 'Личная Библиотека Борхеса Рис. 7,31. Отображение поля типа Логический (Да/Нет) 4. Закройте таблицу, сохранив изменения. Допустим, нам нужно установить флажок Списано для всех книг, которые при обретены до 1 января 1997 года. Для этого можно использовать запрос обновле ния записей. Этот запрос можно создать за три шага: Х создать запрос выборки; Х преобразовать запрос выборки в запрос обновления; Х выполнить запрос, обновив данные в отобранных записях. Итак, начнем с запроса выборки. 1. Выделите в списке таблиц таблицу И з д а н и я. Щелкните на панели инструментов на стрелке кнопки Новый объект (New Object) и выберите в списке значение Запрос (Query). В диалоговом окне Новый запрос (New Query) выберите в списке Конструктор (Design) Появляется окно конструк тора запросов с таблицей Издания на верхней панели. 2. Перенесите в бланк запроса поля Название, ДатаПокупки, Списано. 3. Введите условие отбора записей Ч в столбец ДатаПокупки выражение < 01.01.1999 (рис. 7.32). 4. Выполните запрос, чтобы убедиться, что записи отбираются правильно. Изменим запрос, превратив его в запрос обновления. Для этого вернитесь в режим конструктора, щелкните на панели инструментов на стрелке кноп ки Тип запроса (QueryType) [ДЯ Занятие 7. Анализ и изменение данных с помощью запросов Затем выберите команду Обновление (Update). Изменяется заголовок запро са, и появляется дополнительная строка Обновление (Update То). При этом ис чезают строки Сортировка (Sort) м Вывод на экран (Show). ЧислоСтранмц ТипИздания Заметки Списано tU :: _ J^a ?Д.; ; ,.Д Х^.; .-; , ' Название ДатаПокупки Списано Имя таблицы; Издания Издания Издания МП. п$яу. а а SfaJBD.Х; :ХХ ; < #01.01. 1999* или; Ж 1 : ' л ли ; Рис. 7.32. Отбор писем для установки флажка Списано 5. Введите в строку Обновление (Update To) столбца Списано значение Да (Yes) (рис. 7.33). ""' т ' < -. gj-npocljaanpoc иа ofiHOBiiefi^ """ ~.- - '-""v-"^-" ".. шшшмдишнмш " : ' : " ' '' : :Х: I В --: ' -| | оЩ| Н ЧислоСтранмц iu ТипИздания ; -; .; Х' Х |У ||." "-... " '.--' TI. ' '! ; ? ' ' "..-.:: ' Х :.. ; - Х. - - - Xv.jT.'.; . Заметки Списано "^ ; ; :tt.rt.::; ; :: 1. " ^ = f /?-,., ' t| lj " Х' ' Ь 4I?HWff.tf; *. .. >f~* 1 'fii il.. Х - : -Ч* *. '. : :Поле; ' Название -^i _; Ji....-.!!-? --'. :. л,,'Д а^ -,, 1 1 Х; ' ДатаПокупкм Списано щ\ Издания Издания Издания Ой-юБпенмЁ1 Х Да Услреиеотоорз: <#С1, 01,1999* Х :- *. -- - Х..яда: ^и ш. V' Рис. 7.33. Создание запроса обновления данных На панели инструментов нажмите кнопку Запуск ( R u n ) Ш и выполните прос. Изменение данных с помошью запросов В данном случае для выполнения запроса нужно нажать именно кнопку За пуск (Run), так как требуется не просто переключиться в режим таблицы, а выполнить действия над данными. Access выдает сообщение о количестве обновляемых записей (рис. 7.34). 6. Подтвердите выполнение обновления. Будет обновлено следующее ч-каа запрей: 4. Ш ХХ.ХХwwivm. Поста пожатий гюжи Vi--'" отмена г,-i-f:).,)!"-' Ш Рис. 7.34. Предупреждающее сообщение о числе обновляемых записей №! Одним из важных правил во время работы с запросами изменения (обнов ления, добавления, удаления) является обязательное создание резервной копии таблицы, в которую необходимо внести изменения. Дело в том, что исправления, внесенные запросами изменения, необратимы, к тому же час то нелегко обнаружить записи, измененные ошибочным запросом. 7. Сохраните запрос, дав ему имя Запрос на обновление, и закройте. В списке за просов появился запрос с другим значком (рис. 7.35). Восклицательный знак в значке означает запрос изменения, а карандашик Ч тип запроса Ч обновление. Ш Библиотека Данные : база данных (формат Access 2BOO] 1Р !у Создание запроса в режиме конструктора ?Ыяи1йгИ$ ^У Создание запроса с помощью мастера Задерживающие ^ниги р Издания&Двторы Отчеты Книги по авторам Количество изданий по разделам "J Образец Фильтра по Форме Поступления за период Средние сроки наличия книг на руках Рис. 7.35. Запрос обновления в окне базы данных Раскройте список таблиц в окне базы данных и откройте таблицу Издания. Убедитесь, что у записей, имеющих значение в поле Д а т а П о к у п к и меньше 01.01.1999, флажок Списано установлен (рис. 7.36). 208 Занятие 7. Анализ и изменение данных с помошью запросов 1405..жесткая, суперобложка ^В сборник включены 1333 четверо мягкая '28. Российская энциклопедия для па( "21.09.199В 620 книга Жесткий '21.09.1999 550 книга.жесткая, а сверху мягкая....йД.. 11.09.199В; 430 книга ; Швейцарский философ иссладуэт жесткая D 80 книга жесткая 21.09.1999! ,П Х 28.03.2000: 630 книга жесткая К семантике садрер:парковы* сти 15.12.1999; 356 книга жесткая П Библиотека славянской литератур 592 книга жесткая 15.09.2000' О" 20.09.1999 556 книга ХЛичная библиотека Борхеса жесткая D ш. t*j Н Г -Х i/ij !*!>':- 1! Рис. 7.36. Результат запроса обновления записей 9. Закройте таблицу Издания. Запрос создания новой таблицы В следующем упражнении мы должны скопировать записи о книгах, которые по метили как списанные, в другую таблицу, чтобы не удалять их совсем. Однако та кую таблицу нужно еще создать, и сделать это можно также с помощью запроса, При этом необходимо выполнить те же три шага: Х создать запрос выборки; Х преобразовать запрос выборки в запрос изменения, задав место размещения новой таблицы; Х выполнить запрос изменения, тем самым поместив отобранные записи в но вую таблицу. ЗАДАНИЕ Создайте самостоятельно запрос выборки. Для этого можно слегка изменить уже имеющийся запрос Книги по авторам. Откройте его в режиме конструктора и до бавьте в бланк запроса ряд полей. Первым должно идти поле КодИздания из таблицы Издания. После поля Название должны идти все остальные поля таблицы Издания, кро ме КодМеста, которое нас теперь не интересует. Обязательно включите поле Списано, так как именно по нему нужно отбирать записи. Введите условие отбора и выполните за прос, чтобы убедиться, что действительно отбираются записи с пометкой Списано. Сни мите для поля Списано флажок Вывод на экран (Show) и командой Файл > Сохранить как (File * Save As) сохраните запрос под именем Запрос на создание таблицы. ГАГ Нельзя сохранять запрос кнопкой Сохранение (Save) или закрывая запрос, как мы это делали раньше, так как тогда запрос сохранится подтем же име нем, удаляя тем самым старый запрос. Выполните следующие шаги. 1. Преобразуйте запрос выборки в запрос создания таблицы. Для этого щелк ните на стрелке кнопки Тип запроса (Query Type) и выберите в списке команду Изменение данных с помошью запросов Создание таблицы (MakeTabLe) 1. Диалоговое окно Создание таблицы (Make Table) показано на рис. 7.37. Рис. 7.37, Диалоговое окно Создание таблииы В текстовом поле и м я таблицы (Table Name) введите имя новой таблицы Ста р ы й фонд. В базе данных Access не должно быть таблицы ил и запроса с таким именем. Новая таблица может быть создана в текущей базе данных или в другом файле. Оставьте переключатель по умолчанию (в текущей базе д а н н ы х ) и на жмите кнопку ОК. 2, Закройте запрос. Теперь в окне базы данных ему соответствует другой зна чок, сопровождаемый восклицательным знаком (рис. 7.38). Это означает, что запрос является запросом изменения. рБибймотекаПанные: база данных (фи ^ OWflbfTb x/^VfJ].^ I Ч i Создание запроса в режиме конструктора Создание запроса с помощью мастери Задерживающие книги Запрос на обновление ИзданияЬАвторы и по авторам Количество изданий по разделам Образец фильтра по форме Поступлемия за период.. ; Среямие срони наличия книг на рукак Рис. 7.38. Запрос изменения в окне базы данных Если вы не видите этой строки меню, подождите пару секунд, удерживая указатель на меню. Появятся скрытые строки меню. Занятие 7. Анализ и изменение данных с помощью запросов Выполнение запроса приведет к созданию таблицы, в которую будут поме щены все записи, отобранные первоначальным запросом выборки. Поля таб лицы будут соответствовать полям, включенным в результирующее множе ство запроса. 3. Для выполнения запроса дважды щелкните на его имени в окне базы данных. При этом он не отображает результирующий набор записей, а выполняет нужное действие. Перед его выполнением появляется сообщение (рис. 7.39), предупреждающее о том, что в новую таблицу будут внесены изменения. В иовую табпицу будет помещено следуннцее ч^по^иифй'З-. По.:гв нд+атя* кнсч* и "Да" отменаизмсненим станет неео^южна. Рис. 7.39. Предупреждающее сообшение о внесении изменений 4. Нажмите кнопку Да (Yes). Поскольку запрос запускается впервые, будет соз дана новая таблица Старый фонд. Раскройте список Таблицы (Tables) в окне базы данных; в списке появилась новая таблица (рис. 7.40). Ьиб л потека Данные : база данным {формат Access Создание таблицы в режиме конструктора :r".""" " Создание таблицы с помощью мастера S i of.HUM Создание таблицы путем ввода данных АвторИ здание Авторы Города Издания Издательства М ест аХранения Разделы Д*j Избран.. Рис. 7.40. Новая таблица в окне базы данных 5. Откройте новую таблицу, дважды щелкнув на ее значке. Она содержит те же данные, что и запрос создания таблицы в режиме таблицы (рис. 7.41). Изменение данных с помощью запросов 21 Детская литеру Еремеева Оль! Мы вдем ребе 1: 199Э; 13-Детская.лнтер^Митрошенкое /.Мы ждем ребе '_ Хайям Омар \ Рубай 1995: 7 Поэзия 1994 4! 20" Психология Х Вейс Францией Нравственные 1989. 6-Философия ; Платон :Федр ' [Счетчик! s ЩШР '*> Рис. 7.41. Таблица, созданная при помощи запроса Новая таблица не наследует свойства полей и первичный ключ из первич ных таблиц. Поэтому в полях Код Издательства и КодГорода отображаются действительно коды, а не значения из столбца подстановки, как в таблице Издания. 6. Закройте таблицу Старый фонд. Запрос создания таблицы может использоваться многократно для создания временных таблиц, при этом каждый раз будет создаваться новая таблица и удаляться старая. Запрос удаления записей Запросы удаления записей позволяют отобрать требуемые записи и удалить их за один прием. Принцип создания такого запроса аналогичен созданию запроса обновления, однако, удаляя записи из связанных таблиц, необходимо помнить о том, что при этом не должна нарушаться целостность данных. Когда две таблицы связаны отношением лодин-ко-многим, нельзя удалять за писи из таблицы лодин, если в таблице многие присутствуют соответству ющие им подчиненные записи. Сначала должны удаляться записи в таблице многие, и только потом соответствующие им записи в таблице лодин. Чтобы упростить этот процесс, Access позволяет при определении связей между таб лицами установить флажок каскадное удаление связанных записей (Cascade Delete Related Records). В следующем упражнении мы будем удалять записи из таблицы Издания, кото рые имеют значение Да в поле Списано. Однако таблица Издания связана отно шениями лодин-ко-многим с таблицами А в т о р И з д а н и е и Ф о р м у л я р ы. А как у нас установлены флажки для этих связей? 1. Нажмите на панели инструментов кнопку Схема д а н н ы х (Relationship) pjl Дважды щелкните на линии связи таблиц Издания и АвторИздание. Появится диалоговое окно Изменение связей (Edit Relationships). Флажок каскадное об новление связанных полей (Cascade Update Related Fields) установлен, а флажок каскадное удаление связанных записей (Cascade Delete Related Records) Ч нет. 212 Занятие 7. Анализ и изменение данных с помошью запросов Установите его (рис. 7.42). Теперь при удалении записи из таблицы Издания будет одновременно удаляться ссылка на это издание из таблицы Авторы. .S J^J A BTQ рИ: дан ие Издания ilti.. ^КодИздания КодИэдания,v Х Х ; Х-' г^*ЗД'^ c-UHOa; tnii.i.?rr.aw,,.i. Х ' ">^ :-' |,,,-'5д.- |,<.; Х!>_-<-* к..c>'i | |. -.-,Х Ш Ji '-. один-^с тети! Т иг отношения: Рис. 7.42. Установка флажка каскадное удаление связанных полей Аналогично установите флажок каскадное удаление связанных записей (Cascade 'Delete Related Records) в связи между таблицами Издания и Формуляры. 2. Создайте запрос выборки на базе таблицы Издания. Дважды щелкните на эле менте л* в списке полей таблицы И з д а н и я Ч в бланк запроса будут включе ны все поля таблицы. Чтобы установить условия отбора, включите в бланк запроса поле Списано и введите в строку Условия отбора (Criteria) значение Да (Yes). Снимите флажок Вывод на экран (Show) для этого поля, так как его значение уже отображается (Издания.*) (рис. 7.43). \& Запрос! : запрос на выборку '' : Х- " ' ' 'Х 7. " ' : i Х; ' М'. ш 'Ш.---. -. --.- -L M^.jf ЧиспоСтрани1|У ТипИэдания \ :| Заметки |j^ Списано т Ч * Издания.* Списано ша Издания 14з дания Е D П Да ХГ ", -.. № :^ ' Рис. 7.43 Создание запроса, включающего все поля таблииы Издания 3. Запустите запрос выборки, чтобы проверить, правильно ли отобраны запи си. Оказываются отобранными 5 записей. Изменение данных с помошью запросов 21 Чтобы преобразовать запрос выборки в запрос удаления записей выберите команду Запрос > Удаление (Query > Delete) (или щелкните на панели инстру ментов на кнопке Тип запроса (QueryType)). Строки Сортировка (Sort) и Вывод на экран (Show) исчезают, появляется строка Удаление (Delete) (рис. 7.45). В первом столбце строки Удаление появляется значение Из (From), которое показывает, что будут удаляться записи из таблицы И з д а н и я. Во втором столбце строки Удаление появляется значение Условие (Where). Это означает, что этот столбец используется для определения критерия отбора записей для удаления. jзi Запрос 1 : запрос на удаление ЧислоСграниЛ ТипИздания Заметки |. Списано _т] iU Издания,* Списано Издания Издания Из Условие aJicru'pS. Да израз и; !! ш Рис. 7.44. Запрос на удаление Нажмите кнопку Запуск (Run). Выводится сообщение с приглашением под твердить удаление записей и информация о количестве записей, которые бу дут удалены (рис. 7.45). Их будет столько же. сколько было отобрано в за просе изменения. В этот момент еще можно отменить удаление. Для этого достаточно нажать кнопку Нет в окне сообщения. Нажмите кнопку Да, под тверждая удаление. Из указанной табшцы будет удалено следующее число записей: 4. ':',Х>". Рис. 7.45. Предупреждающее сообщение об удалении записей Операция удаления записей будет выполнена, причем одновременно будут удалены соответствующие записи из связанных таблиц АвторИздание и Фор муляры, если они там были. 21 4 Занятие 7. Анализ и изменение данных с ломошью запросов 4. Сохраните запрос, дав ему имя Запрос на удаление, и закройте его. В списке запросов окна базы данных появится новый запрос со значком удаления и'восклицательным знаком л! (рис. 7.46). Ш БмбпмотекаДанные ; база дэнныи (Формат /, KUHCT p; rrop ; :1Щ Создание запроса в режиме конструктора щ Создание запроса с помощью мастера j ^ Заде ржив зющ ие к ни ги :.iri|. ХХ ы :?Х*! Запрос на обновление ?J ? Запрос на создание таблицы fc? ^ Издания^Авторы Хt^ Книги по авторам i"'ip Количестео изданий по разделам. [^1 Образец Фильтра по форме ^ Поступления за период ^ Средние сроки наличия книг на рукам ' '.'.. -. - - - - -- Х - -....-..-.--....; ,.Щ. Рис. 7.46. Запрос на удаление в окне базы данных Запрос добавления записей Запрос добавления записей используется, чтобы скопировать данные из одной таблицы в другую. Так же как и другие запросы изменения, он строится на базе запроса выборки, поскольку сначала нужно отобрать те данные, которые в виде записей будут добавлены в целевую таблицу. В запросе выборки необходимо соединить требующиеся таблицы, включив в ре зультат запроса те поля, которые должны составить добавляемые записи, а так же поля для установления критериев отбора записей. Для добавления данных имена полей запроса и полей таблицы, в которую зано сится информация, должны быть идентичными. Если имена различны, в бланке запроса добавления надо указать их соответствие. 1. Откройте таблицу И з д а н и я и пометьте несколько изданий как списанные. 2. Откройте в режиме конструктора запрос Запрос на создание таблицы. Этот за прос позволяет отобрать из таблицы И з д а н и я все записи, у которых установ лен флажок Списано. Преобразуем этот запрос в запрос добавления. Выберите команду Запрос > Добавление (Query > Append) или щелкните на стрелке кнопки Тип запроса (Query Type) и выберите в списке команду Добавление (Append). Появляется диалоговое окно Добавление (Append), аналогичное диалого вому окну Создание таблицы (Make Table) (рис. 7.47). 3. В поле И м я таблицы (Table N a m e ) выбирается в списке имя таблицы, в кото рую будут добавляться записи. В данном случае по умолчанию в нем указана Изменение данных с помошью запросов 21 таблица Старый фонд, участвовавшая в запросе создания таблицы. В эту таб лицу мы и будем добавлять записи, поэтому нажмите кнопку ОК. Добавление записей в таблицу цмя твбяицы; .. ^старый фонд! Хv 1.1.; ХХ Рис. 7.47. Диалоговое окно Добавление В бланке запроса добавления появляется дополнительная строка Добавление (Append To). В ней указываются имена полей таблицы, в которую будут добав лены записи (рис. 7.48). По умолчанию в строке Добавление (Append To) содер жатся имена, совпадающие с именами полей в бланке запроса. Если какие-то имена в запросе и в таблице не совпадают, в соответствующем раскрывающем ся списке можно выбрать поле таблицы, которое соответствует полю запроса. gp Запрос на создание таблицы : запрос на добавление 'Х ! : -- '. Г: ".-.ХХ Х Х ~ даа ъ "' \. Хп ЦэтаПои^пки J 1_ ", КпдАвтсра ?ХХ | ЧислоСтраниц -~- л. ^"^ КцдРаздепа КодАвгсра : ТипИэдания Раэдеп -Х| Фамилия i1. ; . . "- Заметки 'Х 4 ГодРожден>) Списано |у "' ::,. *,-,.V, J, ^. -"'" " Х' Х "- ' Ч ш- ^^ Ч Ч =- Ч *' К.одИздатепьсиз Код Издания Раэдег Автор [Фамипи Название ГодИздания i л,. Х>:Х Издания Издания Издания Издания Раздегы по возрастанию по возрастанию.V : Раздег Автор Название ГодИздания КодИздатепьс1; з Кед Издания ЦВбсв.; ; <И -.. LS i li LlM MMMj : ^ Рис. 7.48. Запрос добавления Нажмите кнопку Запуск (Run), чтобы выполнить запрос. Прежде чем новые записи будут добавлены в таблицу, появляется сообщение о числе добавля ющихся записей. Записи будут добавлены только после нажатия кнопки Да (Yes). Если нажать кнопку Нет (No), вставка записей будет отменена. Из таблицы Издания записи при этом удалены не будут. Вы можете в этом убедиться, открыв ее. 216 Занятие 7. Анализ и изменение данных с помошью запросов Сохраните запрос, выполнив команду Файл > Сохранить как (File > Save As). Дайте новому запросу имя Запрос на добавление. Новый запрос изменения появляется в окне базы данных (рис. 7.49). 4. Откройте таблицу Старый фонд и посмотрите, какие записи были добавлены в таблицу. ЦбибяиотекаДанные: база данных (фермат Access ?.-., ШтШ\ Создание запроса в режиме конструктора Создание запроса с помощью мастера Задерживающие книги тптиггптшии ; f*$ Запрос на обновление Slf Запрос на создание таблицы >! Х Запрос на удаление _j3 И здания&Авторы р Книги по авторам _JJ Количество изданий no pa зле лам Образец Фильтра по Форме ; р ^ Поступления за период $0$ Средние сроки наличия книг на руках Рис. 7.49. Запрос добавления в окне базы данных Самой частой ошибкой при выполнении этого запроса является попытка вставить записи, у которых значение первичного ключа совпадает с уже имеющимися в таблице записями. Такие записи вставлены не будут, и будет выдано сообщение об их количестве. Подведение итогов Завершив второе занятие по изучению запросов, вы научились: Х группировать записи в запросе и подсчитывать количество записей в каждой группе; Х подсчитывать среднее значение в числовом поле по каждой группе записей; Х создавать сводные таблицы и выполнять их трансформирование; Х создавать сводные диаграммы; Х создавать и выполнять запросы изменения: добавлять, удалять и обновлять записи, создавать новые таблицы. Приобретенные навыки понадобятся на следующих занятиях, когда на базе за просов мы будем создавать другие объекты базы данных: формы, отчеты, стра ницы. -О 2. и О е ЗАНЯТИЕ Знакомство с формами и элементами управления ТЕМА ЗАНЯТИЯ На этом занятии вы узнаете: Х для чего предназначены формы; Х какие существуют режимы работы с формами; Х что представляет собой конструктор форм; Х какие существуют элементы управления в форме; Х как их перемещать и изменять размеры; Х как сохранить форму в базе данных; Х как создать форму с помощью мастера автоформ. Формы как средство ввода, просмотра и изменения данных Для выполнения упражнений данного занятия потребуется файл Библиоте ка.mdb, который вы создали на Занятии З 1. Откройте его. перед началом за нятия. Формы как средство ввода, просмотра и изменения данных Microsoft Office Access 2003 позволяет организовать удобный и интуитивно по нятный интерфейс пользователя для работы с данными двумя способами: Х с помощью форм и отчетов; Х с помощью страниц доступа к данным. На данном занятии мы познакомимся с формами, которые представляют собой традиционный способ организации интерфейса пользователя. Формами назы ваются настраиваемые диалоговые окна, сохраняемые в базе данных в виде объ ектов специального типа, так же как таблицы и запросы. Во многих случаях формы являются более удобным способом ввода, про смотра и изменения данных, чем таблицы. Они содержат элементы управле ния: текстовые поля для ввода и правки данных, кнопки, флажки, переклю чатели, списки, надписи, а также рамки объектов для отображения графики и объектов OLE, с помощью которых осуществляется доступ к данным в таб лицах. Элементы управления позволяют отображать данные полей таблицы в удобном и наглядном формате, выполнять проверку корректности данных при вводе. С помощью подчиненных форм можно в одном окне показать од новременно данные из главной и подчиненных таблиц, что тоже бывает очень удобно. Тем не менее нужно отметить, что часто хочется просмотреть сразу большое количество записей, найти или отобрать требуемую запись и тут же ее отре дактировать. В этом случае удобный режим просмотра и редактирования данных в форме таблицы, который обеспечивает Access, оказывается очень полезным, и многие опытные пользователи не хотят от него отказываться. И это нормально. Только тогда рекомендую установить все возможные пра вила проверки вводимых данных как на уровне поля, так и на уровне запи сей, чтобы защитить эти данные от некорректного ввода. Источником данных для формы может быть либо таблица, либо запрос. Как правило, формы, предназначенные для ввода данных, строятся на базе табли цы, так как при вводе нужно обеспечить ввод всех или большинства полей в таб лице. Примеры вы можете найти на сайте издательства www.piter.com на странице, посвящен ной этой книге. 220 Занятие 8. Знакомство с формами и элементами управления Из этого правила бывают исключения. Например, в ситуации, когда таблица заполняется в несколько приемов, может быть несколько форм для ввода в нее данных, и, следовательно, в качестве базы для этих форм могут исполь зоваться запросы. Формы, которые должны в удобном виде представлять данные, как правило, строятся на базе запросов, так как они могут отображать не все поля таблицы или данные из разных таблиц. На этом занятии мы создадим достаточно простую форму и познакомимся с элементами управления, которые могут применяться в формах. Впоследствии вы сможете обращаться к материалу этого занятия как к справке для создания форм в своих приложениях. Сначала нужно определить, какие формы потребуются для работы с нашей ба зой данных Библиотека. Очевидно, нужна удобная форма для ввода новых изда ний, так как если вводить данные прямо в таблицы, придется сначала открыть таблицу Издания и ввести все данные о новой книге, затем открыть таблицу Ав торы и ввести автора или несколько авторов, если книг таких авторов в библио теке еще нет. И, наконец, нужно открыть таблицу АвторИздание, чтобы связать за писи в обеих таблицах: Авторы и Издания. Согласитесь, это не очень удобно. Фор ма для ввода издания, которую мы создадим, так и будет называться Ч Издания. Затем необходима форма, которая позволила бы легко найти нужную книгу нужного автора. Эту форму назовем Карточка автора, и она же обеспечит ввод данных об авторе. Кроме того, у нас будет форма Читатели, отображающая не только личные дан ные читателя, но и книги, которые он брал в библиотеке. И еще хотелось бы иметь формы, дающие возможность легко оформить выдачу книг читателю и, соответственно, сдачу книг. Мы создадим одну форму, но так, что ее можно будет использовать для выполнения и той и другой операции, и выглядеть она будет как две разные формы. Назовем ее Выдать книгу. Такие формы нам предстоит создать на протяжении ближайших занятий. Режимы работы с формами Существует несколько режимов работы с формами: режим формы, режим таб лицы, режим конструктора, режим сводной таблицы и режим сводной диаграм мы. Режим работы выбирается так же, как и при работе с запросами: либо с по мощью кнопки Вид (View) панели инструментов Конструктор форм (Form Design), либо с помощью соответствующей команды меню Вид (View) (рис. 8.1). Пользователи приложений Access работают с данными в базе данных в режиме формы: добавляют новые записи, удаляют или изменяют записи в таблице или запросе, являющемся источником данных для формы. По умолчанию форма от крывается из окна базы данных именно в этом режиме. На рис. 8.1 показана форма И з д а н и я, которую нам предстоит создать, открытая в режиме формы. Режимы работы с формами -.ш. 'Щl'~-: т Рис. 8.1. Форма Издания, открытая в режиме формы Название: Название Рис, 8.2. Форма Издания, открытая в режиме конструктор; 222 Занятие Э. Знакомство с формами и элементами управления В режиме конструктора можно разрабатывать формы, изменять их структуру, внешний вид, добавлять и удалять элементы управления. Этим режимом обыч но пользуются разработчики приложений Access, На рис. 8.2 показана та же форма Издания, открытая в режиме конструктора. Режим таблицы, так же как и режим формы, позволяет добавлять новые запи си, удалять и редактировать записи в таблице, являющейся источником данных для формы. Однако в этом режиме не применяются свойства форматирования элементов управления, которые, собственно, и дают преимущества формам как средству ввода и отображения данных. На рис. 8.3 показана форма Издания, от крытая в режиме таблицы. Как видите, она ничем не отличается от обычной таблицы. Режимы сводной таблицы и сводной диаграммы практически ничем не отлича ются от соответствующих режимов для таблицы или запроса и используются, естественно, для представления и анализа сводных данных. Ш Издания : Форма Философия Утешение философией" и другие трзктаты Творения преподобного Максима Исповедника Религия Творения преподобного Максима Исгюв&дника Религия Х Чтения и рассказы по истории России История ; Белая гвардия; Мастер и Маргарита Художественная литература :Федр Философия " Рубай.Художественная литература ^Медный всадник, Художественна я литература ХДрамы Драматургия ; Африка Художественная литература О началах Философия Счастливые родители _ Детская литература Мы ждет ребенка Детская литература i Евгений Онегин -Художественная литература } Прощай оружие Художественная литература ^Понедельник начинается в субботу Фантастика ! Современный..французский детективный.роман Детективы I Оправдание вечности Фантастика M ~ jJ-Mittj "Х ; -' Х JИ,i!J Рис. 8.3. Форма Издания, открытая в режиме таблицы Режим конструктора форм Для создания форм в Access есть несколько мастеров, которые облегчают этот процесс, однако то, что получается в результате работы мастера, как правило, является только первым приближением к желаемому результату. Формы для того и создаются, чтобы предоставить пользователю максимальное удобство. Их разработке уделяется особое внимание, и нужно поработать в режиме конст руктора, чтобы получить требуемый вид формы. Поэтому, прежде чем начинать Режим конструктора форм создавать формы для нашего приложения, посмотрим, что представляет собой конструктор форм, какие возможности имеются у разработчика форм спи Да1ШЫХ чтобы ' ПКУ K o H C T KTO ' нт PV P/Design) на панели инструментов окна базы дан Ф РМЫ единственнь Щ Р-здалсш - Область дан иых 3. Выберите в главном меню 1Access команду Вид > Заголовок/примечание фор, (View > Form H e a d e r F o o t e r 1. В макете появятся еще два раздела Form Header/Footer) 4. Выберите команду Вид > Колонтитулы (View > Page Header/Footer) Макет фоп отображав Рис. 8.4. Разделы формы Access Структура формы Разделы макета имеют следующее назначение: Х Заголовок формы (Form Header). Раздел заголовка добавляется в форму вместе с разделом примечания формы. В область заголовка можно поместить текст; Эта команда не отображается по умолчанию в меню View (Вид), поэтому нужно его t вернуть, подведя указатель мыши к двойной стрелочке на нижней кромке меню 224 Занятие 8. Знакомство с формами и элементами управления графику и другие элементы управления. Если форма состоит из нескольких страниц, заголовок отображается только на первой ее странице. Х Верхний колонтитул (Page Header). Раздел верхнего колонтитула страницы до бавляется в форму вместе с разделом, определяющим нижний колонтитул. При печати многостраничной формы верхний колонтитул воспроизводится вверху каждой страницы. Эти два раздела используются только при печати формы, поэтому они отображается только в том случае, когда форма откры та в режиме предварительного просмотра. Разделы Верхний колонтитул и Нижний колонтитул используются обычно не при создании форм, а при создании отчетов аналогичной структуры. Х Область данных (Detail). Область данных определяет основную часть формы, содержащую представляемые данные. Этот раздел может содержать элемен ты управления, отображающие данные таблиц и запросов, неизменяемые данные, например, надписи, данные, которые запрашиваются у пользователя и в дальнейшем обрабатываются программно. Х Н и ж н и й колонтитул (Page Footer). Раздел нижнего колонтитула страницы все гда добавляется в форму вместе с разделом, определяющим верхний колон титул. При печати многостраничной формы нижний колонтитул воспроиз водится в нижней части каждой страницы, Так же как и верхний колонтитул, он отображается, только когда форма открыта в режиме предварительного просмотра. Х Примечание формы (Form Footer). Раздел примечания добавляется в форму вместе с разделом заголовка и отображается в нижней части формы. При пе чати многостраничной формы примечание формы воспроизводится только внизу последней страницы. Из всех разделов обязательным является только Область д а н н ы х (Detail), кото рый создается по умолчанию. Остальные разделы могут быть добавлены с по мощью команд меню, как мы это сделали ранее. Если форма уже содержит эти разделы, соответствующая команда отмечается флажком (рис. 8.5). 5. Для удаления разделов заголовка и примечания снимите эти флажки. Высоту раздела формы можно изменить, перетаскивая нижнюю границу разде ла. Например, чтобы изменить высоту области данных, подведите указатель м ы ш и к нижней границе этого раздела так, чтобы он изменил форму (горизон тальная черта со стрелками), и переместите границу вверх (для уменьшения высоты) или вниз (для увеличения). Размер формы определяется ее правой и нижней границами. Правая граница макета формы позволяет задать ширину формы. Чтобы ее изменить, перетащи те правую границу макета формы вправо или влево. Нижняя граница макета формы позволяет определить ее высоту. Чтобы изменить высоту формы, пере тащите нижнюю границу макета формы вниз или вверх. На макете формы видны также вертикальная и горизонтальная линейки, кото рые предназначены для разметки формы и выделения нескольких соседних элементов управления. Режим конструктора форм Конструктор ^к Сводная таблица Сводная диаграмма J All+Enter | Object Dependencies j..,11 Список полей м/ Колокгигулы J Заголовок/примечание Формы J Область задач ! Панелиинструментов Рис. 8.5. Меню Вид в режиме конструктора форм Вертикальная и горизо? стальная полосы прокрутки позволяют просматри вать части формы, оказавшиеся за границами экрана в режиме Конструктора формы. Панели инструментов При работе в режиме конструктора форм используются три панели инстру ментов: Х Панель элементов (Toolbox) (рис. 8.6). Рис. 8.6. Панель элементов Эта панель предназначена для добавления в форму новых элементов управ ления. Наиболее часто применяемые элементы управления расположены прямо на панели. Остальные выводятся на экран при нажатии кнопки Дру гие элементы (More Controls) [$|Г. Кроме кнопок элементов управления, на панели элементов есть две специ альные кнопки; Выбор объектов (Select Objects), Мастера (Control Wizards). Кнопка Выбор объектов (Select Objects) автоматически отжимается, когда поль зователь выбирает один из элементов панели. Ее нужно снова нажать, если вы передумали размещать этот элемент. Нажатая кнопка Мастера (Control Wizards) обозначает, что при размещении элемента управления в форме за пустится соответствующий мастер. При отжатой кнопке мастера не запуска 8 Зак. 226 Занятие 8. Знакомство с формами и элементами управления ются. Мастер элементов позволяет создавать такие сложные элементы управ ления, как поля со списком, командные кнопки, группы элементов выбора." Практически для каждого сложного элемента управления существует свой мастер элементов. Их удобно использовать для обучения на начальном этапе работы с Access, а в дальнейшем они применяются только для выполнения стандартных операций. Панель элементов можно переместить по экрану в удоб ное место. Переместите панель элементов так, чтобы она оказалась прижатой к нижней ЗАДАНИЕ границе окна приложения (рис. 8.7). ЦЗ Библиотека Формат Сервис Окно Справка Рис. 8.7. Панель элементов, прижатая к нижней граниие окна приложения Панель элементов появляется на экране по умолчанию всякий раз при пере ходе в режим конструктора. Если ее закрыть, то в следующий раз она не появится. Чтобы отобразить ее снова, выберите команду Вид > Панели ин струментов (View > Toolbars), а затем установите флажок Панель элемен тов (Toolbox). Основные команды меню, используемые в режиме конструктора форм, про дублированы кнопками панели инструментов Конструктор форм (Form Design) Режим конструктора форм 22/ (рис. 8.8), с самыми полезными кнопками вы познакомитесь по мере их ис пользования в процессе настройки формы. Рис. 8.8. Панель инструментов Конструктор форм Панель инструментов Формат (форма/отчет) (Formatting (Form/Report)) (рис. 8.9) предназначена для форматирования выделенных элементов управления. Функции большинства элементов этой панели аналогичны функциям эле ментов, которые можно увидеть на панелях инструментов Microsoft Office Word 2003, предназначенных для форматирования текста, поэтому дополни тельных комментариев не требуется. Самый левый раскрывающийся список Объект (Object) отображает имя выделенного элемента управления формы и позволяет быстро выделить любой объект в форме, выбрав его имя в списке, Несколько кнопок справа позволяют задать цвета и стили оформления эле ментов управления и разделов формы. ПолеО Tahoma Рис. 8.9. Панель инструментов Формат (форма/отчет) Элементы управления формы На панели элементов размещаются элементы управления, с которыми мы бу дем знакомиться на этом занятии, чтобы иметь общее представление о тех ин струментах, которые есть у разработчика приложения Access. На следующих за нятиях мы уже будем пользоиаться этими инструментами, чтобы создать формы нашего приложения. Внешний вид и поведение элементов управления определяется их свойствами. Свойства элемента, а также свойства всей формы или ее раздела можно увидеть в диалоговом окне Свойства (Properties). Окно Свойства (Properties) появляется на экране при нажатии на панели инструментов Конструктор форм (Form Design) кнопки Свойства (Properties) [Щ или при выборе одноименной команды в кон текстном меню. Типы элементов управления Все элементы управления в Access делятся на три типа, различающиеся тем, как для них определяется источник данных: присоединенные, свободные и вы числяемые. Присоединенные элементы управления связаны с полями базовой таблицы, то есть той таблицы, которая является источником данных для формы. Если источ ником данных является запрос, то присоединенные элементы управления могут связываться с полями разных таблиц. В присоединенном элементе отображаются данные, содержащиеся в соответствующем поле таблицы, и при изменении дан 228 Занятие 8. Знакомство с формами и элементами управления ных в форме изменяется значение поля таблицы. В присоединенных элементах можно отображать все типы данных, которые существуют в Access. Свободные элементы управления с таблицами никак не связаны. Они предна значены либо для ввода информации, используемой потом в других целях (обычно макросами или программами VBA), либо для отображения объектов OLE, хранящихся в самих формах. Свободными элементами являются также все элементы, предназначенные для улучшения визуального восприятия форм, линии, прямоугольники, рисунки, надписи. Вычисляемые элементы управления Ч это такие элементы, значения которых рассчитываются на основе значений других элементов. В качестве источника данных для этих элементов служат выражения или функции. Элемент управления Поле Наиболее распространенными элементами управления являются текстовые по ля (Text Box), так как они служат как для ввода, так и для отображения данных. Длина текста, вводимого в текстовое поле, ограничена 65 535 символами. Тек стовые поля позволяют вводить произвольные значения (конечно, в соответ ствии с типом данных), однако можно ограничить множество допустимых зна чений с помощью масок ввода или правил проверки введенного значения. Обычно значения полей вводятся пользователем, однако их можно задать про граммно, присваивая значение свойству Текст (Text) этого элемента. Если в текстовом поле должен отображаться длинный текст, который не может поместиться в отведенную для этого поля область, в нем справа можно вывести полосу прокрутки. Для этого нужно установить значение свойства Полосы про крутки (ScroUBars) равным По вертикали (Vertical), хотя по умолчанию оно имеет значение Отсутствует (None). Значением текстового поля может быть гиперссылка. Для этого, если поле яв ляется присоединенным, нужно, чтобы соответствующее поле в базовой табли це или запросе имело тип данных Г и п е р с с ы л к а (Hyperlink). Если поле является свободным или вычисляемым, нужно установить значение свойства Гиперссыл ка (Is Hyperlink) равным Да (Yes). 1. Щелкните на панели элементов на кнопке Поле (Text Box) Указатель изменит форму. Щелкните на области данных формы. На форме появится элемент управления Поле (рис. 8.10). В диалоговом окне Свойства (Properties) отображаются свойства этого поля. Вы видите, что значение свойства Д а н н ы е (Control Source), определяющее источник данных для эле мента управления, не заполнено, значит, этот элемент управления является свободным. Когда вы размещаете поле на форме, одновременно с полем создается еще один элемент управления Ч Надпись (Label). Он выводится слева от поля и представляет собой значение свойства Подпись (Caption) или (если свойство Подпись не задано) свойства И м я поля (Name) связанного с ним поля таблицы и заканчивается двоеточием (:). Такой способ отображения принят по умол чанию, но его можно изменить. Режим конструктора форм ЩФорма1 : Форма.-:да, Нет,', Параметр базы данных Рис. 8.10. Элемент управления Поле и его свойства 2. Выделите на панели элементов кнопку Поле (Text Box). В диалоговом окне Свойства будут отображаться значения свойств текстового поля, которые устанавливаются по умолчанию, Ч стандартные свойства (рис. 8.11). б5[й"Поле" - станаартныв свойства ] Данные | События | Другие | Бг.е Гч.'Род H.II iqj.-iH. l\Всегда^ _.Отсутствуют l.I. ; ^ет""" \ Зсм Тип фона.,..,- Обычньй ; ; у топленное^ Тип границы. 9!У IcT?^eJ_v. 'Х EZ :Сверетонкая Цвет те*сга ^Tahoma Г6 ^ ^ 'обычный, Курсив.... jHej (ПоДчейкнут Ыет"Щ"" 1Да_^_ Подписи с даоето Позиция подписл Koj^I Позиция Подшей |0см шбычное Рис. 8.11. Значения по умолчанию свойств элемента управления Поле 230 Занятие 8. Знакомство г формами и элементами управления Два свойства, Добавление подписи (Auto Label) и Подписи с двоеточием (Add Colon), имеют значение Да (Yes). Изменив эти значения, вы можете избежать присоеди нения надписи к текстовому полю или добавления двоеточия в конце надписи. Книги, научные публикации