Как разбить таблицу в access

Как разбить таблицу в access

Реляционная модель баз данных подразумевает под собой понятие уровней нормальности таблиц. Есть пять уровней. С ними мы познакомимся попозже, а пока посмотрим как в таблице бороться с избыточностью. Давате к нашей базе данных «Телефоны» добавим поле должность. Дл этого выделите таблицу и нажмите кнопку «Конструктор». Внесите поле и закройте его. Также внесите вот такие данные.

Как видите поле «Должность» имеет повторяющиеся значения. То есть количество названий специальностей ограничено и они будут повторяться. В этом нет ничего страшного, но вот такой вопрос. Что вы будете делать, если специальность программист будет переименована в программер 🙂 искать во всей таблице . А если у Вас людей работает тысячи. Кроме того под это поле нужно выделять место. Проще будет поставить код специальности и место будет меньше. Одно из главных правил «Повторяющиеся» записи должны быть преобразованы в отдельную таблицу. Можно сказать, что мы создаем классификатор. Слово классификатор больше по смыслу подходит. Итак, нам нужна таблица с наваниями специальностей. Давайте ее создадим. Вот такую.

Занесите в нее названия специальностей. Их всего две: программист и оператор.

Вот теперь нам надо связать эти две таблицы. Откройте таблицу «телефоны» в конструкторе. Удалите поле должность. Будем создавать новое поле, но начнем сразу с выбора типа поля. Там есть пункт мастер подстановок.

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

Между двумя таблицами существует связь. Мы с Вами сейчас сделали довольно большой шаг вперед. Мы из большой таблицы выделили в отдельную таблицу классификатор. Это операция является основой структуризирования информации в базах данных.

TarasovVL_LectureAccess

но Изменение страницы кнопочной формы , рис. 8.5, а затем окно Диспетчера кнопочных форм , рис. 8.2. В списке форм окна базы данных появится форма с названием Switchboard , а в списке таблиц появится таблица Switchboard Items , содержащая описания элементов кнопочной формы, рис. 8.7.

Рис. 8.7. Таблица, описывающая элементы кнопочной формы

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

Вывод формы/страницы выбираем главную кнопочную форму.

Рис. 8.8. Выбор формы, начинающей работу с базой данных

Созданная кнопочная форма показана на рис. 8.9.

Рис. 8.9. Вид главной кнопочной формы

8.2. Анализ базы данных

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

8.2.1. Анализ таблиц

С помощью анализа таблиц можно выявить повторяющиеся данные в таблицах и разместить повторяющиеся сведения в новых таблицах.

Выполним команду Сервис, Анализ, Таблица . Начнет работать мастер анализа таблиц. Первые два диалога показаны на рисунках 8.10, 8.11. Они объясняют, как таблицу можно разделить на две или более для устранения избыточности данных.

Рис. 8.10. Первый диалог мастера анализа таблиц с объяснением постановки задачи

Рис. 8.11. Второй диалог мастера анализа таблиц с примером

На третьем диалоге, рис. 8.12, выбираем таблицу для анализа, например, Постав-

Рис. 8.12. Выбор таблицы для анализа

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

Рис. 8.13. Выбор способа разделения таблицы

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

Рис. 8.14. Информация мастера анализа таблиц

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

Рис. 8.15. Разделение таблицы

Ряд действий в окне Анализ таблиц можно выполнить с помощью кнопок: – выводит окно с советами; – переименовать таблицу; – сделать поле ключевым;

– добавить ключевое поле.

Окно с перечнем советов приведено на рис. 8.16.

Рис. 8.16. Советы по разделению таблиц

После нажатия кнопки Далее на диалоге, показанном на рис. 8.15, появится диалог с вопросом о создании запроса вместо старой таблицы, рис. 8.17. Если согласиться на создание запроса, то будет создан запрос Поставщики , который будет использован везде, где ранее использовалась таблица Поставщики , а сама таблица Поставщики будет названа

Рис. 8.17. Предложение создать запрос вместо разделенной таблицы

Откажемся от замены старой таблицы запросом. В результате будут созданы две новые таблицы, рис. 8.18. Далее можно решить, использовать ли их вместо одной старой или нет.

Рис. 8.18. Две таблицы, полученные из таблицы Поставщики

8.3. Защита базы данных

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

8.3.1. Установка пароля

Установка пароля возможна, если база данных открыта в режиме монопольного доступа . Для этого при открытии БД нужно выбрать режим Монопольно в списке кнопки

Открыть диалога Открытие файла базы данных , рис. 8.19.

Рис. 8.19. Выбор режима открытия базы данных

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

Рис. 8.20. Задание пароля

Рис. 8.21. Ввод пароля при открытии базы данных

