By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,709 Members | 2,211 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,709 IT Pros & Developers. It's quick & easy.

Calculate the variance between two values in the same field

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?
Apr 10 '19 #1

✓ answered by twinnyfo

The recommended Query in Post #7 is all SQL--not 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:
Expand|Select|Wrap|Line Numbers
  1. tblReadings
  2. RdngID       Autonumber, PK
  3. RdngDate     Date
  4. UNITNO       FK to Tenant/Owner table
  5. ElecRdng     Actual meter reading
  6. StfElecRdng  Actual meter reading
  7. 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):

Expand|Select|Wrap|Line Numbers
  1. SELECT Readings.UNITNO, 
  2.        Max(DateID) AS M3, 
  3.        Max(ELECTRICITY) AS R3, 
  4.        T2.M2, 
  5.        T2.R2, 
  6.        T2.M1, 
  7.        T2.R1, 
  8.        [R2]-[R3] AS U2, 
  9.        [R1]-[R2] AS U1, 
  10.        [U1]/[U2] AS Var 
  11. FROM (SELECT Readings.UNITNO, 
  12.              T1.M1, 
  13.              T1.R1, 
  14.              Max(DateID) AS M2, 
  15.              Max(ELECTRICITY) AS R2 
  16.       FROM (SELECT UNITNO, 
  17.                    Max(DateID) AS M1, 
  18.                    Max(ELECTRICITY) AS R1 
  19.             FROM Readings 
  20.             GROUP BY UNITNO) AS T1 
  21.       INNER JOIN Readings 
  22.       ON T1.UNITNO = Readings.UNITNO 
  23.       WHERE ELECTRICITY<[T1].[R1] 
  24.       GROUP BY Readings.UNITNO, T1.M1, T1.R1) AS T2 
  25. INNER JOIN Readings 
  26. ON T2.UNITNO = Readings.UNITNO 
  27. WHERE ELECTRICITY<[T2].[R2] 
  28. 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
Share on Google+
34 Replies


NeoPa
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 meta-data 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 second-to-last 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.
Apr 10 '19 #2

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.
Apr 11 '19 #3

ADezii
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.
Apr 11 '19 #4

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 :)
Apr 11 '19 #5

ADezii
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.
Apr 11 '19 #6

twinnyfo
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:

Expand|Select|Wrap|Line Numbers
  1. SELECT tblMeterReadings.CustID, 
  2.        T2.M1, 
  3.        T2.R1, 
  4.        T2.M2, 
  5.        T2.R2, 
  6.        Max(tblMeterReadings.Meterdate) AS M3, 
  7.        Max(tblMeterReadings.MeterReading) AS R3, 
  8.        [R1]-[R2] AS V1, 
  9.        [R2]-[R3] AS V2
  10. FROM (SELECT tblMeterReadings.CustID, 
  11.              T1.M1, 
  12.              T1.R1, 
  13.              Max(tblMeterReadings.Meterdate) AS M2, 
  14.              Max(tblMeterReadings.MeterReading) AS R2 
  15.       FROM (SELECT CustID, 
  16.                    Max(Meterdate) AS M1, 
  17.                    Max(MeterReading) AS R1 
  18.             FROM tblMeterReadings 
  19.             GROUP BY CustID)  AS T1 
  20.       INNER JOIN tblMeterReadings 
  21.       ON T1.CustID = tblMeterReadings.CustID 
  22.       WHERE (((tblMeterReadings.MeterReading)<[T1].[R1])) 
  23.       GROUP BY tblMeterReadings.CustID, T1.M1, T1.R1)  AS T2 
  24. INNER JOIN tblMeterReadings 
  25. ON T2.CustID = tblMeterReadings.CustID
  26. WHERE (((tblMeterReadings.MeterReading)<[T2].[R2]))
  27. GROUP BY tblMeterReadings.CustID, 
  28.          T2.M1, 
  29.          T2.R1, 
  30.          T2.M2, 
  31.          T2.R2;
I've tried it with these values and it works:
Expand|Select|Wrap|Line Numbers
  1. CustID  Meterdate  MeterReading
  2. 1       1/1/2019       1001
  3. 1       2/1/2019       2001
  4. 1       3/1/2019       3005
  5. 1       4/1/2019       3024
  6. 2       1/1/2019       5000
  7. 2       2/1/2019       5255
  8. 2       3/1/2019       5500
  9. 2       4/1/2019       5757
Results:
Expand|Select|Wrap|Line Numbers
  1. CustID M1       R1   M2       R2   M3       R3   V1  V2
  2. 1      4/1/2019 3024 3/1/2019 3005 2/1/2019 2001 19  1004
  3. 2      4/1/2019 5757 3/1/2019 5500 2/1/2019 5255 257 245

Hope this hepps!
Apr 11 '19 #7

Rabbit
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
Apr 11 '19 #8

ADezii
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.
Apr 11 '19 #9

NeoPa
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.
Apr 12 '19 #10

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.
Apr 12 '19 #11

P: 13
The meter readings are the ACTUAL readings from each meter.
Apr 12 '19 #12

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 :)
Apr 12 '19 #13

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?
Apr 12 '19 #14

P: 13
@Rabbit. Thank you for your contribution. Will that give me an annual reading?
Apr 12 '19 #15

