access在哪里可以设主键_构建在Excel上的财务模块-VBA语言概览第三部分-1 数据原理及Access基本功能讲解...

于 2020-12-21 06:22:52 发布
阅读量592 收藏 1
点赞数
文章标签： access在哪里可以设主键
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_39978863/article/details/111652609
版权

前言：

第三部分的第一期终于写好了，本来规划简单讲讲Access数据库的应用，没想到越写越多，写了差不多有1万字，而这仅仅是我想讲内容的1/3，连自己都对这篇幅感到吃惊。

我突然想了想自己做这个专题的初衷是什么，为了炫技吗？当然不是，如果炫技可以讲更加复杂的。我在多年的工作里面发现很多同学在干数据整理和数据分析工作时候，往往困于缺乏一点数据结构化存储的理解，导致在使用Excel或者其他数据工具相当低效，甚至很多Excel高手往往想着是怎么用复杂的公式或编程解决问题，而不是首先想到怎么管理自己的数据。一旦数据日积月累，越来越庞大的时候，很多人也缺乏基本的数据管理概念，导致Excel版本迭代过多，最后自己都搞不清楚。我将其称为工作数据的碎片化，这是仅仅执着于Excel数据管理肯定会有的情况。而我写此文的目的也在于此，从数据库基本原理讲起，再慢慢开始我们的Access之旅，而Access作为最简单的数据库产品，其易操作所见即所得的风格，也是大家最好的数据库工具启蒙（当下互联网关于Access的资料确是太少了）。

不期望大家能从我的这些浅薄文章中得到某些解决现实问题的答案，如果能有人获得一点启发，获得一点思路，对我来说这一系列文章也算有了那么点价值。

正文如下：

1 数据库原理

上一期我们说到单纯使用Excel表格管理数据的缺点：

1、不利于数据合并，相信读者也看到了我们这样的表格适合作为单月的处理，如果要分多月处理，要么是每个月一张这样的表格，然后在三大报表地方分别建立12个子列存放中间月份数据，这一个月添加数据之前要人工初始化，将上个月资产负债表、利润表和现金流量表数据贴过来

2、不利于数据查询，我们想联查几个月的数据必须一个个表格取翻，万一改了一个其中月份的表格，其他表格意味着必须要联动修改，否则后面表格期初数不对，这样会造成财务报表的多版本问题。所以到此为止，我们要想一个另外更有效的方案，如何保留灵活性，又对数据查询和数据输入解耦合。我们预想如下：

4a41ce5da258a3cd277b7704f1c67343.png

为了完成解耦的目标，我们需要搭配使用ACCESS数据库

1.1 什么是数据库？

个人的理解来讲是我们结构化和持久化管理和存储数据的工具，如果你对它没什么概念，就将其理解为容纳得下更多数据的EXCEL表格，只是它的数据存储方式遵循一定的格式没有EXCEL那么自由，也没有那么多复杂的表格操作，它的中心就是存储数据，让后让你可以SQL语句的形式方便的查询数据。

一个数据库由N个数据表组成，我们具体的数据就存在数据表中。

我们可以打开Access新建一个空白数据库，我们将其取名为accountant.accdb：

2156844358cdb9e77224df2f075f6901.png

目前是个空数据库，里面还没有任何数据表，在我们创建自己的数据表之前，让我们先来理解一下数据的三个范式概念。

1.2 数据库的三个范式

如前所述，数据库是对数据进行结构化存储的工具，那么数据的结构化要遵循什么原则呢？

这就是我们要说的三个范式：

1 第一范式（1NF）

在任何一个关系数据库中，第一范式（1NF） 是对关系模式的基本要求，不满足第一范式（1NF）的数据库就不是关系数据库。
所谓第一范式（1NF）是指数据库表的每一列都是不可分割的基本数据项，同一列中不能有多个值，即实体中的某个属性不能有多个值或者不能有重复的属性。如果出现重复的属性，就可能需要定义一个新的实体，新的实体由重复的属性构成，新实体与原实体之间为一对多关系。在第一范式（1NF）中表的每一行只包含一个实例的信息。

简而言之，第一范式就是无重复的列。

比如下面这张员工档案表，如下形式是符合第一范式，因为每个字段姓名、部门、职位，都明确对应单一实例信息

963b6e4edd85242e86c019d5e1caab61.png

如果换成这样，就不符合第一范式，我们将两个本可以独立的信息合并在了一个字段当中，如果我们未来需要在别的地方单独使用小王的职位-会计，这一信息，那么不得不在获取数据的时候把财务部也带出来，这样就会查询冗余了：

7531881b7ba6d1601a26d292f3c5d7e9.png

因此第一范式的关键在于明确，哪些信息是可以合并，哪些信息是不能合并

2 第二范式（2NF）

第二范式（2NF）是在第一范式（1NF）的基础上建立起来的，即满足第二范式（2NF）必须先满足第一范式（1NF）。第二范式（2NF）要求数据库表中的每个实例或行必须可以被唯一地区分。为实现区分通常需要为表加上一个列，以存储各个实例的唯一标识。这个唯一属性列被称为主关键字或主键、主码。
第二范式（2NF）要求实体的属性完全依赖于主关键字。所谓完全依赖是指不能存在仅依赖主关键字一部分的属性。如果存在，那么这个属性和主关键字的这一部分应该分离出来形成一个新的实体，新实体与原实体之间是一对多的关系。为实现区分通常需要为表加上一个列，以存储各个实例的唯一标识。

简而言之，第二范式就是非主属性完全依赖于主关键字。

第二范式是第一范式的延伸，仍然使用我们上面的表，这次我们加了一个编号，这个编号是在整个表中独立唯一的，标识每个员工。为什么要这么做，这就像：我们每个人都有身份证号作为我们唯一标识一样，在员工档案表中，可能存在姓名、部门、职位都一样的几个人，所以我们需要给每个人一个唯一的编号，这个编号同样也标识了在这张表中单独一条的数据记录，它也就是定义中说的主关键字。

同时2NF还有个要求就是非主属性完全依赖于主关键字，我们这里编号是主关键字，而姓名、部门、职位是非主属性，在这张表中，我们无法通过姓名小王推出编号9527，因为叫小王的可能有很多个人，而通过部门无法推出9527，因为财务部可能有很多人，职位亦是同理，而反过来只有编号9527可以明确小王-财务部-会计这条信息。这就是非主属性完全依赖于主关键字。

042d426d871a0a1ccf4eb3b6fe61fc3b.png

3 第三范式（3NF）

满足第三范式（3NF） 必须先满足第二范式（2NF）。简而言之，第三范式（3NF）要求一个数据库表中不包含已在其它表中已包含的非主关键字信息。
简而言之，第三范式就是属性不依赖于其它非主属性。
第三范式是一种更加严格的约束，要求非主属性之间不能存在推导关系。

假设我们的员工档案表变成这样，很显然这张表满足2NF，但存在一个问题，就是部门是可以推导出部门简介的，因为一部门肯定只有一个部门简介，我们没必要把部门简介单独放在员工档案表里，如果我们有几十万员工，但只有10个部门，这样一来10个部门简介会重复几万次，造成不必要的数据冗余

b5b226cc73e71a72726897b8b3d04849.png

这时候，我们大致倾向于拆表，单独新建一张部门表，变成如下形式：

298e5f3f7bb3afccd4019ff7c8b5d639.png

这样一来部门表中部门是唯一不重复的主键，部门表和员工档案表形成一个1对多的关系，对员工档案表而言其部门字段就是外键，其键值与部门表对应！

如此拆分后就满足了第三范式的要求，我们在实践中，基本上要做到保二争三，并不是说一定要刻板地遵循三个范式，这是根据我们实际业务需求而决定的。

2 数据库搭建

2.1 如何设计我们当前的财务系统

首先我们要明确，我们最终生成的结果表：资产负债表、利润表、现金流量表和财务分析表，都不是我们需要具体增删改的表格，他们都是根据先前的财务凭证列表数据进行分类汇总的结果，因此它们也是呈现表。呈现表不需要放进数据库里面，我们数据库里面要放的表是我们需要增删改的基础表格，也就这四张

