Контрольное упражнение

Access автоматически обновляет файл базы данных. Чтобы сохранить его исходный вариант, создайте копию базы данных -.

1. Откройте таблицу Контакты файла Контакты_копия.mdb.

2. Измените тип данных поля Адрес на поле МЕМО.

3. Ограничьте длину текстового поля Имя величиной 30 символов.

4. Поле Фамилии является ключевым. Запретите Access добавлять в таблицу Контакты записи с пустым полем Фамилия.

5. Сохраните структуру таблицы.

6. Создайте новую таблицу с именем Метод.

7. Добавьте в нее поле МетодID с типом данных Счетчик и поле Вариант с типом Текстовой. Первое из них сделайте ключом таблицы.

8. Введите в таблицу три записи, в поле Метод которых введите слова Телефон, Факс, Письмо. Эта таблица будет хранить возможные способы контакта.

9. Добавьте в таблицу Список поле МетодID с числовым типом.

10. С помощью Мастера подстановки назначьте этому полю подстановку, состоящую из полей МетодID и Вариант таблицы Метод.

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

12. Закройте базу данных.

Вопросы для самоконтроля

1. Укажите назначение баз данных.

2. Какие объекты представлены в окне базы данных?

3. Чем определяется состав полей таблицы?

4. Какой параметр определяет длину поля?

5. Как сделать, чтобы в список Подстановки кроме названия метода выводился его кодовый номер?

6. В каком режиме наиболее полно определяются параметры структуры таблицы?

7. На что влияет свойство “Формат поля”?

8. Для чего служат индексы таблицы?

9. Возможно ли выполнение сортировки записей по нескольким полям?

10. Может ли ключ иметь повторяющиеся значения?

11. В каком режиме осуществляется ввод данных в таблицу?

Лабораторная работа №2 подбор требуемых данных

Цель и содержание работы: познакомиться со способами фильтрации данных; научиться сортировать данные, выбирать их на основе введенных условий, выполнять вычисления.

Теоретическое обоснование

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

Аппаратура и материалы. Для выполнения лабораторной работы необходим ПК со следующими характеристиками: процессор Intel с тактовой частотой 800 МГц и выше, оперативная память - не менее 64 Мбайт, свободное дисковое пространство - не менее 500 Мбайт, монитор типа Super VGA (число цветов - 256) с диагональю не менее 15 ². Программное обеспечение – операционная система WINDOWS 98/ NT/ ME / 2000 / XP.

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

Методика и порядок выполнения работы

Запросы

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

Упражнение 1. Мастер запросов

1. В окне базы данных щелкните на кнопке Запросы и дважды щелкните на значке Создание запроса с помощью мастера.

1. В первом окне мастера запросов добавьте в список Выбранные поля поля Дата и Описание таблицы Список, а также поле Фамилия таблицы Контакты.

2. Щелкните на кнопке Далее.

3. Введите имя запроса Перечень контактов и щелкните на кнопке Готово.

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

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

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

Рисунок 2.1 - Результат выполнения запроса

Упражнение 2. Конструктор запросов

Конструктор запросов обеспечивает полное управление параметрами запроса и построение сложных условий отбора данных.

1. Выберите команду Вид ► Конструктор. Окно конструктора показано на рис. 2.2. В его верхней части отображаются списки полей таблиц, к которым обращается запрос, и связи между таблицами. Нижняя область содержит бланк выбора полей таблиц, условий отбора и режимов сортировки.

2. Перетащите пункт Имя таблицы Контакты в верхнюю ячейку четвертой строки бланка запроса. Его имя появится в этой ячейке, а имя соответствующей таблицы — во второй ячейке того же столбца.

Рисунок 2.2 - Конструктор запроса

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

4. Назначьте тот же режим сортировки для поля Имя. Теперь записи результата запроса будут упорядочены по алфавиту фамилий и имен. Имеющийся вариант связи добавляет в результат запроса только те записи связанных таблиц, в которых значения полей Код_Контакты равны. Так как для некоторых людей из таблицы Контакты нет записей в таблице Список, то информация о таких людях не включается в результат запроса. Изменим параметры объединения.

5. Дважды щелкните на линии связи.

6. В открывшемся окне диалога Параметры объединения выберите положение переключателя Объединение всех записей из "Контакты" и только тех записей из "Список", в которых связанные поля совпадают, как показано на рисунке 2.3.

7. Щелкните на кнопке ОК. На одном конце линии связи появится стрелка, указывающая на смену режима объединения.

Рисунок 2.3 - Параметры объединения

8. Щелчком на кнопке Вид панели инструментов запустите запрос повторно. Результат выполнения запроса с учетом сортировки и нового режима объединения будет таким, как показано на рисунке 2.4.

9. Закройте окно, сохранив новую структуру запроса.

Рисунок 2.4 - Результат выполнения нового запроса

Упражнение 3. Условие отбора

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

1. Откройте запрос Перечень контактов в режиме конструктора.

