Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 Other Forums
 MS Access
 Is there any way to do this in an access query?

Author  Topic 

Trash_Heap_6
Starting Member

4 Posts

Posted - 2013-10-04 : 09:57:39
Here is a subset of data. (It is a rather large file that needs to be collapsed)


TEAM_ID PERSON_ID ITEM#
--------- --------- -----
2200 10 1
2200 10 2
2200 10 3
2200 10 10
2200 10 11
2200 10 12
2200 10 13
2300 12 1
2300 12 2
2300 12 3
2300 12 4
2300 12 5


Here is what I am trying to create:


LOW HIGH
TEAM_ID PERSON_ID ITEM# ITEM#
--------- --------- ----- -----
2200 10 1 3
2200 10 10 13
2300 12 1 5


I have tried various Group Querys using MIN and MAX functions, but the big issue is when there is a break in Item# for the same TEAM_ID and PERSON_ID.

jethrow
Starting Member

37 Posts

Posted - 2013-10-04 : 20:58:03
[code]SELECT TEAM_ID,
PERSON_ID,
Min([ITEM#]) AS [LOW ITEM#],
Max([ITEM#]) AS [HIGH ITEM#]
FROM [Table]
GROUP BY TEAM_ID,
PERSON_ID,
INT([ITEM#] / 10)[/code]

Microsoft SQL Server Noobie
Go to Top of Page

Trash_Heap_6
Starting Member

4 Posts

Posted - 2013-10-07 : 11:37:37
Thanks for query. It almost works. I didn't have all the data scenarios in my example. This Query works great if a TEAM_ID,PERSON_ID has ITEM#s that start and end within intervals of 10. I appologize for not supplying enough information. I have other scenarios in the data that go across and stay within boundary of 10. I will clarify below:


TEAM_ID PERSON_ID ITEM#
--------- --------- -----
2200 10 1
2200 10 2
2200 10 3
2200 10 9
2200 10 10
2200 10 11
2200 10 12
2200 10 13
2300 12 1
2300 12 2
2300 12 3
2300 12 4
2300 12 5
2300 12 8
2400 15 19
2400 15 20
2400 15 21
2400 15 22
2400 15 23
2400 15 24
2400 15 25
2400 15 26
2400 15 27
2400 15 28
2400 15 29
2400 15 30
2400 15 31


Here is what I am trying to create:


LOW HIGH
TEAM_ID PERSON_ID ITEM# ITEM#
--------- --------- ----- -----
2200 10 1 3
2200 10 9 13
2300 12 1 5
2300 12 8 8
2400 15 19 31


Here are the results when I run your query.


LOW HIGH
TEAM_ID PERSON_ID ITEM# ITEM#
--------- --------- ----- -----
2200 10 1 9
2200 10 10 13
2300 12 1 8
2400 15 19 19
2400 15 20 29
2400 15 30 31
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-10-07 : 11:50:56
Not necessarily an answer to your question, but this is a class of problems that they refer to as islands and gaps. See here for how you would solve it in T-SQL. You might want to search if solution for that type of problems exists in Access. http://sqlmag.com/sql-server-2012/solving-gaps-and-islands-enhanced-window-functions
Go to Top of Page

jethrow
Starting Member

37 Posts

Posted - 2013-10-09 : 01:30:05
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
Go to Top of Page

Trash_Heap_6
Starting Member

4 Posts

Posted - 2013-10-10 : 11:33:39
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


Thanks for your time, but I am having trouble with this section.

The SELECT statement is labeled as "SUB2" and all it has is "COUNT(*)" as a field.

The WHERE clause just below it has sub2.TEAM_ID, sub2.PERSON_ID, and sub2.[ITEM#].



Go to Top of Page

jethrow
Starting Member

37 Posts

Posted - 2013-10-10 : 16:55:42
quote:
Originally posted by Trash_Heap_6

...I am having trouble...


That would be because I missed 2 sets of parenthesis. It should work now.

Microsoft SQL Server Noobie
Go to Top of Page

Trash_Heap_6
Starting Member

4 Posts

Posted - 2013-10-11 : 15:04:17
BINGO!!! That is it! Thank You!
Go to Top of Page
   

- Advertisement -