ddb489c0ef294c70157198a03417cbcd.png

其中前三张是基础档案表，进行一些基础配置，最后一张会计分录表就是我们实际需要进行增删改查交互的表格，是呈现表的数据来源。

另外根据财务报表和单位往来表的性质，还要加上四张：报表类科目档案表、资产负债表科目期初表和单位往来期初表

2.2 往来单位表档案设计

我们先来看往来单位表，目前还很简单，第一列id是主键，单位税号是、单位名称、地区是非主属性（实际上这里税号也可以作为主键，因为对一家公司而言税号是唯一的，但根据数据库系统的统一设计原则，我们通常在会对每个数据表新建一个id作为其唯一逐渐，最后一列是我们自己在excel中使用方便才设计的，可以不放到数据系统中，这样一来就符合三个范式的规范了。

8c7606e15b585cd7c4d97ad47fe6a42b.png

我们对该表进行整理：

9adf33c1bc4a5574c970822ba0d39a67.png

我们打开Access，点击创建选项卡--表

e452803100270dd467f39957bd506462.png

这时候会出现一个表1，我们选择设计视图，系统会提示我们重命名并保存表：

1a17881b228fff1c88ee71603e53f911.png

我们输入cooperators，这就是我们的往来单位档案表。然后我们按照整理的表格，依次添加字段，保存：

3e70f2b4676b6f622b14b2dc209a40b6.png

注意这里的必须、允许空字符串、索引，三个明细设置对应的内容。

或者我们可以，点击创建选项卡--查询设计

de688efaafe1b78b011054d6949f1e50.png

在查询1 这里点击SQL视图：

32c9258401044d23bdc3457728db0d5c.png

将下面这段代码放入，SQL查询视图，点击运行，同样可以创建一张往来档案表（cooperators），这是SQL创建模式：

create table cooperators (
    ID long not null primary key,
    company_code varchar(255) not null unique,
    company_name varchar(255) not null,
    location varchar(255),
    address varchar(255)
)

5560fa4d578ba531ddd4cd9f3eadb40c.png

关于SQL语言，是我们操作数据库的需要用到的脚本，如果你对它不熟，请先参阅：W3C的SQL教程

以下是我整理的SQL语句最基本的表达形式（实际会复杂很多，所以一定要看教程），先有个概念，然后可以对照着下面的SQL句进行理解，至少能看懂基本的增删改查即可。

ffa10647fe2e9ab2a519c8ae9684e03d.png

这样一来我们的第一张表就创建好了，但数据表里面还是空的，但我们可以利用office的互操作特性，将excel的数据直接复制到Access里面：

先复制数据区域

c78796fa9b8a4e3142e7f48ebff17778.png

然后选中Access中对应表的空行，ctrl+v即可，这里少贴一行，留给大家去输入，试试手感（ ：

81bc4e2c64ad28beea6ddacd789abd75.png

2.3 参考编码表设计

参考编码表是对应现金流量表的信息，这个表就比较简单，我们的建表过程基本同上：

36302ad5563a2d8369ea157f95d311bb.png

先整理一下原始表：

34cde2a33a01aaddc982c4f768c15c0b.png

这里解释下，为什么编码不设置为int类型，因为编码本身是我们对现金流入流出的一个记号：

1101：第一位1代表现金流量表的经营性现金流量，第二位1代表现金流入项目，第三位和第四位01，代表经营性现金流量现金流入项目的的第1个子项目。这种编码是功能性的设计，未来可能需要用字符串方法对其进行查询。（比如，筛选所有开头为1的数据项）

我们重复上面的2.3的步骤，创建一张名为cashflow的表，注意code和item的详细属性里面要将索引一栏改为：有（无重复）：

86f8920d716c0029c782f63cd881dd66.png

对于数据部分同样是一波复制粘贴，带走：

93236c31f4abfd8bc18715edc55cccf9.png

同样可以用sql语句完成：

create table cashflow(
    ID long not null primary key,
    cash_code varchar(255) not null unique,
    cash_item varchar(255) not null unique
)

2.4 科目档案表设计

我们先来看一下当前的科目档案表的样式：

469d023cd7c68a159fc90dfc0dcdc6bc.png

如果我们单纯将这张表直接作为我们的数据表，当然也可以，但我们要考虑一个情况，那就是我们当前只有两个受控项目：单位往来和现金流量受控，如果未来，我们新增一个类似其他项目受控，部门受控，该怎么办？

这时候Excel可以直接再插入一列，Access当然也可以但是这种变动对于数据库来说并不合适，我们需要考虑一定的未来延展性，所以我们可以这么做：

1 首先再Excel，报表编码对应这里插入一列，取名为受控信息。

2 将科目的单位受控和现金流量受控放置到这一列，然后在G2敲入公式：=IF(H2=1,"单位往来受控",IF(I2=1,"现金流量受控",""))，下拉，效果如下：

14dd424a96558e335c6e00d6c0cac9ff.png

这样一来，我们只要上传A列到G列的内容即可，未来哪怕添加了新的部门受控，我们也可以直接修改相应的受控信息列即可。

仍然是整理一下数据表：

d8a34d6fb1fde9e00e004ccf7c4ccda7.png

同样是建表然后复制粘贴，如果你懒得动可以到SQL视图输入如下代码：

create table kemudata(
    ID long not null primary key,
    kemu_name varchar(255) not null unique,
    kemu_code varchar(255) not null unique,
    side varchar(255) not null,
    isfinal varchar(255) not null,
    level int not null,
    report_code varchar(255),
    controll varchar(255)
)

然后仍然是将当前数据粘贴复制到数据表中：

d7d3cba04b22cbe06c408c390ef8f1be.png

2.5 凭证列表设计

首先我们可以明确需要单独插入一列受控信息列：用于合并我们的科目受控情况

2233513c75507e87b877dc7815d92038.png

然后我们再观察一下这张表，表头还有xxx有限公司的字样，这是我们日常凭证记录时的凭证头信息，由于这张表是我们从ERP系统中导出来的格式，属于凭证流水，我们在Excel表格里面可以这么做，但要放到数据库中需要做进一步整理，并不是真正凭证的样子，真正的凭证样式如下：

c1435c4b2ef71bbc02e52ed45f60723f.png

我们仔细观察会发现，一张单独的凭证可以分为两个部分：

1 凭证头部分：日期，凭证号，记账人，所属公司

2 凭证体部分：摘要，科目编码，科目名称，借方金额，贷方金额，科目受控情况

还记得我们在第三范式中举的例子吗？对于凭证列表的记录，我们大致可以勾勒出这样一个模型：

1adf08f36b88ef13cabf00c687ca1745.png

注意，我们在凭证体这张表中首先将现金流量和部门受控的信息都合并在一个字段里面，而科目名称由于可以从科目编码中带出来（还记得第三范式的要求吗？）所以在数据表中不需要单独陈列，尽量减少冗余。

凭证头中，凭证号是唯一的，而凭证体中，凭证号是不唯一的。形成了1对多关系，对于凭证体这张表而言，凭证号就是外键。

当前对于凭证号，我们还有一个问题，就是当前凭证号的编码太过简单仅仅是1，2，3....这样的凭证号在不同月份和不同公司很容易造成重复，所以我们需要对凭证号重新编码：公司_月份_凭证号这样可以保证唯一，比如xxx公司_202001_3这样既保证了唯一性，又保证了可以通过数据字段的操作单独取出实际的凭证号。

你可能会问，这样是否造成信息冗余，毕竟我们在凭证头里面也有公司和记账日期的信息？

如果仅仅是凭证头，那么我们可以通过对凭证号、记账日期、公司做一个联合子健避免信息重复，但由于我们还设计对凭证体的外键，所以必须重新设计凭证号的编码。

我们先回到凭证列表这里，在J列插入受控信息，在J3单元格输入公式：

