Home-theater-designers
Microsoft Access, возможно, самый мощный инструмент во всем пакете Microsoft Office, но он вводит в заблуждение (а иногда и пугает) опытных пользователей Office. С более крутой кривой обучения, чем Word или Excel, как можно осмыслить использование этого инструмента? На этой неделе Брюс Эппер рассмотрит некоторые проблемы, вызванные этим вопросом одного из наших читателей.
Читатель спрашивает:
У меня проблемы с написанием запроса в Microsoft Access. У меня есть база данных с двумя таблицами продуктов, содержащими общий столбец с числовым кодом продукта и соответствующим названием продукта. Я хочу узнать, какие продукты из таблицы A можно найти в таблице B. Я хочу добавить столбец с именем «Результаты», который содержит название продукта из таблицы A, если он существует, и название продукта из таблицы B, если его нет в таблице A. Есть ли у вас какие-либо советы?
Ответ Брюса:
Microsoft Access - это система управления базами данных (СУБД), предназначенная для использования как на компьютерах Windows, так и на Mac. Он использует ядро базы данных Microsoft Jet для обработки и хранения данных. Он также предоставляет пользователям графический интерфейс, который почти исключает необходимость понимания языка структурированных запросов (SQL).
SQL - это командный язык, используемый для добавления, удаления, обновления и возврата информации, хранящейся в базе данных, а также для изменения основных компонентов базы данных, таких как добавление, удаление или изменение таблиц или индексов.
Отправная точка
Если вы еще не знакомы с Access или другой СУБД, я бы посоветовал вам начать с этих ресурсов, прежде чем продолжить:
- Итак, что такое база данных? где Райан Дьюб использует Excel, чтобы показать основы реляционных баз данных.
- Краткое руководство по началу работы с Microsoft Access 2007 который представляет собой общий обзор Access и компонентов, составляющих базу данных Access.
- Краткое руководство по таблицам в Microsoft Access 2007 рассматривает создание вашей первой базы данных и таблиц для хранения структурированных данных.
- Краткое руководство по запросам в Microsoft Access 2007 рассматривает средства для возврата определенных частей данных, хранящихся в таблицах базы данных.
Базовое понимание концепций, представленных в этих статьях, поможет немного легче усвоить нижеследующее.
Отношения с базой данных и нормализация
Представьте, что вы управляете компанией, продающей 50 различных типов виджетов по всему миру. Ваша клиентская база составляет 1250 человек, и в среднем в месяц вы продаете этим клиентам 10 000 виджетов. В настоящее время вы используете одну электронную таблицу для отслеживания всех этих продаж - по сути, одну таблицу базы данных. И каждый год в вашу электронную таблицу добавляются тысячи строк.
Приведенные выше изображения являются частью используемой вами электронной таблицы отслеживания заказов. Теперь предположим, что оба этих клиента покупают у вас виджеты несколько раз в год, так что у вас будет гораздо больше строк для них обоих.
Если Джоан Смит выйдет замуж за Теда Бейнса и возьмет его фамилию, каждую строку, содержащую ее имя, теперь необходимо изменить. Проблема усугубляется, если у вас есть два разных клиента с именем «Джоан Смит». Просто стало намного сложнее поддерживать согласованность данных о продажах из-за довольно распространенного события.
Используя базу данных и нормализовав данные, мы можем разделить элементы на несколько таблиц, таких как инвентарь, клиенты и заказы.
Просто глядя на клиентскую часть нашего примера, мы удалим столбцы для имени клиента и адреса клиента и поместим их в новую таблицу. На изображении выше я также улучшил детализацию для более детального доступа к данным. Новая таблица также содержит столбец для первичного ключа (ClientID) - числа, которое будет использоваться для доступа к каждой строке в этой таблице.
В исходной таблице, из которой мы удалили эти данные, мы должны добавить столбец для внешнего ключа (ClientID), который ссылается на соответствующую строку, содержащую информацию для этого конкретного клиента.
Теперь, когда Джоан Смит меняет свое имя на Джоан Бейнс, изменение нужно сделать только один раз в таблице «Клиент». Любая другая ссылка из объединенных таблиц будет извлекать правильное имя клиента, а отчет, в котором рассматривается то, что Джоан приобрела за последние 5 лет, будет получать все заказы как под ее девичьей, так и с женской фамилией, без необходимости изменять способ создания отчета. .
В качестве дополнительного преимущества это также снижает общий объем потребляемой памяти.
Типы соединений
SQL определяет пять различных типов объединений: INNER, LEFT OUTER, RIGHT OUTER, FULL OUTER и CROSS. Ключевое слово OUTER не является обязательным в операторе SQL.
Microsoft Access позволяет использовать INNER (по умолчанию), LEFT OUTER, RIGHT OUTER и CROSS. FULL OUTER как таковой не поддерживается, но с помощью LEFT OUTER, UNION ALL и RIGHT OUTER его можно подделать за счет большего количества циклов ЦП и операций ввода-вывода.
Выходные данные CROSS-соединения содержат каждую строку левой таблицы в паре с каждой строкой правой таблицы. Единственный раз, когда я видел использование CROSS-соединения, - это во время нагрузочного тестирования серверов баз данных.
Давайте посмотрим, как работают базовые объединения, а затем изменим их в соответствии с нашими потребностями.
Начнем с создания двух таблиц, ProdA и ProdB, со следующими свойствами дизайна.
AutoNumber - это автоматически увеличивающееся длинное целое число, присваиваемое записям по мере их добавления в таблицу. Параметр «Текст» не был изменен, поэтому он принимает текстовую строку длиной до 255 символов.
Теперь заполните их некоторыми данными.
Чтобы показать различия в том, как работают 3 типа соединения, я удалил записи 1, 5 и 8 из ProdA.
Следующий, создать новый запрос перейдя в Создать> Дизайн запроса . Выберите обе таблицы в диалоговом окне Показать таблицу и нажмите Добавить , тогда Закрывать .
Щелкните ProductID в таблице ProdA, перетащите его на ProductID в таблице ProdB и отпустите кнопку мыши, чтобы создать связь между таблицами.
Щелкните правой кнопкой мыши строку между таблицами, представляющими отношения между элементами и выберите Присоединиться к свойствам .
По умолчанию выбран тип соединения 1 (INNER). Вариант 2 - это соединение LEFT OUTER, а 3 - соединение RIGHT OUTER.
Сначала мы рассмотрим ВНУТРЕННЕЕ соединение, поэтому нажмите OK, чтобы закрыть диалоговое окно.
В конструкторе запросов выберите поля, которые мы хотим видеть, из раскрывающихся списков.
Когда мы запускаем запрос (красный восклицательный знак на ленте), он покажет поле ProductName из обеих таблиц со значением из таблицы ProdA в первом столбце и ProdB во втором.
Обратите внимание, что результаты показывают только значения, в которых ProductID равен в обеих таблицах. Несмотря на то, что в таблице ProdB есть запись для ProductID = 1, она не отображается в результатах, поскольку ProductID = 1 не существует в таблице ProdA. То же самое относится к ProductID = 11. Он существует в таблице ProdA, но отсутствует в таблице ProdB.
Используя кнопку «Просмотр» на ленте и переключившись в представление SQL, вы можете увидеть SQL-запрос, сгенерированный дизайнером, который использовался для получения этих результатов.
SELECT ProdA.ProductName, ProdB.ProductName FROM ProdA INNER JOIN ProdB ON ProdA.ProductID = ProdB.ProductID;Вернувшись в представление «Дизайн», измените тип соединения на 2 (ЛЕВЫЙ ВНЕШНИЙ). Запустите запрос, чтобы увидеть результаты.
Как видите, каждая запись в таблице ProdA представлена в результатах, в то время как в результатах отображаются только те записи в ProdB, которые имеют соответствующую запись ProductID в таблице ProdB.
Пробел в столбце ProdB.ProductName - это специальное значение (NULL), поскольку в таблице ProdB нет соответствующего значения. Это окажется важным позже.
SELECT ProdA.ProductName, ProdB.ProductName FROM ProdA LEFT JOIN ProdB ON ProdA.ProductID = ProdB.ProductID;Попробуйте то же самое с третьим типом соединения (RIGHT OUTER).
Результаты показывают все из таблицы ProdB, в то время как она показывает пустые (известные как NULL) значения, в которых таблица ProdA не имеет совпадающего значения. Пока что это приближает нас к результатам, желаемым в вопросе нашего читателя.
SELECT ProdA.ProductName, ProdB.ProductName FROM ProdA RIGHT JOIN ProdB ON ProdA.ProductID = ProdB.ProductID;Использование функций в запросе
Результаты функции также могут быть возвращены как часть запроса. Мы хотим, чтобы в нашем наборе результатов появился новый столбец с именем «Результаты». Его значение будет содержимым столбца ProductName таблицы ProdA, если ProdA имеет значение (оно не равно NULL), в противном случае оно должно быть взято из таблицы ProdB.
Для получения этого результата можно использовать функцию немедленного IF (IIF). Функция принимает три параметра. Первое - это условие, которое должно оцениваться как истинное или ложное значение. Второй параметр - это значение, которое должно быть возвращено, если условие истинно, а третий параметр - это значение, которое должно быть возвращено, если условие имеет значение Ложь.
Полная конструкция функции для нашей ситуации выглядит так:
IIF(ProdA.ProductID Is Null, ProdB.ProductName,ProdA.ProductName)Обратите внимание, что параметр условия не проверяет равенство. Значение Null в базе данных не имеет значения, которое можно было бы сравнить с любым другим значением, включая другое значение Null. Другими словами, Null не равно Null. Всегда. Чтобы обойти это, мы вместо этого проверяем значение с помощью ключевого слова Is.
Мы также могли бы использовать Is Not Null и изменить порядок параметров True и False, чтобы получить тот же результат.
Помещая это в конструктор запросов, вы должны ввести всю функцию в поле Поле :. Чтобы заставить его создать столбец «Результаты», вам нужно использовать псевдоним. Для этого перед функцией укажите «Результаты:», как показано на следующем снимке экрана.
Эквивалентный код SQL для этого будет:
SELECT ProdA.ProductName, ProdB.ProductName, IIF(ProdA.ProductID Is Null,ProdB.ProductName,ProdA.ProductName) AS Results FROM ProdA RIGHT JOIN ProdB ON ProdA.ProductID = ProdB.ProductID;Теперь, когда мы запустим этот запрос, он выдаст такие результаты.
как добавить приложения на vizio tv
Здесь мы видим, что для каждой записи, в которой таблица ProdA имеет значение, это значение отражается в столбце «Результаты». Если в таблице ProdA нет записи, запись из ProdB появляется в результатах, что именно то, что спросил наш читатель.
Дополнительные ресурсы для изучения Microsoft Access см. В книге Джоэла Ли «Как изучить Microsoft Access: 5 бесплатных онлайн-ресурсов».
Делиться Делиться Твитнуть Эл. адрес Стоит ли переходить на Windows 11?
Окна были переработаны. Но достаточно ли этого, чтобы убедить вас перейти с Windows 10 на Windows 11?
Читать далее Похожие темы- Продуктивность
- Спросите у экспертов
Брюс играет с электроникой с 70-х, компьютерами с начала 80-х и точно отвечает на вопросы о технологиях, которые он не использовал и не видел все время. Он также раздражает себя, пытаясь играть на гитаре.
Ещё от Bruce EpperПодписывайтесь на нашу новостную рассылку
Подпишитесь на нашу рассылку, чтобы получать технические советы, обзоры, бесплатные электронные книги и эксклюзивные предложения!
Нажмите здесь, чтобы подписаться