Как писать запросы в access. Создание простого запроса. Создание запроса в режиме конструктора

Лекция 16. Создание запросов средствами MS Access 2000

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

§ соединение данных нескольких таблиц;

§ отображение только требуемых полей;

§ формирование сложных критериев отбора записей;

§ вычисления с использованием данных из исходных таблиц;

§ группировку информации по каким-либо критериям;

§ модификацию данных в таблицах.

MS Access предоставляет удобные механизмы быстрого создания запросов без особой подготовки, тогда как в других СУБД часто требуются знание языковых средств, для создания запросов. Для создания запросов MS Access предлагает следующие средства:

§ Конструктор запросов – средство конструирования запросов с использованием QBE (Query by example – запрос по образцу), требует минимальных знаний. Средство, уступающее режиму SQL .

§ Простой запрос – позволяет быстро создавать запросы на основе одной или нескольких таблиц, в том числе итоговые запросы.

§ Перекрестный запрос – позволяет создавать результирующие таблицы на основе результатов расчетов, полученных при анализе группы таблиц;

§ Запрос на повторяющиеся записи – средство создания запросов для выявления повторяющихся записей, выполнено в виде мастера.

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

Создание запросов начинается с выбора категории «Запросы» окна диалога «Базы данных». Затем, кнопкой «Создать» может быть вызвано окно диалога «Новый запрос», в котором выбирается один из вариантов создания запроса (рисунок 16.1).

Рисунок 16.1 - Диалоговое окно «Новый запрос»

Создание простых запросов с помощью мастера

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

Рисунок 16.2 - Окно мастера создания запросов

Процесс создания запроса с помощью мастера сводится к следующим шагам:

§ Запускается мастер создания простых запросов, например командой – Создание запроса с помощью мастера

§ в раскрывающемся списке «Таблицы и запросы» последовательно выбираются таблицы или запросы, информация из которых необходима пользователю, а затем, из списка «Доступные поля» в список «Выбранные поля» перемещаются требуемые поля (рисунок 16.2).

§ На втором шаге работы мастера определяется тип запроса: подробный или итоговый. Если выбран итоговый запрос, то необходимо определить итоговые операции над полями запроса: Max , Min , Sum , Avg или Count .

§ На третьем шаге указываем имя запроса и один из вариантов действий: открыть запрос для просмотра данных или изменить макет запроса , в первом случае результаты запроса будут выданы на экран, а во втором, запрос откроется в режиме конструктора.

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

Конструктор редко используется для создания запросов, чаще используется как средство модификации уже существующих. Конструктор запросов использует QBE (Query by example – запрос по образцу) позволяющий сформировать относительно сложные запросы на основе специального бланка, заполнение которого позволяет сформировать запрос, безусловно, этот способ уступает непосредственному использованию конструкций языка SQL , но требует значительно меньших знаний.

При необходимости можно использовать язык SQL , для этого необходимо перейти в режим SQL , переход выполняется командой контекстного меню конструктора запросов – «Режим SQL ».

Окно конструктора (рисунок 16.3) разделено на две части. Верхняя часть предназначена для расположения таблиц, на основе которых формируется запрос. Добавление таблиц в запрос осуществляется командой панели инструментов - Отобразить таблицу .

Рисунок 16.3 - Окно конструктора запроса. Пример ввода условия.

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

Включение новых полей в запрос выполняется простым перетаскиванием имени поля из списков полей таблиц (верхней части конструктора) в требуемый столбец строки «Поле:», при этом имя таблицы определяется автоматически.

При необходимости сортировки по какому-либо полю в столбце этого поля устанавливается свойство «Сортировка» в значение По возрастанию или По убыванию , выбором из раскрывающегося списка.

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

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

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

Количество строк в QBE может варьироваться в зависимости от типа создаваемого запроса, например при использовании группировки появится новая строка – «Групповая операция».

Установка критериев отбора записей

