Пример создания сложной базы данных в access 2019 пошагово со схемами: таблицы, схемы данных, запросы, формы

Содержание

Создание базы данных в Excel

63678 08.09.2016 Скачать пример

При упоминании баз данных (БД) первым делом, конечно, в голову приходят всякие умные слова типа SQL, Oracle, 1С или хотя бы Access. Безусловно, это очень мощные (и недешевые в большинстве своем) программы, способные автоматизировать работу большой и сложной компании с кучей данных. Беда в том, что иногда такая мощь просто не нужна. Ваш бизнес может быть небольшим и с относительно несложными бизнес-процессами, но автоматизировать его тоже хочется. Причем именно для маленьких компаний это, зачастую, вопрос выживания.

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

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

Со всем этим вполне может справиться Microsoft Excel, если приложить немного усилий. Давайте попробуем это реализовать.

Шаг 1. Исходные данные в виде таблиц

Информацию о товарах, продажах и клиентах будем хранить в трех таблицах (на одном листе или на разных — все равно). Принципиально важно, превратить их в «умные таблицы» с автоподстройкой размеров, чтобы не думать об этом в будущем. Это делается с помощью команды Форматировать как таблицу на вкладке Главная (Home — Format as Table). На появившейся затем вкладке Конструктор (Design) присвоим таблицам наглядные имена в поле Имя таблицы для последующего использования:

Итого у нас должны получиться три «умных таблицы»:

Обратите внимание, что таблицы могут содержать дополнительные уточняющие данные. Так, например, наш Прайс содержит дополнительно информацию о категории (товарной группе, упаковке, весу и т.п.) каждого товара, а таблица Клиенты — город и регион (адрес, ИНН, банковские реквизиты и т.п.) каждого из них.

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

Шаг 2. Создаем форму для ввода данных

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

В ячейке B3 для получения обновляемой текущей даты-времени используем функцию ТДАТА (NOW). Если время не нужно, то вместо ТДАТА можно применить функцию СЕГОДНЯ (TODAY).

В ячейке B11 найдем цену выбранного товара в третьем столбце умной таблицы Прайс с помощью функции ВПР (VLOOKUP). Если раньше с ней не сталкивались, то сначала почитайте и посмотрите видео тут.

В ячейке B7 нам нужен выпадающий список с товарами из прайс-листа. Для этого можно использовать команду Данные — Проверка данных (Data — Validation), указать в качестве ограничения Список (List) и ввести затем в поле Источник (Source) ссылку на столбец Наименование из нашей умной таблицы Прайс:

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

=ДВССЫЛ(«Клиенты[Клиент]»)

Функция ДВССЫЛ (INDIRECT) нужна, в данном случае, потому что Excel, к сожалению, не понимает прямых ссылок на умные таблицы в поле Источник. Но та же ссылка «завернутая» в функцию ДВССЫЛ работает при этом «на ура» (подробнее об этом было в статье про создание выпадающих списков с наполнением).

Шаг 3. Добавляем макрос ввода продаж

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

Т.е. в ячейке A20 будет ссылка =B3, в ячейке B20 ссылка на =B7 и т.д.

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

Alt+F11 или кнопку Visual Basic на вкладке Разработчик (Developer). Если эту вкладку не видно, то включите ее сначала в настройках Файл — Параметры — Настройка ленты (File — Options — Customize Ribbon). В открывшемся окне редактора Visual Basic вставляем новый пустой модуль через меню Insert — Module и вводим туда код нашего макроса:

Sub Add_Sell()
    Worksheets("Форма ввода").Range("A20:E20").Copy                         'копируем строчку с данными из формы
    n = Worksheets("Продажи").Range("A100000").End(xlUp).Row                'определяем номер последней строки в табл. Продажи
    Worksheets("Продажи"). Cells(n + 1, 1).PasteSpecial Paste:=xlPasteValues 'вставляем в следующую пустую строку
    Worksheets("Форма ввода").Range("B5,B7,B9").ClearContents               'очищаем форму
End Sub

Теперь можно добавить к нашей форме кнопку для запуска созданного макроса, используя выпадающий список Вставить на вкладке Разработчик (Developer — Insert — Button):

После того, как вы ее нарисуете, удерживая нажатой левую кнопку мыши, Excel сам спросит вас — какой именно макрос нужно на нее назначить — выбираем наш макрос Add_Sell. Текст на кнопке можно поменять, щелкнув по ней правой кнопкой мыши и выбрав команду Изменить текст.

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

Шаг 4. Связываем таблицы

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

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

Для этого на вкладке Данные (Data) нажмите кнопку Отношения (Relations). В появившемся окне нажмите кнопку Создать (New) и выберите из выпадающих списков таблицы и названия столбцов, по которым они должны быть связаны:

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