=IF(K3<>"",LEFT(K3,5),IF(L3<>"",LEFT(L3,4),""))

下拉，这样我们将受控信息的编码都提取出来合并到了同一列

0bca9b4f2b41d8e0baa3a51e4f8b5ad1.png

然后我们来整理一下数据表：

凭证头（voucherhead）：

5b53bdf1751c5fee25a905335d6fed7a.png

凭证体（voucherbody）：

d622db5ec7e641978a8e1971d625df0d.png

然后重复我们上述建立数据表的做法，新建两张表，当然你也可以分别复制如下sql代码到sql视图中点击运行，表格自动创建

凭证头sql，注意这里要保证在voucherhead的视图设计中vid的索引一栏是：有（无重复）

create table voucherhead(
    ID long not null primary key,
    vid varchar(255) not null unique,
    company varchar(255) not null,
    book_date date not null,
    book_keeper varchar(255) not null
)

凭证体sql

create table voucherbody(
    ID long not null primary key,
    vid varchar(255) not null,
    summary varchar(255) not null,
    kemu_code varchar(255) not null ,
    debit double,
    credit double,
    control_code varchar(255),
)

但接下来问题来了，如何导入数据，我们要一步步来：

1 凭证头表的数据：

  • 首先将凭证日期，凭证号，凭证类型，三列贴出来，放到一个临时表格中，然后选中去除重复项

a67e76fdf19064abfe3a12b93394fc5b.png

这里我们介绍一个Excel上校验数据唯一性的方法：

在F类输入： =COUNTIF(C:C,C2)然后下来， COUNTIF函数的作用是合计某个区域中存在某值的数量。如果F列最后都显示为1（通过从合计数或者筛选判断），那么证明某列都是非重复数据。

ac85a764ef9b8918fdb72c2716c8c1ce.png

最后我们将表格整理为如下格式，白色区域就是我们要复制到数据库表的部分：

至于VID怎么拼接请看公式：

=C2&"_"&YEAR(H2)&IF(LEN(MONTH(H2))=1,"0"&MONTH(H2),MONTH(H2))&"_"&F2

164c530c5f9f887e38f3cc2cfc91d083.png

凭证头的数据整理完了，但我们可以直接贴过去了，如下：

8f3b01470b6131eb7dbeb46951477321.png

2 凭证体的数据：

  • 整理方式类似凭证头，结果如下

8a621b22ef6805017381723877e02c86.png

仍然复制粘贴到voucherbody中，结果如下：

e7a6d537504ba66551c99387fcf6ec48.png

接下来我们还要做一件事情，就是定义voucherhead和voucherbody中vid的连结关系，按照我们的设计，voucherhead中的凭证号是唯一的，对应着voucherbody中的凭证号，也就是说vid是voucherbody的外键，于voucherhead中的vid对应。

我们打开voucherhead，点击：表--关系

f143529f8e772b3fe8346ac036f78491.png

会见到如下形式：

1b7fe148ae456c2fdd01bb8d14097e23.png

我们鼠标左键按住voucherhead的vid拖动到voucherbody的vid中，会弹出一个对话框，联级删除，联级更新也勾上：

082e553e76ea469285aa1fe1da493acc.png

联级删除和联级更新的意思是，一旦voucherhead删除或更新了数据，则voucherbody对应的数据也会被更新或删除：比如voucherhead直接删除了vid为3的凭证，那么voucherbodyvid为3的记录也被删除。

到此为止，我们目前三张主表的大致关系如下：

369617b121c54740b0574c3e45849975.png

我们可以用它来设计一个查询，复原一下我们Excel里面的凭证列表，点击创建--查询设计，然后按照下图操作：

518ac33ec720217140736afc66fbbf62.png

转到设计标签卡--点击运行：

cbd664c5d3220522a05548e8b0723aaa.png

我们的凭证列表就完成：

c1dff507bdd814af2601e286df058fc6.png

我们可以将查询保存起来起名为voucherlist，也可以保存它的sql视图（这个我们未来会在VBA中用到它）：