Определение критериев выборки в запросах является одной из основных задач. Чтобы сформировать требуемый пользователю набор данных необходимо определить значения условий отбора в строке «Условие отбора:» QBE. В условиях отбора можно использовать логические операторы (or , and , not ), операторы сравнения (<, >, <=, >=, <>, = ), а также операторы Between , In и Like .

Можно задать несколько условий отбора, соединенных логическим оператором или ( or ) , для некоторого поля одним из двух способов:

1. ввести всё условие в одну ячейку строки «Условие отбора:», с использованием логическогооператораили (or ).

2. одну часть условия указать в ячейке строки «Условие отбора:», а вторую часть условия вписать в нижнюю ячейку строки «Или:».

Оба варианта будут эквивалентными, второй вариант изображен на рисунке 16.4. Результатом этого запроса будет список студентов имеющих оценки 4 или 5.

Рисунок 16.4 - Использование логического оператора или ( or ) .

Логическая операцияи ( and ) используется в том случае, когда должны быть выполнены оба условия одновременно и только в этом случае запись будет включена в результирующий набор данных. Например, условие >2 and <5 даст список студентов имеющих оценки 3 и 4.

Логическая операция and может быть использована не только для одного поля, но и для нескольких полей, условия в которых должны выполниться одновременно, однако, в этом случае она используется неявно. На рисунке 16.5 показано неявное использование логической операции and . В результате такого запроса будут выданы все студенты, проживающие в городе «Уфа» и имеющие оценку 5. Фактически, если заглянуть в код SQL (в режиме SQL ) то можно увидеть следующее условие:

[Студенты].[Город] = “Уфа” and [Студенты и занятия].[Балл] = “5”

Рисунок 16.5 – Пример использования логической операции и ( and )

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

Оператор B etween позволяет задать диапазон значений, например диапазон от 10 до 20 может быть определен следующим образом:

between 10 and 20

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

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

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

* - обозначает любое количество символов;

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

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

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

Like П*ов

Особый подход к работе с полями типа дата. Значение даты в условиях отбора должно быть заключено в символы #. Например:

>#31.12.1996#

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

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

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

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

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

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

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

Надбавка: [ Оклад]*0.15

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

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

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

Допустим, существует запрос «Отпуск товаров со склада», который содержит поля «КолОтп» (количество отпущенное) и «ЦОтп» (цена отпускная), требуется определить сумму продажи. Открываем запрос в режиме конструктора, переходим на пустой столбец и запускаем построитель выражений командой «Построить», контекстного меню . Находим в среднем списке имя поля «ЦОтп» и дважды щелкаем по нему мышкой. Нажимаем кнопку «*» и повторяем ту же последовательность действий для поля «КолОтп». Получаем требуемое выражение (рисунок 16.6).

Рисунок 16.6 - Окно построителя выражений

Прежде чем применить созданное выражение, рекомендуется определить имя вычисляемого поля, например «Сумма»:

Сумма: [ЦОтп]*[КолОтп]

После нажатия «ОК» полученный результат будет помещен в новый столбец бланка QBE.

Контрольные вопросы

1. Что такое запрос?

2. В чем заключаются функции запроса?

3. Какие способы создания запросов существуют в MS Access ?

4. Чем отличаются возможности различных способов создания запросов?

5. Как создается запрос с помощью мастера?

6. Что представляет собой бланк запросов?

7. Как сформировать условия отбора записей?

8. Назовите основные логические операторы.

9. Что представляет собой оператор b etween?

10. В чем назначение оператора Like ?

11. Как создаются вычисляемые поля?

12. Преимущества и недостатки конструктора запросов?

Задания для самостоятельной работы

Задание 1. Создайте запрос «Просроченные книги», по следующему описанию:

1. В окне «Базы данных» выбираем категорию «Запросы».

2. Открываем окно мастера командой «Создание запроса с помощью мастера».

