想学做ACCESS的查询，但不知如何做，请高手帮忙。

lzgbc · 发表于 2009-7-22 15:42

ACCESS的查询如何做列的统计呀。请高手帮忙做做，我来学习一下，谢谢

Renco · 发表于 2009-7-23 15:41

请按系统的F1或帮助说明,里面有示例及说明,

yjvaps · 发表于 2009-7-24 02:16

是不是这样的

lzgbc · 发表于 2009-7-24 16:04

没有列的“合计”数呀。。

zez · 发表于 2009-7-24 19:01

查询1：
SELECT [车队] & "  小计" AS [车队], '' AS 拖车号, '' AS 客户名称, Sum(Sheet1.应收) AS 应收之总计, Sum(Sheet1.应付) AS 应付之总计, nz(Sum(Sheet1.应收))-nz(Sum(Sheet1.应付)) AS 收入
FROM Sheet1
GROUP BY [车队] & "  小计"
UNION SELECT "总  计" AS [车队], '' AS 拖车号, '' AS 客户名称, Sum(Sheet1.应收) AS 应收之总计, Sum(Sheet1.应付) AS 应付之总计, nz(Sum(Sheet1.应收))-nz(Sum(Sheet1.应付)) AS 收入
FROM Sheet1;
查询2：
SELECT [车队] & "  小计" AS [车队], '' AS 拖车号, Sheet1.客户名称, Sum(Sheet1.应收) AS 应收之总计, Sum(Sheet1.应付) AS 应付之总计, nz(Sum(Sheet1.应收))-nz(Sum(Sheet1.应付)) AS 收入
FROM Sheet1
GROUP BY [车队] & "  小计", Sheet1.客户名称
UNION SELECT "总  计" AS [车队], "" AS 拖车号, "" AS 客户名称, Sum(Sheet1.应收) AS 应收之总计, Sum(Sheet1.应付) AS 应付之总计, nz(Sum(Sheet1.应收))-nz(Sum(Sheet1.应付)) AS 收入
FROM Sheet1;
查询3：
SELECT DISTINCTROW Format$([Sheet1].[日期],'yyyy-mm') AS [日期按月], [车队] & "  小计" AS [车队], "" AS 拖车号, "" AS 客户名称, Sum(Sheet1.应收) AS 应收总计, Sum(Sheet1.应付) AS 应付总计, nz(Sum(Sheet1.应收))-nz(Sum(Sheet1.应付)) AS 收入
FROM Sheet1
GROUP BY Format$([Sheet1].[日期],'yyyy-mm'), [车队] & "  小计", Year([Sheet1].[日期])*12+DatePart('m',[Sheet1].[日期])-1;

biaotiger1 · 发表于 2009-7-28 08:22

在3楼高手基础上增加了合计字段

biaotiger1 · 发表于 2009-7-28 08:33

原帖由 zez 于 2009-7-24 19:01 发表
查询1：
SELECT [车队] & " 小计" AS [车队], '' AS 拖车号, '' AS 客户名称, Sum(Sheet1.应收) AS 应收之总计, Sum(Sheet1.应付) AS 应付之总计, nz(Sum(Sheet1.应收))-nz(Sum(Sheet1.应付)) AS 收入
FROM Shee ...

牛人

nz我刚开始学查询设计就知道了，第一次见到的如下
1、[车队] & " 小计" AS [车队]
2、'' AS 拖车号, '' AS 客户名称

楼上两位都是高手，学习了

lzgbc · 发表于 2009-7-28 23:40

谢谢，楼上的朋友，以上的我学会了，再次说声谢谢。

		自动登录	找回密码
密码			免费注册

[求助] 想学做ACCESS的查询，但不知如何做，请高手帮忙。

是不是这样的

回复 3楼 yjvaps 的帖子