使用 Access 查询设计器轻松构造复杂 SQL 语句 (2)

article/2024/11/14 12:30:22

上一篇的 sql 语句蛮简单的，接下来，来看一个相对复杂的的场景。假设我们要编写一个查询，计算期间物料进出存的查询。需要用到 5 个表，将创建表的 sql 语句贴在下面，示例数据也已经上传到 github。

物料主数据表 (material_numbers)：

create table material_numbers ([MaterialNo] varchar(20) primary key,[Description_e] varchar(100) null,[Description_c] varchar(100) null
);

仓位 （storage_locations）

    create table storage_locations([StorageLocation] varchar(10) PRIMARY KEY,[LocationType] varchar(10) NULL,[Description] varchar(50) NULL);

movement_types:

CREATE TABLE movement_types([MovementTypeID] varchar(10) PRIMARY KEY,[Description] varchar(50) NULL,[InOutSign] varchar(1) NULL
);

stock_movement_headers:

CREATE TABLE stock_movement_headers([DocNo] varchar(10) PRIMARY KEY,[MovementType] varchar(10) NULL,[RefDocNo] varchar(10) NULL,[VendorID] varchar(10) NULL,[CustomerID] varchar(10) NULL,[DocDate] datetime NULL,[PeriodID] varchar(10) NULL,[GCInvoiceNo] varchar(15) NULL,[LongText] text NULL,[PostedBy] varchar(10) NULL,[PostedDate] datetime NULL,[Remarks] varchar(255) NULL
);

stock_movement_details:

CREATE TABLE stock_movement_details([DocNo] varchar(10),[MaterialNo] varchar(20),[StorageLocation] varchar(10) NULL,[Qty] float NULL,[Remarks] varchar(255) NULL,PRIMARY KEY(DocNo, MaterialNo)
);

在 MS Access 中表主要字段及表之间的关系：

查询条件是计算出 2008 年 4 月份物料的进出存，筛选条件：

- MaterialNo: Like "B180*'
- StorageLocation: 1001

最终的数据显示格式如下：

第一步：构建物料清单

首先构建 material_numbers 和 storage_locations 的组合 (笛卡尔乘积)。考虑到有些物料没有交易数据，这种组合能得到交易项全部为 0 的数据。在查询设计界面中，加入 material_numbers 和 storage_locations 两个表，双击 MaterialNo 和 StorageLocation 字段，将其加入到查询的字段。两个表为独立的状态，这样查询的结果就是一种笛卡尔乘积。在【条件】中，MaterialNo 输入 “B180*”, StorageLocation 输入 “1001”:

选中 material_numbers 表，将右边【属性表】表的别名改为 M，同样的方法将 storage_locations 表的别名改为 L。设置别名的目的是让 SQL 语句变得清晰。如果【属性表】没有出现，选中表，右键菜单有【属性】项，点击调出。完成后的界面应该如下所示：


进入 SQL 视图，此时 SQL 语句如下。因为 Access 不支持代码格式化和高亮，代码借助 Visual Studio Code 进行格式化。

SELECTM.MaterialNo,L.StorageLocation
FROMmaterial_numbers AS M,storage_locations AS L
WHERE(((M.MaterialNo) Like "B180*")AND ((L.StorageLocation) = "1001"));

第二步：构建交易清单

新建一个查询，将查询命名为 txns。查询基于三个表：

- stock_movement_headers: 交易表头
- stock_movement_details ： 交易行项目
- movement_types ： 移动类型，表示出入库的类型

因为没有维护表之间的关系，需要在查询中维护 stock_movement_headers 表和 movement_types 表之间的关系（左连接）：

双击或拖放的方式选中需要的字段到查询中：


为了方便后面的根据年月筛选，基于 DocDate 新增两个计算字段： TxYear 和 TxMonth，分别表示交易的年和月。在表达式生成器中维护：


增加一个计算列 ActualQty, 正数表示入库，负数表示出库：

ActualQty: IIf([InOutSign]="+",Nz([Qty]),-1 * Nz([Qty]))

为了 SQL 语句更加清晰，设置三个表的别名：

H:  stock_movement_headers
D:   stock_movement_details
MVT:  movement_types

然后对 materialNo 字段和 storageLocation 字段设置上一步相同的条件。设置完成后，查询设计视图的界面如下：

进入 SQL 视图，得到本步骤的 SQL 语句：

SELECTH.DocDate,D.MaterialNo,D.StorageLocation,H.MovementType,D.Qty,MVT.InOutSign,Year([DocDate]) AS TxYear,Month([DocDate]) AS TxMonth,IIf([InOutSign] = "+", Nz([Qty]), -1 * Nz([Qty])) AS ActualQty
FROM(stock_movement_headers AS HLEFT JOIN movement_types AS MVT ON H.MovementType = MVT.MovementTypeID)INNER JOIN stock_movement_details AS D ON H.DocNo = D.DocNo
WHERE(((D.MaterialNo) Like "B180*")AND ((D.StorageLocation) = "1001"));

运行一下查询，此时的界面如下：

第三步：添加条件列

接下来基于查询 txns 创建一个查询，使用行转列的方法增加三个计算列：

- BeginQty: 期初余额
- StockIn: 期间入库数量
- StockOut: 期间出库数量

将查询命名为 txn_summary，首先选取相关的列：


添加条件列，在表达式生成器界面中设置 BeginQty 的公式如下：

BeginQty: IIf([TxYear]<2008 Or ([TxYear]=2008 And [TxMonth]<4),[ActualQty],0)

同样的方法，添加计算列，得到物料入库数量和出库数量：

StockIn: IIf([TxYear]=2008 And [TxMonth]=4 And [InOutSign]="+",[ActualQty],0)
StockOut: IIf([TxYear]=2008 And [TxMonth]=4 And [InOutSign]="-",[ActualQty],0)

删除查询中不相关的字段(TxYear, TxMonth, InoutSign, ActualQty)，然后点击功能区上的汇总：

将需要计算的字段改为合计：


完成本步骤，得到的 SQL 语句为：

SELECTtxns.MaterialNo,txns.StorageLocation,Sum(IIf([TxYear] < 2008Or ([TxYear] = 2008And [TxMonth] < 4),[ActualQty],0)) AS BeginQty,Sum(IIf([TxYear] = 2008And [TxMonth] = 4And [InOutSign] = "+",[ActualQty],0)) AS StockIn,Sum(IIf([TxYear] = 2008And [TxMonth] = 4And [InOutSign] = "-",[ActualQty],0)) AS StockOut
FROMtxns
GROUP BYtxns.MaterialNo,txns.StorageLocation;

因为本步骤基于查询 txns，所以可以将查询稍加修改，并且插入上一步骤 txns 查询的代码。From 子句原来是这样的：


将其修改为：

括号中插入上一步 txns SQL 语句的码, 将其作为子查询：

SELECTtxns.MaterialNo,txns.StorageLocation,Sum(IIf([TxYear] < 2008Or ([TxYear] = 2008And [TxMonth] < 4),[ActualQty],0)) AS BeginQty,Sum(IIf([TxYear] = 2008And [TxMonth] = 4And [InOutSign] = "+",[ActualQty],0)) AS StockIn,Sum(IIf([TxYear] = 2008And [TxMonth] = 4And [InOutSign] = "-",[ActualQty],0)) AS StockOut
FROM(SELECTH.DocDate,D.MaterialNo,D.StorageLocation,H.MovementType,D.Qty,MVT.InOutSign,Year([DocDate]) AS TxYear,Month([DocDate]) AS TxMonth,IIf([InOutSign] = "+", Nz([Qty]), -1 * Nz([Qty])) AS ActualQtyFROM(stock_movement_headers AS HLEFT JOIN movement_types AS MVT ON H.MovementType = MVT.MovementTypeID)INNER JOIN stock_movement_details AS D ON H.DocNo = D.DocNoWHERE(((D.MaterialNo) Like "B180*")AND ((D.StorageLocation) = "1001"))) AS txns
GROUP BYtxns.MaterialNo,txns.StorageLocation;