3. В раскрывающемся списке «Таблиц и запросы» выбираем таблицу «Книги». Из списка «Доступные поля» в список «Выбранные поля» перемещаем поля «Название» и «Автор».

4. В раскрывающемся списке «Таблиц и запросы» выбираем таблицу «Экземпляры». Из списка «Доступные поля» в список «Выбранные поля» перемещаем поля «Инвентарный номер», «Дата возврата» и «Наличие».

5. В раскрывающемся списке «Таблиц и запросы» выбираем таблицу «Читатели». Из списка «Доступные поля» в список «Выбранные поля» перемещаем поля «Номер_ЧБ», «ФИО», «Тел_дом», «Тел_раб». Нажимаем кнопку «Далее».

6. На данном этапе выбираем подробный запрос и нажимаем кнопку «Далее».

7. Указываем имя запросаПросроченные_книги и нажимаем кнопку «Готово».

8. Откроем запрос«Просроченные_книги» в режиме конструктора, нажав кнопку «Конструктор» (рисунок 16.7).

9. В бланке запроса в строке «Условие отбора» для поля «Дата_возврата» укажем значение < Now ().

10. В бланке запроса в строке «Условие отбора» для поля «Наличие» установим значение нет и снимем флажок «Вывод на экран».

11. Закроем окно конструктора.

Таким образом, отбираемые записи должны удовлетворять сразу двум условиям: дата возврата должна быть меньше текущей и книга должна отсутствовать в библиотеке.

Задание 2. Создайте простой запрос, на получение информации о том, к каким предметным областям относятся книги.

Задание 3. Из запроса «Просроченные_книги» извлеките информацию о должниках (Номер читательского билета, ФИО, Домашний и рабочий телефоны)

Задание 4. Создайте запрос на получение списка книг одного автора.

Сегодня мы начнем рассматривать такое приложение как — Microsoft Access 2003 , которое умеет создавать собственные базы данных (формат mdb ), а также создавать клиентские приложения к существующим базам на основе MS SQL Server. Темой сегодняшней статьи будет создание новых запросов из Access, имеется в виду, как простые запросы, так и различные функции, представления и процедуры. Под запросом здесь понимается объекты базы данных.

О Microsoft Access

Microsoft Access – программный продукт компании Microsoft, являющейся реляционной СУБД. Имеет огромные возможности при организации базы данных, создания отдельного приложения, которое может взаимодействовать с множеством других СУБД. Наиболее часто встречающееся решение клиент-сервер, где в качестве клиента выступает приложение, написанное в Access (язык VBA, формы и многое другое ), а сервером является СУБД Microsoft SQL Server. Однако Access поддерживает и взаимодействие с другими СУБД, например, такими как: MySql или PostgreSQL. О Access можно разговаривать долго, но цель сегодняшней статьи именно создание запросов (объектов ) из Access.

Переходим к практике и начнем с простой базы mdb, т.е. как там создать эти самые запросы.

Создание запросов в Microsoft Access 2003 — база MDB

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

Примечание! Имеется в виду, что база у Вас уже есть.

И перед Вами откроется новое окно выбора типа запроса

Давайте рассмотрим каждый из этих типов запросов подробней.

Типы запросов в Access 2003 — база MDB

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

Если не нравится это делать в графическом редакторе, то можете переключиться в режим SQL, для этого нажмите пункт меню «Вид» , затем «Режим SQL» .

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

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

Повторяющееся записи – это как видно из названия поиск повторяющихся записей.

Записи без подчиненных – это поиск тех записей, которые отсутствуют в той или иной таблице.

С базами mdb достаточно, так как их редко используют на предприятиях, обычно используют следующую схему – пишут отдельный клиент, а все данные хранят на сервере с помощью СУБД в нашем случае — это MS SQL Server, а клиент Access (.adp).

Создание запросов в Microsoft Access 2003 — база MS SQL Server

Давайте рассмотрим создание новых запросов из клиента Access на базе MS SQL Server (подразумевается, что клиент adp и база данных на основе MS SQL Server у Вас уже есть ).

