Query (the last five visits to each spot)

[eloi-ribeiro.blogspot.com]

Hi,

I'm having a problem with a query. I'm running Access 2000 and a table
with 35000 rows.

The table contains several visits to the spots with a CODE (spot code)
and DATE (visit date) columns. Each spot have 10 visits or more, so
the CODE is repeated in the column, an the respective DATE of that
visit, witch are not repeated. I would like to know the last five
visits to each spot. If it's possible.

Thanks in advance. Regards,

Eloi

[Rich P]

Hi Eloi,

Try something like this:

SELECT * FROM yourTbl t1 WHERE EXISTS
(SELECT * FROM (SELECT TOP 5 * FROM yourTbl t2 WHERE t2.code = t1.code
ORDER BY datefield desc) t3 WHERE t3.rowID = t1.rowID)

This sample requires that you have an autonumber column which would
uniquely identify each row. I named my autonumber column 'rowID'. Here
we use the Top clause to retrieve the top 5 rows for each code based on
the most recent dates. Example: code 'A' is listed for '1/1/07',
'1/2/07',...'1/10/07'

Select Top 5 * from yourTbl where code = 'A' Order By DateField Desc

This would return all the rows for code = 'A' for dates
1/6/07, 1/7/07, 1/8/07, 1/9/07, 1/10/07

We order the dates in Descending mode so that the first dates in the
return list are the most recent dates : 1/10/07 is more recent than
1/1/07. In the first sample above, I generalize this so that we can
retrieve the top 5 rows for each group of codes instead of just one
code. I use a subquery which I name t3, and I link the subquery to
yourTbl using the autonumber field 'rowID'
Rich

*** Sent via Developersdex http://www.developersdex.com ***

[eloi-ribeiro.blogspot.com]

Hi Rich,

Thanks for answering. I tried out your sql code and it didn't seem to
work, it returns an empty table. Here is the sql sentence I had used:

SELECT * FROM yourTbl AS t1
WHERE EXISTS (SELECT * FROM (SELECT TOP 5 * FROM yourTbl AS t2
WHERE t2.CODE = t1.CODE
ORDER BY DATEFIELD desc) t3 WHERE t3.ROWID = t1.ROWID)
;

Here is the first 30 row's from the table:

ROWID CODE DATEFIELD
1 OL0021 23/12/2004
2 OL0001 23/12/2004
3 OL0009 23/12/2004
4 OL0023 23/12/2004
5 OL0008 23/12/2004
6 OL0010 23/12/2004
7 OL0013 23/12/2004
8 OL0002 23/12/2004
9 OL0007 23/12/2004
10 OL0011 23/12/2004
11 OL0024 23/12/2004
12 OL0025 23/12/2004
13 OL0022 23/12/2004
14 OL0026 23/12/2004
15 OL0005 23/12/2004
16 OL0004 23/12/2004
17 OL0014 23/12/2004
18 OL0012 23/12/2004
19 OL0039 23/12/2004
20 OL0003 23/12/2004
21 OL0040 23/12/2004
22 OL0015 23/12/2004
23 OL0006 23/12/2004
24 OL0001 14/01/2005
25 OL0004 14/01/2005
26 OL0009 14/01/2005
27 OL0008 14/01/2005
28 OL0015 14/01/2005
29 OL0003 14/01/2005
30 OL0006 14/01/2005
|
|-this are repeated a lot more times with a different
date [DATEFIELD].

Do you know why it's not working?
Thanks again for wasting you time with this.

Regards,

Eloi