Включение полей в запрос

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

В самом начале списка полей, находящегося в верхней половине окна запроса (а также в раскрывающихся списках в первой строке бланка QBE), находится специальный символ «*», означающий «Все поля». Если необходимо включить в запрос все поля таблицы, не надо определять каждое по отдельности в бланке QBE. Достаточно перетащить «*» из списка полей в бланк QBE. Учтите, что вы можете повторно включить столбец поля таблицы в бланк запроса, чтобы определить условия отбора для них. В этом случае, чтобы поля дважды не выводились в наборе записей запроса снимите флажки Вывод на экран в столбцах бланка, содержащих повторяющиеся поля.

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

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

Ввод условий отбора

Ввод условия отбора в запросе аналогичен заданию условия на значение для поля таблицы. Когда вы вводите условия отбора для нескольких полей, то все выражения в строке Условие отбора или в строке или должны принимать значение Истина для любой записи, включаемой в набор записей запроса. Это означает, что Асcess выполняет логическую операцию AND над условиями отбора, находящимися в одной строке. В таблице приведены значения выражения, состоящего из двух условий, связанных оператором AND. Очевидно, чтобы результат операции AND  имел значение Истина, оба условия должны быть истинными; только в этом случае  запись отбирается запросом.

Когда вы задаете для некоторого поля несколько условий отбора, соединенных логическим оператором OR, то для того, чтобы запись была отобрана истинным должно быть хотя бы одно из них. Есть два способа задать несколько связанных оператором OR условий для одного поля. Можно ввести все условия в одну ячейку строки Условие отбора, соединив их оператором OR. Другой вариант: ввод каждого условия в отдельную ячейку строки или. При использовании нескольких строк или для отбора записи достаточно выполнения всех условий в одной из строк или.

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

Результат применения к двум условиям логических операций AND  и  OR

AND

Истина	Ложь
Истина	Истина (Отбирается)	Ложь (Отвергается)
Ложь	Ложь (Отвергается)	Ложь (Отвергается)

ФамилияТип данныхOR	Истина	Ложь
Истина	Истина (Отбирается)	Истина (Отбирается)
Ложь	Истина (Отбирается)	Ложь (Отвергается)

 

Исключить группу данных из состава анализируемых запросом записей позволяет следующий критерий

< > 4

В этом случае можно не использовать кавычки.

Оператор Between позволяет задать диапазон значений, например:

between 10 and 20

Оператор In позволяет задавать используемый для сравнения список значений. Например:

in (“первый”,”второй”,”третий”)

Оператор Like полезен для поиска образцов в текстовых полях, причем можно использовать шаблоны:

* — обозначает любое количество ( включая нулевой) символов;

? — любой одиночный символ;

# — указывает что в данной позиции должна быть цифра.

Например: для выбора фамилии, начинающейся с буквы П и с окончанием “ов” можно записать

like П*ов

Можно ввести дату и время, при этом значения должны быть заключены между символами #. Например:

#10 мая 1998# 

>#31.12.96#

В Access используется ряд других функций, которые помогут задать условия отбора для даты и времени, например:

Day(дата) – возвращает значение дня месяца в диапазоне от 1 до 31

Month(дата) – возвращает значение месяца года в диапазоне от 1 до 12

Year(дата) – возвращает значение года в диапазоне от 100 до 9999

Создать параметрический запрос. Запросы, представляющие собой варианты базового запроса и незначительно отличающиеся друг от друга, называются параметрическими. Результирующая таблица будет зависеть от того параметра, который пользователь введет при запуске запроса. На рис.5,а Представлен запрос, который позволяет выбрать из таблицы результатов сдачи экзамена группу студентов, которые сдали этот экзамен на ту или иную оценку. В поле Условие отбора в квадратных скобках указывается текст, который появляется в диалоговом окне при запуске запроса и приглашает ввести нужный параметр запроса рис.5,б. После ввода нужного значения нажмите ОК и перед Вами появится результирующая таблица рис.5,в. Очевидно, что если бы был введен другой параметр запроса (например оценка «2»), то список студентов был бы совершенно другой.

