Имя вычисляемого поля: Выражение для построения вычисляемого поля

Если при создании вычисляемого поля пользователь не указывает имя, то Access по умолчанию присвоит ему имя Выражение1. Имя для вычисляемого поля рекомендуется задавать по двум причинам: во-первых, для заголовка столбца таблицы, содержащей динамический набор запроса, и, во-вторых, для обращения к этому полю в форме, отчете или другом запросе.

Рассмотрим пример. Требуется вывести список читателей в алфавитном порядке, содержащий фамилии, инициалы и домашний адрес. Поскольку поля с инициалами читателей ни в одной таблице базы данных Библиотека нет, нам потребуется создать вычисляемое поле для выделения инициалов из имени и отчества читателей. Вычисляемое поле назовем Фамилия и инициалы. Запрос сохраним под именем Список читателей с инициалами.

Вид бланка запроса для вывода списка читателей с фамилиями и инициалами представлен на рис. 1.

 

Рис. 1. Бланк запроса для вывода списка читателей.

 

Выражение в вычисляемом поле, приведенном на рис. 1, является текстовым. Оператор конъюнкция ("&") в нем используется для сцепления строк, так, например, выражение [Фамилия]&" " сцепляет значение поля Фамилия с пробелом. Функция Left$([Имя];1) в данном контексте используется для выделения одной левой буквы из значения поля Имя. Динамический набор записей в результате выполнения данного запроса будет иметь вид, представленный на рис. 2.

 

Рис. 2. Динамический набор запроса.

Убедитесь в том, что в рассмотренном примере вместо функции Left$([Имя];1) мы бы могли воспользоваться более общей функцией Mid$([Имя];1;1). Функция Mid$([Имя поля];n;m) позволяет из указанного поля выделить подряд расположенных m символов, начиная с номера n. Более того, тот же результат получился бы, если мы в названиях используемых функций убрали бы символ доллара ("$").

Задание

 

1. Создайте вычисляемое поле для вычисления новой цены книг. Новая цена должна определяться умножением значения поля Стоимость на величину 1,1. Вычисляемое поле назовите Новая цена. Для этого поля установите денежный формат. В динамический набор запроса включите следующие поля: Автор, Название, Год издания, Новая цена. Запрос сохраните под именем Стоимость книг с учетом инфляции.

2. Создайте вычисляемое поле для вычисления цены книг в условных единицах. Курс доллара примите равным 2165 белорусских рублей. В динамический набор включите те же поля, что и в предыдущем примере, но вычисляемое поле назовите Цена в у_е. Для вычисляемого поля в его свойствах задайте пользовательский формат, позволяющий отображать информацию так, как это показано на рис. 3.

 

Рис. 3. Форматирование вычисляемого поля.

 

Получение указанного результата могут обеспечить свойства вычисляемого поля, приведенные на рис. 4. Запрос сохраните под именем Стоимость книг в условных единицах.

 

Рис. 4. Свойства вычисляемого поля.

3. Подсчитайте, сколько книг заказывал каждый читатель за весь период использования абонемента библиотеки. В динамический набор включите вычисляемое поле Фамилия и инициалы и новое поле Количество, значения которого вычисляются в результате применения групповой операции Count над полем Код книги из таблицы Выдача книг (см. рис. 5).

 

Рис. 5. Использование групповых операций для вычислений.

 

Для поля Фамилия и инициалы в строке Групповая операция бланка запроса выберите операцию Группировка, а в строке Сортировка выберите направление сортировки: по возрастанию. Запрос сохраните под именем Количество прочитанных книг.

4. По аналогии с предыдущим заданием, используя групповые операции, создайте запрос, который позволит получить информацию о том, сколько раз заказывали каждую книгу. В динамический набор запроса включите поля Автор, Название из таблицы Книги и новое поле Рейтинг, значение которого указывает, сколько раз заказывалась данная книга (см. рис. 6).

 

Рис. 6. Результат выполнения запроса Рейтинг книг.

Книги, которые не заказывались, в динамический набор запроса включать не надо. Запрос сохраните под именем Рейтинг книг.

Перекрестный запрос

Лабораторная работа № 7

Цель работы: Сформировать умения для создания перекрестных запросов.

 

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

Проще всего создать перекрестный запрос с помощью мастера по разработке перекрестных запросов. При необходимости перекрестный запрос можно создать без помощи мастера. Рассмотрим два этих способа создания перекрестного запроса.

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

– Находясь в окне базы данных, выбирают корешок Запрос и нажимают кнопку Создать.

– В диалоговом окне Создание запроса нажимают кнопку Мастера по разработке запросов.

– В первом окне Мастера по разработке запросов выбирают пункт Перекрестный запрос.

– Далее выполняют инструкции, появляющиеся в диалоговых окнах. В последнем диалоговом окне нажимают кнопку Готово.

Для создания перекрестного запроса без помощи мастера надо выполнить следующую последовательность действий:

1. В окне базы данных выберите корешок Запрос и нажмите кнопку Создать. В появившемся диалоговом окне Создание запроса нажмите кнопку Новый запрос.

2. Выберите таблицы или запросы, содержащие поля, которые следует включить в запрос. Перенесите нужные поля в строку Поле бланка QBE и задайте условия отбора.

