Do you really want record 13-4 repeated? Must the result be updatable? If
you don't need the record repeated, this should work:
SELECT Group, Sequence, Text
FROM MyTable
WHERE Sequence IN
(SELECT Top 2 Sequence
FROM MyTable As T2
WHERE T2.Group = MyTable.Group
ORDER BY Sequence)
OR Sequence IN
(SELECT Top 2 Sequence
FROM MyTable As T3
WHERE T3.Group = MyTable.Group
ORDER BY Sequence DESC)
To get the repeated record, you'll need a UNION query that won't be
updatable:
SELECT Group, Sequence, Text
FROM MyTable
WHERE Sequence IN
(SELECT Top 2 Sequence
FROM MyTable As T2
WHERE T2.Group = MyTable.Group
ORDER BY Sequence)
UNION ALL
SELECT Group, Sequence, Text
FROM MyTable
WHERE Sequence IN
(SELECT Top 2 Sequence
FROM MyTable As T3
WHERE T3.Group = MyTable.Group
ORDER BY Sequence DESC)
John Viescas, author
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
Running Microsoft Access 2000
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)
For the inside scoop on Access 2007, see:
http://blogs.msdn.com/access/
-----Original Message-----
From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf
Of Jerry Hull
Sent: Saturday, May 06, 2006 7:30 PM
To: [email protected]
Subject: [ms_access] First and last TWO items in a group
I have a database with the following fields:
Group: numeric
Sequence: numeric
Location: text
The database is sorted on Group & Sequence, and has a variable number of
records for each group. I need to create a query that returns the first two
and last two records for each group. There will (I think) always be at
least three records for each group, and so in those cases where there are
only three records the second and second to last records will be the same.
So if the original data is:
Group Sequence Text
1 3 point23
1 7 point56
1 8 point87
1 10 point101
7 2 locale5
7 4 point5
7 5 area10
7 13 point45
7 21 locale6
7 25 area71
7 26 area84
13 1 camp6
13 4 camp8
13 7 camp13
I need to end up with:
1 3 point23 (first)
1 7 point56 (second)
1 8 point87 (second to last)
1 10 point101 (last)
7 2 locale5 (first)
7 4 point5 (second)
7 25 area71 (second to last)
7 26 area84 (last)
13 1 camp6 (first)
13 4 camp8 (second)
13 4 camp8 (second to last)
13 7 camp13 (last)
Thanks for any help!!
Jerry Hull
[EMAIL PROTECTED]
[Non-text portions of this message have been removed]
Yahoo! Groups Links
SPONSORED LINKS
| Microsoft access database | Database development software | Database management software |
| Database software | Inventory database software | Membership database software |
YAHOO! GROUPS LINKS
- Visit your group "ms_access" on the web.
- To unsubscribe from this group, send an email to:
[EMAIL PROTECTED]
- Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.
