Как сделать запрос на добавление в access?

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

Получение начальных значений типа Счетчик, отличных от 1

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

Как вы узнали в главе 2, программа Access всегда генерирует значения типа Счетчик, начиная с 1. (Единственное исключение — применение случайных чисел или кодов репликаций, два редких варианта, описанных в разд. «Применение поля типа Счетчик без раскрытия реального размера вашей таблицы» главы 2.) Но существует множество причин, вызывающих желание изменить такое поведение программы. Например, компании Boutique Fudge хочется начать нумерацию своих клиентов с 1000, а номеров товаров — с 5000, или начать нумеровать свои заказы с 10 000. Эти схемы нумерации часто облегчают бухгалтерский учет. Они позволяют сохранять постоянным количество цифр в значениях типа Счетчик, помогают разделить коды в двух разных таблицах и не смущаться, сообщая клиенту о том, что он сделал заказ номер 1.

К счастью, существует (немного неуклюжий) способ обмануть систему и заставить Access начать отсчет с любого нужного вам числа. Для того чтобы сделать то, что вы не можете сделать сами, применяется запрос на добавление. Просто вставляется запись с заданным значением типа Счетчик. После того как запись создана, программа Access наращивает значения, начиная со вставленного вами значения. Таким образом, если вы добавили запись типа Счетчик со значением 999, Access присвоит следующей записи значение 1000 и т. д.

Вот как это делается.

1. Создайте новую таблицу (Создание > Таблицы > Конструктор (Create > Tables > Table Design)).

Эта таблица будет храниться всего несколько минут.

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

Обычно у него имя Код (ID).

3. Измените тип данных поля на Числовой (вместо Счетчик) и убедитесь в том, что размер поля — Длинное целое (Long Integer) (стандартный выбор).

4. Щелкните правой кнопкой мыши заголовок таблицы и выберите Режим таблицы.

Сохраните таблицу, когда программа Access напомнит об этом, но не беспокойтесь о ее имени, имя Табляца1 вполне подходит. Когда Access предложит создать первичный ключ, щелкните мышью кнопку Нет.

5. В Режиме таблицы введите в поле с типом данных Числовой временной таблицы значение, на 1 меньшее того, которое вы хотите использовать в качестве начального в поле с типом данных Счетчик.

Если вы хотите начать со значения 100 в поле с типом Счетчик, введите в поле с типом Числовой значение 99. Закройте таблицу.

6. Создайте новый запрос (Создание > Другие > Конструктор запросов (Create > Other > Query Design)).

В появившемся диалоговом окне Добавление таблицы (Show Table) выберите созданную вами временную таблицу (Таблица!.) и щелкните мышью кнопку Закрыть (Close).

7. Выберите на ленте Работа с запросами J Конструктор > Тип запроса > Тип запроса:

добавление (Query Tools | Design > Query Type > Append) для изменения типа запроса на запрос на добавление.

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

8. Дважды щелкните кнопкой мыши поле, которое вы добавили в вашу таблицу (например Код (ID)).

Программа Access задаст в строке Добавление то же имя, это как раз то, что надо.

9. Выберите на ленте Работа с запросами | Конструктор > Результаты > Выполнить

(Query Tools | Design > Results >Run).

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

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

Начиная с этого момента и далее, значения типа Счетчик будут увеличиваться, начиная с добавленного значения.

11. Удалите временную таблицу, созданную в пункте 1, поскольку она вам больше не нужна.

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

Создание запросов в базе данных Access 2007

Учебная дисциплина
Информатика и компьютерная техника
Модуль 2. Офисные прикладные программы Microsoft Office 2007

2.4. Microsoft Access 2007

2.4.5. Создание запросов и поиск информации в базе данных

В СУБД Access 2007 можно создавать queries для отображения требуемых полей из записей одной или нескольких таблиц.

В СУБД Access 2007 применяются различные типы запросов: на выборку, на обновление, на добавление, на удаление, перекрестный query, выполнение вычислений, создание таблиц. Наиболее распространенным является query на выборку. Применяются два типа запросов: query по образцу (QBE) и query на основе структурированного языка запросов (SQL).

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

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

На скриншоте (рисунок 1) средства сортировки и фильтрации выделены скругленным прямоугольником красного цвета.

