计算同一字段中两个值之间的差异 [英] Calculate the variance between two values in the same field

问题描述

大家好。我正在创建一个数据库，用于保存住宅区的数据。每个月都会获取最新的电力和水量读数。我需要计算最后阅读量和每个住宅单位之前阅读量之间的方差和方差百分比。我不熟悉VBA或其他任何东西如果可能的话需要一些相当简单的东西吗？

Hi everyone. I''m creating a database that holds the data for a residential complex. Each month the latest electricity and water readings are taken. I need to calculate the variance and the variance percentage between the last reading amount and the previous reading amount per residential unit. I am not familiar with VBA or anything so need something fairly simple if possible?

推荐答案

我可以尝试用一般性的术语来解释但是更具体，更有帮助，将依赖本应作为问题一部分的信息。在这种情况下你的（可能）单个表的布局。


一般来说，你需要一个SELECT查询，它返回整个组中最大日期的值，其中组可能是该属性，以及该组中的最大日期小于另一个最大日期。因此，您需要两个日期来查找计算差异百分比所需的值。

I can try to explain in general terms but anything more specific, and probably helpful, would rely on information that should have been included as part of the question. In this case the layout of your (presumably) single table.

Generally speaking then, you would need a SELECT query that returns you values for the maximum date across that group, where the group would probably be the property, as well as the maximum date across that group that is less than the other maximum date. Thus you''d have the two dates you need to find the values you need to calculate your variance percentage.

ShazzieH：

我不熟悉VBA或其他什么，如果可能的话需要一些相当简单的东西？

ShazzieH:
I am not familiar with VBA or anything so need something fairly simple if possible?

不幸的是，你问了一个需要复杂SQL的问题。一旦你分享了你桌子的元数据，我们就可以进一步了解这一点，这样我们就知道数据是如何组合在一起的。


与Excel不同，Access是一个合适的数据库工具所以与序数（第1，第2等）并不是很好。它可以使用ORDER BY子句进行排序，但记录中没有任何关于它们在整体顺序中的位置的指示。因此，我们必须找到尴尬的方法。 Max（）可以很好地找到一个序列中的最后一个，但是找到倒数第二个意味着找到最后一个然后经过并找到排除前一个最后一个的最后一个。痛苦的我知道，但对于一个问题来说，这可能是最不痛苦的方法，因为数据库对于数据库来说是非常自然的 - 当然，它基于Set Theory。

Unfortunately you''ve asked a question that requires complicated SQL. We can get further into that once you''ve shared the meta-data of your table so we know how the data all fits together.

Unlike Excel, Access is a proper database tool so doesn''t really work well with ordinals (1st, 2nd etc). It can order using an ORDER BY clause but the records don''t come out with any sort of indication as to where abouts they come in the overall order. Thus we have to find awkward ways around that. Max() is fine to find the last in a sequence, but finding the second-to-last means finding the last then going through and finding the last of the set that excludes the previous last. Painful I know, but probably the least painful approach for a question that''s so unnatural for databases - which are based on Set Theory of course.

我创建了4个表：1）Reading_Dates，其中包含DATEID（主要字段），READING_DATE（实际日期）和月份的批量计量表。 2）读数其中包含DATEID，UNITNO，ELECTRICITY，STAFFELEC，WATER等字段。其他2个表只包含租户和所有者信息。我创建了一个列出UNITNO，READING_DATE和ELECTRICITY的查询。我过去曾设法编辑Select Queries并且（有点）创建了我自己的但我必须得到帮助。这个答案是否有助于你帮助我？提前谢谢你。

I have created 4 tables: 1) Reading_Dates which has DATEID (primary field), READING_DATE (Actual date) and the Bulk meter amount for the month. 2) "Readings" which contains DATEID, UNITNO, ELECTRICITY, STAFFELEC, WATER as the fields. The other 2 tables just contain Tenant and Owner information. I created a query which lists the UNITNO, READING_DATE and ELECTRICITY. I have in the past managed to edit Select Queries and (sort of) created my own but I''ve had to have help doing them. Does this answer help you to help me?? Thank you in advance.

@NeoPa：

首先，我希望你很好。难道这不是一个相对简单的问题，GROUP BY [Property＃]有2个计算字段返回[方差]和[方差％]？所有逻辑都将包含在由这些字段调用的公共函数中，并为每个字段返回相应的值。

@NeoPa:
First and foremost, I hope you are well. Couldn''t this be a relatively simply matter, GROUP BY [Property#] with 2 Calculated Fields returning [Variance] and [Variance%]? All Logic would be contained within the Public Functions that are called by these Fields and return the respective Values for each.

这篇关于计算同一字段中两个值之间的差异的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！