Access 2007数据分析：表结构、SQL查询、清洗与操作查询实战

简介：《Access 2007数据分析技巧详解》面向希望借助关系型数据库提升数据处理与分析能力的Access用户，也适合需要从Excel过渡到更大规模数据管理的职场读者。全书围绕Access 2007展开，先说明其在数据分析中的定位及相较Excel在可扩展性、分析透明度、数据与呈现分离、共享处理等方面的优势，再讲解表创建、数据类型、数据导入、关系型数据库与查询基础。书中重点覆盖聚合查询、制表/删除/追加/更新等操作查询和交叉表查询，并给出查找删除重复记录、填充空白字段、字段连接、文本与大小写转换、去除首尾空格、查找替换等数据转换方法。包内共1个PDF文件，大小约11.35MB，便于在电脑端阅读与检索；已有80人学习。读者可据此系统掌握从数据整理、查询汇总到清洗转换的完整分析流程，积累可直接迁移到实际工作的查询思路与操作范式。

1. 十万行台账的分水岭：为什么数据分析要落在 Access 2007 而不是继续用表格

一份两万多行的销售台账放在表格软件里，筛一次要等三秒，改一处公式全表重算，发出去三个人各改一版——到这一步再谈 Access 2007 数据分析，就不是工具偏好问题了。这份资料讲的是把原始数据先放进一个关系型数据库里，用查询技巧把统计口径固定成可复用的对象，再用数据转换把脏字段洗干净，最后才谈呈现。书里没有一上来教控件和窗体，顺序是表、查询、操作查询、清洗，这条线对做报表的人是对的。

它适合手上有几万到几十万行、每个月都要重复出同一套口径报表的人，也适合已经会用透视表但每次都得从头拖一遍的人。反过来，如果数据只有几百行、一周看一次，用表格更省事，硬上数据库是给自己找麻烦。

2. 关系型底座先立住：表结构、字段类型与导入的三个决策点

这一章是整份资料里最不性感、但最容易埋雷的部分。表结构一旦定错，后面所有查询都在替它擦屁股：数字存成文本，排序和汇总全错；金额用单精度浮点，累加到几万行后合计对不上账，你会怀疑人生。我见过太多人跳过这一章直接去学交叉表，结果卡在“数据类型不匹配”上出不来。

所以顺序是先想清楚数据会不会反复刷新，再决定导入方式；再按字段的真实语义定类型；最后才动手导，并且导完立刻做一次行数和合计对账。

2.1 导入表还是链接表：先按“数据会不会变”分流

Access 2007 的外部数据入口在功能区“外部数据”选项卡里，Excel、文本文件、其他 Access 数据库、ODBC 都在这一排。同一次操作里通常有两个选项：“将数据导入当前数据库的新表中”和“通过创建链接表来链接到数据源”。这两个不是风格差异，是两种完全不同的数据结构。

导入表是把数据复制一份进当前.accdb文件，好处是这份数据变成真正的本地表，可以建索引、可以写更新和删除、可以当生成表查询的目标；代价是源文件再改，你这边不会跟着变，得重新导一次。链接表是保留一个指针，数据还在原来的 Excel 或文本文件里，好处是永远读最新，代价是几乎所有的写操作都会翻车。

对照着看更清楚：

我的判断标准很土：这份数据只用来分析一次，导入；每天或每周要刷新，用链接表接进来，然后用一个生成表查询把它落地成本地表，后续所有分析都对着落地表做。这样既拿到了最新数据，又保住了本地表的索引和可更新性。链接表只当“进水口”，不当“操作台”。

还有一个细节值得当场记住：链接 Excel 表时，Access 的判断依据是每列前若干行的内容。整列本来该是数字，但前几行里有“—”“N/A”或者空串，这一列就会被判成文本，之后所有 SUM 都返回空或者报类型不匹配。这不是 bug，是它在按样本猜。所以链接表只适合看，不适合算。

2.2 字段类型与主键：把“看起来像数字”的列定死

新建表用“创建”选项卡里的“表设计”，2007 的字段类型列表比早期版本多了“附件”，去掉了用户级安全那一套。类型选择的原则只有一条：按这一列的语义选，不按它现在长什么样选。