8.3.2. Удаление пароля

Для удаления пароля базу данных следует открыть в монопольном режиме, рис. 8.19. Если пароль установлен, то в меню появится команда Сервис, Защита, Удалить пароль базы данных… , выводящая окно, рис.8.22, на котором нужно ввести старый пароль. После нажатия OK пароль будет удален.

Рис. 8.22. Ввод старого пароля при его удалении

Объекты Модули Access содержат программный код на языке программирования

Visual Basic for Application (VBA). Этот язык встроен в Access, он также используется в

Word, Excel и некоторых других приложениях. VBA не следует путать с языком Visual Basic (VB), который является независимым средством разработки. Язык Basic (Beginners All – purpose Symbolic Instruction Code – универсальный язык для начинающих) появился в 1964 г. В 1992 г. Microsoft выпустила первую систему визуальной разработки приложений Visual Basic. В 1994 г. появился языкVBA для использования в составе других приложений.

9.1. Первая программа

Командой Сервис , Макрос , Редактор Visual Basic в отдельном, независимом от Access окне загружается редактор VBA, рис. 9.1. Здесь может быть открыто несколько окон различного назначения, для которых родительским окном будет окно редактора. Видом среды VBA управляют команды меню View .

Рис. 9.1. Редактор VBA и контекстное меню для проекта

Рис. 9.2. Настройка свойств проекта

С каждой базой данных связывается проект VBA, структура которого показывается

в окне Project . Командой контекстного меню Properties , рис. 9.1, выводится окно свойств

проекта, рис.9.2, в котором можно сделать некоторые настройки, например, изменить имя проекта, задать описание проекта в поле Project Description , связать с проектом файл справки в поле Help File Name .

Новый модуль кода включается в проект, если в разделе Модули окна базы данных нажать кнопку Создать или выполнить команду Insert , Module в редакторе VBA. Модуль по умолчанию будет назван Module1 . Выполним эту команду и в появившемся окне редактирования программы, рис.9.3, введем следующий код:

Здесь слово Sub начинает подпрограмму, слова End Sub завершают подпрограмму. Стандартная функция MsgBox создает на экране окно и выводит в нем текст, передаваемый ей в качестве аргумента.

В состав модуля могут входить несколько подпрограмм.

Выполним команду File , Save , рис.9.3, и сохраним модуль под именем Module_Hello .

Рис. 9.3. Сохранение модуля

Для запуска программы выполним команду Run , Run Sub/UserForm или просто нажмем F5 . Результат работы программы показан на рис.9.4.

Рис. 9.4. Окно работающего программного модуля

Модуль из проекта удаляется командой File, Remove или такой же командой контекстного меню.

9.2. Краткий обзор языка VBA

9.2.1. Алфавит и лексика

Алфавит включает большинство символов кодовой таблицы, в том числе и русские буквы, причем русские буквы можно использовать при образовании имен объектов программы, но надо помнить, что это справедливо лишь в локализованной (русифицированной) версии Access. Большие и малые буквы считаются одинаковыми.

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

Простой способ разбить большую таблицу в MS Access

У меня есть таблица в базе данных MS Access 2010, и ее можно легко разделить на несколько таблиц. Однако я не знаю, как это сделать, и до сих пор связываю все данные вместе. Кто-нибудь знает простой способ это сделать?

7 ответов

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

Вы должны перейти на другую систему баз данных, например MSSQL, mySQL. Вы не можете сделать репликацию в MsAccess .

Не понимаю, что вы имеете в виду под split up into multiple tables .

Две таблицы имеют одинаковую структуру? вы хотите разделить таблицу на две части . означает, что если исходная таблица имеет поля A, B, C, D . тогда вы хотите разделить ее на Table1: A, B и Table2: C, D.

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

Где у вас проблемы с мастером анализатора таблиц? Возможно, вы сможете обойти проблему, с которой столкнулись.

В Microsoft Access 2012, Инструменты баз данных, Анализ таблицы . Я использую мастер, чтобы разделить большую таблицу на несколько нормализованных таблиц. Надеюсь, это поможет.

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

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

А) Процедура создания новой таблицы через SQL: CREATE TABLE t002 (ID002 INTEGER PRIMARY KEY, CONSTRAINT SomeName FOREIGN KEY (ID002) REFERENCES t001(ID001)); Две таблицы связаны друг с другом через внешний ключ.

Б) Процедура добавления необходимых полей в новую таблицу (t002). В следующем примере кода мы будем использовать только одно поле и назовем его [MyFieldName].

C) Процедура добавления всех значений поля ID001 из таблицы t001 в поле ID002 в таблице t002 через SQL: INSERT INTO ID002 (t002) SELECT t001.ID001 FROM t001;

D) Процедура передачи значений из полей в t001 в поля в t001 через SQL: UPDATE t001 INNER JOIN t002 ON t001.ID001 = t002.ID002 SET t002.MyFieldName = t001.MyFieldName;

E) Процедура удаления (отбрасывания) рассматриваемых полей в таблице t001 с помощью SQL: ALTER TABLE t001 DROP COLUMN MyFieldName;

Е) Процедура, которая вызывает их все по очереди. Имена полей передаются в процесс как параметры при вызове процедуры f.

Как разделить одно поле на несколько в Microsoft Access ⁠ ⁠

Расскажу и пошагово покажу, как разделить одно поле на несколько полей, используя встроенные функции Microsoft Access.

Возьмем для примера поле «ФИО». Задача будет следующая: раскидать отдельно по полям фамилию, имя и отчество.

Для решения поставленной задачи нам понадобятся функции: Left, Mid, InStr и IIF. Никакого VBA использовать не будем. Только конструктор, только хардкор)))

Также отмечу, что в данном примере фамилия, имя и отчество разделены одиночными пробелами.

Соответственно, чтобы вытащить фамилию, нам надо взять определенное количество знаков с левой части строки «ФИО». Здесь нам поможет функция LEFT.

У функции LEFT очень простой синтаксис.

LEFT([Строка];Число). [Строка] — это поле, с которым мы работаем. В нашем примере — поле «ФИО». Число — количество знаков, которое вытащит функция, с левой части поля «ФИО». Но проблема в том, что мы не знаем сколько символов указывать с левой стороны строки ФИО, так как у каждой персоны количество символов в фамилии разное.

Соответственно, нам нужна позиция первого пробела в поле «ФИО» между фамилией и именем. и здесь нам на помощь приходит функция InStr. Данная функция ищет позицию вхождения указанного символа в строке. InStr([Строка];Символ). [Строка] — это поле, в котором мы будем искать символ или группу символов. Символ — это то, что мы ищем.

В нашем случае получается: InStr([ФИО];» «). Далее данное выражение мы помещаем во второй параметр функции LEFT. Чтобы в поле «Фамилия» у нас не было лишнего пробела, для функции LEFT мы количество знаков во втором параметры уменьшаем на единицу.

Проверяем. Фамилия есть.

Далее нам необходимо найти позицию второго пробела в поле «ФИО» между именем и отчеством. Но для начала, мы вы вытащим из поля «ФИО» только имя и отчество. Здесь нам поможет функция Mid. Данная функция возвращает указанное количество знаков, начиная с указанного места строки. Mid([Строка];Старт;Длина). [Строка] — это поле, с которым мы работаем, Старт — позиция в строке, с которой мы будем вытаскивать символы, Длина — сколько символов мы будем вытаскивать. Если Длина не указана, функция Mid берет символы с позиции Старт и до конца строки.

В нашем примере в качестве второго параметра функции Mid будет использована функция InStr, которая в свою очередь ищет первый пробел в поле ФИО. Соответственно, InStr([ФИО];» «) — это позиция Старт для функции Mid. Получаем: Mid([ФИО];InStr([ФИО];» «))

Чтобы не было лишнего пробела, позицию Старт увеличиваем на 1 символ.

Запоминаем, что Mid([ФИО];InStr([ФИО];» «)+1) выводит имя и отчество без фамилии.

И теперь здесь мы будем искать номер позиции пробела между именем и отчеством.

Напомню, за поиск символа отвечает InStr.

Позицию пробела находим так: InStr(Mid([ФИО];InStr([ФИО];» «)+1);» «)-1

Снова воспользуемся функцией LEFT. Получим, Left(Mid([ФИО];InStr([ФИО];» «)+1);InStr(Mid([ФИО];InStr([ФИО];» «)+1);» «)-1)

Чтобы не запутаться: Mid([ФИО];InStr([ФИО];» «)+1) — это первый параметр функции LEFT (строка ИО (имя и отчество), с которой мы работаем). InStr(Mid([ФИО];InStr([ФИО];» «)+1);» «)-1 — второй параметр (сколько символов мы берем с начала ИО и до пробела).

В примере, есть записи без отчества, в таких случаях мы получим ошибку #Функ!. Пока не обращаем внимания, вернемся к этому моменту чуть позже.

Фамилию и имя получили, осталось отчество.

Здесь напишем: Mid(Mid([ФИО];InStr([ФИО];» «)+1);InStr(Mid([ФИО];InStr([ФИО];» «)+1);» «)+1), где

Mid([ФИО];InStr([ФИО];» «)+1) — 1 параметр функции Mid, который указывает, что мы работаем с полем ИО. InStr(Mid([ФИО];InStr([ФИО];» «)+1);» «)+1 — второй параметр, позиция, с которой мы начинаем вытаскивать отчество, т.е с первого символа сразу после пробела.

