Требования к запросам
Если при редактировании схемы запросов существует требование, чтобы ни одна таблица не была открыта, то при работе с запросами открытые таблицы никак не влияют на запросы. Для создания запросов выполните команду Создание → Конструктор запросов. После этого открывается окно для создания запросов, а также окно с перечнем таблиц, запросов или таблиц и запросов одновременно. Если такое окно не открыто, например, оно было закрыто случайно, то открыть его можно следующим образом: щелкните правой клавишей мыши в свободном месте окна запросов и из открывшегося контекстного меню выполните команду Добавить таблицу. В окне с перечнем таблиц добавьте нужные таблицы и/или запросы (запросы можно использовать точно так же, как и обычные таблицы, потому что в них имеются точно такие же поля, как и в таблицах). Так как у нас пока нет никаких запросов, то пока мы можем работать только с таблицами.
В окне Добавление таблицы выделите таблицы (поочередно или сразу все с нажатой клавишей Shift для смежных или клавишей Ctrl для несмежных) и нажмите на кнопку Добавить.
Если таблица была добавлена в запрос случайно, то щелкните по этой таблице (в любом месте) правой клавишей мыши и из открывшегося контекстного меню выполните команду Удалить таблицу. Таблица удаляется всего лишь из запроса, а не из БД. Закройте окно Добавление таблицы.
При создании запросов используются не все таблицы. Для большинства типов запросов число лишних открытых таблиц не имеет значения, лишние таблицы просто не будут участвовать в запросе. Но в некоторых типах запросов лишние таблицы открывать нельзя, иначе будет выводиться сообщение об ошибке, причем, обычно, в чем состоит ошибка, не говорится. К таким типам запросов относятся Обновление, Удаление.
Расставьте таблицы так, чтобы они позволяли видеть связи между таблицами (Рис. 13).
Рис. 13. Открытые таблицы для запроса
В нижней части конструктора запроса находятся столбцы, состоящие из нескольких полей. Заполнение столбцов выполняется слева направо. В верхней строке каждого столбца указывается поле, необходимое для запроса. Поле можно выбрать несколькими способами:
1. В очередном пустом столбце Конструктора поместите курсор в строку, в которой находится надпись Поле. После этого в правой части поля появляется миниатюрная треугольная кнопочка. Нажмите на нее и выберите нужное поле. Каждое поле в таблице состоит из 2-х частей: имени таблицы, в которой это поле имеется, и имени поля в этой таблице. Все поля сортированы по именам таблиц. Если одно и то же поле создано в нескольких таблицах, то из какой именно таблицы брать это поле, совершенно все равно.
2. Подхватите поле из таблицы и перетащить его в столбец конструктора запросов (Рис. 14). Отпустите мышь.
Рис. 14. Перетаскивается поле Код_тура
3. Поместите курсор в тот столбец, в который нужно вставить поле. В таблице выполните двойной щелчок мышью по имени поля.
Если поле было вставлено ошибочно, то его можно удалить. Для этого наведите указатель мыши над именем столбца так, чтобы появилась небольшая стрелка, направленная вниз. Щелкните мышью для выделения столбца. Щелкните правой клавишей мыши по выделению. Выполните команду Вырезать из контекстного меню.
Другой вариант: можно не удаляя столбец с полем просто отключить это поле. Для этого нужно сбросить флажок из опции Вывод на экран в столбце с полем, которое нужно отключить.
Для примера создадим запрос Анализ продаж. Выберите следующие поля: Код_заказа, Дата, Наименование_тура, Цена, Количество, Название_фирмы, Город, Телефон, Скидка (Рис. 15).
Рис. 15. Конструктор запроса с выбранными полями
Для выполнения анализа и расчета нажмите на кнопку Конструктор → Выполнить. Укажите имя запроса. После этого результаты запроса будут выведены на экран (Рис. 16). Так как записей в таблице Запросы всего 100, то и в результатах запроса должно быть 100 записей.
Рис. 16. Результаты запроса Анализ продаж
В данном запросе мы использовали все 4 исходные таблицы. После создания запроса под каждой таблицей в списке таблиц теперь находится имя созданного запроса.
В следующем запросе создайте те же самые поля, что и в предыдущем (Код_заказа, Дата, Наименование_тура, Цена, Количество, Название_фирмы, Город, Телефон, Скидка). Прокрутите список столбцов в Конструкторе запросов вправо так, чтобы был виден следующий (справа) пустой столбец. В верхнем поле этого пустого столбца введите следующую формулу:
[Цена]*(1-[Скидка])*[Количество]
В квадратных скобках указаны поля, участвующие в расчете формулы. В отличие от аналогичного приложения Microsoft Excel, в котором формула начинается с символа =, в приложении Microsoft Access символ = в формуле не используется.
Щелкните мышью в любом свободном поле, например, на 1 строчку ниже. Сразу после этого перед формулой появляется автоназвание этого поля: Выражение1. Формула и автоназвание разделены между собой символом двоеточия. Выделите мышью текст автоназвания (двоеточие или символы формулы ни в коем случае выделять не нужно). Введите вместо него заголовок поля К оплате. Выполните команду Конструктор → Выполнить (кнопка в виде восклицательного знака). Укажите имя запроса (Анализ продаж с оплатой).
В списке таблиц (в левой части рабочего окна Access ) выделите последний запрос, щелкните по нему правой клавишей мыши и из открывшегося контекстного меню выполните команду Конструктор. Выделите столбец с формулой и удалите эту формулу. Обратите внимание, что в предыдущем примере мы создавали формулу с участием полей. Эти поля мы указывали в формуле вручную. Это очень опасный способ, так как ошибка в одной букве названия поля или символе приведет к неработоспособности всей формулы, а вместе с формулой и всего запроса. Поэтому формулы обычно создают с помощью Построителя выражений. Для этого в первом поле столбца щелкните правой клавишей мыши и из открывшегося контекстного меню выполните команду Построить. После этого открывается окно Построитель выражений (Рис. 17).
Рис. 17. Создание формулы с помощью построителя
В левом списке находятся все объекты БД. Откройте группу Таблицы. Откройте таблицу Туры: список полей таблицы выводятся в средней части построителя. Двойным щелчком включите поле Цена в формулу. Квадратные скобки и имя таблицы включаются в формулу автоматически. Одиночным щелчком вставьте символ умножения (*). Вставьте открывающуюся круглую скобку. Введите с клавиатуры цифру 1. Вставьте символ вычитания (-). В списке таблиц щелкните по таблице Клиенты. Двойным щелчком включите поле Скидка в формулу. Вставьте закрывающуюся круглую скобку. Вставьте символ умножения (*). В списке таблиц щелкните по таблице Заказы. Двойным щелчком включите поле Количество в формулу. Нажмите на кнопку ОК. В созданной формуле с клавиатуры потребовалось ввести только цифру 1 — все остальные объекты (поля, операторы и даже скобки) выбирались готовыми: чем меньше потребуется ручного ввода, тем меньше ошибок будет в формуле.
После возвращения в Конструктор запросов название поля измените точно так же, как в и предыдущем примере (щелкните по соседнему полю и автоназвание Выражение1 измените на К оплате).
Как видно из этого примера работа с построителем намного проще, чем ручное создание формулы. Если при ручном вводе формулы нам потребовалось все символы вводить вручную, что рано или поздно приведет к ошибкам, то при использовании построителя нам пришлось с клавиатуры ввести всего один символ: цифру 1.
Для создания следующего запроса откройте конструктор запросов (команда Создание → Конструктор запросов). В предыдущих примерах мы в качестве исходных данных использовали вкладку Таблицы в окне Добавление таблицы. Запрос Анализ продаж является компиляцией из всех 4-х таблиц. Поэтому использовать таблицы в следующем запросе не обязательно. В окне Добавление таблицы перейдите на вкладку Запросы и добавьте запрос Анализ продаж в исходные данные создаваемого запроса. Включите в новый запрос следующие поля: Код_заказа, Дата, Наименование_тура, Название_фирмы, Город, Телефон. В столбце с полем Город найдите поле Условие отбора. Введите в это поле Волгоград. Щелкните мышью в любом соседнем поле (лучше пустом): название города будет автоматически заключено в парные кавычки. Данная операция не является обязательной: просто здесь объясняется, откуда берутся кавычки — они устанавливаются автоматически. Поэтому нет никакого смысла вводить их вручную. В столбце с полем Наименование_тура в поле Условие отбора введите Стамбул (Рис. 18). Выполните команду Конструктор → Выполнить. Сохраните запрос под именем Анализ по регионам (Рис. 19).
Рис. 18. Параметры запроса Анализ по регионам
Рис. 19. Результаты запроса Анализ по регионам
Откройте запрос Анализ по регионам в режиме Конструктора. Измените Волгоград на Ижевск. Наименование_тура измените из Стамбул на Мальорка. Выполните запрос, убедитесь в правильности работы запроса. Самостоятельно выберите Город и Наименование_тура и выполните запрос.
В предыдущем запросе мы указывали условия отбора записей в теле Конструктора. Кроме этого имеется возможность выбора условий непосредственно при работе с запросом. Такие запросы называются параметрическими. Условия поиска указываются не в кавычках, как мы это делали в предыдущем запросе, а в квадратных скобках, причем указывать нужно не конкретное значение, а вопрос (Рис. 20). Например, в предыдущем примере мы в качестве параметра поиска указывали город Волгоград. В параметрическом запросе нужно ввести между квадратными скобками: Введите город. Выполните запрос. Укажите имя запроса Анализ по регионам-П. Введите параметры в окно Введите значение параметра (Рис. 21). Нажмите на кнопку ОК. Укажите следующий параметр (так как в нашем запросе имеются 2 условия в квадратных скобках).
Рис. 20. Параметрический запрос
Рис. 21. Ввод параметра
Сравните 2 запроса, созданные по-разному, но выдающие один и тот же результат.
Выполните этот же запрос с другими значениями параметров.
Запрос: список фирм из города Пенза
Откройте Конструктор запросов. В качестве исходных данных используйте таблицу Клиенты. Выберите поля Название_фирмы, Город, Индекс, Адрес и Телефон. Для поля Город выберите условие: Пенза (Рис. 22). Выполните запрос. Сохраните запрос под именем Фирмы Пензы.
Рис. 22. Параметры для запроса по фирмам Пензы
Запрос: список агентов, стаж работы которых не превышает 5 лет
Откройте Конструктор запросов. Откройте таблицу Сотрудники. Выберите следующие поля: ФИО и Стаж. Для поля Стаж укажите условие отбора (Рис. 23):
Выполните запрос. Сохраните запрос под именем Стаж до 5 лет.
Рис. 23. Выборка по стажу
Запрос: список сотрудников старше 25 лет
Откройте Конструктор запросов. Откройте таблицу Сотрудники. Выберите следующие поля: ФИО, Дата_рождения. Дополнительно в следующем поле введите формулу с помощью построителя:
DateDiff(«d»;[Сотрудники]![Дата_рождения];Now())
Функция DateDiff выбирается в группе Функции → Встроенные функции → Дата/время. Эта функция позволяет определить разницу между двумя датами. Синтаксис этой функции следующий (обязательные аргументы):
DateDiff(interval, date1, date2[, firstdayofweek[, firstweekofyear]])
interval — единица измерения расчета. Значение d, например, измеряет разницу в днях. Значение заключается в парные кавычки.
date1, date2 — даты, участвующие в расчете. Из date2 вычитается date1.
Остальные аргументы необязательные.
Аргумент date1 это дата рождения сотрудника. Аргумент date2 это текущая дата. Функция текущей даты и времени называется Now(). У функции нет аргументов, так как текущая дата берется из системной даты. Назовите это поле Возраст.
Возраст измеряется в днях. Поэтому у 25-летнего сотрудника возраст будет 25*365=9125 дней (Рис. 24). Сохраните запрос под именем Сотрудники старше 25 лет (Рис. 25).
Рис. 24. Выборка по возрасту
Рис. 25. Результат запроса
Сразу после этого обычно делаются следующие замечания: более привычно считать в годах, а не в днях, и как округлить возраст до некоторого приемлемого значения.
Все изменения мы будем выполнять по шагам, чтобы пользователь мог понять, что одно изменение формулы потребует другого. Чтобы перевести значение в днях в годы, нужно всего лишь исходную формулу разделить на число дней в году:
DateDiff(«d»;[Дата_рождения];Now())/365
Условие отбора измените на 25. Выполните запрос: теперь в поле Возраст находится много символов решетки: увеличьте ширину этого поля, чтобы увидеть результат. Теперь результат действительно выводится в годах, но с множеством знаков после запятой. Знаков может быть так много, что они могут даже не поместиться по ширине монитора. Поэтому это число нужно как то ограничить. Снова войдите в Конструктор и Построитель. Функция округления называется ROUND. В качестве обязательного аргумента нужно указать саму формулу, которую нужно округлить. Если число знаков после запятой не указано, то число округляется до целого. Поэтому укажем, что округлять нужно до 1 знака после запятой:
Round (( DateDiff (» d «;[Дата_рождения]; Now ())/365);1)
Закройте построитель, выполните запрос. Возможен запрос с отключенным расчетным полем Возраст.
Запрос: список клиентов, не получивших скидку
Откройте Конструктор запросов. Откройте таблицу Клиенты. Выберите следующие поля: Название_фирмы, Код_агента, Скидка. Для поля Скидка укажите условие отбора: 0. Выполните запрос. Сохраните запрос под именем Клиенты без скидки.
Более сложный запрос: вывести клиентов, получивших скидку. В этом случае нужно указать условие отбора:
То есть здесь указывается: не равно 0. Еще один способ: выделите пустую строку с условием отбора. Вызовите Построитель. Нажмите на кнопку Not, то есть Нет (или Не). Введите с клавиатуры 0. Нажмите на кнопку ОК. В условии отбора будет выведено:
Not 0
То есть в формуле указано: не 0. Выполните запрос.
Запрос: заказы за последнюю неделю текущего месяца
Так как в учебном курсе невозможно угадать срок выполнения расчетов студентами, то указанные сроки в нашем примере придется скорректировать вручную. Для этого откройте таблицу Заказы и в поле Дата измените даты заказа (примерно в 10-15 записях) так, чтобы они были датами за предыдущую неделю на момент создания данного запроса (то есть датами, отстоящими от текущей даты не более, чем 7 дней).
Откройте Конструктор запросов. Откройте таблицу Заказы. Выберите следующие поля: Месяц, Дата, Количество, Код_тура.
Сначала определим, что такое «последняя неделя». Это дни, отстоящие от текущей даты до 7 дней. Значит, чтобы найти записи за этот период нужно от текущей даты вычесть 7 дней: все записи в диапазоне от текущей даты и максимум 7 дней будут считаться записями за последнюю неделю. В строке условия отбора откройте Построитель. Нам потребуется оператор Between… And… (Между… и…), который находится в группе Операторы → Сравнения. Вставьте этот оператор в формулу:
Between Выражение And Выражение
Вместо слова Выражение мы должны подставить функции текущей даты. Текущую дату можно подставить с помощью функции Date(). Выделите первое «Выражение». Перейдите в группу Функции → Встроенные функции → Дата/время. На место выделенного вставьте функцию Date(). Выделите второе «Выражение». Введите там следующую конструкцию:
(Date()-7)
Функция Date() вводится также из списка функций даты и времени: вручную вводится только цифра 7, так как минус также вставляется из списка операторов. В какой последовательности указывать искомый диапазон не имеет никакого значения. То есть можно указать так:
Between (Date()-7) And Date()
Between Date() And (Date()-7)
Выполните запрос . Сохраните его под именем «Заказы за текущую неделю». Помните, что за время, прошедшее с момента создания запроса и представлением готовых расчетов преподавателю, текущая неделя может измениться и тогда снова придется вручную изменять даты оформления заказов: поэтому готовую работу нужно предоставить как можно быстрее.
Запрос: список телефонов клиентов, оформивших заказ вчера
Для начала убедитесь, что в таблице Заказы имеются записи с датами заказов накануне даты расчета. Если таких записей нет, то измените некоторые даты оформления заказов.
Откройте Конструктор запросов. Откройте таблицы Заказы и Клиенты. Из таблицы Клиенты выберите следующие поля: Название_фирмы, Телефон. Из таблицы Заказы выберите поле Дата. В условиях отбора записей в поле Дата с помощью Построителя создайте выражение:
Date()-1
Выполните запрос. Сохраните его под именем «Вчерашние заказы». Помните, что за время, прошедшее с момента создания запроса и представлением готовых расчетов преподавателю, вчерашнее число может измениться и тогда снова придется вручную изменять даты оформления заказов.
Запрос: анализ популярности туров по России в апреле в г. Пенза
Откройте Конструктор запросов. Откройте таблицы Заказы, Клиенты и Туры. Из таблицы Заказы выберите поля Месяц и Количество. Из таблицы Туры выберите поле Тип_тура. Из таблицы Клиенты выберите поле Город.
Введите условия отбора: в Месяц — Апрель; Тип_тура — Россия; Город — Пенза. В одном и том же месяце может быть несколько записей, удовлетворяющим условиям запроса. Например, в августе месяце из Пензы тысячи людей уезжают на отдых в Сочи. Если сейчас запустить созданный запрос, то может оказаться, что по одному туристическому направлению может оказаться несколько записей. Суммировать общее значение придется вручную. Чтобы указать программе, что найденные одинаковые количества путевок нужно суммировать, а результат выводить одной строкой, необходимо выполнить следующие действия: Щелкните правой клавишей мыши в любой свободной ячейке любого столбца, даже пустого. Выполните команду Итоги. Между строками Имя таблицы и Сортировка появится еще одна строка, которая называется Групповая операция. В каждом непустом столбце появляется значение Группировка. В поле Количество щелкните по значению Группировка. После этого в правой части ячейки появляется миниатюрная треугольная кнопочка. Нажмите на нее и в открывшемся списке выберите значение Sum , то есть суммирование строк с одинаковыми значениями.
Выполните запрос. Сохраните его под именем «Туры по России в апреле в Пензе».
Запрос: объем продаж агента «Павлухина Мария Сергеевна» за апрель
Несмотря на кажущуюся простоту это сложный запрос. Откройте Конструктор запросов. Откройте таблицы Заказы, Сотрудники и Туры. Из таблицы Сотрудники выберите поле ФИО. Из таблицы Заказы выберите поле Месяц. Из таблицы Туры ничего выбирать не нужно: в этом то вся сложность и необычность запроса. Дело в том, что мы собираемся рассчитать объем продаж, а для этого нужно знать 2 аргумента: Количество и Цена. Аргумент Количество можно взять из таблицы Заказы. А вот аргумент Цена находится в таблице Туры, поэтому эта таблица должна быть открыта. Если в предыдущих запросах мы использовали поля в явном виде, то здесь мы впервые используем поле в неявном виде, в данном случае — в формуле. Если таблица Туры не будет открыта, то программа не сможет прочитать цены туров и попытается это выяснить в окне, похожем на параметрический.
В следующем пустом поле (третье поле слева) в строке Поле поместите курсор и вызовите Построитель. Откройте группу Таблицы и откройте список полей таблицы Заказы. Двойным щелчком вставьте поле Количество. Вставьте символ умножения (*). Откройте список полей таблицы Туры. Двойным щелчком вставьте поле Цена. Данная формула рассчитывает стоимость тура, оформленным одним клиентом. Но нам нужна сумма всех стоимостей. Для этого откройте группу Функции → Встроенные функции → Статистические. Поместите курсор в начало формулы. Двойным щелчком вставьте функцию Sum. Удалите символы «expr»). Установите курсор в конце формулы. Вставьте закрывающуюся круглую скобку. Полная формула должна быть такой:
Sum([Заказы]![Количество]*[Туры]![Цена])
Нажмите на кнопку ОК. Выделите автоназвание поля Выражение1 и вместо него введите название Продажи.
Щелкните правой клавишей мыши в любой свободной ячейке любого столбца, даже пустого. Выполните команду Итоги. Между строками Имя таблицы и Сортировка появится еще одна строка, которая называется Групповая операция. В каждом непустом столбце появляется значение Группировка. В поле Продажи щелкните по значению Группировка. После этого в правой части ячейки появляется миниатюрная треугольная кнопочка. Нажмите на нее и в открывшемся списке выберите значение Выражение. Выполните запрос. Сохраните его под именем «Продажи Павлухиной за апрель».
Как пользоваться Microsoft Access
wikiHow работает по принципу вики, а это значит, что многие наши статьи написаны несколькими авторами. При создании этой статьи над ее редактированием и улучшением работали, в том числе анонимно, 10 человек(а).
Количество просмотров этой статьи: 281 793.
Microsoft Access — это программа для создания баз данных, которая позволяет с легкостью управлять и редактировать базы данных. Она подходит буквально для всего, начиная от небольших проектов и заканчивая крупным бизнесом, она очень наглядна. Это делает ее прекрасным помощником для ввода и хранения данных, поскольку пользователю не нужно иметь дело с таблицами и графиками. Приступайте к чтению, чтобы научиться использовать Microsoft Access максимально эффективно.
Создание запросов в Access
Запрос (query) – это средство, позволяющее собрать необходимую информацию из объемной базы данных, т.е. это вопрос, сформированный по отношению к БД. Создание запросов в Access может осуществляться несколькими способами: по образцу (QBE)и с помощью структурированного языка запросов (SQL).
QBE – средство для поиска необходимых сведений в базе.
SQL – составленные программистом инструкции, задающие ход дальнейших действий для полученных результатов.
Создание запросов в Access 2007, 2010, 2013:
Перед созданием необходимо определиться с:
- полями в БД, по которым будет происходить поиск информации;
- предметом поиска;
- перечнем полей, которые будут демонстрироваться в результате выполнения задания.
В окошке «База данных» следует выбрать вкладку с соответствующим названием и дважды щелкнуть по ней, после чего на экране появится новое окно Конструктора.
-
В окне «Добавление таблицы» выберите один или несколько документов, которые следует использовать и нажмите кнопку «Добавить». После всех выбранных вариантов, нажмите кнопку «Закрыть».
Как видите, создание запросов в Access не настолько сложный процесс, как это могло показаться в начале.
Выбор данных в MS Access по дню, месяцу и году
Проблема: необходимо вывести данные о товарах, которые прибыли в заданное пользователем время (пользователю в начале предлагается ввести дату От и До).
Я знаю функцию Year, которая позволяет работать с годом, однако, необходимо, чтобы была возможность ввода полной, точной даты (т.е. дд.мм.гггг).
Как можно сделать выборку не только по году, но и еще по дню и дате (пользователь вводит день, месяц и год)?
Upd. Убираю Year:
Пробую вводить данные в формате дд.мм.гггг (11.11.2017 и 12.10.2018). Выдает: 
Судя по комментариям, вы уже разобрались в проблеме, однако хочу обратить внимание, что вы можете неверно указывать формат даты. Дело в том, что даты должны быть строго в формате USA-date, а именно #M/D/YYYY# . Разделитель должен быть именно / , а не точка или пробел. Порядок указания месяца и дня так же важны. Сперва указывается месяц, затем день. При этом числа должны быть в односимвольном формате (вместо 01-31 для дней и 01-12 для месяца должно быть указано 1-31 и 1-12 соответственно). Дата с обоих сторон заключается в решетку # .