Assuming you table is called YourTable:
SELECT TEAM_ID, PERSON_ID, IIF(ISNULL(MIN), NextMIN, MIN) AS LOW, MAX AS HIGH
FROM (
SELECT a.TEAM_ID, a.PERSON_ID, a.MIN, a.MAX,
( SELECT TOP 1 MIN
FROM SubQuery AS b
WHERE b.KEY < a.KEY
ORDER BY b.KEY DESC ) AS NextMIN
FROM SubQuery AS a)
WHERE NOT ISNULL(MAX)
... where SubQuery is:
SELECT TEAM_ID, PERSON_ID, [ITEM#], MIN, MAX, key
FROM ( SELECT sub1.TEAM_ID,
sub1.PERSON_ID,
sub1.[ITEM#],
IIF((SELECT Count(*)
FROM YourTable AS sub2
WHERE sub1.Team_ID = sub2.Team_ID
AND sub1.Person_ID = sub2.Person_ID
AND sub2.[ITEM#] = sub1.[ITEM#]-1), NULL, sub1.[ITEM#]) AS MIN,
IIF((SELECT Count(*)
FROM YourTable AS sub2
WHERE sub1.Team_ID = sub2.Team_ID
AND sub1.Person_ID = sub2.Person_ID
AND sub2.[ITEM#] = sub1.[ITEM#]+1), NULL, sub1.[ITEM#]) AS MAX,
sub1.TEAM_ID & sub1.PERSON_ID & String(2-Len(sub1.[ITEM#]),"0") & sub1.[ITEM#] AS KEY
FROM YourTable AS sub1 )
WHERE NOT IsNull(MIN)
OR NOT IsNull(MAX)
Note that these queries assume your data is Ordered by TEAM_ID, PERSON_ID, ITEM# - though you could ensure that fairly easily. Also, these fields need have values with the same Length (the ITEM# field is forced to be 2 chars via "0" padding)
EDIT: added 2 sets of missing ()
Microsoft SQL Server Noobie 
Is there any way to do this in an access query?
Posted - 2013-10-04 : 09:57:39