计算期末库存

联合查询 material (别名 MT) 和 txn_summary (别名 TX)，创建一个新的查询：


此时的 SQL 语句为：

SELECTMT.MaterialNo,MT.StorageLocation,TX.BeginQty,TX.StockIn,TX.StockOut,[BeginQty] + [StockIn] + [StockOut] AS EndQty
FROMmaterials AS MTLEFT JOIN txn_summary AS TX ON (MT.StorageLocation = TX.StorageLocation)AND (MT.MaterialNo = TX.MaterialNo);

将 FROM 子句后 materials 和 txn_summary 替换为子查询，得到最后完整的 SQL 语句：

SELECTMT.MaterialNo,MT.StorageLocation,TX.BeginQty,TX.StockIn,TX.StockOut,[BeginQty] + [StockIn] + [StockOut] AS EndQty
FROM(SELECTM.MaterialNo,L.StorageLocationFROMmaterial_numbers AS M,storage_locations AS LWHERE(((M.MaterialNo) Like "B180*")AND ((L.StorageLocation) = "1001"))) AS MTLEFT JOIN (SELECTtxns.MaterialNo,txns.StorageLocation,Sum(IIf([TxYear] < 2008Or ([TxYear] = 2008And [TxMonth] < 4),[ActualQty],0)) AS BeginQty,Sum(IIf([TxYear] = 2008And [TxMonth] = 4And [InOutSign] = "+",[ActualQty],0)) AS StockIn,Sum(IIf([TxYear] = 2008And [TxMonth] = 4And [InOutSign] = "-",[ActualQty],0)) AS StockOutFROM(SELECTH.DocDate,D.MaterialNo,D.StorageLocation,H.MovementType,D.Qty,MVT.InOutSign,Year([DocDate]) AS TxYear,Month([DocDate]) AS TxMonth,IIf([InOutSign] = "+", Nz([Qty]), -1 * Nz([Qty])) AS ActualQtyFROM(stock_movement_headers AS HLEFT JOIN movement_types AS MVT ON H.MovementType = MVT.MovementTypeID)INNER JOIN stock_movement_details AS D ON H.DocNo = D.DocNoWHERE(((D.MaterialNo) Like "B180*")AND ((D.StorageLocation) = "1001"))) AS txnsGROUP BYtxns.MaterialNo,txns.StorageLocation) AS TX ON (MT.StorageLocation = TX.StorageLocation)AND (MT.MaterialNo = TX.MaterialNo);

示例数据

github: stocks.accdb


http://chatgpt.dhexx.cn/article/JYVlEIno.shtml

相关文章

基于Access的学生信息管理系统设计（上）：表建立、表查询设计

基于Access的学生信息管理系统设计（上）：表建立、表查询设计

在学校的教务管理中&#xff0c;学生信息的管理是的一非常重要的环节&#xff0c;学生信息管理是一个学校信息管理的核心和信息管理的基础。现在我们国家的学校的信息管理系统开始逐步实现信息化管理&#xff0c;但是这样的管理机制并不能完全适应信息时代的超高速发展的各种要…
阅读更多...
Access教程 第三章 查询

Access教程 第三章 查询

本章内容 ◆ 查询的概念、种类和作用。 ◆ 各种查询的建立。 ◆ 查询的应用。 一、查询的概念 1 ．什么是查询 查询就是依据一定的查询条件，对数据库中的数据信息进行查找。它与表一样，都是数据库的对象。它允许用户依据准则或查询条件抽取表中的记录与字段。 Acce…
阅读更多...
SQL Server 不允许保存更改的解决方法

SQL Server 不允许保存更改的解决方法

