Сложные запросы в аксесс

Сложные запросы в аксесс

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

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

1. Откройте базу данных «Фирма», созданную заранее.

2. Выполните команду: вкладка ленты СозданиеМастер запросов – Простой запрос.

3. В появившемся диалоговом окне (рисунок 3) укажите таблицу Сотрудники и выберите поля Фамилия, Имя, Телефон. Нажмите кнопку Далее.

Рисунок 3. Создание простого запроса

4. Введите имя запроса – Телефоны – и нажмите кнопку Готово. Перед вами появится запрос, в котором можно просмотреть телефоны сотрудников.

5. Следующий запрос попробуйте создать с помощью Конструктора, для этого выполните команду: вкладка ленты Создание – Конструктор запросов. В диалоговом окне Добавление таблиц выберите таблицу Клиенты и щелкните на кнопке Добавить, а затем – на кнопке Закрыть.

6. Чтобы перенести нужные поля в бланк запроса, необходимо по ним дважды щелкнуть левой кнопкой мыши (рисунок 4).

Рисунок 4. Создание запроса в режиме Конструктора

7. Чтобы отсортировать записи в поле Название компании в алфавитном порядке, необходимо в раскрывающемся списке строки Сортировка выбрать пункт по возрастанию. Сохраните запрос с именем «Адреса клиентов».

8. Самостоятельно создайте запрос «Дни рождения», в котором можно будет просмотреть дни рождения сотрудников.

9. Допустим, мы хотим узнать, у кого из сотрудников день рождения в текущем месяце, например в апреле. Для этого откройте запрос в режиме Конструктора. В строке Условие отбора для поля «Дата рождения» введите значение *.04.* . В данной записи * означают, что дата и год рождения могут быть любыми, а месяц 4-ым (т.е. апрель). После этого окно запроса должно выглядеть так, кА оно представлено на рисунке 5.

Рисунок 5. Создание запроса

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

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

Рисунок 6. Создание запроса с параметром

12. Запись Like[Введите дату] означает, что при открытии запроса появится диалоговое окно (рисунок 7) с текстом «Введите дату» и полем для ввода условия отбора. Если ввести условие *.04.*, то в запросе появится список сотрудников родившихся в апреле. Запустите запрос еще раз и введите значение *.05.*, посмотрите, как изменился запрос.

Рисунок 7. Окно для ввода условия отбора

13. Измените запрос «Телефоны» так, чтобы при его запуске выводилось диалоговое окно с сообщением «Введите фамилию». Поскольку в запросе нужно ввести конкретную фамилию, в условии отбора слово Like писать не надо.

14. Измените запрос «Телефоны» так, чтобы при его запуске запрашивались не только фамилия, но и имя сотрудника.

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

16. Сделайте так, чтобы столбец Отметка о выполнении не выводился на экран.

Читайте также:  Клуб любителей экстрима мегафон отключить

17. Создайте запрос Сумма заказа, в котором будут отображаться заказы на сумму более 50 000 руб.

18. Измените запрос, чтобы сумма заказа была от 20 000 до 50 000 руб. для данных запросов в условии отбора можно использовать операторы сравнения >, =, и логические операторы And, Or, Not и другие.

19. Иногда в запросах требуется произвести некоторые вычисления, например посчитать подоходный налог 13% для каждой сделки. Для этого откройте запрос Сумма заказа в режиме Конструктора. В пустом столбце бланка запроса щелкните правой кнопкой мыши на ячейке поле и в появившемся контекстном меню выберите команду Построить. Перед вами появится окно Построитель выражений (рисунок 8), который состоит из трех областей: поля выражения, кнопок операторов и элементов выражения. Сверху располагается поле выражения, в котором оно и создается. Вводимые в это поле элементы выбираются в двух других областях окна Построителя.

20. В левом списке откройте папку Запросы и выделите запрос Сумма заказа. В среднем списке выделите поле Сумма и нажмите кнопку Вставить. Идентификатор этого поля появится в поле Построителя.

21. Щелкните на кнопке * и введите 0,13 (рисунок 8). Таким образом мы посчитаем подоходный налог 13%.

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

22. Нажмите кнопку ОК, после чего в ячейке свойства Поле появится значение «Выражение1:[Сумма]*0,13».Замените Выражение1 на Налог и закройте Конструктор. Откройте запрос и посмотрите, что у вас получилось.

23. Используя Построитель выражений, добавьте в запрос Сумма заказа поле Прибыль, в котором будет вычисляться доход от заказа (т.е. сумма минус налог).

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

1. Вычисления в запросах

1.1. Создать запрос Фамилия_СтоимостьЗаказа с полями Производитель, Модель, ДатаЗаказа, Цена, СтоимостьДоставки, Стоимость без доставки (=Цена*Количество), Стоимость с доставкой (=Цена*Количество+СтоимостьДоставки).

1.2. Создать запрос с параметром Фамилия_Цены в Евро ( в котором цены переведены в евро, а курс евро вводится как параметр) с полями Производитель, Модель, ДатаЗаказа, Цена, Цена в евро (=Цена/Курс), СтоимостьДоставки, СтоимостьДоставки в евро. Установить для полей Цена в евро и СтоимостьДоставки в евро установить соответсвующий денежный формат.

