Trouble with too much data???

[Crombam]

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

[FishVal]

Hello, Marco.

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

Regards,
Fish

[Crombam]

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

[FishVal]

There should be 2 tables instead of 44.

[tblMeters]
MeterID - Primary key

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

Regards,
Fish

[Crombam]

Hi Fish,

After the holidays I started to read my way through understanding Nomalization.
SO I've created now two tables:

tblRaedings [StartDate], [EndDate], [MeterID], [Reading]
tblGauges [MeterID(PK)] and [Factor]

After running the first query I succeeded in calculating the usage per gauge, but...now I want to calculate the actual usage per cost center. Therefore some gauges have to be deducted from other. E.g. if I want to assign gauge 2 to a cost center then I first have to deduct the usage from gauge 42 from gauge 2. After doing so, I can make a join to the Cost Center table so each gauge gets the assigment to a cost center it is set up for. I'm not able to get it working, othet then creating many queryies, which doesn't make any sence. How can I get this to work?

Rg.

Marco

[FishVal]

Hello, Marco.

Assuming relation between "gauge 42" and gauge 2" is permanent and exclusive, you just need to make tblGauges self-related.

tblGauges
MeterID, PK
[Factor]
RelatedMeterID, FK(tblGauges)

This table could be joined with tblReadings twice (on MeterID and RelatedMeterID) .

Regards,
Fish

[Crombam]

Hi Fish,

Thanks for working me through, I realy appreciate this!
OK, so what you say will go if I was to deduct the usage of 1 meter from another. Does this work when for multiple meters? For example, gauge 13. If I want to calculate its usage for assigning a cost center I have to deduct gauge 9, 11, 12, 20 and 21. By the way, yes the relation is permanent and exclusive.

Thanks again...

Rg.

Marco

[FishVal]

Could you please outline clearly business rules of your application?

Regards,
Fish

[Crombam]

Hi Fish,

I'm not sure what you mean? If you mean what the purpose is I will try to explain the best I can. We have 42 gauges on Site. They are read every month at the same time. Whith these readings we try to charge 4 Cost Centers (Cost carriers Site). However, we have main gauges and subgauges. Out of the 42 gauges in total we have 12 main gauges and 15 sub gauges. So not all gauges are used in this calculation, but are read for usage purpose. The main gauges are set up before the sub gauges, so in general you can say that multiple sub gauges must make 1 main gauge. Through time the gauge numbers didn't change at all, however the Cost Centers did. E.g. Gauge 13. Gauge 13 is a main gauge, under gauge 13 there are 5 sub gauges installed. Because gauge 13 has to be spread out over 2 Cost Centers we have to calculate its usage after deduction of the sub gauges. So, gauge 13 minus 9, 11, 12, 20 and 21 will be divided over two Cost Centers for different %. That means that gauge 13 has a user connected to it which has to be calculated and assigned to 2 Cost Centers.
At the end I want to add up all usages for the 4 Cost Centers and calculate its percentage in regard to the total Kwh used for a month. Next to the tables described I have a tabel called tblKostenAfreke ningsSysteem (a bit long but for now functional). In this table the meters are setup for the percentages they are assigned to per Cost Center. It works fine, only that I can't calculate the actual usage of gauge 13 for example. It gives me the reading of gauge 13, not the actual usage (remember; to calculate its actual usage you have to deduct the subgauges).
I hope this is what you meant by business rules for the application. If not let me know.

Regards,

Marco

[FishVal]

Hello, Marco.

Yes, this is exactly what I've meant. Wish all the posters were giving so clear and thorough explanations. :)

"Applicatio n business rules" is not something I've invented myself :D. It is a term to refer to a set of of rules of how application has to behave to perform accordingly to what it has been developed for.

So, apparently, gauge to subgauges relation is 1toM.
This makes approach with self-related table quite feasible:

tblGauges
MeterID, PK
MainMeterID, FK(tblGauges)
.... all the rest fields

To sum readings of all subgauges you can use grouping query like the following:

SELECT tblGauges.MainMeterID, Sum(tblReadings.Reading) AS SubGaugesTotal FROM tblGauges INNER JOIN tblReadings ON tblGauges.MeterID=tblReadings.MeterID GROUP BY tblGauges.MainMeterID;

