UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Calculations, Any Version    
 
   
Brabee
post Jan 23 2020, 09:48 AM
Post#1



Posts: 4
Joined: 23-January 20



Hi Everybody,

I am new on this forum and would like to use this opportunity to greet you all. Compliments for the new year (2020)

My electricity usage database (attached) has the following information:

a} I want to calculate the units of electricity used/consumed for a period (between two dates or another period).
b) How can I create a query or a report to calculate the units of electricity consumed, where the readings on the prepaid electricity box are recorded as "Current Reading or Readings before new units"?
c) You will see in the table above that in some cases "current reading is "0". It means simply that there were no units left, thus zero. And, where there were no new units were purchased, the new units purchased are given as zero and the Cost as well.

NB: "current reading" indicates how many units are remaining on the prepaid box. My application is Access 365, (MS Access 2007-2016 file format)

Please help.

Thank you.


Attached File(s)
Attached File  Electricity_2020.zip ( 11.28K )Number of downloads: 6
 
Go to the top of the page
 
theDBguy
post Jan 23 2020, 11:33 AM
Post#2


UA Moderator
Posts: 77,506
Joined: 19-June 07
From: SunnySandyEggo


Hi. Welcome to UtterAccess! welcome2UA.gif

Got no time to download your file right now but happy to see you joined the forum. Cheers!

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
RJD
post Jan 23 2020, 01:48 PM
Post#3


UtterAccess VIP
Posts: 10,213
Joined: 25-October 10
From: Gulf South USA


welcome2UA.gif

Well, I am not really sure how you want the calculations. Getting the days since the last record is easy, but guidance is needed on the usage calculation. Perhaps if you gave us a formula and the correct responses for each record we could work into the proper query calculations. For example, is the calculation of usage ...

[PreviousCurrentReading]+[PreviousNewUnitsPurchased]-[CurrentReading]

... or will it involve the current NewUnitsPurchased as well? You will have to be more specific about the calculation you want to convert to SQL.

Take a look at my demo/first attempt attached. See if this is anywhere close to getting the pieces you need.

Oh, and it would be helpful if you could post a db with a table instead of the Word document. Saves us having to create that (already done in my demo this time).

HTH
Joe
Attached File(s)
Attached File  Electricity_2020.zip ( 27.67K )Number of downloads: 4
 

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
Brabee
post Jan 24 2020, 03:22 AM
Post#4



Posts: 4
Joined: 23-January 20



Hi RJD,
Thanks for your proper reply. I want to reply as follows on your request for clarity:

YOUR QUESTION/REMARK
1. I am not really sure how you want the calculations. Getting the days since the last record is easy, but guidance is needed on the usage calculation. Perhaps if you gave us a formula and the correct responses for each record we could work into the proper query calculations. For example, is the calculation of usage ...

[PreviousCurrentReading]+[PreviousNewUnitsPurchased]-[CurrentReading]

MY RESPONSE WITH EXPLANATIONS:
1. New units purchased are entered with the date, total units and amount; therefore, the calculation should be: current units+units purchased=total units on the electricity box
If current units are 0 (zero), the calculation should be current units+units purchased which will be; 0+purchased units. These calculations are correctly displayed in your query.
2. Day difference should be; date of electricity reading taken minus the previous date a reading was taken; which need to calculate units of electricity used between two dates. For eg, if I read the reading as 100 on 01/01/2020 and the next date of reading is 10/01/2020 whereas the reading is 0, it will mean that I used 100 units over nine (9) days. So units used between 01/01/2020 and 10/01/2020 = 100 and day difference will be 9 depending on how Access will calculate it (if hours are entered). For e.g. 01/01/2020 09:00 to 10/01/2020 09:00 will be = 9 days.
3. If the current reading is ten (10) on the meter, but 100 units were purchased, it means 100-10 =90, etc

NB: The aim of the database is to calculate electricity usage over a period of time, which will include: Total units per month quarter or year (additionally any period), and what the cost was. Other statistical data will be a bonus, such as; average, minimum and maximum usage, etc.

Moreover, I observed the following calculations in your query did not display well:
1. Previous dates are not displayed for each row; it appears only in some rows of the query.
2. Usage days are not calculated correctly.
3. Units used are not calculating at all in some rows, and where it has been calculated it was wrong, for eg. please check in the row of 19/01/20.

