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 tblKostenAfrekeningsSysteem (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. :)

"Application 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.StartDate < [tblReadings].StartDate AND Alias2.MeterID = [tblReadings].MeterID) AND Alias.MeterID = [tblReadings].MeterID)<0,(1000000-(SELECT [Reading] FROM [tblReadings] AS Alias WHERE StartDate = (SELECT Max(StartDate) FROM [tblReadings] AS Alias2 WHERE Alias2.StartDate < [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.StartDate < [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

[FishVal]

Hmm. Assuming:

• Both queries perform as expected.
• The 1st query returns "brutto" usage of all gauges (1 record per gauge).
• The 2nd return subgauges totals (1 record per main guage).

You could join both'em with outer join:
[code]
SELECT [1st].MeterID, [1st].Usage - [2nd].SubGaugesTotal AS MainMeterNettoUsage FROM [1st] LEFT JOIN [2nd] ON [1st].MeterID=[2nd].MainMeterOD;
[/cope]

P.S. The formula really impresses. :D Couldn't cope with it because there were !@#%!@$% many characters where.

[Crombam]

Hi Fish,

Just tried your code after re-writing it to my queries. It doesn't work like it should and I cannot figure out why?
The first query (qryElecUsage) contains the following fields:
- tblReadings.StartDate --> Filter on date
- [Usage] (it's the code like in post #15)
- tblGauges.MeterID
- tblGauges.MainMeterID

The second query (qryElecSubGauges) contains the following fields:
- qryElecUsage.Usage (called SubGaugesTotal) --> Sum
- qryElecUsage.MainMeterID --> Group By

Both queries are connected by an outer join from qryElecUsage.MeterID to qryElecSubUsage.MainMeterID.

When running your code it keeps asking me for a parameter value for SubGaugesTotal, now when giving the subtotal for gaige 2 from qryElecSubGauges it deducts this value from all of the gauges?
We are near the end now, it almost works..... ;-))

Rg.

Marco

[FishVal]

Hmm. Weird. Usually it happens when field name is misspelled and thus could not be found in source tables/queries.

Could you post SQL of the query?
And ... just for fun, try to fetch all fields from the query changing it to the following:

SELECT qryElecUsage.*, qryElecSubGauges.* FROM qryElecUsage LEFT JOIN qryElecSubGauges ON qryElecUsage.MeterID=qryElecSubGauges.MainMeterID;
 

Does it still ask for parameter?
If no, then what name it gives for the field expected to be [SubGaugesTotal]?

[Crombam]

Ok, here the code:

SELECT qryElecUsage.MeterID, [qryElecUsage]![Usage]-[qryElecSubGauges]![SubGaugesTotal] AS MainMeterNettoUsage
FROM qryElecUsage LEFT JOIN qryElecSubUsage ON qryElecUsage.MeterID = qryElecSubUsage.MainMeterID;

I tried your new code, when running it no parameter value is asked and the values displayed are the correct calculated values in field SubGaugesTotal.

Marco

[FishVal]

Sure, it asks for parameter.
Look at your code - in calculated field you use
[qryElecSubGauges]![SubGaugesTotal]
as field name, while the query name is qryElecSubUsage.

[Crombam]

After a while you get a slight tunnel vision. Sorry about that...;-))
Works great now, I just have to figure out how I get the rest of the usages in the query. The last overall query calculated the correct netto usages, but I can't see the usages for the rest of the meters, that is the meters where no submeters are installed.

Thanks sofar for all your help, I would like to add you as a friend if that's ok with you?

Rg.

Marco

[FishVal]

@Crombam
Looking at the query I guess you nevertheless get records for all gauges regardless whether they have subgauges or not (that is LEFT JOIN for) but Usage appears to be empty.

This happens because you subtract Null from "brutto" gauge usage. The only thing you have to do is to replace Null values with zeroes using Nz() function.

[qryElecUsage]![Usage]-Nz([qryElecSubUsage]![SubGaugesTotal]) AS MainMeterNettoUsage

Thanks sofar for all your help, I would like to add you as a friend if that's ok with you?

Rg.

Marco

:) Oh, that's ok with me.

[Crombam]

Hi Fish,