Thus obtained dataset will contain records where MainMeterID appears together with toatal of sub-gauges readings and could be easily joined with [tblReadings] to subtract sub-gauges totals from the relavant main-gauges readings.
Obviously tblReadings appearing in the query above is not [tblReadings] as is, but a subset of [tblReadings] filtered to return only relevant readings (another business rule ;) - not sure maybe the last reading or the reading in some date interval).

Regards,
Fish

[Crombam]

Hi Fish,

Thanks for the detailed explanation. I'm not sure I understand the self related feature. How do I do that? I understand how it works, but I seem to not get it going. Could you explain more?

Rg.

Marco

[FishVal]

"Self-related" means the table contains foreign key field which primary key is the same table primary key.
This configuration allows items stored in table be linked into a tree data structure - like file system with folders and subfolders with infinite possible level of nesting. However in your case you most likely to use only one-level nesting - maingauges/subgauges.

To establish this kind of relation:

• open "Relations" window
• make sure two "copies" of the table appear on it
• drag-and-drop relation just like if they are different tables

[Crombam]

In the window Relations I have now 3 tables:
1. tblGauges
2. tblGauges_1
3. tblReadings
For both tblGauges and tblGauges_1 the following fields:
- [MeterID] (PK)
- [Factor]
- [MainMeterID]
It's the same tabel only tblGauges_1 is a copy of the tblGauges

The following relations are defined:
a. 1-to-many from [tblGauges]![MeterID] to [tblReadings]![MeterID]
b. 1-to-many from [tblGauges_1]![MeterID] to [tblGauges]![MainMeterID]
Following your descriptions this should be accurate?
I ran the query and I get a uge number, but this could be correct because it sums the readings instead of the usages. So I will set up the criteria again to make sure the data is read like it should be. This helps a lot, thanks again.

Rg.

Marco

[FishVal]

Tables setup looks fine.
As for incorrect summing - this what I've meant in last paragraph of post #10. I didn't make any suggestions on this because business rules of this part are somewhat unclear to me:

• On the one hand - usage for a single gauge within a certain period could be easily calculated via aggregating query as difference between the earliest (or least) reading and latest (or biggest) reading. This doesn't deal correctly with possible gauge resetting (installing new one). However, if new/resetted gauge gets new ID (which makes sense), then this will not be a problem.
• On the other hand - to get correct results on a main gauge you need readings made in the same time for the main gauge and all its subgauges.

If you outline business logic for this part I could help you with its implementation in your database.

Regards,
Fish.

[Crombam]

Hi Fish,

If I just had the usages per meter (as input into a table this would be fine, no harm done, however it's not), instead I decided to use the readings (I want to use this later on when creating a form for the mechanics to see the last reading).

I calculate the usage as follows:
IIf([Reading]-(SELECT [Reading] FROM [tblReadings] AS Alias WHERE StartDate = (SELECT Max(StartDate) FROM [tblReadings] AS Alias2 WHERE Alias2.StartDat e < [tblReadings].StartDate AND Alias2.MeterID = [tblReadings].MeterID) AND Alias.MeterID = [tblReadings].MeterID)<0,(10 00000-(SELECT [Reading] FROM [tblReadings] AS Alias WHERE StartDate = (SELECT Max(StartDate) FROM [tblReadings] AS Alias2 WHERE Alias2.StartDat e < [tblReadings].StartDate AND Alias2.MeterID = [tblReadings].MeterID) AND Alias.MeterID = [tblReadings].MeterID))+[Reading],[Reading]-(SELECT [Reading] FROM [tblReadings] AS Alias WHERE StartDate = (SELECT Max(StartDate) FROM [tblReadings] AS Alias2 WHERE Alias2.StartDat e < [tblReadings].StartDate AND Alias2.MeterID = [tblReadings].MeterID) AND Alias.MeterID = [tblReadings].MeterID))*[tblGauges]![Factor]

Bear in mind that when the meter is back on 0 again this is already taken care of in the formula.

To deal with the time frame I put a filter in the query:
[Forms]![SelectPeriod]![Start Date]
This deals with the great amount of data I would get if not using a filter.

The usage calculation is no big deal, but integrating this into calculating the usages of the subgauges is. I'm not able to deduct the subusages of the usages?

I created 2 queries:
1 for calculating usages for all gauges and 1 for the total of the subgauges. What I can't manage is deduct these from one another? What am I doing wrong?

Rg.

Marco