2. Использование в запросах групповых операций

Создать запрос Фамилия_Итоги1 (Фамилия_Итоги2. ) на основе таблиц : Модели, Склад

2.1. Получить данные о количестве моделей и количестве экземпляров на складе каждого производителя

2.2. Количество моделей с цветным дисплеем по группам производителей и количество экземпляров на складе

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

2.4. Для каждого производителя получить минимальный, максимальный и средний вес телефона

2.5. Для каждого производителя получить максимум режима ожидания, максимум режима разговора

2.6. Для каждого производителя узнать количество моделей с цветным дисплеем

2.7. Узнать количество моделей каждого производителя с фотокамерой и диктофоном

2.8. Узнать количество моделей каждого производителя с фотокамерой или диктофоном

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

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

3.1. Создать запрос Фамилия_Перекрестный1, который бы позволил узнать количество моделей у каждого производителя каждого типа корпуса (тип корпуса должен иметь словесное обозначение, для чего можно воспользоваться функцией IIf() или Switch())

3.2. Создать запрос Фамилия_Перекрестный2, который бы позволил узнать количество заказанных моделей за каждый месяц для каждого поставщика
(для отображения названия месяца можно воспользоваться функциями MonthName() и Month())

4. Запросы на изменение:

4.1. Запросы по созданию таблицы

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

4.2. Запрос на обновление данных

Обновить записи в таблице Заказы с учетом того, что СтоимостьДоставки из Украины возросла в 1,2 раза (в запросе используются данные из таблиц Заказы и Поставщики)

4.3. Запрос на добавление данных

Добавить в таблицу Временная записи из таблицы Заказы с ценой более 300 000 рублей

4.4. Запрос на удаление данных

Удалить из таблицы Временная Заказы 2004 года

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

Существует четыре типа запросов на изменение: на удаление, на обновление и добавление записей, а также на создание таблицы.

Читайте также:  На это устройство невозможно получать подтверждения steam

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

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

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

— добавление полей на основе условий отбора;

— добавление записей, если некоторые поля из одной таблицы не существуют в другой. Запрос на добавление добавит данные в совпадаю­щие поля и пропустит остальные.

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

— создание таблицы для экспорта в другую базу данных Microsoft Access;

— создание отчетов, содержащих данные нескольких таблиц;

— создание резервной копии таблицы.

— создание архивной таблицы, содержащей старые записи;

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

Выбор данных из одной таблицы

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

После выполнения запроса на выборку Microsoft Access создает набор записей, содержащий отобранные данные, с которыми можно работать также как и с таблицей.

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

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

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

Рис.10.1. Окно конструктора запросов

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

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

Читайте также:  Смарт приставка ксиаоми ми бокс 3

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

Кроме этого Access предоставляет специальные операторы для отбора данных, выводимых в запросе:

between — определяет диапазон значений. Between 10 and 20 означает то же самое, что и выражение >=10 and 10. В этом случае выбираются все записи поля, вычисляемое поле которых >10;

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

year(дата) — возвращает значение года в диапазоне от 100 до 9999;

weekday(дата) — возвращает целое число от 1(Воскресенье) до 7(Суббота), соответствующее дню недели;

date() — возвращает текущую системную дату.

Вычисляемые поля. Можно выполнить вычисления с любыми полями таблицы и сделать вычисляемое выражение новым полем в наборе записей. При этом можно использовать любые функции, встроенные в Access, и выполнять над полями таблицы арифметические операции с помощью операторов: +, -, *, /, , ^, mod, &. Например, пусть имеется имя поля с именем “Количество”, где записано количество единиц товара и поле “Цена, где записана стоимость единицы товара. Тогда для подсчета стоимости товара в пустое поле бланка запроса нужно ввести выражение Количество*Цена и значения этих полей будет перемножено.

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

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

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

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

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

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

sum — вычисляет сумму всех значений заданного поля в каждой группе. Используется только для числовых и денежных полей;

avg — Вычисляет среднее арифметическое значение всех значений данного поля в каждой группе;

min, max — вычисляет наименьшее (наибольшее) значение поля внутри группы;

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

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

Ссылка на основную публикацию
Сколько человек сидит в одноклассниках
Mail.Ru Group исследовала и сравнила аудитории самых популярных в России социальных сетей — «Одноклассники», «Мой Мир», «ВКонтакте», Facebook и Twitter....
Сигнал flash в телефоне panasonic
● 19.12.08 13:08 - krepsky - 9 / 19.12.08 Два дня ломаю голову… Такая ситуация - купили партию телефонов Panasonic...
Сигналы материнской платы при загрузке
BIOS (Basic Input/Output System – базовая система ввода-вывода). Программа системного уровня, предназначенная для первоначального запуска компьютера, настройки оборудования и обеспечения...
Сколько четырехзначных чисел можно составить из нечетных
Условие Решение 1 Решение 2 Решение 3 Поиск в решебнике Популярные решебники Издатель: Н. Я. Виленкин, В. И. Жохов, А....
Adblock detector