Отчество выводится отдельно, но только в тех случаях, когда у нас хранится полное ФИО.

Вернемся к полю «Имя». Исправим ошибку #Функ! Воспользуемся функцией IIF. Синтаксис: IIF(Условие;ДА;НЕТ). Условие — это выражение, которое мы будем использовать в качестве проверки, ДА — выражение, которое вернется если условие ДА, НЕТ — выражение, которое вернется если условие НЕТ.

Соответственно, нам надо проверить равна ли 0 позиция пробела между именем и отчеством. У 8 и 9 записи пробела после имени нет, следовательно позиция равна 0.

Если позиция ровна 0: IIf(InStr(Mid([ФИО];InStr([ФИО];» «)+1);» «)=0;

тогда из ИО вытаскиваем имя, используя: Mid([ФИО];InStr([ФИО];» «)+1)

иначе Left(Mid([ФИО];InStr([ФИО];» «)+1);InStr(Mid([ФИО];InStr([ФИО];» «)+1);» «)-1))

Целиком получаем: IIf(InStr(Mid([ФИО];InStr([ФИО];» «)+1);» «)=0;Mid([ФИО];InStr([ФИО];» «)+1);Left(Mid([ФИО];InStr([ФИО];» «)+1);InStr(Mid([ФИО];InStr([ФИО];» «)+1);» «)-1))

Аналогично с отчеством.

Если позиция ровна 0: InStr(Mid([ФИО];InStr([ФИО];» «)+1);» «)=0

тогда в поле «Отчество» мы помещаем значение NULL (пусто),

иначе помещаем отчество: Mid(Mid([ФИО];InStr([ФИО];» «)+1);InStr(Mid([ФИО];InStr([ФИО];» «)+1);» «)+1)

Целиком получаем: Отчество: IIf(InStr(Mid([ФИО];InStr([ФИО];» «)+1);» «)=0;Null;Mid(Mid([ФИО];InStr([ФИО];» «)+1);InStr(Mid([ФИО];InStr([ФИО];» «)+1);» «)+1))

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

Надеюсь помог! Это первый длиннопост, поэтому не судите строго!

635 постов 14.5K подписчиков

Правила сообщества

2. Публиковать посты соответствующие тематике сообщества

3. Проявлять уважение к пользователям

4. Не допускается публикация постов с вопросами, ответы на которые легко найти с помощью любого поискового сайта.

По интересующим вопросам можно обратиться к автору поста схожей тематики, либо к пользователям в комментариях

Важно — сообщество призвано помочь, а не постебаться над постами авторов! Помните, не все обладают 100 процентными знаниями и навыками работы с Office. Хотя вы и можете написать, что вы знали об описываемом приёме раньше, пост неинтересный и т.п. и т.д., просьба воздержаться от подобных комментариев, вместо этого предложите способ лучше, либо дополните его своей полезной информацией и вам будут благодарны пользователи.

Утверждения вроде «пост — отстой», это оскорбление автора и будет наказываться баном.

Но в БД как было одно поле, так и осталось. Вопрос не раскрыт

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

блин, пипец, как неудобно. спецом посмотрел в access, реально нет более простого способа :0

для Excel сразу вспомнил «текст по столбцам» — решается этим инструментом в пару кликов. не говорю уже про power query и другие мега штуки Excel, но вот, чтобы спец.инструмент, база данных, решал таким образом такую тривиальную задачу. одна причина — мелкософту неинтересно поддерживать эту прогу, поэтому ее не развивают, как excel

Реально кто то еще юзает Ацесс?

Приводим данные в порядок с помощью Microsoft Access (часть8)⁠ ⁠

Microsoft Access предоставляет множество визуальных средств разработки приложения по работе с базой данных. Но рано или поздно возникает необходимость в более тонкой настройке отдельных ее частей и добавление нового функционала, который невозможно реализовать с помощью мастеров и конструкторов. Для этого Microsoft Access да и все программы из пакета MS Office могут расширять свой функционал с помощью встроенного языка программирования VBA (Visual Basic for Applications). Изучив основы языка VBA на одном из продуктов MS Office, в нашем случае это Access, его можно применять в других программах (Excel, Word, Power Point). Задачи автоматизации рутинных процессов не меняются долгое время. Не вдаваясь в новомодные течения, круг задач которые решает VBA будет востребован еще долго. Преимущества VBA в том, что он консервативен, но по прежнему востребован и не подвержен безумным обновлениям, что позволяет неискушенному пользователю спокойно и стабильно работать.

Создадим форму, которая всегда будет открываться при запуске нашей базы. Так называемое главное окно. На вкладке Создание нажмем Пустая форма.