对于表的修改出现不允许保存更改的解决方法&#xff0c;刚开始以为是不能保存设置主键&#xff0c;后来发现不是这样的&#xff0c;是设置的问题。 点击【工具】——【选项】&#xff0c;点击【设计器】——【表设计器和数据库设计器】&#xff0c;把“阻止保存要求重新创建表…
阅读更多...
【小5聊】Sql Server 2012 更改数据类型之提示不允许保存更改解决方法

【小5聊】Sql Server 2012 更改数据类型之提示不允许保存更改解决方法

1、创建表&#xff0c;然后添加了一两个字段保存创新一张新表 2、当再次更改其中一个字段的数据类型时&#xff0c;提示如下信息 3、解决方法&#xff0c;如下 【一句话一感想一心情】版本更新&#xff0c;增加测颜值推荐图文&#xff0c;来体验下句子的魅力
阅读更多...
SQL Server不允许保存更改【解决】

SQL Server不允许保存更改【解决】

问题&#xff1a; 解决&#xff1a; 1、工具→选项 2、设计器→取消”阻止保存要求重新创建表的更改“→确定
阅读更多...
SQL Server 【不允许保存更改】问题解决图解

SQL Server 【不允许保存更改】问题解决图解

问题点 当表已经被设计保存后&#xff0c;再次设计表&#xff0c;使用设计-增加删除列 或其他方式修改了表的结构时&#xff0c;选择保存。 无法保存&#xff0c;出现以下警告提示&#xff1a; 不允许保存更改。您所做的更改要求删除并重新创建以下表。您对无法重新创建的表进行…
阅读更多...
SQL Server修改表结构，不允许保存更改解决方案

SQL Server修改表结构，不允许保存更改解决方案

解决方案 当修改表结构时&#xff0c;sql server会弹出对话框&#xff0c;显示以下内容&#xff1a; 不允许保存更改。您所做的更改要求删除并重新创建以下表。您对无法重新创建的表进行了更改或者启用了“阻止保存要求重新创建表的更改”选项。 分析这句话&#xff0c; 1、…
阅读更多...
SQL SERVER—修改时不允许保存修改

SQL SERVER—修改时不允许保存修改

【前言】 在之前机房合作的时候&#xff0c;也经常遇到SQL Server错误提示&#xff1a;不允许保存修改这个问题。当时不是连接的自己本地的数据库&#xff0c;以为是数据库在谁的本地电脑上才能修改&#xff0c;现在才明白&#xff0c;其实不是这样的。下面来看看如何解决问题…
阅读更多...
修改SQLServer数据库已存在的表结构保存后提示“不允许保存更改”解决办法

修改SQLServer数据库已存在的表结构保存后提示“不允许保存更改”解决办法

报错截图如下&#xff1a; 解决方法如下&#xff1a; 1、单击SQL Server的“工具”→“选项”&#xff0c;如下图&#xff1a; 2、选择“设计器”&#xff0c;右侧取消“阻止保存要求重新创建表的更改(S)”勾选。
阅读更多...
SQL Server 不允许保存更改。您所做的更改要求删除并重新创建以下表。您对无法重新创建的表进行了更改或启用了“阻止保存要求重新创建表的更改”选项

SQL Server 不允许保存更改。您所做的更改要求删除并重新创建以下表。您对无法重新创建的表进行了更改或启用了“阻止保存要求重新创建表的更改”选项

解决方法&#xff1a; 1、 2.
阅读更多...
SQL Server 不允许保存更改

SQL Server 不允许保存更改

在创建了表之后&#xff0c;添加了数据&#xff0c;想修改一下数据库的数据结构&#xff0c;比如把varchar改成nvarchar&#xff0c;sql通常会弹出如下错误提示&#xff1a; 解决办法&#xff1a;工具》选项》Designers__表设计器和数据库设计器__然后勾选掉“阻止保存要求重新…
阅读更多...
SQL server修改字段名等出现不允许保存更改的解决方法

SQL server修改字段名等出现不允许保存更改的解决方法

