SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Other Forums
 MS Access
 Is there any way to do this in an access query?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Trash_Heap_6
Starting Member

USA
4 Posts

Posted - 10/04/2013 :  09:57:39  Show Profile  Reply with Quote
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.

Edited by - Trash_Heap_6 on 10/04/2013 11:26:37

jethrow
Starting Member

USA
37 Posts

Posted - 10/04/2013 :  20:58:03  Show Profile  Reply with Quote
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)


Microsoft SQL Server Noobie
Go to Top of Page

Trash_Heap_6
Starting Member

USA
4 Posts

Posted - 10/07/2013 :  11:37:37  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3326 Posts

Posted - 10/07/2013 :  11:50:56  Show Profile  Reply with Quote
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

USA
37 Posts

Posted - 10/09/2013 :  01:30:05  Show Profile  Reply with Quote
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

Edited by - jethrow on 10/10/2013 19:36:23
Go to Top of Page

Trash_Heap_6
Starting Member

USA
4 Posts

Posted - 10/10/2013 :  11:33:39  Show Profile  Reply with Quote
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

USA
37 Posts

Posted - 10/10/2013 :  16:55:42  Show Profile  Reply with Quote
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

Edited by - jethrow on 10/10/2013 19:41:34
Go to Top of Page

Trash_Heap_6
Starting Member

USA
4 Posts

Posted - 10/11/2013 :  15:04:17  Show Profile  Reply with Quote
BINGO!!! That is it! Thank You!
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000