MS Access 2007: Subtracting values from two consecutive records

[WuJu]

Hello.

I am having a problem due to my inexperience with Access or database.

Here is my problem:

-I have a table set up to enter numbers on a hourly basis from each facility's power meter reading.
-I made a query to add up all the facility's meter reading for each hour of period yr 2000-yr 2009
-I need to subtract a previous hour's value from current hour value and report that value.
-I do not know how to subtract previous record from current record.

Please help me out.


I am using MS Access 2007.
So far, I used to write SQL in query for all the calculation.
If the query is not the best option for this problem, then please suggest me with some detail information.
I am pretty new to the database, ACCESS, and SQL.

The following table is a part of the output from my query, and I need to know how much more/less MWh is increased/decreased in every hours.
In order to do that, I need to subtract Total_MWh values from two consecutive records.

Date Hour Total_MWh
1/1/2000 1 182.789
1/1/2000 2 182.996
1/1/2000 3 183.006
1/1/2000 4 182.981
..... ... .....
12/31/2009 24 180.903

This table contains usage of Mega Watt hour(MWh) of every hour in every day for 10 years.

 

[just_some_guy]

WuJu,

What a coincidence, I came here to find out the same thing (well, not the exact same. That would be creepy, wouldn't it?). I need to figure out how to subtract one record from the previous record as well, and keep a running total. I'm doing some online research on it, and if I find anything that will help, I'll make sure I post it here.

 

[John Big Booty]

Perhaps this article will help.

 

[just_some_guy]

John Big Booty,

The second part of the article looks like it should work for what I need. Thanks a ton for posting it!

WuJu,

Good luck!

 

[WuJu]

thanks for your post. Hope to find the solution and share with me.

 

[WuJu]

Thanks for your help. The link that you addressed was good for sum up the total of previous records. I don't need to sum up, but need to subtract individual current record to the just one behind previous record. DSUM doesn't give me the subtraction of individual previous value. Thank you for your help though.

 

[John Big Booty]

The solution may be for a Sum. All you are doing is a negative Sum, so it should be pretty straight forward to achieve the result you are after with a little thought.

 

[WuJu]

Thanks for your advise, but when I need the difference from the current record to previous record, I only subtract recrod one behind current record. (Tot - current record) is all the value of sum of the all the previous records. I need to do (Current record - adjacent (just one behind) previous record).

Here is one idea I have to use Dlookup, and please advise me my idea is okay.
Dlookup can give me a column for record just one behind current record.
I can subtract this column from current record, and get the result I want it.

The problem is that I need to have ID field of number data type in order to use Dlookup.
I don't have ID field in my table, and I can't make ID field in my table with some reason.


But, if you can help me to make a query that generate a column for ascending order of the number then I can use Dlookup.

Do you know how to make a query that count each row (each record has one row) and display number in the field coumn ("ID" in following example)?

For example

date Hour MWh ID
1/1/2010 1 78 1
1/1/2010 2 67 2
1/1/2010 3 80 3

Your help has been very greaful.

regards,

WUJU

 

[John Big Booty]

Perhaps one of these articles on ranking will help.

 

[gemma-the-husky]

The best solution, if its feasible is to store the previous reading with each record. Then the data is available without complicated re-reading.

This only really becomes problematic if you are inserting/modifying data within the sequence, as then you have to modfiy the previous/next records - so you are back to square one. In your situation, this situation is unlikely, as the data is all timestamped.

 

[Husan]

Hi,

This may work, if your hour field is a numerica value and always increment by 1, then you can link the table to itself within a query. You would relate the date and the current hour with the previous hour, giving you the previous reading. for example if yr table is called tblReadings then try this:

SELECT
tblReadings.Date,
tblReadings.Hour,
tblReadings.Total_MWh,
tblReadings.Total_MWh AS [Current],
tblReadingsPrev.Total_MWh AS Previous,
[tblReadings]![Total_MWh]-[tblReadingsPrev]![Total_MWh] AS [Usage]
FROM
tblReadings INNER JOIN tblReadings AS tblReadingsPrev
ON tblReadings.Date = tblReadingsPrev.Date
WHERE
tblReadingsPrev.Hour=([tblReadings]![Hour]-1);

Husan

 

[Husan]

Hi,

An alternative method would be to add another field to your table which would be a numeric sequence of all the readings. ie the first reading would have a readingID of 1, the second 2 etc:

ReadingID Date Hour Total_MWh
1 1/1/2000 1 182.789
2 1/1/2000 2 182.996
3 1/1/2000 3 183.006
4 1/1/2000 4 182.981

You could then use dlookup to look up the value in the previous ReadingID ie:

SELECT
tblReadings.ReadingID,
tblReadings.Date,
tblReadings.Hour,
DLookUp("Total_MWh","tblReadings","ReadingID=" & [ReadingID]-1) AS Previous,
tblReadings.Total_MWh AS [Current],
tblReadings.Total_MWh, [Total_MWh]-DLookUp("Total_MWh","tblReadings","ReadingID=" & [ReadingID]-1) AS [Usage]
FROM tblReadings
ORDER BY tblReadings.ReadingID;


Husan

 

[WuJu]

Thanks John, Gemma, and Husan.

I will try all of your suggestions.
John, I will try your ranking method to generate ID and use Dlookup to see if it works for me. Thanks.

Gemma, could you advise me how to store previous reading with each record?
Do you have same idea as Husan's first suggestion? I would like to approach this without making another table for previous data. Yes. Ican copy and paste with one shift to make previous data, but I have a lots of inquiries for this problems and I want to generate output without me being manually copy and pasted for each task.

Husan, thanks for your 2 suggestions. Your first suggestion should work, but I would like to approach this without making another table for previous data. If you you know how to store previous reading with each record without me being copy and paste to generate another table, please let me know.

For your 2nd suggestion, I have millisons of data record from output of a query. So I I can't add field for a numeric sequence manually. That's why I post if someone can help me numbering record using query, so I can use Dlookup. John reply me with ranking/numbering record, so I will try that. If you have any other idea, please post here, so I can try it. Thank you for your kindly help with even SQL code ;-)