One of my endresults is being completed. After replaceing the zero values with function Nz() I created a new query called qryUsagePerCC. It contains only 2 fields CostCenter and the sum of NettoUsages * % key. So now I see the usages per Cost Center.
I was wondering if it is possible to put next to the UsagePerCC a field in which the percentage is being displayed in regard to the total amount of electricity used within a defined time frame? To make it a bit challenging I have to exclude 1 Cost Center. SO in total there are 5 Cost Center, 1 of them is being charged to a 3td party on Site. For our own Site the 4 remaining Cost Centers have to be added together and I want each of the Usages per CC being displayed in percentage of the total of those 4 CCs.
I suspect I could add one more key field to the Cost Center table with Site or 3td party identification. Then I could create another query for only Site related Usages and devide the UsagePerCC by the total Site related usage?

Marco

[Crombam]

Hi Fish,

I already created the report I was looking for. The percentages are done as well. In total I have 6 queries now. Apart from the Usage and the SubUsage, I created a query for calculating the total per Gauge (thanks for your help again). After this I had to creat one query for the TotalUsagePerCC, TotalUsageCompany and the calculation of the percentages. Works great now.

Now I'm looking to another part of the database, the input of the readings per Gauge. Because I have now just 1 table for all readings, how can I creat a form to input the readings for all gauges?
Explanation: one of the mechanics will write all readings on a form which is then used to input this data once a month into the tabel. I want to input all the data in a single form (will be done by mechanic) and I want a kind of a report in which the last reading is being displayed, so he knows right upfront if a reading is incorrect. Any suggestion?

Thanks.

Marco

[FishVal]

Hello, Marco.

Glad you have a good progress with your database. :)

@Crombam
I need some more information about the procedure.

• Is there expected multiple readings per gauge monthly or only one? I guess it is one, but you'd better acknowledge this.
• Do all readings have the same date?

.... and I want a kind of a report in which the last reading is being displayed, so he knows right upfront if a reading is incorrect. Any suggestion?

I've looked on [tblReadings] structure and got two questions.

• Do you have PK in the table?
• You have [StartDate] and [EndDate] fields. Does it mean that [StartDate] has to be of the same valure as [EndDate] of the previous reading of a particular gauge?

[Crombam]

Hi Fish,

The form went well, just want to hear a second opinion. I created an additional query in which the actual readings and the readings for the previous month are being calculated. I created a report (for printing) that after selecting the actual date of regeristing the readings shows me the readings for the month before. Next to it I created an unbound label to have the mechanic write down the new reading (that's why it is printable).

For the input form, here the answers to your questions:
1.

Is there expected multiple readings per gauge monthly or only one? I guess it is one, but you'd better acknowledge this.

There is only 1 reading per gauge per month.

2.

Do all readings have the same date?

Yes, all readings will be conducted at the same day.

To answer your questions about the report:
1.

Do you have PK in the table?

Not yet. I guess MeterID would suite? Or StartDate, because every date is unique for each Meter.

2.

You have [StartDate] and [EndDate] fields. Does it mean that [StartDate] has to be of the same valure as [EndDate] of the previous reading of a particular gauge?

No, the [EndDate] is inserted for future use only. At this moment in time there is no need for this field.

Marco

[FishVal]

@Crombam
Ok. I will make some trials on this and, hope soon, will return. Maybe next year. :)

To answer your questions about the report:
1.
Not yet. I guess MeterID would suite? Or StartDate, because every date is unique for each Meter.

If so, then [MeterID] and [StartDate] should be the parts of composite PK since their combination is expected to be unique or you should add an autonumber field to make a single-field PK.
You know, I wouldn't say which option is better.
On the one hand it is preferrable to avoid composite keys as they are troublesome in establishing table relations and requires synchronized form controls to polulate FK fields in other tables. However you table has no FK(s) in other table(s).
On the other hand autonumber is somewhat meaningless in context of the table thus serving for record identification only. However, for developer table having single-field PK makes many things simpler and enhances database performance. Who does care about other meaning? User could well not be aware of existance of the field to say nothing about "extra space for storage" which is more than negligible. :)

Personally I prefer single-field PK, but it is my own humble opinion.

Happy New Year.

[Crombam]

Thanks Fish,

Take your time, I won't be working this year anymore ;-))
Happy New Year to you too.

Rg.

Marco

[FishVal]

New Year gift - database sample.
The approach is similar to that described in
Access: Rendering slots for possible records in subform based on outer join query .
What is different is that the main form is unbound.
Any further explanation next year.

Wish you Happy New Year.

Best regards,
Fish.