P: 13

Hi everyone. I'm creating a database that holds the data for a residential complex. Each month the latest electricity and water readings are taken. I need to calculate the variance and the variance percentage between the last reading amount and the previous reading amount per residential unit. I am not familiar with VBA or anything so need something fairly simple if possible?
 
The recommended Query in Post #7 is all SQLnot VBA.
I am a bit confused on the structure of your tables:
I have created 4 tables: 1) Reading_Dates which has DATEID (primary field), READING_DATE (Actual date) and the Bulk meter amount for the month. 2) "Readings" which contains DATEID, UNITNO, ELECTRICITY, STAFFELEC, WATER as the fields.
So you have a table that simply identifies the reading dates? What is the "Bulk meter amount"? What do the fields ELECTRICITY, STAFFELEC and WATER represent? Meter readings? Usage?
Perhaps a more straightforward table structure would be as described above:  tblReadings

RdngID Autonumber, PK

RdngDate Date

UNITNO FK to Tenant/Owner table

ElecRdng Actual meter reading

StfElecRdng Actual meter reading

WtrRdng Actual meter reading
Also, as mentioned above, you can't get a "variance" until you have at least three months of data. The first usage is simply the past month's usage. The second usage is the month prior's usage; and the variance is the difference between those two usages.
However, using your current structure, and based upon your data (with an added month of readings to make this work), and an added calculation for Variance vs. Usage, here is what you should have (no VBA, just SQL):  SELECT Readings.UNITNO,

Max(DateID) AS M3,

Max(ELECTRICITY) AS R3,

T2.M2,

T2.R2,

T2.M1,

T2.R1,

[R2][R3] AS U2,

[R1][R2] AS U1,

[U1]/[U2] AS Var

FROM (SELECT Readings.UNITNO,

T1.M1,

T1.R1,

Max(DateID) AS M2,

Max(ELECTRICITY) AS R2

FROM (SELECT UNITNO,

Max(DateID) AS M1,

Max(ELECTRICITY) AS R1

FROM Readings

GROUP BY UNITNO) AS T1

INNER JOIN Readings

ON T1.UNITNO = Readings.UNITNO

WHERE ELECTRICITY<[T1].[R1]

GROUP BY Readings.UNITNO, T1.M1, T1.R1) AS T2

INNER JOIN Readings

ON T2.UNITNO = Readings.UNITNO

WHERE ELECTRICITY<[T2].[R2]

GROUP BY Readings.UNITNO, T2.M2, T2.R2, T2.M1, T2.R1;
As you can see, all you need to do is convert the variance to a percentage to get your rsults:
Share this Question
Expert Mod 15k+
P: 31,489

I can try to explain in general terms but anything more specific, and probably helpful, would rely on information that should have been included as part of the question. In this case the layout of your (presumably) single table.
Generally speaking then, you would need a SELECT query that returns you values for the maximum date across that group, where the group would probably be the property, as well as the maximum date across that group that is less than the other maximum date. Thus you'd have the two dates you need to find the values you need to calculate your variance percentage. ShazzieH:
I am not familiar with VBA or anything so need something fairly simple if possible?
Unfortunately you've asked a question that requires complicated SQL. We can get further into that once you've shared the metadata of your table so we know how the data all fits together.
Unlike Excel, Access is a proper database tool so doesn't really work well with ordinals (1st, 2nd etc). It can order using an ORDER BY clause but the records don't come out with any sort of indication as to where abouts they come in the overall order. Thus we have to find awkward ways around that. Max() is fine to find the last in a sequence, but finding the secondtolast means finding the last then going through and finding the last of the set that excludes the previous last. Painful I know, but probably the least painful approach for a question that's so unnatural for databases  which are based on Set Theory of course.
 
P: 13

I have created 4 tables: 1) Reading_Dates which has DATEID (primary field), READING_DATE (Actual date) and the Bulk meter amount for the month. 2) "Readings" which contains DATEID, UNITNO, ELECTRICITY, STAFFELEC, WATER as the fields. The other 2 tables just contain Tenant and Owner information. I created a query which lists the UNITNO, READING_DATE and ELECTRICITY. I have in the past managed to edit Select Queries and (sort of) created my own but I've had to have help doing them. Does this answer help you to help me?? Thank you in advance.
  Expert 5K+
P: 8,634
 @NeoPa:
First and foremost, I hope you are well. Couldn't this be a relatively simply matter, GROUP BY [Property#] with 2 Calculated Fields returning [Variance] and [Variance%]? All Logic would be contained within the Public Functions that are called by these Fields and return the respective Values for each.
 
P: 13

Thank you, I am well, I hope you too. Your answer sounds good but, HOW do I do it? Could you give me an example perhaps? I'm sure I could work it out from there? Thank you again :)
  Expert 5K+