Назовем ее main.

Далее Файл -> Параметры -> Текущая база данных, опция Форма просмотра, в выпадающем списке выберем форму main. Форма будет открываться при запуске приложения и с помощью нее можно организовать навигацию и доступ к другим объектам базы данных.

На вкладке Работа с базами данных нажмем на кнопку Visual Basic. Откроется окно редактора VBA. В редакторе мы будем писать код, который сможет обращаться ко всем нашим объектам (таблицы, формы, запросы, отчеты и т. п.) и принадлежащим им элементам, менять их свойства и поведение.

Минимальной структурной единицей программы на VBA является процедура (ее еще называют макросом). Процедура начинается со служебного слова Sub, далее следует имя процедуры (как правило отражает назначение процедуры), круглые скобки, заканчивается процедура служебной конструкцией End Sub. Внутри процедуры пишется код на языке VBA.

Sub MyFirstProcedure()

<Код на языке VBA>

На панели инструментов нажмем Insert — > Module. Модуль это еще один объект нашей базы. В нем мы будем размещать наши процедуры, а затем вызывать их на исполнение. В окне кода модуля Module1 напишем следующее:

Sub MyFirstProcedure()

Debug.Print “Hello, world!”

Убедимся что у нас открыто окно Immediate (View -> Immediate Window), оно необходимо для того, чтобы иметь возможность увидеть результаты выполнения программы в процессе ее отладки. На панели инструментов нажмем зленый треугольник (Run Sub), в окне Immediate увидим результат выполнения процедуры.

Теперь выведем наше приветствие на форму main. В режиме конструктора добавим текстовое поле и в окне свойств поля дадим ему имя txtHello.

С помощью VBA обратимся к этому полю из процедуры и поместим в него текст — Hello, world! Процедура будет иметь следующий вид:

Sub MyFirstProcedure()

Form_main.txtHello.Value = «Hello, world!»

Запустим ее на исполнение и убедимся, что текстовое поле txtHello формы main получило значение — «Hello, world!»

Приводим данные в порядок с помощью Microsoft Access (часть7)⁠ ⁠

Продолжим знакомство с SQL. Рассмотрим запросы на вставку, обновление и удаление данных.

Запрос на вставку данных начинается с оператора INSERT INTO, далее следует имя таблицы и в скобках перечисление полей таблицы в которые мы хотим добавить данные, затем оператор VALUES и в скобках значения полей через запятую. Важно правильно соотнести перечисление полей и значений, иначе из-за несовпадения типов вставка не произойдет или произойдет со вставкой данных не в те поля. Заметим, что текстовые значения полей мы заключаем в двойные кавычки, а вот значение поля [companyid] нет, так как значение данного поля числовое.

Запрос на вставку данных будет иметь вид:

INSERT INTO contacts (name, surname, email, companyid)

VALUES («Мария», «Пыхтина», «mariya85@ya.ru», 1);

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

Убедимся, что данные добавлены корректно.

Запрос на обновление предназначен для изменения данных полей. Начинается запрос с оператора UPDATE, затем следует имя таблицы, оператор SET и перечисление полей с их новыми значениями.

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

Запрос на обновление данных будет иметь вид:

UPDATE contacts SET email=»mariya85@rambler.ru» WHERE >

Убедимся, что данные обновились.

Запрос на удаление начинается с оператора DELETE, затем следует символ (*) — что означает удаление записи полностью, если же нам надо удалить значение нескольких полей мы пользуемся запросом на обновление, затем оператор FROM с указанием имени таблицы, сразу же добавим секцию WHERE иначе будут удалены все записи таблицы.

Удаленные записи нельзя будет восстановить. Поэтому делать это нужно крайне аккуратно. И не забывать о том, что у нас есть связанные данные в других таблицах, которые тоже придется удалять. Чаще всего данные отмечаются каким-либо признаком и просто исключаются из выборки. Для этого у нас может послужить поле [active]. Так же при удалении записи, значение поля [id] с типом данных счетчик исключается из счетчика.

Запрос на удаление данных будет иметь вид:

DELETE * FROM contacts WHERE id =21;

Убедимся, что указанная запись удалена.

Приводим данные в порядок с помощью Microsoft Access (часть6)⁠ ⁠

Основу работы с реляционными базами данных составляет язык структурированных запросов SQL (Structured Query Language). Это набор инструкций по манипулированию объектами базы данных, с помощью них мы можем создавать таблицы, добавлять в них данные, производить выборку данных, удалять данные и удалять таблицы. Для всего этого мы пользовались соответствующими мастерами, но под капотом Access формулировал эти задачи на языке SQL и даже генерировал для нас готовый SQL код. Основные команды языка применимы к большинству реляционных баз данных (MS SQL Server, Oracle, MySQL, PostgreSQL, SQLite и т. п.), это устоявшийся стандарт в мире реляционных баз. Различные СУБД расширяют возможности SQL собственным функционалом, но основной синтаксисе с незначительными особенностями применим ко всем.