twinnyfo
Expert Mod 2.5K+
P: 3,282
The recommended Query in Post #7 is all SQL--not 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:
Expand|Select|Wrap|Line Numbers
  1. tblReadings
  2. RdngID       Autonumber, PK
  3. RdngDate     Date
  4. UNITNO       FK to Tenant/Owner table
  5. ElecRdng     Actual meter reading
  6. StfElecRdng  Actual meter reading
  7. 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):

Expand|Select|Wrap|Line Numbers
  1. SELECT Readings.UNITNO, 
  2.        Max(DateID) AS M3, 
  3.        Max(ELECTRICITY) AS R3, 
  4.        T2.M2, 
  5.        T2.R2, 
  6.        T2.M1, 
  7.        T2.R1, 
  8.        [R2]-[R3] AS U2, 
  9.        [R1]-[R2] AS U1, 
  10.        [U1]/[U2] AS Var 
  11. FROM (SELECT Readings.UNITNO, 
  12.              T1.M1, 
  13.              T1.R1, 
  14.              Max(DateID) AS M2, 
  15.              Max(ELECTRICITY) AS R2 
  16.       FROM (SELECT UNITNO, 
  17.                    Max(DateID) AS M1, 
  18.                    Max(ELECTRICITY) AS R1 
  19.             FROM Readings 
  20.             GROUP BY UNITNO) AS T1 
  21.       INNER JOIN Readings 
  22.       ON T1.UNITNO = Readings.UNITNO 
  23.       WHERE ELECTRICITY<[T1].[R1] 
  24.       GROUP BY Readings.UNITNO, T1.M1, T1.R1) AS T2 
  25. INNER JOIN Readings 
  26. ON T2.UNITNO = Readings.UNITNO 
  27. WHERE ELECTRICITY<[T2].[R2] 
  28. 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:

Attached Images
File Type: png qryVariances.png (10.5 KB, 251 views)
Apr 12 '19 #16

twinnyfo
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.
Apr 12 '19 #17

ADezii
Expert 5K+
P: 8,634
  1. 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.
  2. Sample Data:
    Expand|Select|Wrap|Line Numbers
    1. DATE_READ    UNITNO    EWVALUE    TYPE
    2. 5 /17/2019    Unit D    5000       E
    3. 4 /30/2019    Unit C    5000       W
    4. 4 /10/2019    Unit C    4499       W
    5. 4 /10/2019    Unit D    6689       W
    6. 4 /9 /2019    Unit B    8863       E
    7. 3 /27/2019    Unit A    7496       E
    8. 3 /20/2019    Unit D    5347       E
    9. 3 /15/2019    Unit D    6689       W
    10. 3 /12/2019    Unit B    9102       E
    11. 2 /23/2019    Unit D    4750       E
    12. 2 /22/2019    Unit A    3270       W
    13. 2 /22/2019    Unit A    3459       W
    14. 2 /14/2019    Unit A    7100       E
    15. 1 /3 /2019    Unit A    7695       E
    16. 1 /2 /2019    Unit D    5500       E
    17.  
  3. OUTPUT after Query execution:
    Expand|Select|Wrap|Line Numbers
    1. UNITNO    EVar_EVar%        WVar_WVar%
    2. Unit A    396 | 5.28%       -189 | -5.78%
    3. Unit B    -239 | -2.70%      N/A | N/A
    4. Unit C    N/A | N/A          501 | 10.02%
    5. Unit D    -347 | -6.94%        0 | 0.00%
    6.  
  4. 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.
  5. 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.
Apr 12 '19 #18

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!
Apr 12 '19 #19

twinnyfo
Expert Mod 2.5K+
P: 3,282
Glad I could hepp! Let us know if you need anything else.
Apr 12 '19 #20

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.
Apr 12 '19 #21

ADezii
Expert 5K+
P: 8,634
@twinnyfo:
Congrats on a really professional and efficient solution!
Apr 12 '19 #22

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.
Apr 12 '19 #23

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

But---I was actually quite surprised that I got that little bugger to work. I impressed myself, actually!
Apr 12 '19 #24

P: 13
A wonderful feeling when things work! :)
Apr 12 '19 #25

NeoPa
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.
Apr 13 '19 #26

P: 13
Thank you NeoPa. It's wonderful to have such a great community to go to for help. I so appreciate it.
Apr 13 '19 #27

NeoPa
Expert Mod 15k+
P: 31,489
: - )
Apr 13 '19 #28

twinnyfo
Expert Mod 2.5K+
P: 3,282
@NeoPa: Yes, I believe Rabbit is the main source for my understanding of subqueries.
Apr 13 '19 #29

NeoPa
Expert Mod 15k+
P: 31,489
That's true for me too Twinny.
Apr 14 '19 #30

Rabbit
Expert Mod 10K+
P: 12,365
Aww shucks, you're making me blush
Apr 14 '19 #31

twinnyfo
Expert Mod 2.5K+
P: 3,282
I've never seen a pink golf ball before....
Apr 15 '19 #32

Rabbit
Expert Mod 10K+
P: 12,365
It's a Go stone
Apr 15 '19 #33

twinnyfo
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!
Apr 15 '19 #34

NeoPa
Expert Mod 15k+
P: 31,489
Whaaaah?
It's an orange golf ball surely.
Apr 17 '19 #35

Post your reply

Sign in to post your reply or Sign up for a free account.