N high filtering: msAccess2003

  • Thread starter Thread starter C Conwell
  • Start date Start date
C

C Conwell

I have a large dataset consisting of hourly data from
multiple locations. I am wanting to retrieve the Top 15
hours from each individual location in the dataset.
I have tried the Top function, but it will only show me
the top 15 data values from all of the data. I have
tried doing a pivot view, but cannot find a function to
limit it to the top 15 as well.
It would be appreciated if anyone can assist.

Thanks,

Craig
 
Craig,

How are the locations identified in your data? If you can't
do that I'm not sure you can do what you want.

If you can then the first thing I'd try is to modify that
query you mentioned using the Top function by adding
appropriate values into the criteria box of the query design
grid. Perhaps make it a parameter query where you prompt
for the location. Or a different query for each location,
that's a rather messy way to do it but it would work.

That should give you a set of data for each location.

Of course, if you can't identify rows by location then the
foregoing is irrelevant.
 
Nick,
The data is arranged in a format as follows:

Location, Date/Time, Data Value

In which there are over 400 locations, and hourly meter
data for each month.

I had reasonable success by doing a Top 15 query, by
site, but it would require doing over 400 queries. This
may have to be done by generating a macro that will loop
through and get each location, and then perform an append
query to run the analysis.
I was hoping for a more simple approach, but it appears
that it is going to require quite a bit of work.
Your thoughts on this, as well as time are appreciated.
Craig
 
Craig said:
Nick,
The data is arranged in a format as follows:

Location, Date/Time, Data Value

In which there are over 400 locations, and hourly meter
data for each month.

I had reasonable success by doing a Top 15 query, by
site, but it would require doing over 400 queries. This
may have to be done by generating a macro that will loop
through and get each location, and then perform an append
query to run the analysis.
I was hoping for a more simple approach, but it appears
that it is going to require quite a bit of work.
Your thoughts on this, as well as time are appreciated.
Craig
Click to expand...

You can do this by making use of a subquery. For example,

SELECT R.Location, R.ReadingTime, R.Reading
FROM tblReadings AS R
WHERE R.ReadingTime In
(SELECT TOP 15 ReadingTime From tblReadings T
WHERE T.Location = R.Location
ORDER BY T.Reading DESC)
ORDER BY R.Location, R.Reading DESC;

The only hitch is that, if any location has multiple readings with the
same value in the 15th slot, there will actually be more than 15 records
returned for that location.
 
Dirk,
That did the trick. The performance is pretty slow,
but with 30 days of hourly data for 473 locations, I can
see why.

Again, thanks

Craig
 
Craig said:
Dirk,
That did the trick. The performance is pretty slow,
but with 30 days of hourly data for 473 locations, I can
see why.

Again, thanks
Click to expand...

You're welcome. Make sure all three of the fields are indexed. If they
aren't, indexing them may improve performance a lot.
 
Back
Top