“货币”这个类型值得单独说一句。它固定四位小数、做精确运算，累加几万行不会出现 0.0000001 的偏差；双精度是二进制浮点，做乘除没问题，做大量累加就会出现合计差几分钱的情况，对账时非常难受。凡是和钱有关的列，直接选货币，别犹豫。

主键同样要当场定。没有主键的表在数据表视图里删改记录，很容易弹出“由于您和其他用户同时试图更改数据，无法保存对记录的更改”，看着像玄学，实际就是引擎找不到唯一标识来定位那一行。要么加一个自动编号字段当代理键，要么把业务上真正唯一的字段组合设成主键。业务键建主键时，记得同时把索引属性设成“有（无重复）”；经常出现在 WHERE 和 GROUP BY 里的字段，单独建“有（有重复）”索引，几万行的差别肉眼可见。

2.3 一次完整的导入落地：从建表到校验计数

我的习惯是反着来：不直接用导入向导建表，而是先手工建一张空表，字段名、类型、长度全按目标定好，再用追加的方式把数据灌进去。这样字段类型永远不会被源数据“猜”错，导入规范也可以保存下来下次复用。

具体步骤是这样：

1. 按 2.2 的规则建好空表，比如tbl订单_导入，主键先不设，导入完成后再加。
2. “外部数据”→“Excel”，选中源文件，选“将数据追加到现有表中”，指定目标表。
3. 在向导里点“高级”，逐列核对字段类型和跳过行数，然后点“另存为”把这套设置存成一个导入规范，下次直接选。
4. 导入完成后不要急着做查询，先跑一次对账。

对账这一步很多人省掉，后面所有结论都建在错误数据上。我一般用一个查询把三件事一次看完：行数、关键字段的空值数、金额合计。

-- 导入后的第一道体检：行数、空主键、金额合计 SELECT Count(*) AS 总行数, Sum(IIf([客户编号] Is Null, 1, 0)) AS 空客户编号行, Sum(IIf([订单日期] Is Null, 1, 0)) AS 空日期行, Sum([金额]) AS 金额合计 FROM [tbl订单_导入];

这段 SQL 里Count(*)数的是物理行数，不管字段是否为空；IIf(条件, 1, 0)是 Access 里的条件表达式，配合Sum就变成条件计数，比写四个独立查询快得多；Sum([金额])只有在金额列类型正确时才有意义，如果它返回空，基本可以断定这一列是文本类型，回到表设计里改掉再重导。字段名里有中文或空格时用方括号包住，这是 Access SQL 的硬要求，不加就会报“语法错误”。

行数和源文件对不上时，先看是不是首行标题被当成数据了，再看有没有隐藏行、合并单元格。金额合计对不上时，先看有没有负数被当成文本、有没有千分位逗号没清掉。这两条排查完，九成问题就定位了。

3. 把口径写进 SQL：从设计器网格到聚合、参数与日期边界

查询是这份资料的重心，也是最值得花时间的地方。理由很实在：网格拖出来的查询，换个字段就要重拖一遍，口径散落在十几条查询里，三个月后自己都说不清那张报表是怎么算的；写成 SQL 之后，口径是一个可以被读、被复制、被对比的文本，改哪儿一目了然。这份资料虽然以设计器为主线，但每个章节后面都会把对应的 SQL 摊开给你看，这个做法我认可。

3.1 设计器网格和 SQL 视图是一一对应的

“创建”选项卡里点“查询设计”，关掉弹出的显示表对话框，右键或者用“视图”按钮切到“SQL 视图”，你会看到一条空白的 SELECT。把同一件事在两边各做一遍，对应关系就清楚了。

设计器下半部分的网格有四行：字段、表、排序、显示，加上“条件”和“或”两行，它们分别对应 SELECT 列表、FROM、ORDER BY、是否输出、WHERE 的第一组条件和后续 OR 分支。点工具栏上那个 Σ（总计）按钮，网格会多出“总计”行，这时候查询从明细变成聚合，GROUP BY就出现了。

SELECT [订单].客户编号, [订单].订单日期, [订单].金额 FROM 订单 WHERE [订单].订单日期 >= #2024-1-1# AND [订单].订单日期 < #2025-1-1# ORDER BY [订单].金额 DESC;