ВПР, если бы ее использовали.

Само-собой, аналогичным образом связываются и таблица Продажи с таблицей Клиенты по общему столбцу Клиент:

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

Шаг 5. Строим отчеты с помощью сводной

Теперь для анализа продаж и отслеживания динамики процесса, сформируем для примера какой-нибудь отчет с помощью сводной таблицы. Установите активную ячейку в таблицу Продажи и выберите на ленте вкладку Вставка — Сводная таблица (Insert — Pivot Table). В открывшемся окне Excel спросит нас про источник данных (т.е. таблицу Продажи) и место для выгрузки отчета (лучше на новый лист):

Жизненно важный момент состоит в том, что нужно обязательно включить флажок

Добавить эти данные в модель данных (Add data to Data Model) в нижней части окна, чтобы Excel понял, что мы хотим строить отчет не только по текущей таблице, но и задействовать все связи.

После нажатия на ОК в правой половине окна появится панель Поля сводной таблицы, где нужно щелкнуть по ссылке Все, чтобы увидеть не только текущую, а сразу все «умные таблицы», которые есть в книге.А затем можно, как и в классической сводной таблице, просто перетащить мышью нужные нам поля из любых связанных таблиц в области Фильтра, Строк, Столбцов или Значений — и Excel моментально построит любой нужный нам отчет на листе:

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

Обновить (Refresh), т.к. автоматически она этого делать не умеет.

Также, выделив любую ячейку в сводной и нажав кнопку Сводная диаграмма (Pivot Chart) на вкладке Анализ (Analysis) или Параметры (Options) можно быстро визуализировать посчитанные в ней результаты.

Шаг 6.

Заполняем печатные формы

Еще одной типовой задачей любой БД является автоматическое заполнение различных печатных бланков и форм (накладные, счета, акты и т.п.). Про один из способов это сделать, я уже как-то писал. Здесь же реализуем, для примера, заполнение формы по номеру счета:

Предполагается, что в ячейку C2 пользователь будет вводить число (номер строки в таблице Продажи, по сути), а затем нужные нам данные подтягиваются с помощью уже знакомой функции ВПР (VLOOKUP) и функции ИНДЕКС (INDEX).

Ссылки по теме

Как использовать функцию ВПР (VLOOKUP) для поиска и подстановки значений

Как заменить ВПР функциями ИНДЕКС и ПОИСКПОЗ
Автоматическое заполнение форм и бланков данными из таблицы
Создание отчетов с помощью сводных таблиц

Усовершенствованный дизайн форм в Microsoft Access – Учебники Holowczak.com

Опубликовано 3 июня 2013 г.

5 апреля 2022 г. автор: holowczak

Введение

Если вы освоили основы Microsoft Access и ищете более продвинутые советы и приемы, вы попали по адресу. Если вы только начинаете работать с Microsoft Access, я предлагаю вам сначала ознакомиться с моим учебным пособием по MS Access 2010–2019, поскольку оно содержит инструкции по всем основным компонентам Access (таблицы, запросы, формы, отчеты и формы навигации), включая краткое введение. к реляционным базам данных. После того, как вы освоите эти основы, вернитесь к этому руководству для более продвинутых функций.

Обратите внимание, что работа, проделанная в этом руководстве по формам, предполагает, что вы внесли изменения и украшения в макеты таблиц, как показано в руководстве Расширенный дизайн таблиц базы данных в MS Access.

Снимки экрана и примеры для этого расширенного руководства были сделаны с помощью Microsoft Access 2013. Это последняя версия на данный момент. По большей части показанные здесь функции и экраны практически идентичны версиям Access 2010, 2013, 2016 и 2019.

Целью этого руководства является охватить более продвинутые функции MS Access Forms Design, включая следующие:

1. Четыре основных типа форм в MS Access
2. Свойства дизайна форм в MS Access
- 2.1 Дизайн таблиц и дизайн форм
- 2.2 Темы форм в Microsoft Access 2010 и 2013
- 2.3 Доступ к свойствам формы
- 2.3 Свойства поля формы доступа
3. Поля поиска (несвязанные) в MS Access
4. Работа с флажками в формах доступа
- 4.1 Добавление флажка, привязанного к столбцу (полю) таблицы
- 4.2 Добавление флажка в качестве несвязанного элемента управления
5. Работа с кнопками выбора (переключателями) в формах доступа
- 5.1 Добавление опциональных кнопок (переключателей), привязанных к столбцу таблицы (полю)
- 5.2 Добавление кнопок выбора в качестве несвязанных элементов управления
6. Настройка форм с помощью командных кнопок
7. Встраивание диаграмм в формы Microsoft Access