I will try all of your suggestion on this weekend, and I will post result later so that other people can have benefit.

Thank you all again for your kindly help

WUJU






Gemma,

 

[Husan]

Hi,

In my 1st suggestion, you actually dont need to make a copy of the table. It works only from 1 table. Whats its doing is referencing the same table twice.
ie in the sql satement:

FROM
tblReadings INNER JOIN tblReadings AS tblReadingsPrev

its still reading the same table tblReadings but using an alias tblReadingsPrev to reference it. A table called tblReadingsPrev doesnt actually exist. So you wouldnt need to copy and paste the table again, it would just work.

Also, you should be able to add a numeric field of AutoNumber type which would automatically populate it sequencially for you. You need to be carefull that all the records are actually in date/hour order though.

Hope this helps


Husan

 

[Dairy Farmer]

I had a situation where I needed to subtract today's reading from yesterday's reading. To solve it I created 2 queries from the same table.

Query 1:
ReadingDate
ReadingQty

Query 2:
ReadingDate2 (ReadingDate + 1)
ReadingQty2

Query2 will give you the Opening Reading for Query1.

 

[WuJu]

Hello Husan,

I tried your option 1. It was beatifully working for everyday for hour 2 - hour1, hour3 - hour2, ..... hour 24- hour23. However, I also need to the day hour 1 - previous day 24 except the first day. So if I am calculating for period january 1, 2005 to December 31, 2009, then I need to calculate the difference of every hour in every day including current day hour 1 - the previous day hour 24 since they are adjacent hours.

