Is there any way to do this in an access query? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

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

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

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

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#].

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

Trash_Heap_6
Starting Member

4 Posts

Posted - 2013-10-11 : 15:04:17

BINGO!!! That is it! Thank You!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources