Calculate change between records

[razchip]

I'm a novice coder and need some help in calculating the difference between two different records in a table and update the table.

Value is the data that I currently have, Value2 (changed amount) is what I need to update (default is zero):

Date Value Value2
02/23/2012 55466
03/01/2012 58198 2732
03/08/2012 59899 1701

Not sure how to go about this.

Thanks for any advice.

Thanks for the help.
Greg

 

[lameid]

You being a novice and this being rather involved, it would be helpful to have the table name, and actual field names and any kind of keys... This looks like it is mileage values to me which would imply there is a vehichle id to consider rather than there just being one vehichle. Then again maybe it is something entirely different.

 

[razchip]

The table name is IPA, we get a weekly download, where the value is a year to date summary (58,899)units to date. I have a report that requires the weekly total, for 03/08/2012 we added 1,701 units (Current week 59,899 - Previous week 58198).

The field names are actually YTDUnits and CurrentUnits

Thanks for the help.
Greg

 

[vbajock]

I you trying to do this via the query interface or are you asking how to write a program to do this?

 

[MajP]

[/code]
SELECT
IPA.dtmDate,
IPA.YTDUnits,
[YTDUnits]-(select Top 1 YTDUnits from IPA as PreviousWeek where IPA.dtmDate > Previousweek.dtmDate order by PreviousWeek.dtmDate desc) AS CurrentValue
FROM
IPA
ORDER BY
IPA.dtmDate;

 

[lameid]

Depending on what you expect the error you might have you might go with MajP's solution or this... My money is on MajP's solution but another way to think about it...

Select
IPA.[Date],
IPA.[YTDUnits],
IPA.[YTDUnits] - NZ(LW.[YTDUnits],0)
From IPA
   Left Join IPA as LW
      On Dateadd("d",-7, IPA.[Date]) = LW.[Date];

 

[lameid]

BTW you did not provide the name of your date field... I used Date whereas Majp used dtmDate, substitute as needed.

I am a little curious if there is a noticeable difference in speed in Access for the two queries.

 

[MajP]

Although it will work if you put it in brackets, I purposely try to avoid words like date, year, month, now or names with spaces for field names. Anything that can be mistaken for an access object or vba reserved word.

Lameid
Your left join should always be faster than the subquery. My thought is what if they change the day of the week they report on or shut down for a holiday. Mine may be more flexible, but likely slower.

 

[razchip]

This worked just fine (MajP's), appreciate the help.

Thanks for the help.
Greg

 

[lameid]

MajP,

While in general I would expect a left join to be faster in general, with the function call in the join, I am not entirely convinced it is faster. I expect there is more onetime overhead in my solution and therefore slower on a "small" number of records whereas yours is linear overhead and at some threshold becomes slower but that is just my guess.

 

[PHV]

lameid, to avoid the function call:
On IPA.Date = LW.Date + 7

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[lameid]

To clarify, my expectation holds when joining on an expression regardless of the function call or other operation. Although I aggree adding a literal is faster than adding days with the function and while it has not changed as long as I have worked with Access, I would still avoid writing code that is dependent on assumptions about the way the data is stored (the integer part of the date is days).