C
coreym123
I have records that are tracking locations and amount of money spent
in various categories. Example:
Paris, 10/12/2007, 10500
Rome, 11/15/2007, 9000
Paris, 12/1/2007, 8000
London, 12/15/2007, 7000
Rome, 12/30/2007, 7000
London, 1/10/2008, 3000
Paris, 1/15/2008, 5000
Paris, 1/20/2008, 11000
etc.
I need to make a query that will separate out each location and
analyze the downward (or upward) trend in that location's numbers over
time. For example it would separate out Paris:
Paris, 10/12/2007, 10500
Paris, 12/1/2007, 8000
Paris, 1/15/2008, 5000
Paris, 1/20/2008, 11000
And come back with the percent it decreased/increased over time. I
can't simply take the first and last value and find the % change,
because in this case it would indicate the costs trended up over time
when they did not.
It would do this same thing for every location, coming up with one %
number (positive or negative) for each location.
Any ideas as to how to do this? In Excel I found the linear
regression line for a location and found the difference between the
first and last points of the line - this came back with a reasonable %
trend - but I have no idea how to do this with an Access query.
Any help would be greatly appreciated.
Thank you
in various categories. Example:
Paris, 10/12/2007, 10500
Rome, 11/15/2007, 9000
Paris, 12/1/2007, 8000
London, 12/15/2007, 7000
Rome, 12/30/2007, 7000
London, 1/10/2008, 3000
Paris, 1/15/2008, 5000
Paris, 1/20/2008, 11000
etc.
I need to make a query that will separate out each location and
analyze the downward (or upward) trend in that location's numbers over
time. For example it would separate out Paris:
Paris, 10/12/2007, 10500
Paris, 12/1/2007, 8000
Paris, 1/15/2008, 5000
Paris, 1/20/2008, 11000
And come back with the percent it decreased/increased over time. I
can't simply take the first and last value and find the % change,
because in this case it would indicate the costs trended up over time
when they did not.
It would do this same thing for every location, coming up with one %
number (positive or negative) for each location.
Any ideas as to how to do this? In Excel I found the linear
regression line for a location and found the difference between the
first and last points of the line - this came back with a reasonable %
trend - but I have no idea how to do this with an Access query.
Any help would be greatly appreciated.
Thank you