Как в access: Создание, изменение и удаление отношения

Содержание

Основы создания запросов в Access 2003 | Info-Comp.ru

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

Содержание

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

О 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, так как многие термины ниже Вам будут не понятны. Рекомендуемые статьи:

Видеокурсы по T-SQL
Самоучитель по языку Transact-SQL
Основы языка запросов SQL – оператор SELECT
Как написать функцию на PL/pgSQL?
Что такое представления 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 клиента.

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

Заметка! Для профессионального изучения языка T-SQL рекомендую посмотреть мои видеокурсы по T-SQL.

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

Опубликовано 29.09.2018 автором Konsultant

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

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

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

Зададим следующие свойства для свободного поля «ПолеПоиск» (для этого надо выделить его в режиме конструктора и нажать клавишу F4):

Перейдём на вкладку «Данные» свойств поля со списком. Значения свойства «Данные» оставим пустым. В «Источнике строк» мы выбираем подстановку из поля «ФИО» базового запроса «зап_СписокДляФормы» (этот же запрос «даёт» данные для текущей формы и поле «ФИО» запроса является базовым для поля «ФИО» формы). Свойства «Ограничиться списком», «Автоподстановка» и «Доступ» принимают значение «да».

На вкладке «События» определяем для свойства «После обновления» макрос «МакросПоиск».

Макрос состоит всего из двух команд:

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

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

Теперь нажимаем «Enter» или «Tab» и фокус ввода перейдёт на нужную нам запись в область данных формы:

Ситуация такова:
Есть база .мдб и в ней таблица. В таблице были удалены некоторые записи и в поле с автосчётчиком остались от этого “пробелы”.

Я хочу чтобы в таблице записи снова стали по порядку.

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

Я не знаю способа, как можно было бы скопировать таблицу полностью, с сохранением всех не только записей, но и свойств, заданных из среды MS Access, таких как Format, Lookup… Как задать эти свойства при создании базы програмно, я тоже не знаю.

Я пробовал копировать таблицу так(других способов не знаю):

SELECT * INTO ИмяТабл(куда) FROM ИмяТабл(откуда)
В итоге получилась таблица, содержащая те же данные, но в её свойствах Format и lookup совсем другие значения, отличные от оригинала. Например, в lookup было checkbox, а вновой таблице стало textbox. Свойство Format было YesNo, а стало пустым.

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

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

В качестве примера мы рассмотрим запрос, который будет выбирать из базы данных все заказы за 1996 год и переносить их в другую таблицу так, чтобы таблица «Заказы» (Orders) содержала только актуальные данные. Для этого сначала создадим таблицу, которая будет хранить устаревшие данные:

Раскройте список таблиц в окне базы данных и выделите таблицу «Заказы» (Orders).
Скопируйте таблицу в буфер обмена, нажав комбинацию клавиш +.
Вставьте таблицу из буфера обмена, нажав комбинацию клавиш +. Появляется диалоговое окно Вставка таблицы (Paste Table As).
В группе Параметры вставки (Paste Options) выберите переключатель Только структура (Structure Only).
В поле имя таблицы (Table Name) введите строку: Заказы (архив). Нажмите кнопку ОК или клавишу .

В списке появляется новая таблица, пока пустая, т. к. мы скопировали только структуру таблицы «Заказы» (Orders). Теперь создадим запрос на добавление, который позволит перенести в новую таблицу данные из таблицы «Заказы».

Раскройте список таблиц в окне базы данных и выделите в нем таблицу «Заказы».
Щелкните левой кнопкой мыши по стрелке на кнопке Новый объект (New Object) инструментальной панели и выберите из раскрывшегося списка элемент Запрос (Query). В диалоговом окне Новый запрос (New Query) выберите значение Конструктор (Design View). Появляется окно Конструктора запроса с таблицей «Заказы» в верхней части.
Сначала создайте запрос на выборку нужных записей. Перетащите из списка полей таблицы «Заказы» все поля в строку бланка запроса
Поле (Field).
В строке Условие отбора (Criteria) столбца «ДатаРазмещения» введите выражение Between 101.01.961 And 131.12.961
Запустите запрос на выборку, чтобы проверить, правильно ли отобраны записи. Оказались отобранными 152 записи.
Выберите команду Запрос, Добавление (Query, Append) или щелкните левой кнопкой мыши по стрелке на кнопке Тип запроса (Query Type) и выберите из списка элемент Добавление (Append Query). Появляется диалоговое окно Добавление (Append), аналогичное диалоговому окну Создание таблицы (Make Table) (рис. 8.6).
В поле имя таблицы (Table Name) необходимо выбрать из списка имя таблицы, в которую будут добавляться записи. В данном случае по умолчанию в нем указана таблица «Заказы (архив)», которую мы только что создали. Нажмите кнопку ОК. (Если бы таблица, в которую нужно добавить записи, находилась в другой базе данных, необходимо было бы выбрать переключатель в другой базе данных (Another Database) и затем в поле имя файла (File Name) указать имя файла MDB, который содержит требуемую таблицу. )