Примечание! Данная статья не подразумевает изучение sql, поэтому на момент прочтения этой статьи Вы уже должны понимать основы sql и понятие основных объектов в базе данных таких как: представление, функция, процедура. Если Вы совсем новичок в этом, то сначала, конечно же, рекомендуется освоить SQL, так как многие термины ниже Вам будут не понятны. Рекомендуемые статьи:

• Что такое представления VIEWS в базах данных? И зачем они нужны?

Начало все такое же, открывает проект, затем нажимаем на объекты «Запросы» и жмем кнопку «Создать» .

И теперь подробней.

Типы запросов в Access 2003 — база MS SQL Server

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

SELECT * FROM my_test_tabl_func(par1, par2 ……)

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

Затем если Вы хотите добавить входящие параметры можете просто в условие ставить знак @ и название переменной, например, так:

SELECT * FROM table WHERE kod = @par

После на панели в свойствах функции

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

Конструктор представления – это создание обычного представления, в просто народе «Вьюха» .

Конструктор сохраненной процедуры – создание процедуры с помощью конструктора, принцип такой же, как и в вышеупомянутых функциях. Напомню процедура — это набор sql операторов, как на выборку, так и на изменение данных.

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

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

Ввод табличной функции – это создание функции, которая вернет набор записей. Похожа на встроенную функцию.

Для того чтобы в клиенте access их можно было отличать, у них разные иконки, те самые которые Вы видите при создании того или иного объекта.

При создании всех этих объектов они сохраняются на сервере, и Вы их можете использовать не только из своего adp проекта, но и из других клиентов.

Конечно же, все эти объекты Вы можете создать и на сервере с помощью, например, Enterprise Manager (устарел, сейчас SQL Server Management Studio ), но мы сегодня рассматриваем возможность создания этих объектов из access клиента.

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

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

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

Язык запросов и условия отбора.

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

Это все равно, что дать библиотекарю задачу подобрать книжки на какую-то тематику, - тема будет критерием, раздел или стеллаж будет полем в котором будет осуществляться поиск.

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

> больше

< меньше

>= больше или равно

<= меньше или равно

<> неравно

Так же нужны логические функции:

AND – (И) если нужно, чтобы выполнялись сразу несколько условий

OR – (ИЛИ) если нужно чтобы выполнялось хоть одно условие

NOT – (НЕ) если нужно отобрать записи, исключив подходящие по критерию

Пример условия отбора к полю дата поступления может быть таким

Like 12.12.2016 - будут отобраны строчки таблицы в которых дата будет 12.12.2016.

<> 12.12.2016 - будут отобраны строчки таблицы в которых дата будет 11.12.2016 и раньше, 13.12.2016 и позже.

> 12.12.2016 - будет отобраны строки в которых дата начинается с 13.12.2016.

Если нам нужно усложнить отбор, то можно использовать логические функции И, ИЛИ и НЕ.

К примеру, если мы хотим выбрать дату поступления товара с 10.10.2015 до 12.12.2016, то если мы запишем >=10.10.2015, то будут отобраны даты и позже 12.12.2016, поэтому используем логическую функцию И.

>=10.10.2015 AND <= 12.12.2016

Как создавать запрос с помощью конструктора

1. Нажимаем на вкладку «Создать», а затем на кнопку «Конструктор запросов».

(Рисунок 1)

2. В окне «Добавление таблицы» выбираем таблицу и нажимаем «Добавить», а затем «Закрыть».

(Рисунок 2)

3. Выбираем имя таблицы или таблиц.

(Рисунок 3)

4. Выбираем имена столбцов, которые нам нужно получить в отчете о запросе.

(Рисунок 4)

5. Пишем условие выбора к полям (столбцам) таблицы, указывая критерий по которому будет производиться отбор.

(Рисунок 5)

6. Нажимаем серый крестик, чтобы закрыть конструктор запросов, если нужно записываем название запроса, нажимаем «ок»