Рассмотрим создание запроса на выборку с помощью Конструктора

Для создания нового пустого запроса в режиме конструктора надо щелкнуть на пиктограмме Конструктор запросов (рисунок 2).

Откроется активное окно диалога Добавление таблицы (рисунок 3) на фоне неактивного окна «Запрос1». В этом окне можно выбрать таблицы и queries для создания новых запросов.

В окне Добавление таблицы следует выбрать несколько таблиц из представленного списка таблиц, на основе которых будет проводиться выбор данных, и щелкнуть на кнопке Добавить. После этого закрыть окно Добавление таблицы, а окно «Запрос1» станет активным (рисунок 4).

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

Переместим имена полей с таблиц-источников в Бланк. Из таблицы Группы студентов переместим поле Название в первое поле Бланка, из таблицы Студенты переместим поле Фамилии во второе поле, а из таблицы Успеваемость переместим поле Оценка в третье поле и из таблицы Дисциплины переместим поле Название в четвертое поле Бланка запросов.

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

Условия ограниченного поиска или критерий поиска информации вводится в строке «Условия» отбора и строке «Или». Например, введем критерий поиска — «5/A» в строке «Условия» для поля Оценка. В этом случае в результате выполнения запроса на экране будут отображаться все фамилии студентов, которые получили оценку 5/A (рисунок. 5).

Далее надо закрыть окно запроса Запрос1, появится окно диалога Сохранить, ответить — Да и ввести имя запроса, например «Успеваемость студентов». Для запуска запроса дважды щелкнем на query «Успеваемость студентов», откроется таблица с результатами выполненного запроса (рис. 6).

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

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

Затем надо щелкнуть на кнопке ОК, откроется таблица с результатами выполненного запроса (рис. 8).

В некоторых случаях для создания запросов можно использовать Мастер запросов. После создания запросов на выборку информации из БД Access 2007 можно приступать к формированию форм.

© Обучение в интернет, .
Обратная связь

Запросы на добавление записей

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

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

Если необходимо добавить записи к таблице в другой БД, сначала следует присоединить таблицу-источник к базе, содержащей целевую таблицу, с помощью команд Файл → Внешние данные → Связь с таблицами. Для отбора добавляемых записей нужно составить запрос на выборку. Затем надлежит выполнить составленный запрос и оценить результат, переключившись в режим таблицы с помощью команды Вид → Режим таблицы. После этого необходимо вернуться в режим Конструктор и активизировать команду Запрос → Добавление.

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

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

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

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

Построение запроса на добавление записей:

— в запросе на выборку указать таблицу, из которой будут добавляться данные;

— отобрать данные, которые будут добавляться в другую таблицу;

— в окне Конструктор выбрать меню Запрос→ Добавление. ;

— указать имя таблицы, в которую будут добавляться записи;

— заполнить условия добавления записей.

Упражнения

В БД Супермаркет реализовать следующие действия:

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

2. Увеличить на 50 % оклад конкретно указанного сотрудника с помощью запроса на обновление Надбавка к зарплате.

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

4. Создать запрос на создание таблицы, который организует новую таблицу Расчет зарплаты с полями Ф.И.О., Оклад, Премия (20 % от оклада), Аванс (40 % от оклада), Подоходный налог, Пенсионный взнос, К выдаче.

5. Создать запрос на создание таблицы, который организует новую таблицу Подакцизные товары с полями Номенклатура, ЕдИзмерения, Цена.

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

7. Создать запрос на удаление, с помощью которого можно будет удалять сведения о поставках поставщика, указанного пользователем БД.

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

9. Создать запрос на удаление, который удаляет все сведения о сотруднике старше 70-ти лет (использовать функцию DateDiff).

10. Создать запрос на удаление, который удаляет из БД о поставках сведения, касающиеся конкретно указанного интервала времени (использовать оператор Between).

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

12. Создать новую таблицу Офис с полями КодСотрудника, Ф.И.О., Оклад. С помощью запроса на добавление Офисные работники добавть записи из таблицы Сотрудники, которые касаются только сотрудников, работающих в отделах Администрация и Бухгалтерия.

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

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

2. Что является результатом запуска запроса на изменение?

3. В чем особенности структуры бланка Конструктор запроса на обновление?