问题&#xff1a; 解决方法&#xff1a; 点击 工具->选项->设计器->表设计器和数据库设计器->去掉【阻止保存要求重新创建表的更改】前面的勾
阅读更多...
SQL Server基础 第三章 数据表基本操作（增删改查，不允许保存更改异常！）

SQL Server基础 第三章 数据表基本操作（增删改查，不允许保存更改异常！）

往表里插数据我们现在有两种方式第一种是编辑直接修改&#xff0c;第二种是通过查询来修改数据 两种方法的区别 第一种更直接&#xff0c;如果数据量小那么直接改就好了&#xff0c;那如果数据量稍微庞大我们就需要用新建查询来进行表内容的修改了&#xff01;&#xff01;&a…
阅读更多...
SQL Server不允许保存更改的解决方法

SQL Server不允许保存更改的解决方法

点击上面的【工具】→【选项】 在选项对话框中&#xff0c;点击【设计器】→【表设计器和数据库设计器】 去掉【阻止保存要求重新创建表的更改】前面的勾&#xff0c;然后确定 好啦&#xff0c;再去试试吧&#xff0c;应该可以正常修改表的结构啦 \(^o^)/
阅读更多...
SQLServer2008不允许保存更改

SQLServer2008不允许保存更改

修改方法&#xff1a;
阅读更多...
解决方法-SQLserver建表后更改列，显示不允许保存更改。您所做的更改要求删除并重新创建以下表

解决方法-SQLserver建表后更改列，显示不允许保存更改。您所做的更改要求删除并重新创建以下表

SQLserver建表后更改列&#xff0c;显示不允许保存更改。您所做的更改要求删除并重新创建以下表。 解决方法&#xff1a; 工具选项卡—选项—设计器&#xff0c;去掉"阻止保存要求重新创建表的更改"的勾选就可以了&#xff0c;之后再更改列的时候就不会改不了了。
阅读更多...
SQL Server 之 修改时不允许保存更改

SQL Server 之 修改时不允许保存更改

SQL Server错误提示&#xff1a;不允许保存更改。 不允许保存更改。您所做的更改要求删除并重新创建以下表。您对无法重新创建的表进行了更改或者启用“阻止保存要求重新创建表的更改”选项。 　　修改数据库的数据结构&#xff0c;比如增加字段&#xff0c;修改字段类型&#…
阅读更多...
sql2019更改了表的部分字段设计出现不允许保存更改解决方案

sql2019更改了表的部分字段设计出现不允许保存更改解决方案

保存时出现错误 解决方案&#xff1a; 点击工具下的选项 打开如下窗口&#xff0c;选择左边的设计器&#xff0c;再取消右边的组织保存要求重新创建表的更改&#xff0c;就ok了
阅读更多...
SQL不允许保存更改，阻止保存要求重新创建表的更改

SQL不允许保存更改，阻止保存要求重新创建表的更改

修改数据库表结构时提示【不允许保存更改。您所做的更改要求删除并重新创建以下表。您对无法重新创建的标进行了更改或者启用了“阻止保存要求重新创建表的更改"选项。】 工具/原料 数据库 方法/步骤 1 登录sql2008&#xff0c;找到数据库【news】展开表文件夹 2 在表…
阅读更多...
mysql 不允许保存修改_解决SQL SERVER 2014 不允许保存更改。您所做的更改要求删除并重新创建以下表。您对无法重新创建的表进行了更改或者启用了阻止保存要求重新创建表的更改选项...

mysql 不允许保存修改_解决SQL SERVER 2014 不允许保存更改。您所做的更改要求删除并重新创建以下表。您对无法重新创建的表进行了更改或者启用了阻止保存要求重新创建表的更改选项...

在新安装的SQL SERVER 2014上修改表结构&#xff0c;因为默认启用了"阻止保存要求重新创建表的更改"&#xff0c;所以导致出现警告&#xff0c;提示不允许保存更改。其实这是一个老早的问题了&#xff0c;这个问题最早出现在SQL SERVER 2008上&#xff0c;只要对数据…
阅读更多...
推荐文章

Copyright @ 2022~2023