(Рисунок 6)

7. Дважды кликаем на запросе, чтобы увидеть таблицу с отчетом о результатах запроса.

(Рисунок 7)

Пример запроса.

Поиск товаров поступивших 12.09.2015 или в диапазоне дат между 01.01.2016 и 12.12.2016.

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

(Рисунок 8)

Если запрос не работает

1. Запрос введен правильно, но не работает, то либо таких данных нет, либо тип данных в данном столбце – текстовый.

2. Команды в запросе введены слитно, без пробелов.

3. Если очень много текста, а критерий точно задать нельзя, то можно использовать * (звездочку), которая означает любое количество символов. Так можно в тексте найти человека по фамилии *Иванов*. Так как поиск осуществляется по кодам символов, то текст должен полностью соответствовать. По запросу Like *Иванов* в итоговую таблицу попадут все Ивановичи, Ивановы, Поливановы, так как мы задали маску по которой будут отбираться записи (строчки таблицы) и легко видеть, что в фамилии Иванович есть некоторое количество символов после Иванов, а в фамилии Поливанов, есть некоторое количество символов до Иванов. Так же следует обратить внимание на кавычки, так как звездочки должны стоять до и после кавычек обозначающих, что внутри них текст - Like *”Иванов”*

4. Выбор типа запроса осуществляется на ленте конструктора - запрос на удаление, добавление, создание таблицы.

К одной из таких программ относится MS Access.

Понятие о запросах

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

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

Классификация запросов

Выделяют следующие виды запросов:

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

Запросы на выборку

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

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

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

Создание запросов в MS Access данного вида предполагает, что их можно создать вручную или при помощи "Мастера создания запросов".

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

Для перехода в режим конструктора в Access 2013 нужно в области навигации кликнуть контекстной кнопкой мыши на имени формы и выбрать "Конструктор". Нажав ALT+F8, можно вызвать "Список полей", из которых поля можно перетащить непосредственно в форму.

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

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

• создание новых таблиц;
• добавление записей в исходные таблицы;
• изменение данных в исходных таблицах;
• удаление записей из исходных таблиц.

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

Создание новых таблиц используется при архивировании данных, проведении бэкапов или экспорте данных.

Разновидностью запросов на изменение являются запросы на обновление, при использовании которых обновляются абсолютно все записи, которые удовлетворяют какому-либо заданному условию. Если в комплекс данных необходимо внести изменения, то используют данную разновидность.

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

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

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

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

Перекрестная таблица создается при указании заголовков строк, столбцов, значений и групповой операции.

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

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

SQL-запросы

В Access 2013 и других версий запросы выполняются при помощи языка структурированных запросов SQL.

Для создания SQL-запросов необходимо перейти в режим конструктора, затем на кнопке "Вид" выбрать "Режим SQL". В результате появится диалоговое окно, в котором в поле Select вводим названия столбцов, а в поле From - то, что будет выводиться в строках, например названия поставщиков (если первый столбец "Название").

В Access используется не чистый SQL, а его диалект Jet-SQL. Основными инструкциями для запросов в этом языке являются: SELECT, по которому осуществляется выборка из записей по определенным условиям (названия полей исходных таблиц, переносящихся в результирующую таблицу), UPDATE - используется с целью редактирования записей, DELETE - для удаления каких-либо указанных записей, CREATE - для создания новых объектов БД. В также используются TRANSFORM для построения перекрестных запросов, WITH OWNER-ACCESS OPTION для создания специальных запросов пользователем, не имеющем доступа к таблицам, к которым должен быть доступ у этого запроса, IN (для обеспечения связи с удаленной БД), DISTINCTROW (создание запроса с возможным объединением данных). Также могут применяться итоговые функции SQL, встроенные функции Access и VBA.

В заключение

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

Губская Ольга Васильевна

Тема урока : Создание запросов в СУБД Access

11 класс