P: 8,634

I was actually directing this Reply to NeoPa (@NeoPa:). He is the true expert here and I truly value his opinion. Let's see what he has to say on this matter before we proceed.
  Expert Mod 2.5K+
P: 3,282

ShazzieH,
One question I have concerning this table has to do with the values themselves. Do you record the actual meter readings or the difference in the meter reading from the past month or both. Of course the Db designer in me says the "correct" way is to recod just the meter reading itself, and, since all other values are calculated, there is no need to save those in the table.
However, this presents a slightly more complexified problem, because each time you want to calculate the variance in usage, you actually need the last three readings. Example:
For April'susage, you need the meter reading from April and the meter reading from March. The different in these two readings give you the usage for that month. But, to get the variance from the previous month, you need March's reading (you already have that) and February's reading, so you can get the previous months usage. Then, comparing those two usage values will give you the variance you are looking for.
However, I wanted to give this a try. This is very complicated! Assuming one table named tblMeterReadings, with the Fields CustID, MeterDate and MeterReading, this should give you the info you are looking for:  SELECT tblMeterReadings.CustID,

T2.M1,

T2.R1,

T2.M2,

T2.R2,

Max(tblMeterReadings.Meterdate) AS M3,

Max(tblMeterReadings.MeterReading) AS R3,

[R1][R2] AS V1,

[R2][R3] AS V2

FROM (SELECT tblMeterReadings.CustID,

T1.M1,

T1.R1,

Max(tblMeterReadings.Meterdate) AS M2,

Max(tblMeterReadings.MeterReading) AS R2

FROM (SELECT CustID,

Max(Meterdate) AS M1,

Max(MeterReading) AS R1

FROM tblMeterReadings

GROUP BY CustID) AS T1

INNER JOIN tblMeterReadings

ON T1.CustID = tblMeterReadings.CustID

WHERE (((tblMeterReadings.MeterReading)<[T1].[R1]))

GROUP BY tblMeterReadings.CustID, T1.M1, T1.R1) AS T2

INNER JOIN tblMeterReadings

ON T2.CustID = tblMeterReadings.CustID

WHERE (((tblMeterReadings.MeterReading)<[T2].[R2]))

GROUP BY tblMeterReadings.CustID,

T2.M1,

T2.R1,

T2.M2,

T2.R2;
I've tried it with these values and it works:  CustID Meterdate MeterReading

1 1/1/2019 1001

1 2/1/2019 2001

1 3/1/2019 3005

1 4/1/2019 3024

2 1/1/2019 5000

2 2/1/2019 5255

2 3/1/2019 5500

2 4/1/2019 5757
Results:  CustID M1 R1 M2 R2 M3 R3 V1 V2

1 4/1/2019 3024 3/1/2019 3005 2/1/2019 2001 19 1004

2 4/1/2019 5757 3/1/2019 5500 2/1/2019 5255 257 245
Hope this hepps!
  Expert Mod 10K+
P: 12,365

Depending on how clean and how consistent the readings are, you might be able to join on year * 12 + month  1 = year * 12 + month   Expert 5K+
P: 8,634

I think the key points here is:
I am not familiar with VBA or anything so need something fairly simple if possible?
I need to calculate the variance and the variance percentage between the last reading amount and the previous reading amount per residential unit.
  Expert Mod 15k+
P: 31,489

Hi ADezii.
I'm well thanks, and I hope you are too. Always good to hear from you.
Certainly, if you're relying on Public Functions to do the work for you you're only really hiding the problem. As far as I can see you'd need to use Recordset code (Or Domain functions) within each call and that would give the same logic but less consistently (IE. Shared between VBA & SQL) as well as being less performant. You'd also be relying on the order that the functions were called by ACE or Jet for the code to work as expected. That wouldn't be an approach I'd recommend.
I love Rabbit's suggestion. If that can be confirmed as reliable (We only know so far that it's a previous entry but this assumes an entry per calendar month and logged that way.) then we can save ourselves some trouble.
I'm concerned we're dealing with four tables. The concept is complicated enough without the extra complication of flattening the data first.
Twinny has a good point. How the data's stored is certainly critical. I believe (I don't have so much free time I can check everything in too much detail.) that his SQL represents the logic I was proposing originally. ADezii:
I think the key points here is:
Very true, but practically speaking, with a question such as this, they're on a hiding to nothing. This just isn't simple. If you know a way to provide this in a way that's simple then I bow down to your genius. I'm afraid Shazzie will just have to work with what we can do and realise that database work is very different from most other logic and even those things that can seem relatively straightforward for something like spreadsheet work, can prove much more complex when done in a database. Especially if VBA is their biggest fear.
@Shazzie.
ADezii is one of our experts (Don't pay too much attention to his claims of ordinariness. He's been here helping longer even than I have.) who typically feels a lot stronger in VBA than in SQL, so feels naturally handicapped with a question where the OP (You.) is put off by the very thought of it.
 