а)

б) в)

Рис.5. Параметрический запрос Балл.

Вычисляемые поля

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

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

+  – складывает два арифметических выражения.

-   – вычитает из первого арифметического выражения второе.

*  – перемножает два арифметических выражения.

/   – делит первое арифметическое выражение на второе.

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

^ – возводит первое арифметическое выражение в степень, задаваемую вторым арифметическим выражением.

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

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

На рис. 6 показан пример вычисляемого поля для таблицы тАттестат. Вычисляемое поле Среднее выводит средний балл по трем предметам: Русский, Математика, Физика.

Рис. 6. Запрос на основе таблицы тАттестат с вычисляемым полем Среднее

Итоговые запросы

Для вычисления итоговых значений надо нажать кнопку Групповые операции( ), чтобы в бланке QBE появилась строка Групповые операции. Access использует установку Группировка в строке Групповая операция для любого поля, занесенного в бланк запроса. Теперь записи по каждому полю группируются, но итог не подводится. Если выполнить запрос сейчас, вы получите набор записей, включающий по одной строке для каждого уникального значения поля запроса – но без итогов. Для получения итогов замените установку Группировка в строке Групповая операция на конкретные итоговые функции.

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

Итоговые функции Access:

Sum – вычисляет сумму всех значений заданного поля в каждой группе.

Avg – вычисляет среднее арифметическое всех значений данного поля в каждой группе.

Min – возвращает наименьшее значение, найденное в этом поле внутри каждой группы.

Max – возвращает наибольшее значение, найденное в этом поле внутри каждой группы.

Count  – возвращает число записей, в которых значения данного поля отличны от Null.

StDev – стандартное отклонение всех значений данного поля в каждой группе.

Var –вычисляет дисперсию значений данного поля в каждой группе.

First – возвращает первое значение этого поля в группе.

Last – возвращает последнее значение этого поля в группе.

Модификация данных с помощью запросов на изменение.

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

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

Запрос на Обновление используется для замены в отобранных записях существующих данных.

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

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

Формы

Формы предназначены для ввода, редактирования и просмотра табличных данных на экране в удобном виде.

Формы являются основным средством организации интерфейса пользователя в приложениях Microsoft Access. Формы могут применяться для  различных целей:

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

• Управление ходом выполнения приложения. Чтобы автоматизировать вывод определенных данных или выполнение некоторой последовательности действий, создаются формы для работы с макросами или процедурами Visual Basic для приложений. Для запуска макросов или процедур VBA обычно используются элементы управления, называемые командными кнопками. С помощью макросов и процедур можно открывать другие формы, выполнять запросы или команды меню, фильтровать выводимые на экран данные, устанавливать значения в записях и формах, выводить меню на экран, печатать отчеты и выполнять ряд других действий. Можно так спроектировать форму, чтобы макросы или функции Visual Basic для приложений запускались в ответ на определенные события – например, открытие формы, активизацию определенного элемента управления или изменение данных в форме.

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

• Вывод сообщений. Формы могут предоставлять вспомогательную информацию о работе приложения или о выполняемых действиях. С помощью макрокоманды Сообщение или функции MsgBox Visual Basic для приложений можно вывести на экран информацию, предупреждение или сообщение об ошибках.

• Печать информации. Несмотря на то, что для печати информации чаще всего используются отчеты, можно распечатать информацию, содержащуюся в форме. Поскольку для вывода формы на экран можно определить один набор параметров, а для печати – другой, форма может играть двойственную роль. Например, можно задать два заголовка и два примечания для формы – одну пару (заголовок и примечание) для ввода заказа, а другую – для печати  счета по заказу

• Отчеты

• Отчеты являются выходными документами, предназначенными для вывода на принтер.

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

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

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

4. Макросы используются для автоматизации различных процедур обработки данных, являются программами, состоящими из макрокоманд высокого уровня. Макропрограммирование в Access не требует знания языка VisualBasic. Имеющийся в Access набор из около 60 макрокоманд обеспечивает практически любые действия, необходимые для решения задач.