SELECT 
voucherhead.vid, 
voucherhead.company, 
voucherhead.book_date, 
voucherbody.summary, 
voucherbody.kemu_code, 
kemudata.kemu_name, 
voucherbody.debit, 
voucherbody.credit, 
voucherbody.control_code
FROM kemudata INNER JOIN 
(voucherhead INNER JOIN voucherbody ON voucherhead.vid = voucherbody.vid) 
ON kemudata.kemu_code = voucherbody.kemu_code
WHERE voucherhead.book_date Between #1/1/2020# And #12/31/2020#;

2.5 资产负债表和利润表的基础档案设计

接下来我们再来创建两张基础表，资产负债表和利润表，用以存储基本的资产负债表信息和利润表信息：

两张表的结构基本一模一样：

balancesheet:

8c67e4c9c71beff4a9e7ff1b2fc68a1c.png

建表过程同上，你也可以将下面的sql代码复制到sql视图中运行：

create table balancesheet(
    ID long not null primary key,
    report_code varchar(255) not null unique,
    report_item varchar(255) not null unique,
    item_catalog varchar(255) not null
);

profitstatement:

1754eb2bf7c4b1d93fc851acb05aff57.png 
create table profitstatement(
    ID long not null primary key,
    report_code varchar(255) not null unique,
    report_name varchar(255) not null unique,
    item_catalog varchar(255) not null
);

资产负债表基础数据整理如下：

a1565398ab7c34cd5c0b0c729e36c4cf.png

同样复制到balancesheet的数据表中：

a87c27399613a93c1980ff068727b8d6.png

利润表的数据整理如下，这里的item_catalog就换成利润表项目的借贷方向了：

770edb995fa059ea9539fc5d4bfc9435.png

同样复制粘贴到profitstatement：

b0c206bcd16090437ef623b6fcc02f7d.png

2.6 期初表的设计

我们还剩最后两张表：资产负债表的期初表和往来单位的期初表，这是财务上特有的需求，因为按照年度结转数据，需要将上一会计年度的期末数据结转到本期作为本期的期初，这样可以保证数据的连续性，我们对期初表的设计如下：

资产负债表的期初表：

bsbeginning(注意期初余额要到设计视图里面改为保留两位小数)

35a4619773a0c60b5f4a6564880e03c8.png

建表过程同上，sql代码如下：

create table bsbeginning(
    ID long not null primary key,
    report_code varchar(255) not null,
    report_year long not null,
    beginning_balance double not null
)

资产负债表期初整理如下：

cb84ca940f55ccdefa803a7ece3bd97f.png

同样的粘贴复制到bsbeginning数据表中。

我们这里可以看到bsbeginning同balancesheet又是类似于voucherhead和voucherbody的对应关系，所以我们还是如法炮制，将它们的report_code建立起1对多的对应连线：

6da329279d8a428e649a3267bceb349c.png

接下来我们设计往来单位的期初表，同资产负债期初表类似：

cpbeginning(注意期初余额要到设计视图里面改为保留两位小数):

7b8307c10b7a0a7d397a93146803dda5.png

建表过程同上，sql代码如下：

create table cpbeginning(
    ID long not null primary key,
    company_code varchar(255) not null,
    report_year long not null,
    beginning_balance double not null
)

数据整理如下，同样的粘贴复制到cpbeginning数据表中：

4cb2c2b941e3c7f8cf6d01d7d2786bac.png

同样cooperators表和cpbeginning表的company_code存在1对多连接关系，一如既往地设计他们的关系：

d601084fd33d00ad82352487a43efb31.png

3 初步报表的生成

到这里我们的基础配置和输入导入已经完成了，我们需要通过Access的功能生成初步的三张表：

首先是现金流量表和利润表，这个通过简单的查询设计就可以做到，他们的数据来源均来自于voucherbody

我们的查询设计如下：

3.1 利润表：

1 首先生成1级科目汇总表（注意这张表是本年累计数）

这个查询有点复杂，还涉及Access的一些坑请输入如下代码到sql查询视图页面：

