looking at trends

  • Thread starter Thread starter Roach
  • Start date Start date
R

Roach

I have an existing query which includes date, site number
and index columns. My goal is to look at the index trends
for each site; i.e., did the index go up, down or stay
the same over time, and by how much ( the difference of
indices). The data has from one to five indices per site,
so the ones with one only one index will not have
changed, of course. I'm at a loss as to if Access will
even do this. Any ideas? Thanks.
 
Hi,


Untested, but should work:


SELECT a.siteNumber,
a.[date] As ref,
LAST(a.[index]) As IndexRef ,
b.[date] As PreviousRef
LAST(b.[index]) As PreviousIndexRef,
LAST(a.[index]) - LAST(b.[index]) As IndexChange


FROM (myTable As a INNER JOIN myTable As b
ON a.siteNumber=b.siteNumber)
INNER JOIN myTable As c
ON c.siteNumber=a.siteNumber

WHERE c.[date]<a.[date]

GROUP BY a.siteNumber, a.[date], b.[date]

HAVING b.[date] = MAX(c.[date])


.... and give you, for each site, each one with at least two records (two
different dates), a comparison between a given day, a, and the day preceding
it, b (by preceding, I don't necessary mean the immediate day before, with a
gap of 24h, but the previous day for which we have data, for that site).


Hoping it may help,
Vanderghast, Access MVP
 
Back
Top