5. Модули являются программами на языке, которые служат для реализации нестандартных процедур обработки данных.

6. Страницы доступа к данным – это WEB- страницы, обеспечивающие функциональность стандартных форм и отчетов Access: ввод, редактирование и представление данных.

7. Все данные БД Microsoft Access и средства их отображения хранятся в одном файле с расширением MDB.

9. Задание 1. Ознакомиться с учебной базой данных компании «Борей»

10. Технология

11. 1. Загрузить Microsoft Access 2003.

12. 2. В меню Справка выбрать пункт «Примеры баз данных…», далее – «Учебная база данных «Борей».

13. 5. Закрыть главную кнопочную форму.

14. 6. Просмотреть данные каждой таблицы, открыв их.

15. 7. Просмотреть структуру каждой таблицы в режиме конструктора. Обратить внимание на типы и свойства полей.

16. 8. Открыть таблицу «Клиенты». Выполнить следующие операции:

17. - определить количество записей в таблице;

18. - просмотреть 45-ую запись, введя ее номер в окно номеров записей

19. - рассортировать таблицу по должностям. Для этого установить указатель мыши на заголовок столбца и щелкнуть правой кнопкой мыши. Столбец будет выделен и появится контекстное меню. Выбрать в контекстном меню пункт «Сортировка по возрастанию».

20. - скрыть столбец «Обращаться к», выделив его и выполнив команду ФОРМАТ/ Скрыть. Отобразить скрытый столбец;

21. - выделить первые два столбца и закрепите их, выполнив команду ФОРМАТ/Закрепить. Прокрутить таблицу по горизонтали. Отменить закрепление;

22. - применить фильтр для выделения строк с клиентами в г. Лондон. Для этого выделить в любой строке поле со значением «Лондон» и вызвать контекстное меню. Выбрать пункт «Фильтр по выделенному». Отменить фильтр, щелкнув в контекстном меню по пункту «Удалить фильтр»;

23. - изменить вид сетки таблицы, используя соответствующую кнопку инструментальной панели «Таблица», если такой кнопки нет, то ее необходимо ввести.

24. 9. Просмотреть запросы и их структуру

25. - открыть запрос на выборку товаров с ценой выше средней;

26. - переключить запрос в режим конструктора и просмотреть структуру запроса.

27. 10. Просмотреть формы:

28. - «Сотрудники», вкладки «Служебные данные», «Личные данные»;

29. - «Товары»;

30. - «Типы»;

31. 11. Просмотреть форму «Сотрудники» в режиме конструктора.

32. 12. Просмотреть отчеты:

33. - «Каталог»;

34. - «Продажи по типам»;

35. - «Суммы продаж по годам»;

36. - «Счет»;

37. «Продажи по сотрудникам и странам», введя дату начала: 01.01.1995 и дату окончания: 31.12.1995

38. 13. Просмотреть отчет «Каталог» в режиме конструктора.

41. Часть 2. Создание базы данных в СУБД MS ACCESS

43. Задание 2. Создать в СУБД MS ACCESS базу данных «Анкетирование сотрудников».

45. Технология:

46. Этап 1. Создание файла базы данных

47. 1.1. Осуществите запуск программы MS Access.

48. 1.2. Создайте файл базы данных и сохраните его с именем Анкетирование сотрудников.mdb.

50. Этап 2. Создание таблиц. Работа с записями в таблице.

51. 2.1. Создайте новую таблицу в режиме конструктора.

52. 2.2. Добавьте в таблицу следующие поля:

54. Имя поля

Текстовый

Имя

Текстовый

Отчество

Текстовый

Должность

Текстовый

Год рождения

Числовой

Размер дохода

Денежный

Примечания.

Графу «Описание» можно не заполнять.

В окне «Свойства поля» устанавливаются необходимые значения полей.

2.3. Сохраните изменения в файле. Дайте имя таблице «Анкета». На вопрос «Создать ключевое поле сейчас?» ответьте «Да».

2.4. Заполните таблицу конкретными данными (для этого используйте команду «Открыть»).