(查询的基本原理为：先建立一个从凭证头和凭证体连接起来的vlist作为子查询结果，而子查询中凭证体的科目代码kemu_code只取头4位，这样相当于把一级科目代码筛选出来，然后以这个子查询结果作为新的查询来源，将其与科目档案链接，并根据科目的借贷方向计算科目余额)

SELECT 
kemudata.kemu_name, 
kemudata.kemu_code, 
kemudata.side, 
IIf(IsNull(Sum(vlist.debit)),0,Sum(vlist.debit)) AS debit_sum, 
IIf(IsNull(Sum(vlist.credit)),0,Sum(vlist.credit)) AS credit_sum, 
IIf(kemudata.side="借",(debit_sum-credit_sum),(credit_sum-debit_sum)) AS balance,
kemudata.report_code
FROM 
(SELECT Left(voucherbody.kemu_code,4) AS kemu_code_new, 
voucherbody.debit as debit, 
voucherbody.credit as credit
FROM voucherhead INNER JOIN voucherbody ON voucherhead.vid = voucherbody.vid 
WHERE voucherhead.book_date Between #1/1/2020# And #12/31/2020#)vlist 
INNER JOIN kemudata ON vlist.kemu_code_new = kemudata.kemu_code
GROUP BY 
kemudata.kemu_name, 
kemudata.kemu_code, 
kemudata.side, 
kemudata.report_code;

点击运行后，我们将其保存为一个视图kemusum1cls_view，未来供我们其他的报表生成之用：

6345210ef9001613338d523ada8d1bab.png

2 通过1级科目汇总表生成利润表，同样保存为一个视图profit_view：

SELECT 
[profitstatement].report_code, 
[profitstatement].report_name, 
[profitstatement].item_catalog, 
iif(IsNull(kemusum1cls_view.debit_sum),0,kemusum1cls_view.debit_sum) AS amount
FROM kemudata INNER JOIN 
(profitstatement LEFT JOIN 
kemusum1cls_view ON 
[profitstatement].report_code=kemusum1cls_view.report_code) ON 
[profitstatement].report_code=kemudata.report_code
ORDER BY [profitstatement].report_code;

65c5171ae8f90e53a26b423139549fb0.png

注意：本利润表同样是本年累计数，如果需要看到本期数，需要重新运行一下kemusum1cls_view，将其日期的区间改为类似：#1/1/2020# And #1/31/2020#

可见我们每次在获取资产负债表、利润表、单位往来表时都仰赖于科目汇总表的生成结果，我们当然也可以单独新建一张当月的科目汇总表查询视图和当月的资产负债表、利润表、单位往来表查询视图，专门用以抓取当月数据。

但这个操作可以在我们未来的Excel VBA编程中实现

3.2 现金流量表

sql代码如下

SELECT cashflow.cash_code, cashflow.cash_item, 
IIf(IsNull(Sum(vlist.debit)),0,Sum(vlist.debit)) AS debit_sum, 
IIf(IsNull(Sum(vlist.credit)),0,Sum(vlist.credit)) AS credit_sum, 
IIf((debit_sum-credit_sum)>0,(debit_sum-credit_sum),(credit_sum-debit_sum)) AS balance
FROM 
cashflow LEFT JOIN 
(SELECT voucherbody.control_code,voucherbody.debit,voucherbody.credit 
FROM voucherhead INNER JOIN voucherbody ON voucherhead.vid = voucherbody.vid 
WHERE voucherhead.book_date Between #1/1/2020# And #12/31/2020#)vlist 
ON cashflow.cash_code = vlist.control_code
GROUP BY cashflow.cash_code, cashflow.cash_item;

注意：最后正规格式的财务报表仍然需要到Excel上整理。

0b21cac50da003ba92b688a53930e0d4.png

3.3 资产负债表和单位往里表

3.3.1 资产负债表

终于轮到我们最后的主角，别忘了资产负债表具备期初的性质。

我们首先要拿到所查询年份的资产负债表期初余额，表查询关系如下：

2379a7b4e06f5dc4f1ace1ea97d2bfde.png

sql代码如下，我们将其保存为balancesheet_view视图

SELECT 
bsbeginning.report_code, 
bsbeginning.report_year, 
balancesheet.report_item, 
iif(IsNull(bsbeginning.beginning_balance),0,bsbeginning.beginning_balance) as balance_begin, 
(balance_begin+iif(IsNull(Sum(kemusum1cls_view.balance)),0,Sum(kemusum1cls_view.balance))) AS balance_end
FROM 
((balancesheet INNER JOIN 
bsbeginning ON balancesheet.report_code = bsbeginning.report_code) 
INNER JOIN kemudata ON balancesheet.report_code = kemudata.report_code) 
LEFT JOIN kemusum1cls_view ON kemudata.kemu_code = kemusum1cls_view.kemu_code
where bsbeginning.report_year = 2020
GROUP BY 
bsbeginning.report_code, 
bsbeginning.report_year, 
balancesheet.report_item, 
bsbeginning.beginning_balance;

查询效果如下，我们可以将其同原始报表数据进行核对：

84b88b07ab813ae4c5cf73fb52cedcbe.png

3.3.2 单位往来表：

原理基本和资产负债表相同，查询关系如下：

b83f6d18c76f9b75fee7bce90c5ec08f.png

sql代码如下：

SELECT 
cpbeginning.company_code, 
cooperators.company_name, 
IIf(IsNull(cpbeginning.[beginning_balance]),0,cpbeginning.[beginning_balance]) AS cp_begin, 
IIf(IsNull(Sum(vlist.debit)),0,Sum(vlist.debit)) AS debit_sum, IIf(IsNull(Sum(vlist.credit)),0,Sum(vlist.credit)) AS credit_sum, 
(cp_begin+debit_sum-credit_sum) AS cp_end
FROM cooperators INNER JOIN 
(cpbeginning LEFT JOIN 
(SELECT voucherbody.control_code, voucherbody.debit, voucherbody.credit 
FROM voucherhead INNER JOIN voucherbody ON voucherhead.vid = voucherbody.vid 
WHERE year(voucherhead.book_date)=2020 and voucherbody.control_code<>"")  AS vlist 
ON cpbeginning.company_code = vlist.control_code) 
ON cooperators.company_code = cpbeginning.company_code
WHERE cpbeginning.report_year = 2020
GROUP BY 
cpbeginning.company_code, 
cooperators.company_name, 
cpbeginning.[beginning_balance];

结果如下：

981f16f984a6ff107298311431cd42d8.png

4 总结部分

当下整体表之间的关系如下：

1c73cfe86d1f8472978531f3533f0c52.png

到此为止，我们将所有的原先Excel上工作搬运到了Access上，可见Access同样可以自动化的生成生成我们想要的结果，但看到这里你可能会问，为何要大费周章甚至写那么多sql代码将Excel上可以简单实现的功能放到Access上，这一切真的值得吗?

别忘了我们的目的，我们希望解决的是Excel上财务系统高耦合的特性，我们当前只有一个月的数据，如果多几个月必然难以用Excel管理，我们将基础数据表搭建到Access，将其作为我们的数据库后端，而未来Excel将作为我们的前端，仅仅留下功能性的操作，比如增删改查，而后面的数据处理和生成都交给Access完成，我们配置好基本的查询表意味着一劳永逸，新增数据时无需像Excel那样每次调整表格格式或者重新设置公式。

同时数据存储在Access上意味着我们可以集中管理，可以集中备份，并且可以便捷地新增功能，而不影响整体Excel表结构，这样我们做到了数据和功能的初步分离。

在下一期，我们会通过初步的Excel VBA来操作Access，向Access提交增删改查的命令，实现Excel和Access互操作，我们将Excel视为我们的UI界面，完成我们想要的功能，生成我们想要的结果，届时Access真正的优势会展现出来。

敬请期待

附上文件的下载链接：

链接:https://pan.baidu.com/s/1K6sgVhvQhgmOY61oQfLzRA 密码:ktel

weixin_39978863
关注
  • 0
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值