ACCESS FEED: Re: [MS_AccessPros] calculating net change

Minggu, 01 Mei 2016

Re: [MS_AccessPros] calculating net change

Glenn-

You can do that by using two copies of your table and linking them on SiteID and either the previous SiteHistoryID or SiteHistoryAsOfDate. The reason I ask about those two columns is your data is a bit confusing. For SiteID 700, you have:

3467	17-Apr-16	32211	700
3473	16-Apr-16	32060	700

The lower SiteHistoryID has the higher date.

The SQL might look like:

SELECT S1.SiteHistoryID, S1.SiteID, S1.SiteHistoryCount, S1.SiteHistoryCount - S2.SiteHistoryCount AS Difference

FROM SiteHistory AS S1, SiteHistory AS S2

WHERE S1.SiteID = S2.SiteID

AND S2.SiteHistoryID =

(SELECT MAX(SiteHistoryID) FROM SiteHistory AS S3

WHERE S3.SiteID = S1.SiteID

AND S3.SiteHistoryID < S2.SiteHistoryID);

If it's the date that makes the difference, than use SiteHistoryAsOfDate instead of SiteHistoryID.

John Viescas, Author

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications

SQL Queries for Mere Mortals

http://www.viescas.com/

(Paris, France)

On May 1, 2016, at 3:09 PM, 'Glenn Lloyd' argeedblu@gmail.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

I have data that I want to analyze in terms of daily net change. Data reflecting the current volume is collected on a daily basis from approx. 75 locations. The data provider is unable to collect daily changes but needs this value computed by subtracting the previous reading from the last reading. Some locations have frequently changing daily levels while other have only occasional levels.

I am able to produce a short-term history (7 days) using a crosstab to flatten the date. I use the crosstab as a subquery in which each column is subtracted from the column that represents its predecessor.

The situation is similar to electrical power data where meter readings are periodically collected and consumption is calculated by subtracting the previous reading from the current reading. My data is slightly different because the values may indicate decreases as well as increases but this shouldn't affect the calculation.

Setting up a crosstab to calculate over the long term (as opposed to just the past 7 days) is not really practical. So I am looking for an efficient way to calculate periodic changes for a large number of periods.

Sample Date:

SiteHistoryID	SiteHistoryAsOfDate	SiteHistoryCount	SiteID
3455	17-Apr-16	67035	696
3461	16-Apr-16	67034	696
3463	17-Apr-16	40970	697
3465	17-Apr-16	39186	698
3466	17-Apr-16	33243	699
3467	17-Apr-16	32211	700
3473	16-Apr-16	32060	700
3475	15-Apr-16	27102	701
3481	16-Apr-16	28282	701
3482	17-Apr-16	28000	701
3484	17-Apr-16	25075	702
3487	17-Apr-16	24895	703
3492	16-Apr-16	20506	706
3497	16-Apr-16	20474	707
3519	16-Apr-16	15858	715
3531	16-Apr-16	13706	720
3543	16-Apr-16	10320	728
3560	16-Apr-16	9146	733
3570	16-Apr-16	9270	739
3580	16-Apr-16	7609	742
3629	16-Apr-16	6504	769
3766	16-Oct-12	16417	773