Как самостоятельно составлять запросы в Microsoft Access
Начинающим разработчикам приложений под Microsoft сервер необходимо знать, как делать запросы в Access. Microsoft Access – самая популярная программа для работы с базами данных в Windows. Основной причиной успеха программы является интерактивный интерфейс.
Запросы или команды предоставляют возможность извлекать и фильтровать информацию, вычислять итоги, а также обновлять, перемещать и удалять записи в большом количестве. Освоение команд Microsoft Access улучшит способность программистов управлять данными и упростит разработку приложений.
Визуальное представление таблиц и графических связей между ними делает запросы Microsoft Access чрезвычайно простыми в использовании. Приятный пользовательский интерфейс по-прежнему позволяет выполнять очень мощный и расширенный анализ. Весь механизм смоделирован на системах SQL и может переключаться между графическим дизайном и синтаксисом SQL.
Разработчики сами делают выбор: использовать готовый конструктор или писать код на SQL. Но стоит помнить, что знание многих функций Microsoft Access позволяет быстро выполнять расширенную обработку данных без программирования.
Виды запросов
Microsoft Access поддерживает множество типов. Описание главных категорий:
Команда Select является наиболее распространенной и может использоваться для просмотра и выбора данных для форм, отчетов, элементов управления и других операций. Другие типы создают или изменяют данные и вместе называются запросами действий.
Запросы на примере Select
Для создания команды с помощью конструктора следует выполнить такие действия:
Настройка условий и их типы
Нижняя часть сетки QBE состоит из нескольких строк для условий. Эти дополнительные строки необходимы для того, чтобы указать, какие записи будут получены.
Чтобы еще больше сузить область, можно ввести критерии для нескольких полей одновременно. При вводе критериев в одной строке выполняется операция «И» между полями. То есть извлекаются строки, которые соответствуют критериям в поле 1 и в поле 2 и т. д. Если условия размещены в разных строках, выполняется операция «ИЛИ».
Какие типы запросов поддерживает ms access
7. Формирование запросов в СУБД Access
СУБД Access позволяет создавать запросы трех типов: запросы выбора, перекрестные запросы, запросы действия.
Запрос выбора является наиболее часто используемым типом запроса. Он дает возможность: выбирать записи, удовлетворяющие условиям отбора; включать в результирующую таблицу поля из одной или нескольких таблиц в нужном порядке; осуществлять вычисления над полями БД; выполнять статистические расчеты для групп записей. Разновидностью запроса выбора является запрос с параметрами — это запрос, при выполнении отображающий в собственном диалоговом окне приглашение ввести интересующее пользователя значение критерия отбора записей.
Перекрестный запрос представляет собой специальный запрос итогового типа. Он отображает результаты итоговых статистических расчетов над значениями некоторого поля в виде перекрестной таблицы. В ней значения одного или нескольких столбцов слева образуют заголовки строк, верхняя строка – заголовки столбцов из значений определенного поля, а на пересечении строк и столбцов – итоговые значения.
СУБД Access позволяет создавать запросы с помощью Мастеров и с помощью Конструктора. Мастера используются для создания следующих запросов:
· простого запроса на выборку полей из источника запроса и подведение итогов;
· запроса на поиск повторяющихся записей в таблице;
· запроса на поиск записей, не имеющих подчиненных им записей в другой таблице.
С помощью Конструктора можно создать любой запрос выбора, перекрестный запрос, запрос действия.
7.2. Создание запроса выбора
Для создания запроса выбора с помощью Конструктора необходимо открыть окно Конструктора запроса, выполнив действия:
Окно БД Þ объект Запросы Þ [Создать] Þ
окно Новый запрос Þ выбрать Конструктор Þ
окно Добавление таблицы Þ выбрать таблицы-источники запроса Þ
Окно Конструктора запроса имеет вид как на рис. 1.
Рис. 1. Окно Конструктора запроса
Оно разделено на две панели.
· Сортировка – дает возможность отсортировать записи в результирующей таблице запроса;
· Вывод на экран – позволяет управлять отображением полей в этой таблице;
· Условие отбора – служит для задания условий отбора записей;
· или – позволяет объединять условия отбора логической операцией ИЛИ. При этом условия отбора могут указываться в нескольких строках бланка запроса.
Включение отдельных полей в бланк запроса можно выполнить одним из следующих способов:
· перетащить поле из списка полей в крайнюю слева свободную клетку строки Поле;
· дважды щелкнуть по имени поля в списке полей ;
· щелкнуть в клетке строки Поле и из раскрывающегося списка выбрать нужное поле.
Включение в бланк запроса всех полей таблицы можно выполнить, если:
· дважды щелкнуть по имени таблицы и перетащить все выделенные поля в клетку строки Поле ;
Для очистки отдельных столбцов бланка запроса используется команда
Правка / Удалить столбцы
Для очистки всего бланка запроса служит команда
Правка / Очистить бланк
Формирование запроса на вывод полей из одной или нескольких таблиц
Перетаскиваются из списков полей в строку Поле только те поля, которые должны присутствовать в результирующей таблице запроса, или все поля всех таблиц, а затем в сроке Вывод на экран отмечаются флажками только нужные.
Формирование запроса с применением сортировки
Сортировка возможна по одному или нескольким полям одновременно. Для сортировки по нескольким полям поля располагаются в бланке запроса в том порядке, в котором требуется выполнять сортировку. В Access записи сортируются сначала по самому левому полю, затем по полю, расположенному в следующем столбце справа и т.д. Для задания порядка сортировки необходимо щелкнуть в строке Сортировка для поля, по которому сортируются записи, и из раскрывающегося списка выбрать – по возрастанию или по убыванию.
Формирование запроса с условиями отбора
Формирование запроса с вычисляемым полем
Для создания вычисляемого поля в пустую клетку строки Поле вводится имя вычисляемого поля с двоеточием, после которого – выражение. Например,
Если выражение сложное, то для его создания целесообразно использовать построитель выражений.
Формирование запроса с группировкой
Часто требуется в таблице видеть не все записи, а только итоговые значения по группам записей. Расчет итогов для некоторых полей групп производится с помощью статистических функций, которые были описаны выше.
Для создания запроса с группировкой выполняется следующее:
· перетаскивается в первую клетку строки Поле то поле, по которому производится группировка записей. Затем перетаскиваются в последующие клетки поля, по которым подводятся итоги;
· в строке Групповая операция для полей, по которым подводятся итоги, производятся щелчки и из раскрывающегося списка выбирается требуемая статистическая функция.
Формирование запроса с параметрами
Если необходимо часто выполнять один и тот же запрос выбора, меняя только в условиях отбора значения полей, то целесообразно создать запрос с параметрами. В строках Условие отбора, или для полей, играющих роль параметров, вводится в квадратных скобках текст приглашения на ввод интересующих пользователя значений этих полей. Этот текст будет выводиться в диалоговом окне Введите значение параметра при выполнении запроса.
7. 3. Создание перекрестного запроса
Создание перекрестного запроса с помощью Конструктора начинается с открытия окна Конструктора запроса. В этом окне в бланк запроса последовательно перетаскиваются:
· поля, значения которых будут заголовками строк перекрестной таблицы;
· поле, значения которого будут заголовками столбцов перекрестной таблицы;
· поле, по которому подводится итог с использованием статистической функции.
Затем выполняется команда Запрос / Перекрестный. В результате в бланке запроса появятся две новые строки: Групповая операция и Перекрестная таблица. В строке Перекрестная таблица для полей со значениями в роли заголовков строк выбирается из раскрывающегося списка значение Заголовки строк, а для поля со значениями в роли заголовков столбцов – Заголовки столбцов. В строке Групповая операция для поля, по которому подводится итог, из раскрывающегося списка выбирается необходимая статистическая функция.
7.4. Создание запросов действия
Формирование запроса на создание таблицы БД
После открытия окна конструктора запроса вводится команда
Запрос / Создание таблицы
В появившемся окне Создание таблицы указывается имя создаваемой таблицы и куда ее следует поместить – в текущую БД или в другую БД. Затем из списков полей перетаскиваются в бланк запроса поля, которые должны быть в этой таблице, и при необходимости задаются условия отбора записей. После выполнения запроса новая таблица будет в списке таблиц окна БД.
Формирование запроса на обновление полей таблицы БД
После открытия окна конструктора запроса вводится команда
В бланке запроса появится новая строка Обновление. Затем в бланк запроса перетаскиваются все поля таблицы, и для полей, подлежащих обновлению, в строке Обновление задаются выражения, значения которых будут новыми значениями обновляемых полей. При необходимости обновления значений полей только в некоторых записях задаются условия отбора записей.
Формирование запроса на добавление записей к таблице БД
При открытии окна конструктора запроса в качестве источника запроса указывается таблица, из которой добавляются записи в другую таблицу. Записи таблицы-источника должны содержать такие же поля, что и пополняемая таблица БД. В окне конструктора запроса вводится команда Запрос / Добавление. Появится диалоговое окно Добавление, в котором требуется указать имя пополняемой таблицы и где эта таблица находится – в текущей БД или в другой БД. Кроме того, в бланке запроса появится новая строка Добавление.
Затем перетаскиваются те поля из списка полей таблицы-источника, которые совпадают с полями пополняемой таблицы. Их имена Access автоматически укажет в строке Добавление как имена полей пополняемой таблицы.
Формирование запроса на удаление записей из таблицы БД
Если между таблицами установлена связь с обеспечением целостности данных, но без каскадного удаления записей, то прежде составляется запрос на удаление записей из подчиненной таблицы, а затем – из главной.
Если связь между таблицами с обеспечением целостности данных и с каскадным удалением записей, то создается запрос на удаление записей только из главной таблицы.
При составлении запроса на удаление записей из главной таблицы (в обоих случаях) в окне конструктора запроса вводится команда Запрос / Удаление.
В бланк за п роса перетаскиваются поля, участвующие в условиях отбора записей на удаление, и задаются условия отбора.
7.5. Выполнение и сохранение запроса
После формирования запроса его необходимо выполнить. Из окна Конструктора запроса это можно сделать, введя одну из команд:
Вид / Режим таблицы
7 . Формирование запросов в СУБД Access
Тренировочные задания
1. Создать многотабличный запрос на вывод из БД ДЕКАНАТ-БУХГАЛТЕРИЯ сведений о студентах следующей структуры:
В запросе записи рассортировать по алфавитному порядку ФИО.
2. Создать запрос на вывод из БД сведений о семейных студентах (которые женаты или замужем). Результирующая таблица запроса должна иметь следующую структуру:
3. Создать запрос на вывод из БД сведений о неуспевающих студентах (у которых оценка и по информатике, и по математике 2). Результирующая таблица запроса должна быть следующей структуры:
4. Создать параметрический запрос, дающий возможность выводить из БД сведения об успеваемости студентов любой группы. Результирующая таблица запроса должна быть следующей структуры:
5. Создать запрос, в котором рассчитать для каждого студента средний балл, полученный им в сессию. Результирующая таблица запроса должна быть следующей структуры:
6. Создать итоговый запрос, позволяющий для каждого студента рассчитать сумму всех начислений. Результирующая таблица запроса должна иметь следующую структуру:
7. Создать перекрестный запрос, в котором для каждой группы получить суммы начислений по их видам. Результирующая таблица запроса должна иметь следующую структуру:
Запрос сохранить с именем К_ОТЧЕТУ.
7. Формирование запросов в СУБД Access
6. В виде чего выводится результат запроса?
7. Какую структуру имеет бланк запроса, отображаемый в окне Конструктора запроса
8. Как создается вычисляемое поле в запросе?
9. Какая команда используется для выполнения запроса из окна Конструктора запроса?
10. Можно ли сохранить результат запроса?
Выполнение запроса
Запрос — это набор инструкций, которые можно использовать для работы с данными. Для выполнения этих инструкций нужно выполнить запрос. Помимо возврата результатов (которые можно сортировать, группировать или фильтровать), запрос также может создавать, копировать, удалять и изменять данные.
В этой статье объясняется, как запускать запросы, и представлен только краткий обзор различных типов запросов. В этой статье также рассмотрены сообщения об ошибках, которые могут возникнуть при запуске запросов различных типов, а также действия, которые можно предпринять для устранения этих ошибок.
В этой статье нет пошагових инструкций по созданию запросов.
Важно: Если база данных работает в отключенном режиме — режиме с уменьшенной функциональностью, который Access использует для защиты данных в определенных обстоятельствах, выполнить запросы на выполнение действий невозможно. Может появиться предупреждение в диалоговом окне или на панели сообщений.
Дополнительные сведения о отключенном режиме и том, как включить запросы на выполнение, см. в разделе «Выполнение запроса на выполнение действия».
В этой статье
Запуск запроса на выборку или перекрестного запроса
С помощью запросов на выборку и перекрестных запросов можно извлекать и представлять данные, а также поставлять формы и отчеты с данными. При запуске запроса на выборку или перекрестный запрос Access результаты отображаются в Режим таблицы.
Запуск запроса
Найдите запрос в области навигации.
Выполните одно из следующих действий:
Дважды щелкните запрос, который нужно выполнить.
Щелкните запрос, который вы хотите выполнить, и нажмите ввод.
Если запрос, который вы хотите выполнить, открыт в Конструктор, вы также можете запустить его, нажав кнопку «Выполнить» в группе «Результаты» на вкладке «Конструктор» в части Пользовательский интерфейс Microsoft Office Fluent.
Запуск запроса на действие
Существует четыре типа запросов на выполнение действий: запросы на удаление, запросы на обновление и запросы на таблицу. За исключением запросов на создание таблиц (которые создают новые таблицы), запросы на изменение данных в таблицах, на которых они основаны. Эти изменения нельзя отменить, например, нажав CTRL+Z. При внесении изменений с помощью запроса на изменение, который впоследствии вам больше не нужен, обычно необходимо восстановить данные из резервной копии. Поэтому перед выполнением запроса на действие следует всегда убедиться в том, что у вас есть свежий резервная копия данных.
Вы можете снизить риск выполнения запроса на действие, предварительно просмотрев данные, которые будут действовать. Это можно сделать двумя способами.
Просматривайте запрос на действие в представлении таблицы перед его запуском. Для этого откройте запрос в конструкторе, щелкните » Строка состояния Access», а затем выберите в shortcut-меню пункт «Таблица». Чтобы вернуться в конструктор, еще раз щелкните «Вид», а затем в shortcut-меню выберите пункт «Конструктор».
Измените запрос на выборку и запустите его.
Примечание: Обратите внимание на тип запроса на изменение (запрос на обновление, обновление, таблицу или удаление), с помощью чего вы сможете вернуться к этому типу после предварительного просмотра данных с помощью этого метода.
Запуск запроса на выборку в качестве запроса на выборку
Откройте запрос на действие в конструкторе.
На вкладке «Конструктор» в группе «Тип запроса» нажмите кнопку «Выбрать».
На вкладке Конструктор в группе Результаты нажмите кнопку Выполнить.
Запуск запроса
Когда вы будете готовы выполнить запрос на действие, дважды щелкните его в области навигации или щелкните его и нажмите ввод.
Важно: По умолчанию Access отключает все запросы на выполнение действий в базе данных, если не указано, что базе данных доверяете. Это можно сделать с помощью панели сообщений под лентой.
Доверие базе данных
На панели сообщений нажмите Параметры.
Откроется диалоговое окно Параметры безопасности Microsoft Office.
Выберите Включить это содержимое, а затем кнопку ОК.
Запуск запроса с параметрами
При запрос с параметрами запроса в качестве значения при его запуске. При добавлении значения запрос с параметрами применяет его в качестве критерия поля. Поле, к которому оно применяет критерий, заданное в конструкторе запросов. Если при запросе значение не задано, запрос с параметрами интерпретирует входные данные как пустую строку.
Запрос с параметрами всегда является другим типом запроса. Большинство запросов с параметрами — это запросы на выборку или перекрестные запросы, но запросы на приложения, таблицы и обновления также могут быть запросами с параметрами.
Запрос с параметрами нужно выполнить в соответствии с его другим типом, но в целом использовать следующую процедуру:
Запуск запроса
Найдите запрос в области навигации.
Выполните одно из следующих действий:
Дважды щелкните запрос, который нужно выполнить.
Выберите запрос, который вы хотите выполнить, и нажмите ввод.
Когда появится запрос параметра, введите значение, которое будет применяться в качестве критерия.
Запуск специального SQL для конкретного запроса
Существуют три основных типа запросов SQL-запрос: запросы на объединение, запросы к проходимой части и запросы определения данных.
Запросы на объединение объединяют данные из нескольких таблиц, но не так, как другие запросы. В то время как в большинстве запросов данные объединяются путем объединения строк, запросы на объединение объединяют данные путем их объединения. Запросы на объединение отличаются от запросов на объединение, так как они не изменяют таблицы. Запросы на объединение добавили строки в наборе записей, которые не сохраняются после закрытия запроса.
Запросы, которые передаются через базу данных, не обрабатываются обл. базами данных, который поставляется с Access; вместо этого они передаются непосредственно на сервер удаленной базы данных, который обрабатывает и передает результаты обратно в Access.
Запросы определения данных — это особый тип запросов, которые не обработать данные; вместо этого запросы определения данных создают, удаляют или изменяют другие объекты базы данных.
SQL запросы, специфичные для конкретного запроса, невозможно открыть в конструкторе. Их можно открывать только в SQL представлении или запускать. За исключением запросов определения данных, при SQL запроса открывается в представлении таблицы.
Запуск запроса
Найдите запрос в области навигации.
Выполните одно из следующих действий:
Дважды щелкните запрос, который нужно выполнить.
Щелкните запрос, который вы хотите выполнить, и нажмите ввод.
Устранение неполадок с сообщением об ошибке
В следующей таблице показаны распространенные сообщения об ошибках, с которыми вы можете столкнуться. Эти ошибки могут отображаться как сообщение в ячейке (вместо ожидаемого значения) или как сообщение об ошибке. В последующих разделах содержатся процедуры, которые можно использовать для устранения этих ошибок.
Примечание: Это содержимое этой таблицы не является исчерпывающим. Если оно не содержит полученное сообщение об ошибке, вы можете отправить отзыв с помощью формы в конце этой статьи, а также добавить сведения о сообщении об ошибке в поле комментария.
Сообщение об ошибке
Введите несоответствие в выражении.
Запрос может присоединяться к полям с разными типами данных.
Проверьте конструктор запросов и убедитесь, что тип данных в соединяемом поле одинаковый. Инструкции см. в разделе «Проверка полей, которые соединены» в запросе.
Это может произойти, если объект или база данных повреждены.
Сжатие и восстановление базы данных. Инструкции см. в разделе «Сжатие и восстановление базы данных».
Циклая ссылка, вызванная псевдонимом
Псевдоним, присвоенный полю, является компонентом выражения для этого поля.
Псевдоним — это имя, которое предоставляется любому выражению в строке «Поле» на сетке конструктора запросов, которое не является фактическим полем. Access назначает псевдоним за вас, если вы не сделаете этого самостоятельно; например, EXPR1. После псевдонима следует двоеточие (:),а затем — выражение. При запуске запроса псевдоним становится именем столбца в таблице.
Измените псевдоним. Инструкции см. в разделе «Изменение псевдонима поля».
Эта ошибка может возникать, если значение вычисляемого поля больше значения, разрешенного значением свойства FieldSize поля. Это также происходит, если знаменатель вычисляемого поля имеет нулевое значение (0).
Убедитесь, что знаменатель вычисляемой поля не дает нулевых значение (0). При необходимости измените свойство FieldSize.
Запись, на которая ссылается, удалена.
Если запись была случайно удалена, ее необходимо восстановить из резервной копии. Если удаление было преднамеренным, вы можете отклонять это сообщение об ошибке, нажав shift+F9, чтобы обновить запрос.
Проверка соединенных полей в запросе
Чтобы проверить типы данных полей в запросе, взгляните на исходные таблицы в Конструкторе и проверьте свойства полей, которые нужно проверить.
Откройте запрос в конструкторе. Соединения отображаются как линии, соединяющие поля в исходных таблицах. Обратите внимание на имена таблиц и полей для каждого из них.
В области навигации щелкните правой кнопкой мыши каждую таблицу, в запросе для которых есть одно или несколько полей, и выберите «Конструктор».
1. Соединенные поля с разными типами данных.
2. Щелкните таблицу правой кнопкой мыши и выберите «Конструктор».
Для каждого типа данных сравните значения в столбце «Тип данных» на сетке конструктора таблиц для полей, участвующих в этом соединитеке.
1. Проверьте тип данных для полей, которые соединены, в конструкторе таблицы.
Чтобы переключиться на таблицу, чтобы увидеть ее поля, щелкните вкладку с именем этой таблицы.
Сжатие и восстановление базы данных
Запуск сжатий и восстановления базы данных в Access может повысить производительность базы данных. Эта программа создает копию файла базы данных и, если он является фрагментированной, переумногует оголовье хранения файла базы данных на диске. После завершения процесса сжатия и восстановления сжатая база данных будет освободить место на диске и обычно меньше исходной. Часто сжатие базы данных помогает обеспечить оптимальную производительность приложения базы данных, а также устранять ошибки, которые возникают из-за проблем с оборудованием, перебоев в подаче электроэнергии или перебоев в подаче электроэнергии и схожих причин.
После завершения операции сжатия скорость запроса увеличивается, так как данные, на которые они были перезаписаны, перезаписываются в таблицы на нестрогих страницах. Сканировать несколько страниц гораздо быстрее, чем просматривать фрагментированное страницу. Запросы также оптимизированы после каждого сжатия базы данных.
В ходе сжатия можно использовать исходное имя для сжатого файла базы данных или создать отдельный файл с другим именем. Если вы используете то же имя и база данных успешно сжата, Access автоматически заменит исходный файл сжатой версией.
Настройка параметра для автоматизации этого процесса
На вкладке Файл выберите пункт Параметры, чтобы открыть диалоговое окно Параметры Access.
Щелкните «Текущая база данных» и в меню «Параметры приложений»нажмите кнопку «Сжать при закрытии».
Это приводит к автоматическому сжатию и восстановлению базы данных при каждом ее закрытии.
Сжатие и восстановление базы данных вручную
Нажмите кнопку «Работа с базами данных> сжатие и восстановление базы данных.
Изменение псевдонима поля
Откройте запрос в конструкторе.
В сетке конструктора запросов наименуйте поля с псевдонимами. В конце имени поля будет двоеточие, как в поле «Имя»:
Проверьте каждый псевдоним, чтобы убедиться, что он не совпадает с именем поля, которое является частью его выражения. В этом случае измените псевдоним.
