Trouble with too much data???

[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 MainMeterNettoU sage 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.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:
- qryElecUsage.Us age (called SubGaugesTotal) --> Sum
- qryElecUsage.Ma inMeterID --> Group By

Both queries are connected by an outer join from qryElecUsage.Me terID 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 qryElecSubGauge s 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.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.

Marco

[FishVal]

Sure, it asks for parameter.
Look at your code - in calculated field you use
[qryElecSubGauge s]![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]

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.

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 MainMeterNettoU sage

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 , TotalUsageCompa ny 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. :)

....
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) ....

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]

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.
There is only 1 reading per gauge per month.

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

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.