Рис. 8.6. Диалоговое окно Добавление

В бланке запроса на добавление появляется дополнительная строка Добавление (Append To), содержащая названия полей таблицы, в которую добавляются записи (рис. 8.7). В данном случае названия полей запроса и названия полей таблицы совпадают, поэтому по умолчанию вся эта строка заполнена. Если имена полей не совпадают, необходимо выбрать из раскрывающегося списка в строке Добавление (Append To) имя поля результирующей таблицы, соответствующего полю в исходной таблице.
Нажмите кнопку Запуск (Run), чтобы выполнить запрос. Прежде чем новые записи будут добавлены в таблицу, Access выдает сообщение о числе записей, которые предполагается добавить. Вы можете нажать кнопку Да (Yes), и только после этого записи будут добавлены. Если вы нажмете кнопку Нет (No), вставка записей будет отменена.

Замечание

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

Рис. 8.7. Бланк запроса на добавление

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

Access

Access SQL: предложение SELECT — служба поддержки Microsoft

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

Обзор Access SQL см. в статье Access SQL: основные понятия, словарный запас и синтаксис.

В этой статье

Выберите поля: предложение SELECT
Выбрать все поля
Выберите отдельные значения
Используйте замещающие имена для полей или выражений: ключевое слово AS
Выберите с помощью выражения

Выберите поля: предложение SELECT

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

Предположим, вы хотите узнать номера телефонов ваших клиентов. Предполагая, что поле, в котором хранятся телефонные номера клиентов, называется txtCustPhone, предложение SELECT выглядит следующим образом:

ВЫБЕРИТЕ [txtCustomerPhone]

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

Совет: Имя, содержащее пробелы, легче читается и может сэкономить ваше время при разработке форм и отчетов, но в конечном итоге может потребовать больше ввода при написании операторов SQL. Этот факт следует учитывать при присвоении имен объектам в базе данных Access.

Если в вашем операторе SQL есть два или более полей с одинаковыми именами, вы должны добавить имя источника данных каждого поля к имени поля в предложении SELECT. Вы используете то же имя для источника данных, что и в предложении FROM.

Выбрать все поля

Если вы хотите включить все поля из источника данных, вы можете либо перечислить все поля по отдельности в предложении SELECT, либо использовать подстановочный знак звездочки (*). Когда вы используете звездочку, Access определяет, когда выполняется запрос, какие поля содержит источник данных, и включает все эти поля в запрос. Это помогает убедиться, что запрос остается актуальным, если в источник данных добавляются новые поля.

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

Например, предположим, что вы хотите выбрать все поля из таблицы «Заказы», но только адрес электронной почты из таблицы «Контакты». Ваше предложение SELECT может выглядеть так:

ВЫБЕРИТЕ Заказы.*, Контакты.[Адрес электронной почты]

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

Выберите отдельные значения

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

ВЫБЕРИТЕ ОТЛИЧНЫЙ [txtCustomerPhone]

Используйте замещающие имена для полей или выражений: ключевое слово AS

Вы можете изменить метку, отображаемую для любого поля в представлении таблицы, используя ключевое слово AS и псевдоним поля в предложении SELECT. Псевдоним поля — это имя, которое вы назначаете полю в запросе, чтобы облегчить чтение результатов. Например, если вы хотите выбрать данные из поля с именем txtCustPhone, а это поле содержит номера телефонов клиентов, вы можете улучшить читаемость результатов, используя псевдоним поля в операторе SELECT, как показано ниже:

ВЫБЕРИТЕ [txtCustPhone] КАК [Телефон клиента]

Примечание. При использовании выражения в предложении SELECT необходимо использовать псевдоним поля.

Выберите с помощью выражения

Иногда вам нужно просмотреть расчеты на основе ваших данных или получить только часть данных поля. Например, предположим, что вы хотите вернуть год рождения клиентов на основе данных в поле BirthDate в вашей базе данных. Ваше предложение SELECT может выглядеть следующим образом:

SELECT DatePart(«yyyy»,[DateDate]) AS [Год рождения]

Это выражение состоит из функции DatePart и двух аргументов — «yyyy» (константа) и [BirthDate] (идентификатор).

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

Верх страницы

