Создание перекрёстного запроса
Пусть мы хотим получить сведения о том, кто из клиентов и какие продукты покупал, а также в каком количестве, причем данные хотим иметь в виде таблицы (табл1).
В этом случае надо будет создать так называемый перекрестный запрос.
Перекрестный запрос имеет заголовки строк, заголовки столбцов и на их пересечении данные. Данные получаются путем вычисления по встроенным функциям. Перекрестный запрос также называется перекрестной таблицей.
Таким образом, при создании перекрестного запроса необходимо указать:
· откуда брать данные и что с ними делать (суммировать, вычислять среднее, брать максимальное значение и т.д.).
1. Сначала как обычно щелкнуть по объекту «Запросы», щелкнуть по кнопке «Создать», выделить строчку «Конструктор» и ОК.
2. После этого надо добавить те таблицы, откуда будут браться названия строк, столбцов и данные. В нашем случае надо добавить все таблицы.
3. В панели инструментов есть кнопка «Тип запроса» и сразу справа от неё маленькая кнопочка с треугольником, нажав на которую надо выбрать из списка «Перекрестный». После этого в макете запроса должна появится строчка «Перекрестная таблица».
4. В первом столбце макета указывается откуда брать заголовки строк, причем в строке «Перекрестная таблица» надо указать, что это заголовки строк (щелкнуть в этой строке и выбрать из списка). В нашем случае заголовки строк это наименование клиента, поэтому надо выбрать таблицу «Клиенты», поле – «наименование», групповая операция — группировка.
5. Аналогично во втором столбце указывается, откуда брать заголовки столбцов. Нам надо выбрать таблицу «Продукты», поле – «Название», групповая операция – группировка, перекрестная таблица – заголовки столбцов.
6. Ну а в третьем столбце надо указать, откуда брать данные и что с ними делать. Что делать с данными указывается следующим образом. Сначала в строке «Перекрестная таблица» выбирается из списка «Значение», а затем в строке «Групповая операция» выбирается соответствующая операция. В нашем случае имя таблицы – «Заказы», поле – «Количество», групповая операция — Sum, перекрестная таблица – «Значение».
7. Закрыть макет подтвердив сохранение и назвав запрос «Кто что и сколько».
8. Открыть запрос «Кто что и сколько» и убедиться, что он работает.
Создание запроса на обновление данных
Запрос на обновление используется для обновления данных в таблицах. Например, если цена товара изменилась, в таблицу «Товары» необходимо внести новые данные.
Чтобы создать запрос на обновление данных, надо сначала создать обычный запрос на выборку, включив в него поля, подлежащие обновлению и поля, по которым задаются условия отбора записей.
Пусть мы хотим обновить цену товара «Пастила фруктовая» на новое значение, равное 35. Сначала в запрос надо включить поля «цена» и «название», причём условие отбора для поля «название» должно содержать название продукта «Пастила фруктовая» как показано на рисунке.
Затем, не выходя из конструктора, надо открыть список «Тип запроса» в панели инструментов и выбрать из этого списка строчку «Обновление». После этого в макете запроса появится строка «обновление» в которой и надо указать новое значение, как показано на следующем рисунке.
После этого надо закрыть макет запроса, подтвердив сохранение и дав ему имя, например, такое «Изменение цены Пастилы фруктовой».
Этот запрос можно усовершенствовать скомбинировав его с параметрическим запросом. Если вместо конкретного значения цены поставить в квадратных скобках поясняющий текст, например, «Новая цена», тогда при выполнении этого запроса сначала будет запрошено новое значение цены. Аналогичным образом можно использовать и все другие возможности при создании запросов.
Запрос на удаление позволяет удалить записи из таблицы. В запросе указываются таблицы, из которых должны удаляться записи, и задаются условия отбора удаляемых записей.
Пусть мы хотим удалить из таблицы «Заказы» все записи, в которых продуктом служит «Пастила фруктовая». Начало как при создании обычного запроса на выборку на основе двух таблиц: «Заказы» и «Продукты».
Затем надо мышкой перетащить звёздочку из таблицы «Заказы» в строку «Поле» макета запроса, как показано на рисунке.
Во втором столбце макета запроса надо установить условие отбора “Пастила фруктовая” на поле «Название» таблицы «Продукты».
Затем, не выходя из конструктора, надо открыть список «Тип запроса» в панели инструментов и выбрать из этого списка строчку «Удаление». Должно получиться следующее.
После этого можно закрыть макет запроса подтвердив сохранение и присвоим ему соответствующее имя.
Отчет – это форма представления данных, предназначенная для вывода на печать. В качестве исходных данных для отчета могут служить как таблицы, так и запросы.
Отчет лучше всего создавать с помощью мастера отчетов. Возможны два варианта действий, в зависимости от того, сколько источников данных будет использовано для составления отчета.
Первый вариант. Если отчет будет создаваться по одной таблице или по одному запросу.
1. Щелкнуть по объекту «Отчеты», нажать кнопку «Создать», выделить строчку «Мастер отчетов», выбрать внизу из списка источник данных и нажать на кнопку ОК.
2. Из списка «Доступные поля» перенести в окно «Выбранные поля», те поля, которые будут использованы в отчете и нажать кнопку «Далее».
3. Этот пункт делается по мере необходимости! Нажимая на кнопки со стрелками вправо или влево, добавить или убрать уровни группировки данных в отчете. Нажать на кнопку «Далее».
4. Этот пункт делается только в том случае, когда необходимо осуществить сортировку по ПЕРВОМУ полю! Выбрать из списка название поля и, нажимая на кнопку, расположенную справа, установить сортировку по возрастанию или убыванию. Нажать на кнопку «Далее».
5. Выбрать вид макета, ориентацию страниц отчета и нажать на кнопку «Далее».
6. Выбрать требуемый стиль и нажать на кнопку «Далее».
Второй вариант. Отчет будет создаваться на основе нескольких таблиц или нескольких запросов.
1. Щелкнуть по объекту «Отчеты», нажать кнопку «Создать», выделить строчку «Мастер отчетов», строчку источника данных оставить пустой и нажать на кнопку ОК.
2. Выбирать по очереди из списка необходимые таблицы или запросы, перемещать требуемые поля из доступных в выбранные, и когда все необходимые поля будут набраны, нажать на кнопку «Далее».
Макрос – это программа состоящая из последовательности макрокоманд.
Макрокоманда – это инструкция, предписывающая базе данных выполнить определённое действие над каким-либо объектом базы дынных.
Например, макрокомандой можно открыть форму, выполнить запрос, применить фильтр.
Чтобы создать макрос надо перейти на закладку «Макросы» и щелкнуть по кнопке «Создать». Появиться окно макроса.
В столбце «Макрокоманда» из списка выбирается нужная макрокоманда, после чего в нижней части надо указать необходимые аргументы для данной макрокоманды. Например, если в качестве макрокоманды выбрать «ОткрытьФорму», то появиться список её аргументов, среди который есть аргумент «Имя формы». Значение этого аргумента устанавливается выбором имени той формы, которую надо открыть из списка.
В столбце «Примечание» можно написать пояснение к данной макрокоманде, если оно необходимо.
Очередная макрокоманда вносится в следующей строке. И так, до тех пор, пока не будет получен набор макрокоманд, выполняющей поставленную задачу.
После ввода всех макрокоманд необходимо закрыть окно макроса и присвоить ему какое-то имя.
При работе с запросами можно было наблюдать, как окно запроса занимает неудобное положение на экране и имеет неподходящие размеры. При использование макроса для выполнения запроса можно разметы окна и его положение задавать заранее. Для примера, рассмотрим создание макроса для выполнения запроса «Итоги». Этот макрос должен содержать две макрокоманды.
Первая макрокоманда «ОткрытьЗапрос» с аргументом «Имя запроса» — «Итоги».
Вторая макрокоманда «СдвигРазмер», которая имеет четыре аргумента: по правому краю, От верхнего края, ширина, высота. Эти аргументы имеют следующий смысл (см. рис.)
Расстояния измеряются либо в сантиметрах, либо в дюймах – зависит от установок операционной системы.
Все макрокоманды в макросе выполняются последовательно одна за другой. Однако, можно сделать так, что выполнение той или иной макрокоманды будет происходить в зависимости от заданных условий. Для этого в окно создания макроса надо добавить столбец для ввода условий. Это делается с помощью щелчка по кнопке «Условия» в панели инструментов.
Условие представляет собой логическое выражение, аналогичное условию отбора в запросах, только имена полей надо указывать явно.
С помощью задания следующего условия можно обеспечить выполнение некоторого действия в диалоговом режиме. Если в столбце «условие» написать такое выражение
То это вызовет появление на экране диалогового окна с вопросом («Открыть отчёт?» и двумя кнопками ОК и Отмена. Если нажать ОК, то окошко возвращает значение 1 и, следовательно, эта макрокоманда будет выполнена. Если нажать кнопку Отмена, то окошко вернёт значение 2, а 2 1 и, следовательно, эта макрокоманда не будет выполнена.
Если по условию должно быть выполнено несколько макрокоманд, то в столбце «Условие» для этих макрокоманд (кроме первой, где указано условие) надо набрать многоточие (…).
В макросе можно вызывать для выполнения уже существующие макросы. Для этого существует макрокоманда «Выполнить макрос», аргументом которой служит имя вызываемого макроса.
Добавить на форму кнопку, запускающую на выполнение макрос, можно в режиме конструктора формы просто перетащив мышкой макрос из окна базы данных на форму.
Результирующее поле- “Зарплата” , для него установим “значение”
«групповая операция» — Avg,
Сохраним полученный запрос под имеем «Зарплаты по должностям».
Связывание и импорт запроса перекрестной таблицы доступа в Excel — CodeRoad
После этого необходимо выполнить команду Запрос | Перекрестный, в результате чего в макете запроса среди параметров появится строка Перекрестная таблица, в которой для полей запроса можно с помощью разворачивающегося списка выбрать одно из следующих значений.
Создание раскрывающегося списка
Путь: меню «Данные» — инструмент «Проверка данных» — вкладка «Параметры». Тип данных – «Список».
Ввести значения, из которых будет складываться выпадающий список, можно разными способами:
Как сделать выпадающий список в Excel — ЭКСЕЛЬ ХАК
Открывшееся окно конструктора запросов состоит из двух частей: верхняя содержит списки полей выбранных таблиц, а нижняя — бланк QBE для создания запроса. Каждый столбец бланка описывает одно поле, участвующее в запросе.
Как сделать выпадающий список в Excel используя ручной ввод данных
На примере выше, мы вводили список данных для выпадающего списка путем выделения диапазона ячеек. Помимо этого способа, вы можете вводить данные для создания выпадающего списка вручную (необязательно их хранить в каких-либо ячейках).
Например, представим что в выпадающем меню мы хотим отразить два слова «Да» и «Нет». Для этого нам потребуется:
- Выбрать ячейку, в которой мы хотим создать выпадающий список;
- Перейти на вкладку « Данные » => раздел « Работа с данными » на панели инструментов => выбрать пункт « Проверка данных «:
После этого система создаст раскрывающийся список в выбранной ячейке. Все элементы, перечисленные в поле « Источник «, разделенные точкой с запятой будут отражены в разных строчках выпадающего меню.
Если вы хотите одновременно создать выпадающий список в нескольких ячейках — выделите нужные ячейки и следуйте инструкциям выше.
[expert_bq id=»1570″]Другие типы объединение позволяют выводить все записи главной таблицы и только те записи дочерней, для которых совпадают значения связанных полей и все записи дочерней и только те записи главной, для которых связанные поля совпадают. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] Еще одним типом специальных запросов является внешнее объединение, которое применяется в многотабличных запросах. При использовании запросов данного типа итоговый набор записей формируется не только при условии совпадения связанных полей базовых таблиц, но и из записей одной таблицы, не имеющих соответствия в другой.
Как сделать перекрестный запрос в access 2010?
- Создать два именованных диапазона для ячеек « A2:A5 » с именем “Россия” и для ячеек « B2:B5 » с названием “США”. Для этого нам нужно выделить весь диапазон данных для выпадающих списков:
То это вызовет появление на экране диалогового окна с вопросом («Открыть отчёт?» и двумя кнопками ОК и Отмена. Если нажать ОК, то окошко возвращает значение 1 и, следовательно, эта макрокоманда будет выполнена. Если нажать кнопку Отмена, то окошко вернёт значение 2, а 2 1 и, следовательно, эта макрокоманда не будет выполнена.
5 ответов
На самом деле вы можете «cheat» с доступом и Excel и сделать это без какого-либо кода, обернув свой запрос перекрестной таблицы в запрос без перекрестной таблицы.
Во-первых, в Access создайте новый запрос «Моя перекрестная таблица без перекрестной таблицы»и в качестве источника запроса выберите свой запрос перекрестной таблицы, который Excel отказывается видеть. Выведите все столбцы в таблице, используя в качестве выходных данных поле*.
Этот новый запрос теперь будет содержать все, что содержится в вашем запросе перекрестной таблицы, но сам по себе он не является запросом перекрестной таблицы.
С Excel выполните шаги для получения данных из доступа через запрос, и новый запрос «My Non-Crosstab Crosstab» будет доступен для вставки!
По какой-то причине Excel не будет представлять запрос перекрестной таблицы в списке таблиц/запросов, которые вы можете выбрать, но его можно добавить без VBA, и после этого он будет обновляться так же, как обычная таблица, вам просто нужно ввести некоторые базовые SQL в свойствах соединения.
Инструкции предназначены для Excel 2016 &, не тестировавшихся в предыдущих версиях.
Создайте ссылку на другую таблицу или запрос в той же базе данных Access, что и запрос перекрестной таблицы (из обычных данных -> Из Доступа).
Щелкните в любом месте только что показанной таблицы и на вкладке Дизайн выберите Свойства, чтобы открыть диалоговое окно Свойства внешних данных.
Нажмите маленькую кнопку рядом с полем «Имя:» справа, чтобы открыть свойства подключения.
Перейдите на вкладку Определение и измените тип команды на «SQL»
Введите базовый бит SQL: SELECT * ОТ YourCrossTabQueryName
Это должно обновить таблицу с показанной перекрестной таблицей.
- В меню Файл выберите пункт Данные
- Выберите объект доступа (таблицу или запрос), из которого вы хотите извлечь данные.
- Загрузка данных в excel
- в любое время, когда вам нужны обновленные данные, щелкните их правой кнопкой мыши и выберите обновить или выберите обновить все, если существует несколько ссылок.
Это предполагает, что база данных находится в одном и том же физическом местоположении каждый раз, когда вы выполняете обновление, у вас есть подключение к этому источнику.
Я пытаюсь хранить и извлекать данные, которые вводятся в Excel into Access. Я новичок в доступе, и у меня уже есть программа Excel на переднем конце, что приводит меня к импорту из Excel. Я успешно выяснил, как сохранить мой именованный диапазон Excel в нужную таблицу доступа, но теперь мне нужно.
Я обнаружил, что вы не можете этого сделать, если внутренняя база данных является файлом Access accdb
«Здесь следует отметить, что, хотя в настоящее время вы не можете импортировать запросы перекрестной таблицы из Access, вы можете импортировать данные, поддерживающие перекрестную таблицу, в виде отчета о таблице Pivot (в конце концов, таблица Pivot по сути является версией запроса перекрестной таблицы Доступа Excel).»
Вам нужно будет импортировать исходные данные, а затем создать таблицу pivot
Перекрестные запросы
Изменим также заголовки полей выводимой таблицы. Для этого в описании полей в строке «Поле» введем их новые названия, отделяя их от описания двоеточием:
Оценка_Т: Теория
Оценка_П: Практика
Оценка_Л: Лабы
Практическая работа. Создание перекрестных запросов в MS Access
Добавляйте авторские материалы и получите призы от Инфоурок
Информационная безопасность автоматизированных систем
Цели работы: научиться создавать перекрестные запросы на выборку данных с помощью мастера и конструктора запросов.
Перекрестные запросы — это запросы, в которых происходит статистическая обработка данных, результаты которой выводятся в виде таблицы, очень похожей на сводную таблицу Excel. Перекрестные запросы обладают следующими достоинствами:
возможностью обработки значительного объема данных и вывода их в формате, который очень хорошо подходит для автоматического создания графиков и диаграмм;
простотой и скоростью разработки сложных запросов с несколькими уровнями детализации.
Однако они имеют и недостатки — например, нельзя сортировать таблицу результатов по значениям, содержащимся в столбцах, т. к. в подавляющем большинстве случаев одновременное упорядочивание данных в столбцах по всем строкам невозможно. При этом вы можете задать сортировку по возрастанию или по убыванию для заголовков строк.
Создать запрос с помощью мастера форм: Создание/Мастер запросов
Создать с помощью конструктора: Создание/ Конструктор запросов
Изменить запрос с помощью конструктора: Режим/Конструктор
Как сделать выпадающий список в Excel с подстановкой данных (с использованием функции СМЕЩ)
- Заголовки строк — выбирается для поля запроса, значения которого нужно использовать в качестве заголовков строк. В качестве заголовков строк необходимо определить хотя бы одно поле, при этом в строке Групповые операции необходимо выбрать значение Группировка, одну из итоговых функций или Выражение.
- Заголовки столбцов — выбирается для поля запроса, значения которого нужно использовать в качестве заголовков столбцов. С этой целью используется только одно поле, требования к которому аналогичны требованиям к полю, в котором установлено значение Заголовки Строк.
- Значение — выбирается для поля запроса, в котором вычисляется итоговое значение, отображаемое в «ячейках» перекрестного запроса. Такое поле должно быть единственным, при этом в строке Групповые операции для него необходимо выбрать одну из итоговых функций или задать выражение, в котором используются итоговые функции.
- (не отображается) — выбирается для того поля, значения которого не должны отображаться в перекрестном запросе.
использовать mysql_select_db но застрять только с использование одной БД за раз. Это наша текущая настройка и размещение базы данных в качестве идентификатора пространства имен, похоже, не работает (он отлично работает в оболочке MySQL, поэтому я знаю, что это не проблема с нашей настройкой сервера MySQL).