Фамилия	Имя	Отчество	Должность	Год рождения	Размер дохода
Иванов	Олег	Юрьевич	Директор	1964	25000
Петрова	Софья	Павловна	Гл. бухгалтер	1970	20000
Сидоров	Иван	Викторович	Зам. директора	1968	20500
Васильева	Елена	Сергеевна	Ст. экономист	1975	12000
Гришина	Ольга	Алексеевна	Референт	1972	10000
Тарасов	Иван	Игоревич	Комендант	1970	7000
Немов	Сергей	Петрович	Вахтер	1946	3000
Бирюков	Вадим	Данилович	Мл. бухгалтер	1985	5000

Примечания.

Ключевое поле можно не заполнять.

2.5 Закройте файл и сохраните изменения.

2.6 Произведите сортировку таблицу по какому-либо полю (выделяете нужный столбец и сортируете).

2.7. Измените структуру базы данных. Перед полем «Год рождения» вставьте поле «Телефон», тип – текстовый. Используйте контекстное меню.

2.8. Заполните номера телефонов.

2.9. Осуществите поиск по какому- либо признаку (например, по фамилии). Для этого в открытой таблице выделяете столбец поиска и подаете команду Правка/Найти.

2.10. Произведите поиск данных с помощью фильтра. (Открыть таблицу, затем использовать команды: Записи/Фильтр/Изменить фильтр, Записи/Фильтр/Применить фильтр, Записи/Фильтр/Удалить фильтр).

Этап 3. Создание запросов

3.1. Перейдите к объекту Запросы.

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

3.2 Выберите режим создания запроса с помощью конструктора.

3.3 Добавьте таблицу «Анкета», на основе которой будет строиться запрос.

3.4 В нижней части окна «Запрос на выборку» ввести в 1-ю колонку строки «Поле» слово «Фамилия» (или выбрать из списка или перетащить в режиме drag&drop поле «фамилия» из общего списка полей в верхней части окна), а во 2-ю колонку – слово «Год рождения».

3.5 Сохраните запрос под именем «Год рождения сотрудников»

3.6. Просмотрите запрос (команда «Открыть»).

Создание Запроса 2: Требуется составить выборку из таблицы, содержащую фамилии тех сотрудников, которые родились позже 1950 г. и имеют доход менее 13000 руб. Представьте данные в порядке увеличения дохода.

3.7 Выберите режим создания запроса с помощью конструктора.

3.8 Добавьте таблицу, на основе которой будет строиться запрос.

3.9 Добавьте в бланк запроса поля «Фамилия», «Год рождения» и «Размер дохода».

3.10 В строке «Условие отбора» для поля «Год рождения» укажите условие >1950, а для поля «Размер дохода» – условие <13000

3.11 В строке «Сортировка» выберите режим сортировки по возрастанию.

3.12 Сохраните запрос под именем «Сотруднике старше 1950 с доходом меньше 13000»

3.13. Просмотрите созданный запрос.

Создание Запроса 3 Требуется создать запрос «Подсчет числа респондентов», вычисляющий число сотрудников, участвовавших в анкетировании.

3.14 Выберите режим создания запроса с помощью конструктора.

3.15 Добавьте таблицу, на основе которой будет строиться запрос.

3.16 Добавьте в бланк запроса поле «Фамилия».

3.17 Для вычисления итоговых значений надо нажать кнопку Групповые операции ( ), чтобы в бланке запроса появилась строка Групповые операции.

3.18 В списке групповых операций выберите функцию Count .

3.19 Сохраните и просмотрите запрос.

Этап 4. Разработка форм

4.1. Перейдите к объекту Формы.

4.2. Создайте форму для заполнения таблицы «Анкета» с помощью мастера форм (с порядком создания формы разберитесь самостоятельно).

4.3 С помощью созданной формы добавьте новую запись в базу данных.

Этап 5. Создание отчетов

5.1 Создайте отчет «Результаты анкетирования» с помощью мастера отчетов. Для отчета используйте поля «Фамилия», «Должность», «Размер дохода».

5.2 Просмотрите созданный отчет.