这段对应的是最普通的明细查询：WHERE里的日期字面量在 Access 里必须用#包起来，写成'2024-1-1'会按文本比较，结果全错；ORDER BY ... DESC对应网格里“排序”行选“降序”；[订单].这个前缀在单表查询里可以省，多表关联时不能省，否则同名字段会打架。

有一点要提前知道：SQL 视图改过的查询，回到设计器可能提示“无法用图形方式表示”，尤其是用了UNION、子查询、TRANSFORM之后。这不是查询坏了，是图形层表达不了，只能留在 SQL 视图里维护。所以我的做法是网格搭骨架、SQL 改细节，一旦复杂到图形层跟不上，就干脆全用 SQL 写，别来回切。

3.2 聚合查询：WHERE、GROUP BY、HAVING 的三层分工

聚合是数据分析的主菜。Access 的聚合查询有三个执行层，顺序是固定的：先用 WHERE 从明细里筛行，再用 GROUP BY 把行压成组，最后用 HAVING 把组筛掉。搞混 WHERE 和 HAVING 是最常见的错误，比如想筛“金额合计大于一万的客户”，把条件写在 WHERE 里，Access 会先报“试图执行一个不包含指定表达式作为聚合函数一部分的查询”。

SELECT [订单].客户编号, Count(*) AS 单数, Sum([订单].金额) AS 金额合计, Avg([订单].金额) AS 平均单价 FROM 订单 WHERE [订单].订单日期 >= #2024-1-1# AND [订单].订单日期 < #2025-1-1# GROUP BY [订单].客户编号 HAVING Sum([订单].金额) >= 10000 ORDER BY Sum([订单].金额) DESC;

WHERE里的日期范围是行级过滤，作用在聚合之前，参与聚合的只有 2024 年这一年的数据；GROUP BY [订单].客户编号决定结果集每个客户一行；HAVING Sum([订单].金额) >= 10000是组级过滤，作用在聚合之后，所以它能引用聚合函数，而 WHERE 不能。Count(*)数的是组内行数，如果某个客户下单记录里有完全重复的行，它会一起数进去；要按订单号去重，就写Count([订单号])，因为聚合函数会忽略 Null，但不会忽略重复，去重要靠DISTINCT。

DISTINCT在 Access 里的位置是SELECT DISTINCT，设计器里对应查询属性中的“唯一的记录 = 是”。它和GROUP BY的区别是：DISTINCT只去重不聚合，GROUP BY通常会带聚合函数。取唯一客户名单这种活儿用DISTINCT更省事，别硬套GROUP BY。

还有一个属性值得记住：“TOP 值”。设计器里右键查询属性，把“TOP 值”改成 10，SQL 里就会出现TOP 10，配合ORDER BY就是“金额最高的十个客户”。它比先出全部结果再手工看前几行靠谱得多，尤其是数据量大到数据表视图会卡的时候。

3.3 参数查询与日期边界：为什么 Between 会少一天

固定日期范围的查询，下个月就得改一次，很蠢。参数查询解决这个问题：把条件写成[请输入开始日期]，运行时会弹框让你填。但有一个必须做的动作——在 SQL 顶部用PARAMETERS声明参数类型。

PARAMETERS 起 DateTime, 止 DateTime; SELECT [订单].客户编号, Sum([订单].金额) AS 金额合计 FROM 订单 WHERE [订单].订单日期 >= [起] AND [订单].订单日期 < [止] GROUP BY [订单].客户编号;

不写PARAMETERS那行，Access 会把用户输入的值当文本处理，和日期字段比较时可能出现“条件表达式中的数据类型不匹配”，或者更阴的——不报错但结果为空。DateTime这个类型名是 Access SQL 的关键字，写Date也能认，但DateTime更明确。

日期边界是另一个高频事故点。很多人写Between #2024-1-1# And #2024-12-31#，结果 12 月 31 日下午的订单一条都没算进去。原因很简单：日期字段里存的是日期加时间，#2024-12-31#等价于2024-12-31 00:00:00，带时分秒的记录比它大，自然落在区间外。

解决办法是养成左闭右开的习惯：>= 起 AND < 止，止传成下一天或者下一个月初。这样不管字段里有没有时间部分，边界都是干净的。另一个常见做法是用DateValue([订单日期])把时间抹掉再比较，能出结果，但字段上套了函数就没法用索引，几万行以上会明显变慢，只适合临时核对。

顺带说一句按月分组的写法。Format([订单日期], 'yyyy-mm')能出“2024-01”这样的标签，可读性好，但同样会导致索引失效，而且它是文本，排序是按字符串排的。要既快又稳，就在生成表阶段单独算一个月份字段存成日期型的月初，后续聚合直接对它分组。

4. 操作查询四件套：生成表、追加、更新、删除的执行顺序与交叉表转置

操作查询是这份资料里“敢用就回不去”的部分。前面三章的查询都是只读的，跑错了顶多结果难看；操作查询不一样，跑错了数据就没了，而且没有后悔药。所以这一章的重点不是语法，是顺序——先备份，再动数据，动完立刻复核。

4.1 生成表查询：给每一步操作留一层中间层

生成表查询的语法是SELECT ... INTO 新表名 FROM ...。它在 Access 里对应“查询类型”里的“生成表查询”，执行时会问你要新表的名称和目标数据库。

-- 把 2024 年的明细落地成一张本地中间表 SELECT [tbl订单_导入].订单号, [tbl订单_导入].客户编号, [tbl订单_导入].订单日期, [tbl订单_导入].金额, DateSerial(Year([订单日期]), Month([订单日期]), 1) AS 月初 INTO [tbl订单_2024] FROM [tbl订单_导入] WHERE [tbl订单_导入].订单日期 >= #2024-1-1# AND [tbl订单_导入].订单日期 < #2025-1-1#;

DateSerial(Year(d), Month(d), 1)算出每个月的一号，存成日期型，后面按月汇总时直接GROUP BY 月初，既有索引又不会有字符串排序的问题。INTO子句要求目标表不存在，如果表已存在会直接报“表已存在”，所以重跑之前得先删掉旧表。字段类型由源字段推断，文本长度可能被放大到 255，这一点在后续追加时需要留意。

生成表查询真正的价值是当中间层。原始导入表保持只读、不动它；所有清洗、去重、汇总都从它派生新表；出错就删掉中间表重来。这套做法听着笨，但它是唯一能让你在三个月后还敢改逻辑的方式。

4.2 追加、更新、删除：不可逆操作的执行顺序

这三类操作我固定按一个顺序走：先用相同的 WHERE 做一次SELECT Count(*)看清楚影响多少行，再用SELECT INTO做一份备份，然后才执行操作查询，最后再用同一个 WHERE 复核一遍。

追加查询的写法是INSERT INTO 目标表 (字段列表) SELECT ...。字段顺序不必和源表一致，但类型必须兼容，文本往数字里塞会整行被丢弃，而且不报错——这是最坑的地方。

-- 把 2023 年及以前的数据归档到历史表 INSERT INTO [tbl订单_归档] ( 订单号, 客户编号, 订单日期, 金额 ) SELECT 订单号, 客户编号, 订单日期, 金额 FROM [tbl订单_导入] WHERE 订单日期 < #2024-1-1#;

字段列表必须显式写出，不要依赖SELECT *，因为源表加一个字段，SELECT *的列数就变了，追加会直接失败。金额这种关键字段尤其要核对目标表的类型是不是货币，如果是长整型，小数会被静默截断。

更新查询的关键在 WHERE。下面这条把年度金额过十万的客户打上 A 级标记：

UPDATE [tbl客户] SET [客户等级] = 'A' WHERE [客户编号] IN ( SELECT [客户编号] FROM [tbl订单_2024] GROUP BY [客户编号] HAVING Sum([金额]) >= 100000 );

SET后面可以一次改多个字段，用逗号分隔；WHERE ... IN (子查询)在 Access 里可用，但子查询里带GROUP BY时会慢一些。数据量大就分两步：先用生成表查询把 A 级客户编号落地成一张小表，再用UPDATE ... INNER JOIN关联更新，UPDATE 目标表 INNER JOIN 来源表 ON 条件 SET 字段 = 值这个语法 Access 是认的。

