Thanks Fish,
Take your time, I won't be working this year anymore ;-))
Happy New Year to you too.
Rg.
Marco
Crombam
Last Activity: Jan 23 '09, 01:31 PM
Joined: Oct 28 '08
Location: Kerkrade, The Netherlands
-
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)....Leave a comment:
-
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 , TotalUsageCompa ny and the calculation of the percentages. Works great now.
Now I'm looking to another part of the...Leave a comment:
-
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...Leave a comment:
-
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?
...Leave a comment:
-
Ok, here the code:
SELECT qryElecUsage.Me terID, [qryElecUsage]![Usage]-[qryElecSubGauge s]![SubGaugesTotal] AS MainMeterNettoU sage
FROM qryElecUsage LEFT JOIN qryElecSubUsage ON qryElecUsage.Me terID = 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.
MarcoLeave a comment:
-
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.Sta rtDate --> Filter on date
- [Usage] (it's the code like in post #15)
- tblGauges.Meter ID
- tblGauges.MainM eterID
The second query (qryElecSubGaug es) contains the following fields:...Leave a comment:
-
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...Leave a comment:
-
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...Leave a comment:
-
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.
MarcoLeave a comment:
-
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...Leave a comment:
-
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....Leave a comment:
-
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...Leave a comment:
-
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:...Leave a comment:
-
Trouble with too much data???
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.... -
Hi,
I can't stop thinking about this problem. I came up with the following.
Is it possible to create an If-Then Statement where I check the value of a field in the subform and if the value is 0 then it shoudl cancel the report?
Here the code:
Private Sub Report_Load()
If Me!ChtZiektever zuim_(incl%).Fo rm!Year.Value = "" Then Cancel = True
End Sub
...Leave a comment:
-
Closing a report with a subform, when subform is having no data?
Hi to all,
After searching the net for a couple of hours I just have to ask the question. How can I close a report with a subform (Pivot Chart) when this Pivot Chart is having no data?
Explenation:
I have a report, which displays a Pivot Chart (Subform). When clicking a command button for displaying this report a form pop-up wil appear, where one can select a dynamic date range. In this pop-up form I included...
No activity results to display
Leave a comment: