Телеграм Канал " Microsoft Access "

​​Добавление таблиц к запросу.

Как было указано выше, если будет выбран Конструктор запросов (Query Design), откроется диалоговое окно Добавление таблицы (Show Table). В этом диалоговом окне можно выбрать таблицы или запросы с помощью которых подготавливаются данные для рассматриваемого запроса. В Access не имеет значения, применяются ли в качестве источника данных для запроса таблицы или другие запросы. Запросы и таблицы можно выбирать, дважды щелкая по именам таблиц или запросов, которые необходимо добавить, или щелкая по таблицы или запросу с последующим щелчком по кнопке Добавить (Add). Так же предусмотрена возможность выбирать несколько таблиц или запросов используя клавишу <Shift> для выбора непрерывного ряда таблиц или запросов, или клавишу <Ctrl> для выбора отдельно расположенных таблиц или запросов. После выбора необходимых таблиц или запросов щелкните по кнопке Добавить, а затем по кнопке Закрыть (Close)

#Запросы #КонструкторЗапросов #ДобавлениеТаблиц

​​Добавление полей к запросу.

После добавления таблицы или запроса, можно приступить к выбору полей, которые необходимо включить в запрос. Окно запроса состоит из двух разделов. Верхний раздел окна содержит таблицы и запросы, лежащие в основе создаваемого запроса. В нижнем разделе приведены поля, включаемые в результат запроса.
Можно добавить поле в бланк запроса, приведенный в нижнем разделе окна, одним из следующих способов:
▪️ дважды щелкните по имени поля, которое необходимо добавить;
▪️ перетащите отдельное поле из таблицы, расположенной в верхнем разделе окна, в бланк запроса, находящийся в нижнем разделе;
▪️ чтобы выделить в таблице несколько полей, используйте клавишу <Shift>, позволяющую выбрать непрерывный ряд полей, а также клавишу <Ctrl>, с помощью которой можно отмечать отдельно расположенные поля. Перетащите выделенные поля в бланк запроса.

❗️ Чтобы включить все поля в результат запроса, дважды щелкните по звездочке (*), расположенной в списке полей. Безусловно, это очень удобно, но мы считаем такой шаг довольно опасным, поскольку вывод запроса после изменений структуры всей таблицы может неожиданно для пользователя стать совсем другим. Если выбрана звездочка (*), то в результат запроса включаются все поля таблицы, независимо от того, нужны они там или нет. Применение лишних полей может привести к существенному снижению производительности в локальной сети или приложении «клиент/сервер».

#Запросы #ДобавлениеПолей

​​Упорядочивание результатов запросов.

После вызова на выполнение нового запроса обратите внимание на то, что результирующие строки появляются без упорядочения по какому-то принципу, но, вообще говоря, чаще всего требуется, чтобы они были расположены в определенном порядке. Для этого в бланке запроса предназначена строка Сортировка (Sort).
Чтобы отсортировать результат запроса, выполните следующие действия:
1. В режиме конструктора щелкните в бланке запроса по ячейке Сортировка (Sort) того столбца, по значениям которого необходимо отсортировать строки.
2. Воспользуйтесь полем с раскрывающимся списком, чтобы выбрать способ сортировки По возрастанию (Ascending) или По убыванию (Descending).

#Запросы #Сортировка

​​Сортировка по значениям нескольких полей.

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

#Запросы #Сортировка

Уточнение запросов с помощью критериев.

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

= (Равно). Например, ="Москва" - выводит все строки, в которых в рассматриваемом поле находится значение "Москва".

< (Меньше). Например, <100 - выводит все строки, в которых в рассматриваемом поле находится значение меньше 100.

<= (Меньше или равно). Например, <=100 - выводит все строки, в которых в рассматриваемом поле находится значение, меньшее или равное 100.

> (Больше). Например, >100 - выводит все строки, в которых в рассматриваемом поле находится значение больше 100.

