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

您所在的位置：网站首页 › access表达式生成器表达式如何写 › 使用 Access 查询设计器轻松构造复杂 SQL 语句 (2)

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

2023-10-13 16:16| 来源: 网络整理| 查看: 265

上一篇的 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 进行格式化。

SELECT M.MaterialNo, L.StorageLocation FROM material_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 语句：

SELECT H.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 H LEFT 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]

【本文地址】

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

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

今日新闻

推荐新闻