Цель урока : Повторить основные понятия темы Базы данных, Системы управления базами данных; познакомить учащихся с различными способами создания запросов в БД; научить отбирать записи по нескольким условиям, использовать выражения в запросах

Тип урока, продолжительность урока.

Комбинированный урок, 45 минут.

Средства обучения .

Технические средства: - мультимедийный проектор.

Программные средства обучения: - программа MS Access 2007, MS PowerPoint 2007.

Информационные средства: - созданная учителем база данных «Школьная библиотека», презентация «Создание запросов в СУБД Access 2007», задания для компьютерного практикума и самостоятельной работы; тестовые задания.

План урока:

Организационный момент (1 минута )

Постановка темы и целей урока (1 минута)

Фронтальный опрос (7 минут)

Ознакомление с новым материалом (15 минут)

Первичное осмысление и применение изученного.
- Компьютерный практикум (10 минут)
- Самостоятельная работа, тестирование по теме (8 минут )

Подведение итогов урока, выставление оценок (2 минуты)

Домашнее задание (1 минута )

Ход урока

1. Организационный момент

2. Постановка темы и целей урока

Слайд 1

Мы продолжаем изучать базы данных, на прошлых уроках вы познакомились с понятием базы данных, с понятием СУБД, с различными классификациями баз данных, с полями и записями, с типами данных и с операциями над информацией в базах данных. Тема сегодняшнего урока: «Создание запросов в СУБД Access 2007»

Слайд 2

Итак, на этом уроке мы:

1 . Повторим основные понятия темы Базы данных, Системы управления базами данных;

2. Познакомимся с различными способами создания запросов в БД;

3. Научимся отбирать записи по нескольким условиям, использовать выражения в запросах.

3. Фронтальный опрос

Слайд 3

Чтобы повторить основные понятия темы, я предлагаю вам «прогуляться» по полю понятий. Выбираете номер овала, появляется термин и вы формулируете соответствующее правило. (Приложение 1)

4. Ознакомление с новым материалом

Вы научились создавать таблицы, формы. На очереди запросы. Так что же такое запрос?

Слайд 4

Запрос – это требование на отбор записей, хранящихся в таблицах или на выполнение определённых действий с ними.

Запрос это временная таблица. Это значит, что данные в запросах не хранятся постоянно, а только временно вызываются из таблицы по заданному шаблону, в момент активизации запроса. Поэтому в базе данных постоянно сохраняется только шаблон вызова данных, а не сама информация. Следовательно, запрос не занимает много места в БД.

Запросы позволяют :

выбирать нужные поля из разных таблиц БД;

фильтровать и сортировать данные;

производить расчёты;

группировать записи, рассчитывая различные итоговые значения;

вносить изменения сразу в несколько записей;

создавать таблицы как результат запроса.

Слайд 5

Какими бывают запросы?

Запрос на выборку. Осуществляет загрузку и отображение записей в режиме таблицы.

Запрос на изменение. Выполняет операции с записями, удовлетворяющими заданным критериям.

а) запрос на удаление (удаляет заданные записи из таблицы);

б) запрос на обновление (изменяет соответствующие записи таблицы);

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

Запрос с параметрами. П осле запуска на выполнение просит ввести значения определенных параметров.

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

Слайд 6

Способы создания запросов

SQL;

Конструктор;

Мастер.

Существует универсальный язык запросов, на котором формулируются запросы во многих СУБД. Он называется SQL (Structured Query Language [ strʌk.tʃəd ˈkwɪə.ri ˈlæŋɡwɪdʒ ]) – структурированный язык запросов. В большинстве современных СУБД существуют вспомогательные средства для составления запросов, позволяющие обходиться без изучения SQL . В MS Access это конструктор запросов .

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

Слайд 7

Итак, запрос на выборку - это производная таблица , которая содержит те же структурные элементы, что и обычная таблица (столбцы-поля и строки), и формируется на основе фактических данных системы.