NB: If there are zeros under the fields "units purchased" and "amount", it means; only readings were taken and entered. No purchase was done.

Dear RJD, I thank you for your effort so far, because your table and query are in line with what I envisaged. However, there is one more thing I want to ask for; How can a previous reading be displayed automatically on the form when I enter a new reading, and will such automatically displayed previous reading be recorded in the table. I want to explain....if my last reading recorded was 100, but today's reading is 70, will the 100 be displayed as a previous reading on the form? I ask that to prevent that a previous reading is entered wrongly.

I attached the file for your information and convenience.

Kind regards,
This post has been edited by Brabee: Jan 24 2020, 03:23 AM
Attached File(s)
Attached File  Database_Brabee.zip ( 42.9K )Number of downloads: 5
 
Go to the top of the page
 
RJD
post Jan 24 2020, 01:30 PM
Post#5


UtterAccess VIP
Posts: 10,213
Joined: 25-October 10
From: Gulf South USA


Hi again: Sorry, but I am lost in your explanation in the last post...

QUOTE
Moreover, I observed the following calculations in your query did not display well:
1. Previous dates are not displayed for each row; it appears only in some rows of the query.

Previous dates are, indeed, displayed in my demo here for each row except for the first record, where there is no previous record.

QUOTE
2. Usage days are not calculated correctly.

Well, it looks like the usage days are correct in my demo here. This is calculated from the previous date to the current record date. When I run your posted db query, I get negative days elapsed. Turning the calculation around, I get the same values as my demo.

QUOTE
3. Units used are not calculating at all in some rows, and where it has been calculated it was wrong, for eg. please check in the row of 19/01/20.

Units are present for all records in my demo. If they are not correct, please post the correct units for us to see, as previously requested, so we can reverse-engineer the process. Again, as seen here, given US date formats.

I suspect we may have date format issues (you European and me US), so that will affect results in my demo, given the way I am using the dates. You will have to adjust for that if this is the case.

I'll not be here for a while, as I had a medical procedure this morning and am still suffering for the effects of the anesthesia. Perhaps someone else can take a look at this, preferably using European dates, and assist you, while I am not available.

HTH
Joe


--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
Brabee
post Jan 25 2020, 07:21 AM
Post#6



Posts: 4
Joined: 23-January 20



Hi,

Thank you for the latest update. I hope you will recover soon.

Yes, you are right. The issue was about the date format. I changed the computer system date format to US date format, and waolla!!, it worked perfectly. Now, I have another problem with changing the date format on the computer; first of all, I am not used to the US format. All my documents were created in the European format (some archived over years). What can I do to change the format of the database only? I consulted several people about this issue, however, I got no satisfactory reply. There was a proposal to create a virtual machine and place this particular database in that file. I will try it out.
Thank you once again for your valuable support.
Go to the top of the page
 
Gustav
post Jan 25 2020, 12:08 PM
Post#7


UtterAccess VIP
Posts: 2,175
Joined: 21-February 07
From: Copenhagen


Modify your query to pass correctly formatted string expressions for the date values to DMin:

CODE
SELECT
    [Electricity Purchased and Used].ID,
    [Electricity Purchased and Used].[Date and Time],
    [Electricity Purchased and Used].[Current Reading],
    [Electricity Purchased and Used].[New Units Purchased],
    [Electricity Purchased and Used].[Cost New Units],
    [Electricity Purchased and Used].[current reading]+[New Units Purchased] AS [Updated Reading],
    DateDiff("d", DMin("[Date and Time]", "[Electricity Purchased and Used]", "[Date and Time] >#" & Format([Date and Time], "yyyy\/mm\/dd") & "#"), [Date and Time]) AS DaysSinceLast
FROM
    [Electricity Purchased and Used];

Then, do set the date format in Windows back to what your used to have; it will never be a solution to be forced to have it set for the "reversed" US format.
Go to the top of the page
 
Brabee
post Jan 26 2020, 04:41 AM
Post#8



Posts: 4
Joined: 23-January 20



Thanks Gustav,

I will try it out and provide feedback.

Kindest regards,
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    21st February 2020 - 11:28 AM