Проектирование запроса в access

Групповые операции (перекрёстные запросы в access)

Аксесс довольно сложная программа. Это подтверждает тот факт, что в интернете можно найти массу информации по ней. Однако, вы знаете что на самом деле достаточно знать лишь 20% инструментов, чтобы создавать и настраивать 80% баз данных в программе? И научиться этому можно после изучения 40 специальных видеоуроков, в которых описана самая суть без воды.

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

В перекрёстном запросе обычно три поля: поле заголовка столбцов, поле заголовка строк и поле итогового содержимого ячеек.

По умолчанию строка «Групповая операция» в режиме конструктора скрыта.

Группировка

Инструкция «Группировка» разбивает результаты запроса по каждому уникальному значению поля (полей). Например, если в поле Страна встречается 20 раз Литва, 30 раз Польша, 50 раз Чехия, то при группировке по этому полю будет всего три записи – по одной на каждую страну. Если будем добавлять группировку в другие поля, то количество выбранных записей возрастёт, так как программа будет искать уникальное значение не одного, а нескольких полей (например, зададим группировку по странам и по поставщикам. В Литве пять поставщиков, а, следовательно, уникальных записей для Литвы уже будет пять, а не одна).

Инструкция Группировка автоматически задаёт сортировку в алфавитном порядке. Для изменения порядка сортировки надо явно указать программе параметры в строке «Сортировка».

Запрос с параметрами

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

Начинать данную процедуру по выборке данных нужно с создания простого запроса, чтобы выбрать нужные поля. Далее через режим Конструктора обязательно нужно заполнить поле «Условие отбора» и, уже исходя из внесенного значения, будет осуществляться отбор.

Таким образом, на вопрос о том, как создать запрос с параметром в Access, ответ простой — внести исходные параметры для выборки. Чтобы работать с Конструктором необходимо пользоваться Мастером запросов. Там создается первичные данные для фильтрации, которые служат основой дальнейшей работы.

Чтобы изменить запрос:

Когда вы открываете существующий запрос в Access, он отображается в представлении Datasheet, что означает, что результаты вашего запроса будут отображаться в таблице. Чтобы изменить свой запрос, вы должны ввести представление «Дизайн», представление, которое вы использовали при его создании. Существует два способа переключения на представление «Дизайн»:

На вкладке «Главная» на ленте щелкните команду «Вид». В раскрывающемся меню выберите «Просмотр дизайна».
В правом нижнем углу окна Access найдите маленькие значки. Щелкните значок «Вид дизайна», который является значком, самым дальним вправо.

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

Вы можете заметить, что Access предлагает другие виды запросов, такие как Pivot Table View , Pivot Chart View и SQL View. Вы можете игнорировать их; эти представления позволяют использовать расширенные функции, которые вам не нужно использовать для этого руководства или для большинства функций Access.

ПримечанияRemarks

Вы можете использовать вложенный запрос вместо выражения в списке полей оператора SELECT или предложении WHERE или HAVING.You can use a subquery instead of an expression in the field list of a SELECT statement or in a WHERE or HAVING clause. Во вложенном запросе вы используете оператор SELECT для предоставления набора одного или нескольких определенных значений для оценки в выражении для предложения WHERE или HAVING.In a subquery, you use a SELECT statement to provide a set of one or more specific values to evaluate in the WHERE or HAVING clause expression.

Используйте предикат ANY или SOME, которые являются синонимами, для получения записей в основном запросе, который удовлетворяет сравнению с любыми записями, полученными во вложенном запросе.Use the ANY or SOME predicate, which are synonymous, to retrieve records in the main query that satisfy the comparison with any records retrieved in the subquery. Следующий пример возвращает все продукты, для которых цена за единицу выше, чем у любого продукта, продаваемого со скидкой 25 процентов или более:The following example returns all products whose unit price is greater than that of any product sold at a discount of 25 percent or more:

Используйте предикат ALL для получения записей в основном запросе, который удовлетворяет сравнению со всеми записями, полученными во вложенном запросе.Use the ALL predicate to retrieve only those records in the main query that satisfy the comparison with all records retrieved in the subquery. Если вы изменили предикат с ANY на ALL в предыдущем примере, запрос будет возвращать только те продукты, у которых цена за единицу больше, чем у всех продуктов, проданных со скидкой 25 процентов или более.If you changed ANY to ALL in the previous example, the query would return only those products whose unit price is greater than that of all products sold at a discount of 25 percent or more. Это гораздо более строгое ограничение.This is much more restrictive.

Используйте предикат IN для получения только тех записей в основном запросе, для которых определенная запись во вложенном запросе содержит одинаковое значение.Use the IN predicate to retrieve only those records in the main query for which some record in the subquery contains an equal value. В примере ниже возвращаются все продукты со скидкой 25 процентов или больше:The following example returns all products with a discount of 25 percent or more:

С другой стороны, вы можете использовать NOT IN для получения только тех записей в основном запросе, для которых ни одна запись во вложенном запросе не содержит одинаковое значение.Conversely, you can use NOT IN to retrieve only those records in the main query for which no record in the subquery contains an equal value.

Используйте предикат EXISTS (с необязательным зарезервированным словом NOT) в сравнениях ИСТИНА/ЛОЖЬ, чтобы определить, возвращает ли вложенный запрос какие-либо записи.Use the EXISTS predicate (with the optional NOT reserved word) in true/false comparisons to determine whether the subquery returns any records.

Также можно использовать псевдонимы имени таблицы во вложенном запросе для ссылки на таблицы, указанные в предложении FROM за пределами вложенного запроса.You can also use table name aliases in a subquery to refer to tables listed in a FROM clause outside the subquery. Пример ниже возвращает имена сотрудников, чья заработная плата равна или выше средней заработной платы всех сотрудников на аналогичной должности.The following example returns the names of employees whose salaries are equal to or greater than the average salary of all employees having the same job title. Для таблицы «Сотрудники» присваивается псевдоним «T1»:The Employees table is given the alias «T1»:

В приведенном выше примере зарезервированное слово AS не является обязательным.In the preceding example, the AS reserved word is optional.

Некоторые вложенные запросы поддерживаются в перекрестных запросах, в частности, в качестве предикатов (например в предложении WHERE).Some subqueries are allowed in crosstab queries— specifically, as predicates (those in the WHERE clause). Вложенные запросы в виде выходных данных (в списке SELECT) не поддерживаются в перекрестных запросах.Subqueries as output (those in the SELECT list) are not allowed in crosstab queries.

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

Этот тип выборки более сложный. Чтобы разобраться, как создать перекрестный запрос в Access с помощью «Мастера» в данном режиме, нужно кликнуть по этой функции в первом окне.

На экране появится таблица, в которой можно выбрать до трех столбцов, расположенных в оригинале.

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

На фото показано, что перекрестный запрос создан, и что по заданным параметрам совершены необходимые действия.

Чтобы создать общий запрос:

Создайте или откройте запрос, который хотите использовать в качестве итогового запроса . В нашем примере мы хотим найти общее количество, которое мы продали в каждом из наших пунктов меню, поэтому мы будем использовать запрос, показывающий нам все пункты меню, которые мы продали. Если вы хотите следовать в нашей базе данных, откройте запрос пунктов меню.
На вкладке « Дизайн запроса» найдите группу «Показать / Скрыть», затем выберите команду «Итого».
Строка будет добавлена в таблицу в сетке проектирования , причем все значения в этой строке будут установлены в Group By. Выберите ячейку в строке « Всего:» поля, для которого вы хотите выполнить вычисление, и нажмите стрелку раскрывающегося списка, которая появляется.
Выберите расчет, который вы хотите выполнить в этом поле. В нашем примере мы хотим добавить количество продуктов, которые мы продали, поэтому мы выберем опцию Sum.
Когда вы удовлетворены своим дизайном запроса, выберите команду «Выполнить» на вкладке « Дизайн запросов », чтобы запустить запрос.
Результаты запроса будут отображаться в представлении Datasheet запроса, которое выглядит как таблица. Если вы хотите, сохраните запрос, нажав команду «Сохранить» на панели быстрого доступа. Когда появится запрос на его имя, введите нужное имя и нажмите «ОК» .

Новые статьи

Проектирование собственной базы данных в Access — 21/08/2018 15:16
Форматирование форм в Access — 21/08/2018 15:11
Создание форм в Access — 21/08/2018 15:05
Изменение таблиц в Access — 21/08/2018 14:58
Дополнительные параметры отчета в Access — 21/08/2018 14:48
Создание отчетов в Access — 21/08/2018 14:42

Предыдущие статьи

Проектирование запроса в Access — 21/08/2018 04:49
Сортировка и фильтрация записей в Access — 21/08/2018 04:37
Работа с формами в Access — 21/08/2018 04:25
MS Access — Работа с таблицами, создание, удаление, настройка внешнего вида — 20/04/2018 17:18
MS Access — Управление базами данных и объектами — 30/03/2018 16:18
Начало работы в Access. Знакомство с Access 2010 — 10/02/2018 18:24
MS Access: Введение в объекты — Таблицы, формы, запросы и отчеты — 07/02/2018 08:32
MS Access: Что такое база данных? Отличие Access от Excel. — 03/02/2018 18:18

Краткие рекомендации

Подводя итоги, нужно сказать, что решить, как создавать запросы в Access – с помощью Мастера или Конструктора, должен сам пользователь. Хотя, для большинства людей, которые используют СУБД MS Access, больше подойдет первый вариант. Ведь Мастер сам сделает всю работу, оставив для пользователя только несколько кликов мышью, при выборе условий запроса.

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

Есть один момент, который доступен лишь программистам. Так как основным языком СУБД является SQL, то нужный запрос можно написать в виде программного кода. Чтобы работать в данном режиме, достаточно нажать на строку уже созданного запроса, и в открывшемся контекстном меню выбрать «Режим SQL».

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

Запросы в Access

Запросы в Access являются основным инструментом выборки, обновления и обработки данных в таблицах базы данных. Access в соответствии с концепцией реляционных баз данных для выполнения запросов использует язык структурированных запросов SQL (Structured Query Language). С помощью инструкций языка SQL реализуется любой запрос в Access.

Основным видом запроса является запрос на выборку. Результатом выполнения этого запроса является новая таблица, которая существует до закрытия запроса. Записи формируются путем объединения записей таблиц, на которых построен запрос. Способ объединения записей таблиц указывается при определении их связи в схеме данных или при создании запроса. Условия отбора, сформулированные в запросе, позволяют фильтровать записи, составляющие результат объединения таблиц.

В Access может быть создано несколько видов запроса:

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

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

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

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

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

Создание структуры таблицы БД Access командой CREATE TABLE языка SQL

2015-07-04

Инструкция SQL (SELECT) или запрос на выборку данных из таблиц БД Access рассмотрена в работе SQL — язык доступа и управления СУБД Access. В этой статье рассмотрим инструкцию SQL (CREATE TABLE) запроса на изменение.

К этому типу запросов относятся запросы на создание таблицы, на добавление или на удаление записей в таблице и запросы на ее обновление. Структуру таблицы можно создать с помощью оператора CREATE TABLE языка SQL.

Рассмотрим создание структуры таблиц базы данных БД «Деканат» на основе модели «сущность – связь» в СУБД Access с помощью запросов SQL. Для этого создадим новую базу данных sql_training_st.mdb в приложении Access 2007.

Рис. 1.

Следует отметить, что файл новой базы данных сохраним в формате Access 2002-2003. После создания новой БД, в окне приложения будет отображаться окно БД на вкладке Режим таблицы и новая пустая таблица с именем Таблица 1 в режиме таблица.

Рис. 2.

Закрываем Таблицу1, щелкнув правой кнопкой мыши на Таблица1 в окне редактирования, и в контекстном меню выбрав команду Закрыть. Далее создадим структуру таблицы Группы аналогичную структуре таблицы Группы, созданной в Конструкторе, используя команду SQL create table.

Для этого в окне БД щелкаем левой кнопкой мыши на вкладке Создание и выбираем команду «Конструктор запросов». В результате в окне редактирования откроется объект «Запрос1» и окно диалога «Добавление таблицы». Закроем окно диалога, щелкнув левой кнопкой мыши на пиктограмме «Закрыть» в правом верхнем углу этого окна.

Рис. 3.

Затем создаем структуру таблицы «Группы», для этого выберем режим SQL, выполнив команду Вид/ Режим SQL. Удаляем появившуюся в окне запроса команду SELECT и вводим с клавиатуры следующую команду:

create table Группы
(КодГруппы COUNTER CONSTRAINT PrimaryKey PRIMARY KEY,
Название char(6),
Курс int,
Семестр int);

Сохраняем запрос с именем «Создание Группы». В результате в «Области переходов» появится несвязанный объект — «Создание Группы». После сохранения запроса необходимо выполнить этот запрос, щелкая на пиктограмме «Выполнить». В результате выполнения команды «create table Группы» в «Области переходов» появится объект — «Группы: таблицы».

Рис. 4.

Закроем окно «Создание Группы» и откроем объект – «Группы: таблица» в режиме конструктора.

Рис. 5.

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

Затем создаем структуру таблицы «Студенты», для этого выберем режим SQL, выполнив команду Вид/ Режим SQL. Удаляем появившуюся в окне запроса команду SELECT и вводим с клавиатуры следующую команду:

create table Студенты
(КодСтудента COUNTER CONSTRAINT PrimaryKey PRIMARY KEY,
КодГруппы int,
Фамилия char(20),
Имя char(15),
Отчество char(15),
Пол char(1),
Дата_рождения DATE,
Место_рождения MEMO,
FOREIGN KEY (КодГруппы) REFERENCES Группы (КодГруппы));

Для описания связей между таблицами «Группы» и «Студенты» через поле «КодГруппы» (отношение «один-ко-многим»), а также обеспечения целостности базы
данных применена запись «FOREIGN KEY (КодГруппы) REFERENCES Группы (КодГруппы)».

Сохраняем запрос с именем «Создание Студенты». В результате в «Области переходов» появится несвязанный объект — «Создание Студенты». После сохранения запроса необходимо выполнить этот запрос, щелкая на пиктограмме «Выполнить». В результате выполнения команды «create table Студенты» в «Области переходов» появится объект — «Студенты: таблицы».

Рис. 6.

Запрос с параметром

Хотите узнать какие 35 инструментов нужно знать, чтобы научиться создавать базы данных в Аксесс?

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

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

Например, нам часто нужны сведения по сотрудникам, фамилия которых начинается с определённой буквы. Можно сделать около 30 запросов (для почти всех букв русского алфавита), чтобы получать требуемые данные, но это займёт много времени для создания запросов, загромоздит базу данных и вызовет путаницу. Вместо этого создадим один запрос для формирования списка сотрудников, который будет нас спрашивать, с какой буквы начинающиеся фамилии мы хотим видеть. В поле «Условие отбора» введём: Like & «*». На самом деле, если введём букву «С», условие будет читаться Access как Like «С*». Знак амперсанда «&» указывает на необходимость сцепления введённой буквы с остальными символами искомой ячейки, так как «*» обозначает любое количество символов после «С».

Например, выражение Between And запросит ввести начальную и конечную даты и выдаст все записи, содержащиеся в этом диапазоне (программа распознает разные форматы, например: 01.01.2010 или 01,01,2010 или 01/01/2010). Но для этого в режиме конструктора через меню Запрос – Параметры (можно вызвать через контекстное меню) надо вызвать диалоговое окно «Параметры запроса». В столбце «Параметр» указать правильно название, например , а в столбце «Тип данных» указать требуемый тип, в нашем случае «Дата/время». Как правило, указываются параметры для числовых и полей дата/время.

Ещё пример: Like «??.» & & «.????». Программа попросит ввести номер месяца года и выдаст все записи, относящиеся к этому месяцу. Если в параметре ввести знак звёздочки «*», то будут выбраны дни рождений за все месяцы.

Запрос может не работать, если формат даты в условии задан 00.00.0000, а в настройках панели управления установлен 00/00/0000. Тогда надо вместо точек добавить ещё по одному вопросу.