4. В чем особенности структуры бланка Конструктор запроса на удаление?

Тема 10. Отчеты в Access

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

1) предоставляют широкие возможности для группировки и вычисления промежуточных и общих итогов для больших наборов данных;

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

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

Методы работы с отчетами аналогичны методам работы с формами.

Разделы отчета

Пустой макет отчета содержит разделы Верхний колонтитул, Нижний колонтитул, между которыми находится Область данных.

Добавить или удалить верхний и нижний колонтитулы можно с помощью команды Вид → Колонтитулы.

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

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

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

Запрос на добавление записей.

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

• 1. Создайте новый запрос на выборку тех блоков данных, которые будут добавлены в некоторую таблицу, и проверьте его корректность, перейдя в режим Таблицы.
• 2. Преобразуйте запрос на выборку в запрос на добавление, для чего, вернувшись в режим Конструктор, выберите команду Добавление (вкладка ТИП ЗАПРОСА).
• 3. В появившемся окне введите имя таблицы, к которой нужно присоединить данные и нажмите ОК.
• 4. Выполните запрос.

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

• 1. Создайте новый запрос на выборку удаляемых блоков данных; отбор блоков данных выполняется в соответствии с заданными в строке Условие критериями.
• 2. Проверьте корректность сформулированных условий, перейдя в режим Таблицы.
• 3. Преобразуйте запрос па выборку в запрос па удаление записей, для чего, вернувшись в режим Конструктор, выберите команду Удалить (вкладка ТИП ЗАПРОСА)
• 4. В появившейся строке Удалить установите критерии отбора.
• 5. Выполните запрос.

Рассмотрим создание запросов на модификацию на примерах.

Создадим запрос на создание таблицы «Мониторы», полученной из таблицы «Товары». Таблица «Мониторы» должна содержать все поля таблицы «Товары» и только те записи, в которых наименование товара — Монитор.

1. Создадим запрос на выборку данных о мониторах из таблицы «Товары» (рис. 3.45), предварительно создав копию таблицы «Товары».

Рис. 3.45. Запрос на выборку данных о мониторах

Для выбора всех полей таблицы «Товары» использована «*». Условие отбора задано для ноля Наименование. Во избежание дублирования поля Наименование_у в таблице «Мониторы» отсутствует «галочка» в строке Вывод на экран.

2. Изменим тип запроса на выборку в запрос на создание таблицы, при этом появится окно для ввода имени создаваемой таблицы (рис. 3.46).

Рис. 3.46. Запрос на создание таблицы

3. Выполним запрос, при этом появится предупредительное окно (рис. 3.47).

Рис. 3.47. Предупредительное окно

4. Подтвердим создание новой таблицы с выбранными записями, в результате в списке таблиц БД увидим новую таблицу «Мониторы» (рис. 3.48)

Рис. 3.48. Таблица «Мониторы»

Создадим запрос на обновление цен товаров с учетом сезонных скидок на 10% (рис. 3.49).

1. Предварительно создадим копию таблицы «Товары», присвоив ей имя «Копия- Товары». [1] [2] [3] [4]

Рис. 3.49. Запрос на обновление

Любой запрос в MS Access реализуется с помощью языка SQL. Хотя большинство запросов можно построить, пользуясь средствами, которые Access предоставляет в режиме конструктора, и в этом случае они будут храниться в виде инструкций SQL. Некоторые типы запросов можно построить, используя только язык SQL.

Синтаксис инструкции SELECT в MS Access. Ядром языка SQL является инструкция SELECT. Она используется для отбора строк и столбцов из таблиц базы данных и содержит пять основных предложений. В общем случае ее синтаксис можно представить в следующем виде:

| WHERE Спецификация—отбора—строк>] [GROUP BY Спецификация—группировки>] [ORDER BY Спецификация—сортировки>]

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

Первую часть имени (включая точку) можно опустить, если поле имеется только в одной из таблиц предложения FROM.

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

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

Следующее выражение задает ноле из таблицы «Товары», имя которого Наименование: Товары.Наименование.

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

(INNER | LEFT | RIGHT> JOIN <имя—таблицы [[AS] псевдоним ] имя—запроса—на—выборку [[AS] псевдоним ] (