太多数据麻烦??? [英] Trouble with too much data???

问题描述

我正在努力为电力读数创建数据库。我们每个月都要读42米。目前使用的Kwh是在MS Excel中计算的。但是，由于我们需要一个中央数据库，我们想将这些数据集成到MS Access中。这就是我到目前为止所做的。自2001年1月以来，我创建了42个包含所有读数的表格，主要关键是StartDate。对于每个表，我创建了一个查询，其中正在计算实际使用情况（基于之前的读数，前一个月）。每个仪表都有一个因子，基本上使用的Kwh乘以系数得到实际使用的Kwh。到现在为止还挺好。请记住，我现在有42个（子）查询。

最终目标是计算成本中心使用的Kwh。因此，每个仪表都有一个或多个成本中心，用于分配使用的Kwh。

这也不是一个大问题，但是访问变得有点慢，我认为这是因为计算它必须在表中使用的Kwh上执行？为了计算这些使用的Kwh，我创建了27个额外的查询来计算每个计量器使用的Kwh。对于成本结构，一些仪表必须从其他仪表中扣除。我稍后会提供一个例子。然后我创建了4个新查询来过滤成本中心。但是，访问速度有点慢，但执行计算。然后，当尝试创建另一个查询以添加之前的4个查询时，访问权限显示：查询过于复杂。我想将这4个查询加起来，以计算每个成本中心总数的百分比。


这里的例子如下：

Meter 1

仪表2

仪表42

要计算使用的Kwh，将重定向到特定的成本中心，我从仪表2中扣除仪表42 。

最后，我获得了11个计算，这些计算被分配给一个特定的成本中心。


当然最终查询过于复杂。你会如何解决这个问题。如果需要附加信息，可以提供。


早先感谢！


问候，


Marco

Hi,

I''m currently working on creating a DB for electricity readings. We have 42 meters wich are being read every month. Currently the Kwh used are being calculated in MS Excel?. However, due to the fact that we want a central database we''d like to integrate these figures into MS Access?. This is what I have done so far. I''ve created 42 tables with all of the readings since January 2001 up until now, the primary key is the StartDate. For each table I''ve created a query in which the actual use is being calculated (based on the previous reading, the month before). Each gauge has a factor, basicaly the Kwh used are being multiplied by the factor to get the actual Kwh used. So far so good. Bear in mind that I''ve now got 42 (sub)queries.
The ultimate goal is to calculate the Kwh used by Cost Center. Therefore each gauge has 1 or more Cost Centers, to where the Kwh used will be assigned.
This is not a big problem either, however access becomes a bit slow, I presume this is because of the calculations it has to perform on the Kwh used from the tables? To calculate these Kwh used, I''ve created 27 additional queries to calculate the used Kwh per gauge. For the cost structure, some gauges have to be deducted from others. I will provide an example later on. Then I''ve created 4 new queries to filter on the Cost Center. Still, access is a bit slow but performs the calculations. Then when trying to creat another query to add up the 4 previous queries, access says:"query too complex". I want to add up these 4 queries in order to calculate percentages of the total per Cost Center.

Here the example:
Meter 1
Meter 2
Meter 42

To calculate the Kwh used, which will be redirected to the particular Cost Centers I deduct Meter 42 from Meter 2.
At the end I get a 11 calculations which are assigned to one particular Cost Center.

Surely the end query is too complex. How would you solve this problem. If addional information is needed, it can be provided.

Thanks upfront!

Regards,

Marco

推荐答案

你好，Marco。


你肯定需要重新设计你的数据库，考虑到关系数据库的基本原则。


问候，

鱼

Hello, Marco.

You definitely need to redesign you database taking into account fundamental principles of relational database.

Regards,
Fish

Hi FishVal，


谢谢，我已经读了一篇关于同样的文章来自Allen Browne的话题。这听起来很合乎逻辑，但也许我很想念它。据我所知，一切看起来都很好。当然，我错过了大的cahoena。你或者有人引导我通过我的数据库标准化计算能量数据吗？

此刻我有46张桌子。它们的结构如下：

44个表，每个仪表有读数：StartDate，EndDate，MeterID，Reading。

1个带有MeterID和因子的表

1包含MeterID，CostCenter和百分比的表（每个CostCenter）。

我的目标是根据CostCenter计算一个月消耗的总能量百分比和数量。

再次感谢！


Marco

Hi FishVal,

Thanks, I already read a piece about the same topic from Allen Browne. It sounds very logical, but perhaps I''m missing it. As far as I can tell everything looks fine to me. Surely I''m missing the big cahoena. Could you, or someone guide me trough this Normalization of my db for calculating energy figures?
At this moment I''ve got 46 tables. They are structured as follow:
44 tables with readings for each meter: StartDate, EndDate, MeterID, Reading.
1 table with MeterID and Factor
1 table with MeterID, CostCenter and Percentage (per CostCenter).
My goal is to calculate per CostCenter the percentage of total energy consumed in a month and the quantity.

Thanks again!

Marco

应该有2张桌子而不是44张。


[tblMeters]

MeterID - 主键


[tblReadings]

MeterId - 外键（[tblMeters]）

...以及所有其余的


问候，

鱼

There should be 2 tables instead of 44.

[tblMeters]
MeterID - Primary key

[tblReadings]
MeterId - Foreign key ([tblMeters])
... and all the rest

Regards,
Fish

这篇关于太多数据麻烦???的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！