sql — MS-Access -> ВЫБРАТЬ КАК + ЗАКАЗАТЬ ПО = ошибка

спросил 12 лет, 4 месяца назад

Изменено 1 год, 8 месяцев назад

Просмотрено 32к раз

Я пытаюсь сделать запрос, чтобы получить регион, в котором больше всего продаж сладостей. «grupo_produto» — это тип продукта, а «regiao» — это регион. Итак, я получил этот запрос:

 ВЫБЕРИТЕ ВЕРХНИЙ 1 r.nm_regiao, (ВЫБЕРИТЕ КОЛИЧЕСТВО(*)
        ОТ Dw_Empresa
        ГДЕ grupo_produto='1' И
        cod_regiao = d.cod_regiao) в целом
ОТ Dw_Empresa d
INNER JOIN tb_regiao r ON r. cod_regiao = d.cod_regiao ORDER BY total DESC

Затем, когда я запускаю запрос, MS-Access запрашивает параметр «итого». Почему он не учитывает вновь созданный «столбец», который я сделал в предложении select?

Заранее спасибо!

sql
мс-доступ
select
sql-order-by

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

 SELECT
 первая колонка,
 IIF (вторая колонка = '', третья колонка, вторая колонка) Как ваш псевдоним
ОТ
 ваш стол
СОРТИРОВАТЬ ПО
 2 АСК

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

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

Вы можете сделать это так

 выбрать * из(
  выберите a + b как c, * из таблицы)
  заказать по с

Access имеет некоторые отличия от Sql Server.

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

Поскольку Access (ACE/Jet) не соответствует стандарту SQL-92.

Рассмотрим этот пример, действительный SQL-92:

 ВЫБЕРИТЕ a КАК x, c - b КАК Y
  ИЗ MyTable
 ЗАКАЗ
    ПО х, у;

На самом деле, x и y являются единственными допустимыми элементами в предложении ORDER BY , потому что все остальные находятся вне области действия (порядковые номера столбцов в предложении SELECT допустимы, хотя их идентификатор использования устарел).

Однако Access подавляет приведенный выше синтаксис. Эквивалентный синтаксис Access следующий:

 SELECT a AS x, c - b AS y
  ИЗ MyTable
 ЗАКАЗ
    ПО а, в - б;

Однако из комментариев @Remou я понимаю, что подзапрос в предложении ORDER BY недопустим в Access.

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

 ВЫБЕРИТЕ ВЕРХНИЙ 1 * ОТ
(
    ВЫБИРАТЬ
        r.nm_regiao,
        (ВЫБЕРИТЕ КОЛИЧЕСТВО(*)
         ОТ Dw_Empresa
         ГДЕ grupo_produto='1' И cod_regiao = d.cod_regiao) как итог
    ОТ Dw_Empresa d
    ВНУТРЕННЕЕ СОЕДИНЕНИЕ tb_regiao r ON r.cod_regiao = d.cod_regiao
) Т1
ЗАКАЗАТЬ ПО ВСЕГО DESC

(Не проверено)

Как насчет:

 SELECT TOP 1 r.nm_regiao
ОТ (ВЫБРАТЬ Dw_Empresa.cod_regiao,
             Подсчет(Dw_Empresa.cod_regiao) AS CountOfcod_regiao
      ОТ Dw_Empresa
      ГДЕ Dw_Empresa.[grupo_produto]='1'
      ГРУППА ПО Dw_Empresa.cod_regiao
      ORDER BY Count(Dw_Empresa.cod_regiao) DESC) d
ВНУТРЕННЕЕ СОЕДИНЕНИЕ tb_regiao AS r
ON d.cod_regiao = r.cod_regiao

Я предлагаю использовать промежуточный запрос.

 ВЫБРАТЬ r.nm_regiao, d.grupo_produto, COUNT(*) КАК всего
   ОТ Dw_Empresa d ВНУТРЕННЕЕ СОЕДИНЕНИЕ tb_regiao r ON r. cod_regiao = d.cod_regiao
   ГРУППА ПО r.nm_regiao, d.grupo_produto;

Если вы вызываете этот GroupTotalsByRegion, вы можете сделать:

 SELECT TOP 1 nm_regiao, total FROM GroupTotalsByRegion
  ГДЕ grupo_produto = '1' ORDER BY total DESC

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

Как насчет использования: С хх КАК ( SELECT TOP 1 r.nm_regiao, (SELECT COUNT(*) ОТ Dw_Empresa ГДЕ grupo_produto=’1′ И cod_regiao = d.cod_regiao) в целом ОТ Dw_Empresa d ВНУТРЕННЕЕ СОЕДИНЕНИЕ tb_regiao r ON r.