2. В бланке запроса щелкните на ячейке Условие отбора первого столбца правой кнопкой мыши и выберите в контекстном меню команду Построить. Откроется окно построителя выражений (рис. 2.5).

3. В левом списке построителя щелкните на папке Операторы.

Рисунок 2.5 - Построитель выражений

2. В среднем списке выберите категорию Сравнения.

3. В правом списке дважды щелкните на пункте Between.

2. Щелчком выделите в поле формулы первый местозаполнитель «Выражение».

2. В левом списке построителя выражений двойным щелчком откройте папку Функции и щелкните на папке Встроенные функции.

8. В среднем списке построителя выражений щелкните на пункте Дата/время.

9. В правом списке дважды щелкните на функции DateValue, чтобы заменить ею местозаполнитель «Выражение».

10. Выделите местозаполнитель «stringexpr» и введите "1.12.99".

11. Повторяя шаги 6-10, замените второй местозаполнитель «Выражение» на выражение DateValue ("31.12.99").

Формула Between DateValue ("1.12.99") And DateValue ("31.12.99") отбирает те записи, значение поля Дата которых относится к декабрю 1999 г.

12. Щелкните на кнопке ОК. Построенная формула появится в ячейке Условие отбора первого столбца бланка запроса.

13. Сбросьте флажок Вывод на экран первого столбца запроса.

14. Щелчком па кнопке Запуск панели инструментов выполните запрос. Новый результат показан на рисунке. 2.6.

Рисунок 2.6 - Контакты за декабрь 1999 г.

Упражнение 4. Итоговый запрос

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

1. В окне базы данных щелкните на кнопке Запросы.

1. Дважды щелкните на значке Создание запроса в режиме конструктора.

2. В открывшемся окне диалога выделите строку Контакты и щелчком на кнопке Добавить добавьте выбранную таблицу в верхнюю область конструктора запроса.

3. Выделите пункт Список и снова щелкните на кнопке Добавить.

4. Закройте окно диалога Добавление таблицы.

6. Щелкните на кнопке Групповые операции панели инструментов.

7. Перетащите поля Фамилия и Имя таблицы Контакты в ячейки Поле первого и второго столбцов конструктора.

8. В третий, четвертый и пятый столбцы бланка запроса перетащите поле Дата таблицы Список (рис. 2.7).

Рисунок 2.7 - Запрос с групповыми операциями

9. В раскрывающемся списке ячейки Групповая операция третьего столбца бланка запроса выберите пункт Min.

10. В той же ячейке четвертого столбца выберите пункт Мах.

11. В пятом столбце задайте групповую операцию Count. Групповые операции построенного запроса обработают все записи таблицы Список, соответствующие конкретному человеку из таблицы Контакты, и вместо самих данных таблицы Список выведут в соответствующее поле результата запроса только значение величины, вычисляемой по определенной формуле. Доступные групповые операции перечислены в табл. 2.1.

12. Щелкните на кнопке Вид, чтобы выполнить запрос. Появится таблица с пятью столбцами. Два первых столбца содержат фамилии и имена людей. Третий и четвертый столбцы запроса выводят соответственно дату первого и последнего контакта с данным человеком. Пятый столбец содержит число контактов, соответствующих данному человеку. Единственный недостаток построенного запроса — это непонятные имена столбцов.

13. Щелчком на кнопке Вид вернитесь в конструктор запроса.

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

Таблица 2.1 - Групповые операции

Название	Функция
Sum	Сумма значений поля по всем записям
Avg	Среднее значение поля
Min	Минимальное значение
Мах	Максимальное значение
Count	Количество записей, соответствующее поле которых не содержит величины Null
StDev	Стандартное отклонение величин поля от среднего
Var	Вариация значений поля
First	Первое значение в группе
Last	Последнее значение в группе
Group By	Поле, определяющее группу записей, по которой вычисляются статистические параметры. К одной группе относятся все записи, для которых значения поля с режимом Группировка одинаковы
Выражение	Вычисляемое поло, значение которого рассчитывается по сложной формуле
Условие	Режим задания условия отбора для поля, по которому не выполняется группировка. Access автоматически делает такое поле скрытым

13. В ячейке Поле четвертого столбца введите Дата последнего контакта: Дата.

14. В первой строке пятого столбца бланка запроса введите Число контактов: Дата.

17. Снова щелкните на кнопке Вид. Окончательный результат запроса показан на рисунке 2.8.

18. Закройте запрос, сохранив изменения структуры.

18. В окне диалога Сохранение введите имя Итоговый запрос и щелкните на кнопке ОК.

Рисунок 2.8 - Запрос с итоговыми значениями

Упражнение 5. Запрос действия

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

1. В окне базы данных щелкните на кнопке Таблицы.

2. Выделите таблицу Список, данные которой нужно обновить.

3. В палитре кнопки Новый объект панели инструментов выберите пункт Запрос. Откроется окно диалога Новый запрос.

4. Дважды щелкните на строке Конструктор. Таблица Список автоматически появится в окне конструктора запросов.

