计算Access中报表的总计值 [英] Calculating a Sum Value on a report in Access

问题描述

我有一个报告，应该显示转换前后产品的库存价值。

I have a report that is supposed to show the Stock Value of products before and after conversion.

我有源产品产品代码和库存值显示在形式，现在我需要从这一块股票转换的项目的值。

I have the source products product code and stock value displayed on the form, now I need to get the value of the items converted from that one piece of stock.

我假设这将是可能实现使用一些简单的SQL计算具有相同SCID（库存转换ID）的所有产品的总和。我的SCID用于将源产品链接到不同表中的多个结果产品。

I assumed this would be possible to achieve using some simple SQL to calculate the sum of all resulting products with the same SCID (Stock Conversion ID). My SCID is used to link a source product to a number of resulting products in a different table.

在这张图片中我命名了SCID boc在细节部分sSCID，它可能尝试拾取的表中的任何字段。但是基本上我需要获取代码来查找与该框中显示的SCID匹配的结果产品，并将它们全部添加到一起以获得转换的股票的总和。

In this image I have named the SCID boc in the detail section sSCID to try and differentiate it from any fields in tables that it may try to pick up. But basically I need to get the code to look for result products that match the SCID displayed in that box and add them all together to get the sum total of converted stock.

如果上下文帮助我试图创建一个表单，显示股票的前后转换过程中的值，以尝试和计算浪费。

If context helps I am trying to create a form that shows the value of stock before and after a conversion process in order to try and calculate the wastage.

谢谢，
Bob P

Thanks, Bob P

推荐答案

您可以使用DSum或构建报表查询以包含计算。我更喜欢第二个选项。这假设SCID是数字的，您需要为文本SCID添加引号。

You can either use DSum or build your report query to include the calculation. I would prefer the second option. This assumes that SCID is numeric, you would need quotes for a text SCID.

=DSum("sValue","[Stock Conversion Items]","SCID=" & sSCID)

或

 SELECT r.This,r.That,s.ConvValue 
 FROM ReportTable r
 INNER JOIN (
      SELECT SCID, Sum(sValue) As ConvValue 
      FROM [Stock Conversion Items]
      GROUP BY SCID) s
 ON r.sSCID = s.SCID

上述查询对表使用别名r和s： ReportTable r 或 ReportTables As r

The above query uses aliases r and s for the tables : ReportTable r or ReportTables As r

这篇关于计算Access中报表的总计值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！