P: 13

Ooops.:) Sorry. Thank you for referring this to ADezil & Twinnyfo. @ADezil & @Twinnyfo. Yes, it is VERY complicated. I thought I could just create a simple database for this because they currently use Excel and it is SUCH a huge, complicated spreadsheet and EACH formula needs editing every month. Each reading has it's own column and ALL the readings (Electricity, Staff Electricity and Water) are all on one worksheet. The person who actually records the readings struggles terribly with the Excel version. But, I then discovered just HOW complicated this gets in Access too..........
I have used 4 tables. The one is just to record the Unit Owner details: UnitID, UnitNo, Surname, Name, Tel. The second is called Tenants and that is just to record the Tenants details just like the Owners table. The 2 main tables are: Reading_Dates: This is to record the bulk electrical meter reading leading to the complex on a monthly basis. It contains: DateID, Reading_Date, EskomMeter. The main table "Readings" contains the ACTUAL readings per meter per Unit in the complex. The fields in this one are: DateID, UnitNo, Electricity, StaffElectricity, Water.
I have currently brought 2 months data (from Excel) into this table. I'm only going to list the Electricity amounts here because I need to work out the difference between the current month and the previous months usage and then calculate the %Variance. So each one (electricity, staff electricity and water) can be calculated separately. I will then need to balance this to the EskomMeter bulk reading on the Reading_Dates table.
DateID UnitNo Electricity StaffElec Water
1 Unit 01D 139472
2 Unit 01D 139919
1 Unit 02D 176816
2 Unit 02D 176817 (they're away)
1 Unit 03D 53375
2 Unit 03D 53390
1 Unit 04D 143400
2 Unit 04D 143429
The Reading_Dates table:
DateID Reading_Date EskomMeter
1 30/07/2017 225173
2 30/08/2017 249207
I was thinking of creating a data link from Excel to this data and bringing in the current month and previous months data and then having a macro or pivot table or similar to do this calculation. What do you think? Does my explanation above help? I appreciate all the help more than you know. Thank you again.
 
P: 13

The meter readings are the ACTUAL readings from each meter.
 
P: 13

I'm going to give your code a try and see if I can get it to work with my limited VBA / SQL knowledge :)
 
P: 13

this is GREAT! It worked, THANK YOU, THANK YOU :) . I removed the DateID field and am just using the Reading_Date field which made it much easier.
However, I have 2 YEARS of monthly readings and I just need to create a query that gives the last 2 months readings. Will this work like that or do I need to put in a "select date" type of prompt?
 
P: 13

@Rabbit. Thank you for your contribution. Will that give me an annual reading?
  Expert Mod 2.5K+
P: 3,282

The recommended Query in Post #7 is all SQLnot VBA.
I am a bit confused on the structure of your tables:
I have created 4 tables: 1) Reading_Dates which has DATEID (primary field), READING_DATE (Actual date) and the Bulk meter amount for the month. 2) "Readings" which contains DATEID, UNITNO, ELECTRICITY, STAFFELEC, WATER as the fields.
So you have a table that simply identifies the reading dates? What is the "Bulk meter amount"? What do the fields ELECTRICITY, STAFFELEC and WATER represent? Meter readings? Usage?
Perhaps a more straightforward table structure would be as described above:  tblReadings

RdngID Autonumber, PK

RdngDate Date

UNITNO FK to Tenant/Owner table

ElecRdng Actual meter reading

StfElecRdng Actual meter reading

WtrRdng Actual meter reading
Also, as mentioned above, you can't get a "variance" until you have at least three months of data. The first usage is simply the past month's usage. The second usage is the month prior's usage; and the variance is the difference between those two usages.
However, using your current structure, and based upon your data (with an added month of readings to make this work), and an added calculation for Variance vs. Usage, here is what you should have (no VBA, just SQL):  SELECT Readings.UNITNO,

Max(DateID) AS M3,

Max(ELECTRICITY) AS R3,

T2.M2,

T2.R2,

T2.M1,

T2.R1,

[R2][R3] AS U2,

[R1][R2] AS U1,

[U1]/[U2] AS Var

FROM (SELECT Readings.UNITNO,

T1.M1,

T1.R1,

Max(DateID) AS M2,

Max(ELECTRICITY) AS R2

FROM (SELECT UNITNO,

Max(DateID) AS M1,

Max(ELECTRICITY) AS R1

FROM Readings

GROUP BY UNITNO) AS T1

INNER JOIN Readings

ON T1.UNITNO = Readings.UNITNO

WHERE ELECTRICITY<[T1].[R1]

GROUP BY Readings.UNITNO, T1.M1, T1.R1) AS T2