>= (Больше или равно). Например, >=100 - выводит все строки, в которых в рассматриваемом поле находится значение, большее или равное 100.

<> (Не равно). Например, <>"Москва" - выводит все строки, в которых в рассматриваемом поле находится значение, отличное от "Москва".

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

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

Like - обеспечивает сравнение строкового выражения. Например, Like"Моск*" - выполняет поиск всех строк, которые начинаются с подстроки "Моск".

Between - выполняет поиск значений, относящихся к определенному диапазону. Например, Between 5 and 10 - выполняет поиск всех строк со значениями в поле от 5 до 10 включительно.

In - определяет находится ли указанное значение в строке. Например, In("Москва";"Рязань";"Казань") - выводит все строки, в которых значение поля равно "Москва", "Рязань" или "Казань".

Not - еще один вариант оператора проверки на неравенство. Например, Not"Москва" - выведет все строки со значениями, отличными от значения "Москва".

Is Null - выполняет поиск значений Null. Выводит все строки, в которые не были введены данные.

Is Not Null - выполняет поиск всех строк со значениями, отличными от Null. Выполняет поиск всех тех строк, в которые были введены данные.

Звездочка (*) является подстановочным символом. В примере с конструкцией Like"Моск*" запрос выводит все строки, в которых заданное поле начинается с последовательности символов Моск, за которой могут следовать произвольные символы.

#Запросы #Критерии #Операторы

Применение дат в критериях.

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

Date() - вернет все строки с текущей датой;
Day([OrderDate])=1 - вернет все строки, в которых дата заказа относится к первому дню месяца;
Mount([OrderDate])=1 - вернет все строки, в которых дата относится к январю;
Year([OrderDate])=2020 - вернет все строки, в которых дата относится к 2020 году;
Weekday([OrderDate])=1 - вернет все строки, в которых днем недели заказа является воскресение (при условии, что используется принятая нумерация дней недели с воскресения);
Between #1/1/2020# And #31/12/2020# - вернет все строки с данными за 2020 год;
DatePart("q",[OrderDate])=2 - вернет все строки с данными за второй квартал.

Функция Weekday(Date,[FirstDayOfWeek]) действует с учетом применяемого языкового стандарта, а также в зависимости от того, как определен в системе первый день недели. В частности, при вызове функции Weekday() без необязательного аргумента FirstDayOfWeek в качестве первого дня недели по умолчанию принимает день который определен константой vbSunday (воскресение). При значении, равном 0, аргумент FirstDayOfWeek принимает по умолчанию значение, которое определено в системе. Могут быть также другие значения.

#Запросы #Критерии #Функции

​​Создание запросов на основе нескольких таблиц.

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

На скриншоте приведен запрос, в котором выполняется соединения таблиц tblClients, tblOrders, tblOrderDetails и tblItems, в результате чего происходит выборка данных из всех этих таблиц. Обратите внимание на то, что данные полей LastName, FirstName, Phone, Address извлекаются из таблицы tblClients, данные поля OrderDate - из таблицы tblOrders, данные поля Item - из таблицы tblItems, а данные поля Quantity - из таблицы tblOrderDetails.

#Запросы #МноготабличныеЗапросы

​​Нюансы применения многотабличных запросов.

Работая с многотабличными запросами, необходимо учитывать некоторые нюансы их применения, которые касаются обновления данных, а также состава строк в результатах запроса.
Важно учитывать, что некоторые поля в результатах многотабличного запроса обновить невозможно. Это касается полей на стороне "один" связи "один ко многим", на основании которых формируется соединение (если только не был установлен флажок Каскадное обновление связанных полей (Cascade Update Related Fields)). Кроме того, исключена возможность обновлять поле на стороне "многие" связи, по которому выполняется соединение, после обновления данных на стороне "один". Еще более важно то, что некоторые поля могут быть обновлены, но это приводит к весьма неожиданным последствиям. В частности, необходимо учитывать, что может вызвать обновление полей на стороне "один" связи "один ко многим". Безусловно, при этом фактически пользователь обновляет одну строку в исходной таблице на стороне "один" связи, но это может затронуть несколько строк на стороне "многие" той же связи.

Например, показан результат на основе таблиц tblClients, tblOrders, tblOrderDetails и tblItems. В данном случае в определенной строке в выводе запроса значение Павел поля FirstName заменено на Андрей. На первый взгляд может показаться, что это изменение повлияет только на конкретный элемент, тем не менее после перехода к другой записи, изменились все строки, в которых встречался Иванов Павел на Иванова Андрея. Это изменение произошло в связи с тем, что данные о клиенте извлекаются из одной строки таблицы tblClients.

#Запросы #МноготабличныеЗапросы

​Создание вычисляемых полей.

Одно из правил нормализации таблиц гласит, что результаты вычислений не должны храниться в базе данных. Для вывода результатов вычислений необходимые вычисления можно встраивать в запросы, а также отображать результаты вычислений в формах и отчетах, заложив запросы с вычислениями в основу формы или отчета. Можете так же добавлять к формам и отчетам элементы управления, с помощью которых будут выполняться необходимые вычисления. В определенных случаях это может способствовать повышению производительности. Это тему мы рассмотрим чуть позже.
В столбцах результатов запросов могут быть представлены результаты вычисления любого допустимого выражения, в том числе значения, выражаемые пользовательскими функциями. Благодаря этому запросы становятся чрезвычайно мощным средством.
Например, можно рассчитать стоимость товаров на основе полей Price (цена) и Quantity (количество), перемножив между собой, или вывести одним полем фамилию и первую букву имени клиента, написав следующие выражения:

Amount: [tblOrderDetails]![Price]*[tblOrderDetails]![Quantity]

Client: [tblClients]![LastName] & " " & Left([tblClients]![FirstName];1) & "." 

#Запросы #ВычисляемыеПоля

​Использование построителя выражений.

Построитель выражений представляет собой удобный инструмент создания выражений, применяемых не только в запросах, но и во многих других объектах Access. Чтобы открыть диалоговое окно Построитель выражений (Expression Builder), выберите команду Построить (Build) в контекстном меню поля бланка запроса, или команду Построитель (Builder), находящуюся на ленте. Окно Построитель выражений состоит из трех столбцов Элементы выражений (Expression Elements), Категории выражений (Expression Categories) и Значения выражений (Expression Values). В столбце Элементы выражений отображаются объекты базы данных.
Например, с помощью узла Функции (Functions) можно получить доступ к пользовательским и к встроенным функциям. Выберем категорию функций Дата/Время (Date/Time) в столбце Категории выражений, после выбора все встроенные функции для работы со значениями даты и времени появятся в столбце Значения выражений. Если пользователь дважды щелкнет по конкретной функции (к примеру, по функции DatePart()), имя самой функции и имена ее параметров помещаются в текстовое поле в верхней части окна. Обратите внимание на то, что функция DatePart() имеет четыре параметра interval, data, firstdayofweek, firstweekofyear. Если известны значения, которые необходимо подставить в качестве этих параметров, то достаточно ввести вместо обозначений параметров требуемые значения. Чтобы получить подробную информацию об определенной функции, можно вызвать справку к этой функции.

#ПостроительВыражений

​Агрегирование данных с помощью обобщающих запросов.

Обобщающие запросы позволяют легко агрегировать числовые данные. С помощью обобщающих запросов можно вычислить значения функций Sum(), Average(), Count(), Minimum(), Maximum(), а также провести другие агрегирующие вычисления над данными в результатах запроса. Также запросы позволяют вычислить одно значение, относящееся ко всем строкам в результатах запроса, или, при желании, сгруппировать вычисления. Например, можно определить итоговые значения по заказам для каждой строки в результате запроса.

