X   Site Message
(Message will auto close in 2 seconds)

Welcome Guest ( Log In | Register )

 
Reply to this topicStart new topic
> how to use values of previous row in the current row for calculation, Office 2007    
 
   
anup
post Jul 4 2010, 04:43 PM
Post #1

New Member
Posts: 2



hi,
this is my first post. I m using microsoft access 2007. I am a beginner.
I have one column which has dates.
in another column, i wish to calculate the difference between the date in current row and the date in previous row. how do i do that?
Oneed clues or ways to do it. rest i will figure it out.
thanks a bunch.
Go to the top of the page
 
+
John Vinson
post Jul 4 2010, 05:12 PM
Post #2

UtterAccess VIP
Posts: 3,440
From: Parma, Idaho, US



welcome2UA.gif
On Access table may look like a spreadsheet... but it isn't.
A table should be viewed as an unordered "heap" of records. There's no such thing as "the previous row" or "the next row", any more than there is a previous potato in a wheelbarrow full of potatoes <. If you want to find the maximum value of a Date/Time field in an already existing record in your table, you can use the DMax() function to look it up, together with the DateDiff function to calculate the difference (in any time unit from seconds to years) between them; e.g. in a calculated field in a Query you could use
DaysSinceLast: DateDiff("d", DMax("[MyDateField]", "[mytable]", "[MyDateField] <= #" & [MyDateField] & "#"), [MyDateField])
This uses the # delimiter to specify that the value is a date/time datatype, and looks up the largest existing date less than or equal to the date in this record ("previous date"). Not sure if that's exactly what you want!
Do note that you should not attempt to store this date difference in the table; like any other calculated field, it should be calculated as needed, not stored.
Go to the top of the page
 
+
anup
post Jul 4 2010, 05:59 PM
Post #3

New Member
Posts: 2



Hi john,
dont want to calculate the maximum value of date/time. i just want the differences.
let me explain more..
heres is how i want it : difference in dates for current and previous (now i know the term 'previous' doesnt exist, but dont know what else to call it).
Dates Difference
1/23/2006 12:00:00 AM 0
2/6/2006 12:00:00 AM 14
2/13/2006 12:00:00 AM 7
can you pls tell how do i go about it.
appreciate your guidance in this matter.
thanks a lot.
Go to the top of the page
 
+
John Vinson
post Jul 5 2010, 01:57 PM
Post #4

UtterAccess VIP
Posts: 3,440
From: Parma, Idaho, US



You must - no option, no choice, that's just the way relational databases work! - have some field or fields in the table that you can use to identify the "previous record".
If the time values are strictly increasing as you add new records, then the "previous record" indeed should be the one with the maximum value. If not, then you will need some other field in the table to identify the previous record. Access does not keep a "row number"; you're not even guaranteed to find the records in the same order in the table next time you look at it.
If you have only the one field in the table, and the records are not in date order, then you'll need to either redesign your table or use Excel instead!
Go to the top of the page
 
+
BruceM
post Jul 6 2010, 06:59 AM
Post #5

UtterAccess VIP
Posts: 3,666
From: Downeast Maine



Have you tried John's expression? It should work, if I understand the question correctly. You would start by creating a query containing all of the fields from your table, ordered as you choose. Then in a blank column in query design view (use another name instead of DaysSinceLast, if you like, but letters, numbers, and underscores only; no spaces or special characters):
aysSinceLast: DateDiff("d", DMax("[MyDateField]", "[mytable]", "[MyDateField] <= #" & [MyDateField] & "#"), [MyDateField])
You should look up the functions DMax and DateDiff in Help. To get you started, looking at one part of the expression:
DMax("[MyDateField]", "[mytable]", "[MyDateField] <= #" & [MyDateField] & "#")
The above finds the maximum value (most recent date) in MyDateField in mytable (use your actual table and field names) that meet a criteria. At its simplest, DMax will find the maximum MyDateField value:
DMax("[MyDateField]", "[mytable]")
The rest of the expression narrows it down to dates that are less than or equal to the current date (depending on the details, you may want < only, rather than <=). For the purposes of this posting I will assume this is what you want. The number signs are date delimiters, which tells Access that what is between the # signs is a date. If MyDateField is today's date, Access "sees" this:
DMax("[MyDateField]", "[mytable]", "[MyDateField] < #7/6/2010#)
In other words, find the maximum MyDateField value from mytable in which the MyDateField value is less than MyDateField in the current record. I will assume the most recent date previous to the current record was 7/1/2010, which means the DMax function will return that value. Now to the DateDiff function, using 7/1/2010 instead of the DMax function (for illustration only; in practice you would use DMax), and today's date instead of MyDateField:
DaysSinceLast: DateDiff("d", 7/1/2010, 7/6/2010)
This tells Access to calculate the difference in days between the previous greatest value and the current value of MyDateField. In practice, you would need date delimiters when using literal dates (otherwise Access treats it as a math expression), but since you are using fields and functions there should be no need for date delimiters in DateDiff.
This should work for every row (record) in the query except for the first one (the earliest date). To allow for DMax returning Null for the earliest record, use Nz:
DaysSinceLast: DateDiff("d", Nz(DMax("[MyDateField]", "[mytable]", "[MyDateField] <= #" & [MyDateField] & "#"),[MyDateField]), [MyDateField])
This tells Access to use MyDateField instead of the previous greatest value if there is no previous greatest value (i.e. you are at the earliest record).
Go to the top of the page
 
+

Thank you for your support! Reply to this topicStart new topic

Jump To Forum:
 



RSS Search  ·  Go to Top  ·  Lo-Fi Version Time is now: 18th December 2014 - 07:27 AM