删除查询的语法是DELETE FROM 表 WHERE 条件，如果涉及多表或别名，要写成DELETE 表.* FROM 表 WHERE ...。执行前那句提示框会告诉你影响多少行，那个数字一定要看，不看就点“是”的人，早晚要在生产库上重来一次。

4.3 交叉表查询：TRANSFORM…PIVOT 与列漂移

交叉表是把行转成列，Access 的语法结构和其他数据库的透视写法不太一样，是三段式：

TRANSFORM Sum([金额]) AS 金额合计 SELECT [客户编号], Sum([金额]) AS 行合计 FROM [tbl订单_2024] GROUP BY [客户编号] PIVOT Format([月初], 'yyyy-mm') IN ('2024-01','2024-02','2024-03','2024-04');

TRANSFORM后面跟的是单元格里的聚合值，这里是金额合计；SELECT ... GROUP BY决定行方向，每个客户一行，顺便带一个行合计；PIVOT后面决定列方向，Format([月初],'yyyy-mm')生成列名。设计器里对应的操作是“查询类型”→“交叉表查询”，网格里会多出一行“交叉表”，可以给字段指定“行标题”“列标题”“值”。

IN (...)这段列清单是重点，很多人不写，结果源数据里多出一个月，报表就多一列，下个月又少一列，每次都要重排格式，非常难受。写上IN之后，列名和顺序被固定死，不在清单里的值会被丢掉。代价是新增月份要手工补，这是可预期的维护成本，比列漂移好得多。

两个硬限制记一下：交叉表的列数上限是 255，超过就报错；交叉表的结果集不可更新，想改数据必须先落地成普通表。

5. 常见问题排查：五类翻车现场的现象、成因与定点修复

这一章按“现象—原因—解决”来写，全是实际会撞上的。有些属于配置问题，有些属于 SQL 写法问题，还有一些是 Access 本身的机制决定的，知道了一次就不会再犯。

5.1 导入之后字段类型跑偏：两个必须当场发现的现象

现象一：金额列能看见数字，但排序错乱，Sum返回空或者报“数据类型不匹配”。打开表设计一看，字段类型是文本。原因在于导入向导是按前若干行样本推断类型的，源列里只要混进了“N/A”“待定”、千分位逗号或者前后空格，整列就被判成文本；链接 Excel 表时这个判断更激进，因为它连样本行数都不给你看。解决办法是别让向导建表：先按目标类型手工建好空表，再用“追加到现有表”导入，或者在向导的“高级”里逐列指定类型并存成导入规范，下次直接调用。已经导错了的，不要在原表上改类型硬扛，重建一张表重导更快。

现象二：小数位消失，12.34 变成 12，合计差一截。两种可能：字段类型被设成了长整型，或者源数据是用文本存的带小数的值。前者的修复是在表设计里改成货币或双精度，改完 Access 会尝试就地转换，转不动的行会报“类型转换失败”，那就得重导。后者的修复是在导入前把源列清洗成纯数字。这里再强调一次：金额用货币，不要用单精度，单精度只有七位有效数字，几万行累加之后合计对不上，排查起来极其痛苦。

5.2 操作查询阶段的三次翻车

现象三：查询一执行就报“操作必须使用一个可更新的查询”。这条报错的意思是“我能读，但我不知道该怎么把结果写回去”。常见成因有四种：查询基于链接的 Excel 表；查询里用了聚合或交叉表；多表连接里有一侧是外部连接；查询属性里勾了“唯一的记录”。解决办法统一：先用SELECT ... INTO把结果落地成本地表，再对本地表做更新或删除。链接表只当数据入口，不当操作对象，这条规矩能省掉一半的报错。

现象四：更新或删除执行完，影响的行数远超预期，而且撤不回来。操作查询点“是”就提交，Access 的数据表视图没有针对它的撤销。行数超预期的典型原因是 WHERE 里用了 OR 却没加括号，A AND B OR C会被解析成(A AND B) OR C，条件一下子放宽了；另一个原因是 WHERE 里引用了查询里起的别名，别名在 WHERE 阶段还不存在，条件被忽略。我的固定动作是：写完操作查询先切成 SELECT，用同样的 WHERE 跑一次Count(*)，再跑一次SELECT TOP 10 *看样本，确认没问题再改回 UPDATE 或 DELETE。备份用生成表查询，一条语句的事，比出事之后再想办法强。