INNER JOIN Readings

ON T2.UNITNO = Readings.UNITNO

WHERE ELECTRICITY<[T2].[R2]

GROUP BY Readings.UNITNO, T2.M2, T2.R2, T2.M1, T2.R1;
As you can see, all you need to do is convert the variance to a percentage to get your rsults:   Expert Mod 2.5K+
P: 3,282

Shazzie,
Regarding Post #14, if you have two years of data, this query will always give you just the last two months' worth of usage.
I don't know if you want a running tally, but if you do, that would be grounds for a new thread.
  Expert 5K+
P: 8,634
  Glad you are doing well, NeoPa. I'll simply Post the results that I came up with solely for the purpose of conversation and should any further interest arise, I'll provide more detail. I generated the Variance and Variance Percentage between the Last and Prior Meter Readings for both Electric and Water for each unique Unit Number. The Data exists in the most simplest of Tables (not normalized) and consists of only four Fields.
 Sample Data:

DATE_READ UNITNO EWVALUE TYPE

5 /17/2019 Unit D 5000 E

4 /30/2019 Unit C 5000 W

4 /10/2019 Unit C 4499 W

4 /10/2019 Unit D 6689 W

4 /9 /2019 Unit B 8863 E

3 /27/2019 Unit A 7496 E

3 /20/2019 Unit D 5347 E

3 /15/2019 Unit D 6689 W

3 /12/2019 Unit B 9102 E

2 /23/2019 Unit D 4750 E

2 /22/2019 Unit A 3270 W

2 /22/2019 Unit A 3459 W

2 /14/2019 Unit A 7100 E

1 /3 /2019 Unit A 7695 E

1 /2 /2019 Unit D 5500 E

 OUTPUT after Query execution:

UNITNO EVar_EVar% WVar_WVar%

Unit A 396  5.28% 189  5.78%

Unit B 239  2.70% N/A  N/A

Unit C N/A  N/A 501  10.02%

Unit D 347  6.94% 0  0.00%

 The actual Readings, as I see it, are not relevant in this case, what is are the Variance and Variance Percentage between the last two successive Readings.
 Two Public Functions generate the results from two Calculated Fields. Both Variance and Variance Percentage for Electricity and Water exist in the same Field. This is not something that I normally do, but it do feel that it may be warranted in this case.
 
P: 13

@ Twinnyfo and ADezil. Thank you very much. This is exactly what I need. I really appreciate your help, very much! :) Hope you have an absolutely brilliant weekend!
  Expert Mod 2.5K+
P: 3,282

Glad I could hepp! Let us know if you need anything else.
 
P: 13

@Twinnyfo. Thank you for your kind offer and I might just take you up on that as the development goes further :) Take care.
  Expert 5K+
P: 8,634
 @twinnyfo:
Congrats on a really professional and efficient solution!
 
P: 13

Wish this thread had a Like or, even better, a "Love this" button. I would like to have clicked and clicked on that.
  Expert Mod 2.5K+
P: 3,282
 @ADezii: I learned everything I know about subqueries from Bytes. I just recycle the things I've learned here to hepp out other folks.
ButI was actually quite surprised that I got that little bugger to work. I impressed myself, actually!
 
P: 13

A wonderful feeling when things work! :)
  Expert Mod 15k+
P: 31,489
 TwinnyFo:
I learned everything I know about subqueries from Bytes. I just recycle the things I've learned here to hepp out other folks.
If you didn't learn it directly from Rabbit then you sure as hell^H^Hck learned it from someone else  who learned it from Rabbit.
Nice work both of you (Twinny & ADezii), and of course everyone else who contributed.
@Shazzie.
We're all very glad that this has helped you. It's actually a great example of what can be done in SQL using subqueries  even for something as logically complex as this.
 
P: 13

Thank you NeoPa. It's wonderful to have such a great community to go to for help. I so appreciate it.
  Expert Mod 15k+
P: 31,489
   Expert Mod 2.5K+
P: 3,282

@NeoPa: Yes, I believe Rabbit is the main source for my understanding of subqueries.
  Expert Mod 15k+
P: 31,489

That's true for me too Twinny.
  Expert Mod 10K+
P: 12,365

Aww shucks, you're making me blush
  Expert Mod 2.5K+
P: 3,282

I've never seen a pink golf ball before....
  Expert Mod 10K+
P: 12,365
   Expert Mod 2.5K+
P: 3,282

I guess I had it wrong for all these years! My sincerest apologies.
Nice to know something new!
  Expert Mod 15k+
P: 31,489

Whaaaah?
It's an orange golf ball surely.
    Question stats  viewed: 1386
 replies: 34
 date asked: Apr 10 '19