Откроем наш запрос contacts в режиме SQL. В окне построителя запросов мы наблюдаем код на языке SQL, код сгенерировал Access, но мы бы могли написать его сами и он бы заработал. Разберемся в структуре SQL запросов на более простом примере.

Самыми распространенными запросами являются:

запрос на выборку;

запрос на вставку;

запрос на удаление.

Самый простой запрос на выборку начинается с оператора SELECT, затем следует перечисление необходимых полей, затем оператор FROM после чего указывается имя таблицы (таблиц) которым принадлежат эти поля, например:

SELECT name, surname, email FROM contacts;

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

Через вкладку Создание откроем уже не Мастер запросов, а Конструктор запросов. Не будем выбирать таблицу предложенную диалоговым окном и перейдем в SQL режим. Напишем здесь уже известную нам конструкцию SELECT name, surname, email FROM contacts; и отправим ее на исполнение нажав на кнопку с красным восклицательным знаком.

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

Заменим перечисление полей в секции SELECT на символ * (звездочка):

SELECT * FROM contacts; и выполним запрос.

Результатом запроса будут все записи и все поля таблицы contacts.

Предположим нас интересуют только активные контакты. Реализуем это в SQL запросе, для этого существует оператор WHERE, это оператор условия. Запрос будет выглядеть следующим образом:

SELECT * FROM contacts WHERE active=true;

Иными словами показать все записи таблицы contacts где поле [active] имеет значение ИСТИНЫ, в SQL коде мы уже строго придерживаемся английского значения слова ИСТИНА – true и ЛОЖЬ – false.

Мы можем задавать несколько значений в секции WHERE. …WHERE условие1 AND условие2 AND условие3, перечисляя их через логические операторы AND. Например:

SELECT * FROM contacts WHERE active=true AND [regdate]= #27/10/2022#;

Запрос покажет все записи из таблицы contacts со значением поля [active] равным ИСТИНА и датой регистрации [regdate] равной 27.10.2022, заметим что дату мы обернули в символ решетки и разделили день, месяц, год через слеш, это необходимо при работе с датами в запросах.

В нашем запросе contacts, помимо таблицы contacts, есть еще таблица phones. В запросах мы можем указывать больше одной таблицы, но необходимо указывать по каким полям будет объединяться результат, для этого существует оператор INNER JOIN. Вообще тема объединения таблиц довольна обширна, мы же рассмотрим самый распространенный вид объединения — внутренне. Наш запрос усложняется наличием двух таблиц, имена полей теперь необходимо указывать в формате – имя_таблица.имя_поля, так как в разных таблицах могут находится поля с одинаковыми именами и как мы помним у нас в обоих таблицах есть поле [id]. Запрос на объединение таблицы contacts и phones c возможностью выбора полей из обоих таблиц будет выглядеть следующим образом:

SELECT contacts.name , contacts.surname, contacts.email , phones.phone

FROMcontacts INNERJOIN phones ON contacts.id = phones.userid;

Иными словами код говорит – выбрать поля [name], [surname], [email], [phone] из объединенных таблиц contacts и phones, где каждой записи таблицы contacts соответствует запись из таблицы phones, в которых поля [id] и [userid] совпадают.

Выполним запрос нажав на красный восклицательный знак.

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

Дополним наш запрос секцией WHERE, для выбора только активных контактов, при этом само поле [active] выводить не обязательно.

FROM contacts INNER JOIN phones ON contacts.id = phones.userid

WHERE active=true;

Приводим данные в порядок с помощью Microsoft Access (часть5)⁠ ⁠

В этой части мы познакомимся с ещё одним замечательным объектом Access – Отчеты (report).

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

На вкладке Создание выберем Мастер отчетов. В диалоговом окне в качестве источника данных выберем Запрос contacts. Заметим, что если бы мы выбрали в качестве источника таблицу, то на выбор у нас бы был только набор полей из этой таблицы, а в запросе contacts у нас есть набор полей из разных таблиц. Хотя мастер отчетов позволяет выбирать несколько таблиц и тем самым выбрать все нужные нам поля, в запросе contacts у нас есть условие – выводить только активные контакты.

Выберем для отчета поля [name], [surname], [email], [companyid], [phone], далее.

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

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

Завершим работу мастера. Теперь в области объектов базы данных появился новый объект – отчет contacts. Как и другие объекты базы, структуру отчета можно редактировать в режиме конструктора.

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

Изменим надписи полей на удобочитаемые.

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

Приводим данные в порядок с помощью Microsoft Access (часть3)⁠ ⁠

Продолжим совершенствовать нашу базу данных и функционал по работе с ней.

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

Наша форма приобрела аккуратный вид.

Дополним нашу базу данными о компаниях в которых работают наши контакты. Создадим таблицу company с полями [id] – счетчик, [name] – текстовый, [adress] – текстовый. Заполнять таблицу будем импортируя из внешнего источника (файла), можно импортировать данные многими способами (из Excel, файлов Access, файлов других баз данных и др.).

Подготовим простой текстовый файл с набором вымышленных компаний и адресов. Назовем файл company.txt. В первой строчке файла через точку с запятой перечислим название полей, соответствующих нашим полям в таблице company — [name] и [adress]. Перечислим наименования компаний и их адреса отделяя их точкой с запятой.

ООО Мотор;Россия, г. Москва, Космонавтов ул., 76, корп. 1

ЗАО ЖелДор;Россия, г. Москва, Шоссейная ул., 19, корп. 5

ООО ТверьВектор;Россия, г. Тверь, Мирная ул., 87, оф. 30

ОАО СофтОрионЛизинг;Россия, г. Москва, Калинина ул., 12, корп. 2

МКК ГорМобайл;Россия, г. Самара, Луговой пер., 35, оф. 2

ПАО МикроГлавМаш;Россия, г. Москва, Совхозная ул., 12, корп. 2

ПАО Нефть;Россия, г. Уфа, Пушкина ул., дом 32

ОАО МеталВостокОрион;Россия, г. Казань, Озерная ул., 90, корп. 7

ООО Компания РемСбыт;Россия, г. Москва, Вокзальная ул., 86, корп. 6

ПАО ГазВостокМор;Россия, г. Москва, Молодежный пер., 11, корп. 4

ПАО Теле;Россия, г. Москва, Юбилейная ул., 20, корп. 9

ОАО МеталВектор;Россия, г. Хабаровск, Южная ул., 55, оф. 7

ООО Компания ВодСантех;Россия, г. Москва, Заслонова ул., 21, корп. 7

Красная линия показывает границу полей.

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

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

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

Завершаем процесс импорта. Переходим в таблицу company и убеждаемся, что наши данные импортировались корректно.

В таблице contacts добавим поле [companyid], в выпадающем списке типов данных выберем Мастер подстановок

Выберем первый пункт – Значения из другой таблицы, далее.

Выбираем таблицу company в качестве источника данных, далее.

Выбираем поле [name] в качестве источника списка, завершим работу мастера соглашаясь на условия по умолчания.

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

Перейдем в уже знакомую нам Схему данных со вкладки Работа с базами данных и отобразим таблицу company. Курсором мыши перетянем поле [companyid] на поле [id] таблицы company. Теперь наши таблицы связаны связью Один ко многим, что означает: одному контакту соответствует одна компания из множества компаний.

Добавим возможность выбора компании для контакта на форме contacts. Перейдем в режим конструктора формы и на панели элементов управления выберем элемент управления – Поле со списком. Поместим его на свободное место области данных.

Этапы создания поля со списком на форме аналогичны тому, как это было сделано в таблице contacts.

Источник данных таблица company.

Источник списка поле [name].

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

Для удобной работы добавим на форму кнопку сохранения записи. В режиме конструктора из панели элементов управления перетянем элемент управления – Кнопка.

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

Зададим название кнопки как “Сохранить”, завершим работу мастера.

Перейдем в режим работы с формой, теперь кнопку “Сохранить” можно использовать для сохранения записей.

Приводим данные в порядок с помощью Microsoft Access (часть 2)⁠ ⁠

Продолжим совершенствовать нашу базу данных и функционал по работе с ней.

Добавим в таблицу contacts новое поле [active], которое будет характеризовать активность контакта (активен или не активен). Тип данных установим Логический. Логический (или булевый от англ. boolean) тип данных имеет всего два значения ИСТИНА или ЛОЖЬ (также может обозначаться: как 1 или 0, да или нет, true или false) применяется в тех случаях, где состояние объекта может быть охарактеризовано этими двумя значениями, во многих случаях этого достаточно. В дальнейшем этот признак позволит фильтровать выборку данных и строить логику работы. Например, показать список только активных контактов.

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

В таблице contacts, поле [active] получило возможность взаимодействия с ним через элемент управления Checkbox,во многих случаях это удобно, хотя в программе за ним скрывается символ 1 или 0, в зависимости от наличия или отсутствия “галочки”. Мы бы могли удалять записи неактивных контактов, но удалять какие-либо записи в базах данных нужно с особой осторожностью, как правило записи особым образом отмечаются или переносятся, и при необходимости к ним всегда можно вернуться.