5. Перетащите в бланк запроса поле Дата, значение которого нужно обновлять.

6. В палитре кнопки Тип запроса выберите пункт Обновление. Описание всех возможных вариантов запросов, предлагаемых программой Access, приведено в табл. 2.2, Структура бланка запроса видоизменяется в соответствии с типом запроса. В бланке выбранного варианта запроса на обновление появляется поле Обновление, в которое нужно ввести новое значение поля. Чтобы изменить месяц даты с ноября на декабрь, достаточно прибавить к дате 30 дней.

7. Введите в ячейку Обновление формулу [Дата]+30.

8. Чтобы обновлялись только даты, относящиеся к ноябрю 2000 года, введите в ячейку Условие отбора формулу Between DateValue ("1.11.99") And Date Value ("30.11.99") (см. рис. 2.9).

8. Закройте запрос, сохранив его под именем Обновление.

10. Откройте таблицу Список. В ней есть три записи, относящиеся к ноябрю 1999 года.

Таблица 2.2 - Варианты запросов

Тип	Описание
Выборка	Выборка данных в таблицу результата запроса на основе указанных условий отбора
Перекрестный	Результат запроса выводит статистические значения (сумму, количество или среднее) для одного из полей таблицы в зависимости от двух параметров других полей таблицы, задающих заголовки строк и столбцов результата перекрестного запроса
Создание таблицы	Создание новой таблицы в текущей или в другой базе данных на базе информации из имеющихся таблиц
Обновление	Обновление данных таблицы
Добавление	Добавление набора записей в таблицу
Удаление	Удаление записей таблицы в соответствии с указанным критерием

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

Рисунок 2.9 - Запрос на обновление

10. В окне базы данных щелкните на кнопке Запросы.

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

12. Ответьте Да на вопрос о необходимости запуска запроса действия.

14. Щелкните на кнопке Да и изучите изменения, произошедшие с данными таблицы Список.

Фильтры

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

Упражнение 6. Установка фильтра

1. Откройте таблицу Контакты.

2. В столбце Фамилия и выделите ячейку с фамилией Петров.

2. Щелкните на кнопке Фильтр по выделенному. Останутся только те записи, в поле Фамилия которых присутствует значение Петров.

4. Щелкните на кнопке Удалить фильтр.

5. Выберите команду Записи ► Фильтр ► Расширенный фильтр. Откроется окно, похожее на конструктор запроса. Критерий равенства поля Фамилия значению Петров уже будет присутствовать в бланке фильтра.

6. Перетащите поле Рабочий Телефон во второй столбец бланка.

7. В ячейку Условие отбора введите формулу <>Null (рис. 2.10), которая отбирает все непустые поля.

8. Выберите команду Фильтр ► Применить фильтр. В результате таблице Контакты останутся только две записи.

9. С помощью команды Записи ► Удалить фильтр отмените фильтрацию.

Рисунок 2.10 - Окно расширенного фильтра

Упражнение 7. Преобразование в запрос

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

1. Командой Записи ► Фильтр ► Расширенный фильтр активизируйте окно сложного фильтра, созданного в предыдущем упражнении.

2. Выполните команду Файл ► Сохранить как запрос.

3. В открывшемся окне диалога введите имя Фильтр и щелкните на кнопке ОК.

4. Закройте окно фильтра.

5. В окне базы данных появился новый запрос с названием Фильтр. Дважды щелкните на значке этого запроса.

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

6. Активизируйте окно таблицы Контакты.

7. Щелкните на ячейке с фамилией Петров правой кнопкой мыши и выберите в контекстном меню команду Исключить выделенное. В таблице останутся только те записи, в поле Фамилия которых нет слова Петров.

8. Щелчком на кнопке Удалить фильтр отмените фильтрацию данных. Теперь давайте вернем прежний сложный фильтр.

9. Выберите команду Записи ► Фильтр ► Расширенный фильтр.

10. Выберите команду Файл ► Загрузить из запроса.

11. В открывшемся окне диалога выделите запрос Фильтр и щелкните на кнопке ОК.

Старые условия фильтрации появятся в бланке окна фильтра. Теперь для применения сохраненного фильтра достаточно щелкнуть на кнопке Применение фильтра панели инструментов.

Контрольное упражнение

1. Запустите мастер запросов.

2. Добавьте в запрос поля Фамилия и Адрес таблицы Контакты, а затем поля Дата и Описание таблицы Список.

3. Настройте итоговый запрос, подсчитывающий количество записей.

4. Переключитесь в режим конструктора.

5. Скройте третье и четвёртое поля запроса.

6. Закройте запрос, сохранив его под именем Количество записей.

7. Откройте таблицу список.

8. Включите фильтр, оставляющий только записи, относящиеся к фамилии Леонидов.

9. Откройте окно сложного фильтра.

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

11. Назначьте обновленный фильтр.

12. Сохраните настроенный фильтр в виде запроса.

13.Откройте запрос Обновление в конструкторе запросов.

14. Просмотрите его в режиме SQL.

15. Закройте базу данных.