В моем учебном пособии по MS Access были представлены основные шаги по созданию формы «одна таблица» и формы «Основная информация». Некоторые из концепций, которые следует здесь рассмотреть, включают в себя работу с мастером создания форм, выбор таблиц и полей, цветов и дизайнов форм, а также работу с формами (навигацию). Например, в разделе «Создание и запуск формы ввода данных» была создана форма ввода данных клиента:

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

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

1. Четыре основных типа форм ввода данных в MS Access

Типичная схема базы данных может содержать десятки таблиц, каждая из которых содержит несколько столбцов с различными типами данных. Мы разрабатываем приложения (формы, отчеты, меню и т. д.), чтобы облегчить пользователям работу с данными в этих таблицах. В Microsoft Access форма (или форма ввода данных) действует как «окно» в схему базы данных. Индивидуальная форма фокусирует внимание пользователя на одной или нескольких таблицах одновременно. Кроме того, форма может давать подсказки, чтобы пользователь знал, какой ввод ожидается и как следует вводить данные и манипулировать ими. По умолчанию каждая форма в Access имеет возможность запрашивать существующие данные в таблице, изменять существующие данные и добавлять новые данные (записи) в таблицу. Форма создается с использованием основной формы и, возможно, одной или нескольких «подформ», соответствующих таблицам в базе данных. Поля в форме соответствуют столбцам в таблицах базы данных.

Можно создать четыре основных типа форм.

Одностоловая форма. Этот дизайн формы содержит одну форму, соответствующую одной таблице базы данных. Это самый основной тип формы.

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

Основная/подробная форма. Этот дизайн формы содержит «главную» форму и «подчиненную форму», которые расположены в отношении «основной/подробности» (один ко многим).

Форма Master/Detail с полями поиска. Этот дизайн формы аналогичен форме Master/Detail, но имеет дополнительные поля поиска в основных блоках и/или блоках подробностей.

Мой MS Access 2007/2010 Учебное пособие рассказывало, как создавать формы с одной таблицей и основные/подробные формы. В этом руководстве мы расскажем, как добавить «поля поиска» (называемые «несвязанными» полями) в эти формы. Сначала мы углубимся в конструктор форм и свойства форм.

Страницы: 1 2 3 4 5 6 7 8 9 10

Категории: доступ, база данных

Теги: Access 2010, Access 2013, Формы доступа, Дизайн форм доступа, Добавить диаграмму, База данных, Встроить диаграмму в форму, Microsoft Access, Учебник

Поиск Holowczak.com Искать:

Пожертвования Добро пожаловать

Если вы нашли что-то образовательное или развлекательное на holowczak.com, рассмотрите возможность отправки пожертвования через: PayPal
LRC: 0xbe6738ed824cd34b9а109даа956ее448е41а545д
ЭТН: 0x1F8C9e8B4342872f868c1d313C13259bc4683933
BTC: 3JC5ZVGejv1seyALGiiSdbET9zomJz464X
Спасибо!

Реферальный код моего кошелька Loopring: 013928.

Недавние Посты

Обратное проектирование схемы Google BigQuery с помощью Dataedo 10

Категории сообщений Категории сообщенийВыберите категориюДоступ к блогуBloombergC#C++CIS 4620Облачные вычисленияХранилище данных и аналитикаБаза данных MySQLDiagramingFactSetFinancial ITInteractive BrokersJavaMicrosoft OfficeOraclePowerPointПрограммирование HTMLASSQLSQL ServerVisioVisual Studio

Что такое реляционная база данных?

Реляционная база данных организует данные в строки и столбцы, которые вместе образуют таблицу. Данные обычно структурированы по нескольким таблицам, которые могут быть объединены с помощью первичного или внешнего ключа. Эти уникальные идентификаторы демонстрируют различные отношения, существующие между таблицами, и эти отношения обычно иллюстрируются с помощью различных типов моделей данных. Аналитики используют SQL-запросы для объединения различных точек данных и обобщения эффективности бизнеса, что позволяет организациям получать ценную информацию, оптимизировать рабочие процессы и выявлять новые возможности.

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

Столбцы (или поля) для таблицы клиентов могут быть Идентификатор клиента , Название компании , Адрес компании , Промышленность и т. д.; столбцами для таблицы транзакций могут быть Дата транзакции , Идентификатор клиента , Сумма транзакции , Способ оплаты и т. д. Таблицы можно объединить вместе с помощью общего поля Идентификатор клиента . Таким образом, вы можете запросить таблицу для получения ценных отчетов, таких как отчеты о продажах по отраслям или компаниям, которые могут информировать потенциальных клиентов.

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

Атомарность: Все изменения данных выполняются так, как будто это одна операция. То есть выполняются все изменения или ни одно из них.

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

Изоляция: Промежуточное состояние транзакции не видно другим транзакциям, поэтому одновременно выполняемые транзакции кажутся сериализованными.