Например, есть база данных Школьная библиотека, в которой создана таблица Книги. Запрос на выборку записей по полям Автор, Название, Год издания будет выглядеть как на слайде.

Рассмотрим процесс создания этого запроса с помощью Мастера.

Слайд 8

1 шаг – на вкладке Создание выбираем Мастер запросов.

2 шаг – в диалоге Новый запрос выбираем Простой запрос à ОК

Слайд 9

3 шаг – в диалоге Создание простых запросов выбираем из списка таблицу

Слайд 10

4 шаг – перемещаем из списка Доступные поля в список Выбранные поля нужные имена полей à нажимаем кнопку Далее >

Слайд 11

5 шаг – выбираем один из вариантов отображения записей с данными: подробный (без выполнения операции сведения) или итоговый с использованием сведения (нахождение суммы, среднего значения, минимума, максимума для определенных записей) à нажимаем кнопку Далее >

Слайд 12

6 шаг – вводим имя запроса à нажимаем кнопку Готово.

Слайд 13

Теперь рассмотрим пример создания запроса при помощи Конструктора.

1 шаг – на вкладке Создание выбираем Конструктор запросов.

2 шаг – в диалоге Добавление таблицы выбираем таблицы, поля из которых будут использоваться в запросе à Добавить à Закрыть

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

Поле , в которой будут отображаться имена выбранных полей;

Имя таблицы , в которой будет отображаться имена таблиц, в состав которых входят соответствующие поля;

Сортировка , в которой при необходимости можно выбрать вид сортировки;

Вывод на экран , содержащая флажок, установленная метка которого свидетельствует о том, что данное будет отображаться в запросе;

Условие отбора , которая может содержать условие – выражение для выборки данных из соответствующего поля;

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

Слайд 14

3 шаг – выбираем нужные поля.

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

Слайд 15

4 шаг – вводим условие отбора и нажимаем кнопку Выполнить .

Слайд 16

5 шаг – закроем вкладку запроса à в окне сохранения структуры запроса выберем Да à введем имя запроса à ОК

Слайд 17

Логическое выражение, представляет собой операцию отношения, при записи которого используются символы >,<,=, ≠, ≤, ≥. Условие отбора одного поля может быть составным, при этом два логических выражения соединяются знаком логической операции.

Рассмотрим несколько примеров

Слайд 18

Из таблицы Книги выбрать рассказы А.П.Чехова. Условий отбора будет два: по полю Автор и по Полю Наименование. Если условия отбора записаны в одной строке, то они соединяются операцией «и».

Слайд 19

Запрос на выборку: Книги, изданные в 1987 году, или в которых больше 300 страниц. Условия отбора по полям Год издания и Количество страниц записываются на разных строках, поэтому они соединяются операцией «или».

Слайд 20

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

Слайд 21

Рассмотрим некоторые условия отбора.

Слайд 22

И примеры использования символов в шаблонах.

5. Первичное осмысление и применение изученного .
- Компьютерный практикум

1) Создание запроса в режиме Мастера

Создать запрос на выборку фамилии, имени и класса обучения: откройте базу данных Школьная библиотека откройте таблицу Школа ознакомьтесь с содержанием таблицы  выберите кнопку Мастер запросов группы Другие вкладки Создание  выберите в окне Новый запрос в списке видов мастеров запросов Простой запрос  выберите в списке Таблицы и запросы окна Создание простых запросов таблицу Школа переместите из списка Доступные поля в список Выбранные поля имена полей Фамилия , Имя , Класс  Далее Введите имя запроса Класс  Готово откройте запрос в режиме Конструктора задайте сортировку по полю Класс посмотрите как выглядит запрос в режиме SQL . Для этого на вкладке запроса вызовите контекстное меню Режим SQL .

2) Создание запроса в режиме Конструктора