Теперь обратим внимание на поля [phone1] и [phone2]. Исходя из опыта мы предположили, что у человека может быть более одного телефонного номера и создали два поля под эти цели, но а если у человека три телефонных номера и даже четыре? Логично предположить и создать под эти цели максимальное количество полей (помним, что вносить все номера в одно поле через запятую противоречит принципу минимальной неделимости), допустим создадим десять полей для хранения десяти телефонных номеров. И делать мы этого не будем по причине того, что система управления базами данных резервирует память под хранение данных и даже при наличии одного человека с десятью телефонными номерами большое количество пустых мест (рисунок ниже) будет замедлять работу нашей базы, когда она начнет разрастаться данными.

Создадим таблицу phones.

В ней же создадим ключевое поле [id] с типом данных Счетчик, поле [userid] – тип данных Числовой и [phone] – тип данных Текстовый. Поле [userid] является внешним ключевым полем, сюда мы будем записывать значение поля [id](которое соответствует конкретному человеку) из таблицы contacts и его номер телефона, столько раз сколько номеров телефонов у него есть.

Перенесем номера телефонов из таблицы contacts в таблицу phones и удалим поля [phone1] и [phone2].

На панели инструментов откроем вкладку Работа с базами данных и вызовем Схему данных. В конструкторе Схемы данных вызовем Отобразить таблицу и выберем из списка таблиц таблицу contacts и phone. Мы видим модели наших таблиц с соответствующими полями.

Наведем курсор мыши на поле [id] таблицы contacts, нажмем на него левой кнопкой мыши и не отпуская перенесем на поле [userid] таблицы phones. В появившемся диалоговом окне отметим пункт Обеспечение целостности данных (в таблице phones мы не сможем записать номер телефона для несуществующего контакта) и применим изменения.

Теперь наши таблицы связаны отношением – Один ко многим, это значит, что один человек из таблицы contacts может иметь много телефонных номеров в таблице phones. Таким образом наша таблица будет расти вниз, а не в ширь, как это предполагалось у нас изначально.

Откроем форму contacts из панели объектов базы. Теперь наши поля формы [phone1] и [phone2] ссылаются на несуществующие поля таблицы contacts, все верно, мы их удалили.

Откроем форму contacts в режиме Конструктора уже знакомым для нас способом. Здесь мы имеем возможность в графическом режиме изменять размеры и расположение наших полей, а так же менять их свойства, и много чего другого. Выделим область расположения “битых” полей и удалим их.

Реализуем возможность ввода нескольких телефонов для одного контакта. В режиме конструктора формы на верхней панели инструментов, из раздела Элементы управления мы можем добавлять элементы привычные нам по работе с большинством программ, имеющих графический интерфейс (кнопки, выпадающие списки, чекбоксы, радиокнопки, списки и т. п.). Выберем элемент управления Подчиненная форма/отчет (считается, что это неудачный перевод локализации MS Access, логичнее этот элемент управления звучал бы как Подчиненная таблица). На рабочем поле формы курсор мыши изменит свой вид, выделим произвольную область на свободном месте и вставим элемент управления, в дальнейшем мы сможем отредактировать его размер в удобный для работы.

В появившемся Мастере подчиненных форм выберем источником подчиненных данных Имеющиеся таблицы и запросы, далее во втором окне из выпадающего списка таблиц выберем таблицу phones, из левого списка полей таблицы phones выберем поле [phone] и переместим его в правый список соответствующей кнопкой.

Третий этап диалога предлагает нам связать подчиненные данные с таблицей contacts, если же мы выберем <отсутствие> такой связи, в подчиненной таблице, мы просто будем видеть полный список телефонов всех контактов, выберем первый пункт из списка, который соответствует нашей схеме связи, иными словами мы говорим – Показать все телефоны для такого-то контакта. Сохраним работу мастера, сохраним изменения в конструкторе формы.

На панели инструментов, в подразделе Сервис нажмем Добавить поля и перетащим поле [active] на макет формы. Сохраним изменения и выйдем из режима конструктора.

Теперь на форме contacts, в окне подчиненной таблицы, перемещаясь по записям контактов сможем видеть и добавлять необходимое количество телефонов для конкретного контакта, все добавления будут записываться в таблицу phones. Добавим новый телефон для первого контакта и в таблице phones появится еще один телефон, заметьте поле [userid] таблицы phones заполняется автоматически соответствующим [id] (которое идентифицирует конкретного пользователя) из таблицы contacts. Также у нас есть возможность изменять “галочкой” активность контакта и эти изменения тоже будут отражаться в таблице contacts.