TotalSales: Sum([Price]*[Quantity])

#Запросы #АгрегированиеДанных

​Исключение полей из результирующей таблицы.

Время от времени возникает необходимость включить в запрос такой столбец, данные которого не должны отображаться в выводе запроса; такая ситуация часто возникает, если данные столбца применяются исключительно в условиях выборки.
Например, необходимо получить данные о заказах, сгруппированные по клиентам за текущий 2020 год. Для этого необходимо в строке Групповая операция (Total) бланка запроса в столбе OrderDate указать значение Where. Данные столбца, используемого в конструкции Where, исключаются из результатов запроса. Это можно легко определить, поскольку видно, что отметка с флажка в строке Показать (Show) столбца OrderDate снята.

#Запросы

​Запросы. Свойства поля. Изменение поведения поля.

К свойствам поля в запросе относятся свойства Описание (Description), Формат поля (Format), Маска ввода (Input Mask), Подпись (Caption).

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

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

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

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

❗️ Важно знать, как взаимодействует, например, свойство Подпись (Caption) поля таблицы со свойством Подпись (Caption) аналогичного поля в запросе. Все свойства поля таблицы автоматически наследуются в запросах. Но свойства, явно модифицированные в запросе, переопределяют те же самые свойства, заданные применительно к полям таблицы. Кроме того, объекты, основанные на запросе, наследуют свойства запроса, а не свойства исходной таблицы.

#Запросы

​Запросы. Свойства списка полей.

Свойства Список полей (Field List Properties) задают атрибуты каждой таблицы, используемой в запросе. Предусмотрены два свойства списка полей – Псевдоним (Alias) и Источник (Source).

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

Свойство Источник (Source) определяет строку соединения или имя базы данных, если приходится иметь дело с внешними таблицами, которые не связаны с текущей базой данных.

#Запросы

​​Запросы. Свойства запроса.

В Access предусмотрено много свойств запросов, с помощью которых можно управлять поведением всего запроса.
▪️Свойство Описание (Description) предназначено для документального оформления запроса.
▪️Свойство Режим по умолчанию (Default View) определяет режим вывода результатов запроса после его выполнения. По умолчанию это свойство имеет значение Режим таблицы (Datasheet).
▪️Свойство Вывод всех полей (Output All Fields) позволяет отобразить все поля в результатах запроса, независимо от того, установлен ли флажок Показать в тех или иных полях.
▪️С помощью свойства Набор значений (Top Values) можно указать, что должна отображаться определенная начальная часть результатов запроса. Значение этого свойства задается в виде количества строк или процентов.
▪️Свойства Уникальные значения (Unique Values) и Уникальные записи (Unique Records) используются для определения того, должны ли отображаться в выводе запроса только уникальные значения или уникальные строки.
▪️Свойство База данных-источник (Source Database), Строка подключения-источник (Source Connect Str), Время ожидания ODBC (ODBC Timeout), Максимальное число записей (Max Records) полностью относятся к вопросам взаимодействия "клиент/сервер".
▪️Свойство Блокировка записей (Records Locks) учитывается при эксплуатации приложения в многопользовательском режиме.
▪️Свойство Тип набора записей (Records Type) определяет возможность выполнения обновлений применительно к выводу запроса. По умолчанию оно имеет значение Динамический набор (Dynaset), что позволяет обновлять основополагающие данные.
▪️Свойство Фильтр (Filter) определяет вывод заданного подмножества строк, а не всех результатов запроса.
▪️Свойство Порядок сортировки (Order By) определяет последовательность сортировки результатов запроса.
▪️Свойство Ориентация (Orientation) задает компоновку полей слева направо или справа налево в выводимых результатах.
▪️С помощью свойства Имя подтаблицы (Subdatasheet Name) можно указать имя подтаблицы (или запроса), данные которой будут выводиться в виде подтаблицы в текущем запросе.
▪️После определения свойства Имя подтаблицы (Subdatasheet Name) с помощью свойств Подчиненные поля (Link Child Fields) и Основные поля (Link Master Fields) можно указать, какие поля дочерних и родительских таблиц или запросов будут использоваться для связывания текущего запроса с его подтаблицей.
▪️С помощью свойства Высота подтаблицы (Subdatasheet Height) можно задать максимальную высоту подтаблицы.
▪️С помощью свойства Развернутая подтаблица (Subdatasheet Expanded) можно определить, будет ли подтаблица автоматически отображаться в развернутом виде.