3. Выберите команду Перекрестный в меню Запрос или нажмите кнопку Перекрестный на панели инструментов. В бланке QBE появятся строки Групповые операции и перекрестная таблица. По умолчанию ячейки Групповые операции, соответствующие каждому полю, включенному в запрос, будут содержать надпись Группировка.

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

5. Установите указатель в ячейку Перекрестная таблица, соответствующую полю, которое содержит заголовки столбцов, и нажмите кнопку мыши, а затем нажмите кнопку раскрытия списка и выберите строку Заголовки столбцов. Только одно поле в перекрестном запросе может содержать заголовки столбцов. Ячейка Групповые операции, соответствующая этому полю, должна содержать надпись Группировка.

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

7. Установите указатель в ячейку Групповые операции, соответствующую полю, которое содержит значения для вычислений, и нажмите кнопку мыши, а затем нажмите кнопку раскрытия списка и выберите тип групповой операции (например, Sum, Max или Count). Поле, которое содержит значения для вычислений, нельзя использовать для группирования; оно должно содержать результат выполнения групповой операции.

8. Для вывода на экран полученного набора записей выберите команду Таблица в меню Вид или нажмите кнопку Режим таблицы на панели инструментов.

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

 

Рис. 1. Окно в режиме конструктора для запроса Заказы книг по годам.

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

Для решения этого примера нам понадобятся две таблицы из базы данных Библиотека: Читатели и Выдача книг. Разместите списки этих таблиц в верхней части окна запроса, как это сделано на рис. 1.

В бланке QBE окна запроса надо заполнить четыре столбца. Переместите нужные поля из верхней части окна запроса в строку Поле бланка QBE. Первые два столбца должны определять заголовки строк перекрестной таблицы: Фамилия и Итого. Для того чтобы появились в бланке QBE строки Групповая операция и Перекрестная таблица, надо выполнить команду Перекрестная в меню Запрос или нажать кнопку Перекрестный на панели инструментов. После этого заполните строки Групповая операция и Перекрестная таблица. Для первого столбца укажите групповую операцию Группировка, а для второго – Count.

Третий столбец должен определять заголовки столбцов перекрестной таблицы. В нашем примере в качестве заголовков столбцов должны быть выбраны годы из поля Дата заказа таблицы Выдача книг. Для того чтобы выбрать элемент год из значений Дата заказа можно использовать функцию Format. В четвертом столбце бланка QBE надо указать, какие данные будут выведены в перекрестной таблице под заголовками столбцов. С этой целью указано вычисляемое значение, которое определяется функцией Count.

Набор данных, созданный в результате выполнения перекрестного запроса, приведен на рис. 2. Сохраните его под именем Заказы книг по годам.

Рис. 2. Результат выполнения запроса Заказы книг по годам.

 

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

Вид бланка QBE для решения примера приведен на рис. 3. Первые два столбца этого бланка определяют заголовки строк для результирующей таблицы запроса. Третий столбец используется для задания заголовков столбцов. Как и в предыдущем примере для выбора месяца из поля Дата заказа используется функция Format. Поскольку данный столбец используется для группирования записей, в строке Групповая операция бланка QBE указана операция Группировка. Последний столбец указывает, что для определения количества выдач книг с конкретным кодом книги используется групповая операция Count и в требуемой таблице в качестве значений ячеек, расположенных на пересечении строки с кодом книги и столбца с названием месяца, будет располагаться значение этой операции.

 

Рис. 3. Вид бланка QBE для запроса Выдача книг по месяцам.

 

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

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

Определим постоянные заголовки столбцов для нашего примера. Будем считать, что к настоящему времени бланк QBE имеет такой вид, как на рис. 3. Выделите поле Выражение1 (напомним, что оно используется для задания заголовков столбцов) и нажмите кнопку Свойства. После этого на экране появится бланк свойства запроса. В ячейку Заголовки столбцов введите названия месяцев, которые надо использовать в качестве заголовков столбцов (см. рис. 4).

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

Рис. 4. Вид свойства запроса для постоянных заголовков столбцов.

Для вывода на экран полученного набора записей надо выполнить команду Таблица в меню Вид или нажать кнопку Режим таблицы на панели инструментов. Выполните одно из указанных действий, и вы увидите на экране набор записей примерно в таком виде, в каком он приведен на рис. 5. Сохраните этот запрос под именем Выдача книг по месяцам.

Рис. 5. Результат выполнения запроса с постоянными заголовками столбцов.

 

Задание

 

1. Измените решение последнего примера так, чтобы перекрестная таблица содержала информацию о выдаче книг не за весь период работы библиотеки, а только за 2007 год. Полученный запрос назовите Заказы книг в 2007 году.

2. Создайте в электронной таблице MS Excel таблицу телефонных звонков, приведенную ниже.

Фамилия абонента	Город	Телефон	Количество минут
Голубев В.И.	Витебск	20-34-85	5
Василевская И.Л.	Гродно	36-67-91	3
Петрович А.А.	Гомель	44-34-29	8
Мухин П.И.	Брест	34-76-89	5
Петрович А.А.	Могилев	87-45-90	2
Мухин П.И.	Гомель	23-87-46	5
Голубев В.И.	Гродно	68-75-84	4
Мухин П.И.	Брест	65-78-34	6
Василевская И.Л.	Гродно	54-90-28	2

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