现象五：按月统计总是少一天或者多算一天。典型写法是Between #2024-1-1# And #2024-1-31#，而订单日期里带时分秒，1 月 31 日下午的单子全部落在区间外。反过来，如果止日期写成下个月 1 号又用了<=，就会把下个月 1 号 0 点整的记录算进来。统一改成>= 起 AND < 止，止传成下个周期的第一天，两头都干净。这类问题在明细核对时几乎看不出来，只有合计对不上才暴露，所以对账查询要常备。

5.3 文本与空值的隐蔽坑

现象六：两个文本字段用+连接，整列变成空白；Trim之后长度还是不对，和别的表匹配不上。前半个问题出在+：它遇到 Null 会把整个表达式变成 Null，而&会把 Null 当空串处理。连接文本一律用&，或者显式写Nz(字段, '')。后半个问题出在 Trim 的能力边界：Access 的Trim只去掉半角空格 Chr(32)，从网页或系统导出带过来的不间断空格 Chr(160)、制表符 Chr(9)、回车换行 Chr(13)+Chr(10) 它一个都去不掉，肉眼看着一模一样，长度就是差一两个字符，匹配自然失败。解决办法是嵌套Replace，把这几类不可见字符逐个替换掉，再用 Trim 收尾。清洗链的写法在下一章展开。

6. 清洗收尾与自证：拆合字段、不可见字符与两条对账查询

清洗这一步，我的原则是能在一个查询里做完就别拆成五个手工步骤，因为手工步骤无法复现。下面这条查询把字段拆合和不可见字符清理一次做完，字段名按实际替换即可。

SELECT [客户名称], Trim(Replace(Replace(Replace([客户名称], Chr(160), ''), Chr(9), ''), Chr(13) + Chr(10), '')) AS 名称_清洗后, Left([客户编号], 4) AS 地区码, Mid([客户编号], 5, 6) AS 流水号, IIf(InStr([地址], '省') > 0, Left([地址], InStr([地址], '省')), '') AS 省份 FROM [tbl客户_导入];

Replace从内往外执行，先干掉不间断空格，再干制表符，最后干回车换行，外面套Trim收掉残留的半角空格；Left和Mid按固定位置拆分编号，前提是编号长度规整，不规整就改用InStr找分隔符；IIf(InStr(...)>0, Left(...), '')是“找到了才截，找不到给空串”，避免Left遇到 0 位置报错。这套写法比手工一列一列改可靠得多，而且下个月重跑一遍就行。

清洗完必须自证，不能靠肉眼扫。我固定跑两条对账查询，用UNION ALL把源和目标的指标摆在一起：

-- 行数与金额双向对账 SELECT '源' AS 来源, Count(*) AS 行数, Sum([金额]) AS 合计 FROM [tbl订单_导入] WHERE [订单日期] >= #2024-1-1# AND [订单日期] < #2025-1-1# UNION ALL SELECT '目标', Count(*), Sum([金额]) FROM [tbl订单_2024];

行数不一致，通常是去重去多了或者日期边界写错了；行数一致但合计不一致，基本可以断定是金额字段类型不对或者有行被静默丢弃。两条查询都通过，才敢往下一步做交叉表和报表。

提示：UNION ALL两侧的列数和类型必须一致，Count(*)返回长整型、Sum返回数值，混着写一般没问题；但如果某一侧Sum全是 Null，结果会显示空白而不是 0，用Nz(Sum([金额]), 0)包一层更稳。

还有一个习惯值得养成：把数据库拆成前端和后端两个文件，后端只放表，前端放查询、窗体和报表，用“数据库工具”里的“拆分数据库”一条命令就能做。以后清洗逻辑要改、报表要调，只动前端，数据不动，出问题也好回退。这个操作五分钟能做完，能省掉以后无数次“刚才那个查询被我删了”的血泪经验。

从那以后我每次动操作查询之前，都强制走一遍“先 SELECT 计数、再 SELECT INTO 备份、才改类型执行”这套流程，一次没落下过。慢是慢了一点，但再没出现过整表数据回不来的情况。希望帮到你。

本文还有配套的精品资源，点击获取