#Запросы

​​Создание параметрических запросов.

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

Between [Введите начальную дату] And [Введите конечную дату]

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

Чтобы точно указать для Access, какого типа данные должны быть заданы в качестве значений параметров, необходимо определить эти параметры. Для этого выберите команду Параметры (Parameters) в контекстном меню или на вкладке Конструктор (Design) ленты в разделе Показать или скрыть (Show/Hide). В открывшемся окне Параметры запроса (Query Parameters) в столбце Параметр (Parameter) укажите текст, который указан в квадратных скобках условий запроса. В столбце Тип данных (Data Type) укажите к какому типу данных должны относиться вводимые значения.

#Запросы

​​Описание назначения форм.

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

▪️ Ввод данных. Отображение и редактирование данных.
▪️ Управление ходом выполнения работы. Перемещение по задачам приложения.
▪️ Пользовательские диалоговые окна. Предоставления сообщений пользователям.
▪️ Вывод на печать. Предоставление пользователям твердых копий введенных данных.

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

#Формы

​​Создание новой формы.

Существует несколько способов создания новой формы. Самый распространенный способ состоит в том, что вначале используется область переходов для выбора таблиц или запросов, на основании которых должна быть создана форма, после чего достаточно щелкнуть по команде Форма (Form) в группе Формы (Forms) на вкладке Создать (Create). Access создает форму на основе таблиц или запросов и переходит в режим макета, предоставляя пользователю возможность работать с этой формой.

Предусмотрено также несколько других способов, которые могут использоваться для создания формы. К этим способам относится создание:
▪️ разделенной формы (Split Form), состоящей из нескольких элементов;
▪️ пустой формы (Blank Form);
▪️ формы, в которой применяется Мастер форм (Form Wizard);
▪️ формы в виде таблицы (Datasheet);
▪️ формы в виде модального диалогового окна (Modal Dialog);
▪️ формы с помощью Конструктора форм (Form Design).

#Формы

​​Создание формы в режиме конструктора.

Безусловно, Мастер форм (Form Wizard) представляет собой мощное средство, но в большинстве случаев предпочтительным становится создание формы с нуля, особенно если создается форма, не привязанная к данным. Для создания формы без использования мастера отройте вкладку Создать (Create) и щелкните по команде Конструктор форм (Form Design).

#Формы

​​Работа со средствами проектирования форм.

В окне Конструктор форм (Form Design) имеется лента, а также развернута фактически проектируемая форма. Имеется также другие инструменты, позволяющие упростить процесс проектирования, включая окно Список полей (Field List) и Страницу свойств (Property Sheet).

После перехода в режим конструктора форм появляются три дополнительных вкладки: Конструктор (Design), Упорядочить (Arrange) и Формат (Format).
На вкладке Конструктор (Design) имеются кнопки, позволяющие переходить из одного режима в другой, добавлять элементы управления и поля, работать со свойствами формы и элементами управления.
Вкладка Упорядочить (Arrange) включает инструменты, позволяющие управлять компоновкой элементов управления на форме, применяемые для выравнивания элементов управления, для контроля над разбивкой по слоям, управления размерами и т.д..
Вкладка Формат (Format) включает инструменты, позволяющие управлять внешним видом формы, применять стили, условное форматирование, заливки, изменять шрифты и размеры шрифтов и т.д..

#Формы