Создать запрос на выборку учащихся, у кого номер класса кратен 2 и он живет на улице Пушкина или Ленина: выберите кнопку Конструктор запросов группы Другие вкладки Создание  добавьте таблицу Школа закройте окно Добавления таблицы  в макете таблицы двойным щелчком по полю переместите в бланк запроса поля Фамилия , Имя , Улица , Класс в строку Условие отбора под полем Улица введите Пушкина Оr Ленина , под полем Класс введите 8 Оr 10 нажмите кнопку Выполнить  закройте вкладку 8 класс.

3) Создание запроса с параметрами ( в режиме Конструктора )

Из таблицы Школа возьмите поля Фамилия , Имя , Класс , Хобби . В условие отбора поля Хобби введите =[Введите хобби] нажмите кнопку Выполнить введите значение параметра «футбол»  ОК сохраните запрос под именем Футбол .

4) Создание запроса с вычислением (в режиме Конструктора)

Из таблицы Оценки возьмите поля Фамилия , І_семестр , ІІ_семестр в строке Поле в столбце, следующем за столбцом ІІ_семестр , введите выражение Годовая оценка: ([І_семестр]+[ ІІ_семестр])/2 нажмите кнопку Выполнить  закройте вкладку запроса, сохранив его под именем Годовая.

- Самостоятельная работа

По таблице Школа создайте запросы:

С ?

Кто не занимается тяжелой атлетикой?

Кто увлекается аэробикой или футболом?

Кто учится в старших классах?

Кто из мальчиков живет на улице Фрунзе?

Кто из мальчиков с голубыми глазами занимается каратэ?

-Тестирование по теме

Тест:

1. База данных служит для:

хранения и упорядочения информации

ведения расчетно-вычислительных операций

обработки текстовой документации

обработки графической информации

2. Длина поля измеряется в:

миллиметрах

пикселях

байтах

символах

3. Записями считаются:

заголовки

столбцы

строки

таблицы

4. Текстовое поле позволяет занести информацию до:

255 символов

20 символов

65536 символов

1 символа

5. Логические данные - это:

денежные данные

текст

одно из двух значений

числа

6. Свойство автоматического наращивания имеет поле:

числовое

счетчик

МЕМО

логическое

7. Ключевое поле должно быть:

непременно счетчиком

обязательно числовым

уникальным

не должно содержать длинных записей

8. Укажите неправильный тип данных в Access

текстовый

параметрический

числовой

логический

9. Что не является элементом СУБД Access?

модули

запросы

макросы

таблицы

ключи

отчеты

запросы

10. Для чего предназначены запросы?

для хранения данных базы

для отбора и обработки данных базы

для ввода данных базы и их просмотра

для автоматического выполнения группы команд

для вывода обработанных данных базы на принтер.

11. Запишите, какую маску для поиска надо использовать, чтобы найти:

слова: кит, кот, кут ______

числа: 123, 1256000, 12,7 ______

даты: 12.12.2011, 03.12.20013, 26.12.2014 ______.

12. Какие условные выражения надо использовать, чтобы найти:

все числа, не меньшие 105 ______

все слова, кроме физика ______

все даты, ранее 12.11.2013 ______.

6. Подведение итогов урока, выставление оценок

7. Домашнее задание: 3.8, № 3, 6

Ответы:

- Самостоятельная работа

У кого фамилия начинается на букву С ? (Запрос возвращает 4 записи) (1 балл)

Кто учится в старших классах? (Запрос возвращает 2 записи) (1 балл)

У кого группа здоровья основная и он занимается футболом или каратэ? (Запрос возвращает 5 записей) (2 балла)

Кто из мальчиков живет на улице Фрунзе? (Запрос возвращает 2 записи) (2 балла)

Кто из мальчиков с голубыми глазами занимается каратэ? (Запрос возвращает 2 записи) (2 балла)

-Тестирование по теме

>=105

Not физика

< 12.11.2013

16-13 ответов – 4 балла

12-9 ответов – 3 балла

8- 5 ответов – 2 балла

4-1 ответ – 1 балл