Jan 1 2000 hr2 - Jan 1 2000 hr1
Jan 1 2000 hr3 - Jan 1 2000 hr2
......
Jan 1 2000 hr24 - Jan 1 2000 hr23
Jan 2 2000 hr1 - Jan 1 2000 hr24 (option 1 doesn't generate this)
Jan 2 2000 hr2 - Jan 2 2000 hr1
....


So your option 1 was almost working, but quite yet.

I am working on your option 2.
I only know how to add a numeric field of AutoNumber in a table.
But I don't know how to add numeric field of AutoNumber for query.
My record set is from the output of a query.

If you know how to add a numric field of AutoNumber type from the output of a query, or anyway to generate ID filed which is asceding number corresponding to the each records row, then please let me know. Once I can generate ID field, I can use Dlookup.

Thanks for your help.

WUJU

 

[Husan]

Hi,

i dont think an autonumber field can be dynamcally created in a query, only in a table. We could try and generate an auto number using the date/hour but it wouldnt have afixed increment value as the hours will change from 24 to 1.

but i think i do have a solution, its basicaly using a dlookup, we reference the lookup table by date and hour. We calculate which date/hour to use by the following:

date: if the hour is 1 then we use (date - 1) else we use date
hour: if the hour is 1 then we use 24 else we use (hour - 1)

here is the SQL for the query:
------------------------------------------
SELECT
tblReadings.Date,
tblReadings.Hour,
tblReadings.Total_MWh,
IIf([Hour]=1,[Date]-1,[Date]) AS PrevReadingDate,
IIf([Hour]=1,24,[Hour]-1) AS PrevReadingHour,
DLookUp("Total_MWh","tblReadings","[Date]=#" & [PrevReadingDate] & "# and [Hour]=" & [PrevReadingHour]) AS PrevReading,
[Total_MWh]-[PrevReading] AS [Usage]
FROM
tblReadings;
-------------------------------------------------------------------

hope it works

NOTE: u may have issues with date format ie dd/mm/yyy or mm/dd/yyy

 

[gemma-the-husky]

partial quote ...

Thanks John, Gemma, and Husan.

Gemma, could you advise me how to store previous reading with each record?

i am not sure how you input new readings. but at the time you input a reading, you could read the last reading from the file - as long as you input them in order. Add a field to your table to store the "previous reading". And now when you want to use the previous reading, its immediately avaialble. You have to retrieve the value once, at time of insert, but only once ever. Its far more efficeint than having to keep retrieving a previous value.

As I say, it works best if you never (rarely) need to go back on stuff. Because obviously if you have to change a value in reading 12, or delete reading 12, it affects reading 13 as well.

you could populate/initiallise a previous reading field quite easily by iterating a recordset. That would process thoasand of records in a few seconds

The thing is, because an access table is essentially unordered, then needing to use an order is always tricky.

(ie as far as access is concerned the table of readings is just that - a "set" of realted data, with no intrinsic order and a "normal operation" would be to select a sub-set of those readings - eg based on date - and total them - which at no point depends on the items being in a particular order) Thats why its far better if each row is atomic, and doesnt depend on other data in the table. That is spreadsheet thinking.

that is also why when you import data into a table, the order you see it in the table may not match the order it was in the original file. As far as access is concerned, its just related data, with no order.


the best data structure for this is a linked list - but access doesnt lend itself to this type of structure - they do have a collection, but in my experience, it isnt that easy to use.

-------------------
of course the other thing is

for what purposes do you NEED to get the previous reading?

if its in a report, then you dont have the problem. because a report is necessarily an ordered structure, then generally you can use simple code to maniuplate sequential data.

if its in a form, then maybe the movement of a single reading isnt really critical. you can easily average, or use other statistical data over the whole population - since again these are operations that work over the set of data, and dont depend on the particluar order of items within the set - just a thought!

 

[WuJu]

Hello Husan and Gemma,

Thank you for your input and help.
First of all, I finally found a solution.
It is the combination of Husan's option 1 and "UNION"

Gemma, I will try to work with your input : make intermediate tables for current and previous, and simply subtract each other.
Once I am stuck or finish, I will post again, so please check this forum and help me in case I need your help.

Once again, thank you for your kindly help.

WUJU


The following is a SQL code which works great.
(Husan, without your help, I could not drive to the end. Thanks!)

SELECT
tblReadings.Date,
tblReadings.Hour,
tblReadings.Total_MWh,
tblReadings.Total_MWh AS [Current],
tblReadingsPrev.Total_MWh AS Previous,
[tblReadings]![Total_MWh]-[tblReadingsPrev]![Total_MWh] AS [Usage]
FROM
tblReadings INNER JOIN tblReadings AS tblReadingsPrev
ON tblReadings.Date = tblReadingsPrev.Date
WHERE
tblReadingsPrev.Hour=([tblReadings]![Hour]-1);

UNION

SELECT
tblReadings.Date,
tblReadings.Hour,
tblReadings.Total_MWh,
tblReadings.Total_MWh AS [Current],
tblReadingsPrev.Total_MWh AS Previous,
[tblReadings]![Total_MWh]-[tblReadingsPrev]![Total_MWh] AS [Usage]
FROM
tblReadings INNER JOIN tblReadings AS tblReadingsPrev
ON tblReadingsPrev.Date = tblReadings.Date -1
WHERE
tblReadings.Hour= 1 AND tblReadingsPrev.Hour= 24;