Converting list of numbers to ranges

[thebigthing313]

Hello all,

I have a query that returns a list of site numbers inspected given an inspector and a date, but I would like to try and return the records where a list of numbers get summarized into number ranges, as in:

101


102
103
106
108
109
110

To:

101-103
106
108-110

I would like to do this for reporting purposes so I was trying to use the following idea on the link below on the query that the report is based on:
http://social.msdn.microsoft.com/For...orum=accessdev

However, when I try to use it, the new column is just reiterating the results on the original SiteID column. Is it because my circumstances are different? Or am I just using the solution wrong lol.

Note: SiteID though mostly numeric, is stored as a text field since I needed catchall SiteIDs which are text. (I tried wrapping the functions used with VAL, but didn't work)

Eventually on the report itself, I may even want to take each ranged row and then concatenate them with a comma-delimiter, as in "101-103, 106, 108-110".

Any assistance would be greatly appreciated!

[June7]

Are you trying to do a natural breaks grouping? Your requirements appear same as the link example, although it shows dropping the number 10 from the results.

Did you try the query example or the Function code?

The example Function code takes a comma delimited string and returns another comma delimited string of ranges. You want to act on sets of records. This requires each record to consider the value of data in other records - never easy and not what the example Function code is designed for.

I just tested the query example and it works, except returns 106 with a hyphen (106-). A slight change in the syntax fixed that.

SELECT Min(Data) & IIf(Max(Data)=Min(Data), Null, "-" & Max(Data)) AS Range FROM (SELECT A.Data, Count(*) AS Rank FROM Table1 AS A INNER JOIN Table1 AS B ON A.Data >=B.Data GROUP BY A.Data) AS QueryRank GROUP BY Data-Rank;

However, if you want to return all the original records with the group identifier, that is more complicated. Try this:

Query1
SELECT Min(Data) AS Start,IIf(Max(Data)=Min(Data), Null, Max(Data)) AS End FROM (SELECT A.Data, Count(*) AS Rank FROM Table1 AS A INNER JOIN Table1 AS B ON A.Data >=B.Data GROUP BY A.Data) AS QueryRank GROUP BY Data - Rank;

Query2
SELECT Table1.Data, RangeStart & IIf(IsNull(End),Null,"-" & RangeEnd) As Range
FROM Table1, Query1 WHERE Data BETWEEN Start AND Nz(End,Start);

All in one:
SELECT Table1.Data, RangeStart & IIf(IsNull(End),Null,"-" & RangeEnd) As Range
FROM Table1, (SELECT Min(Data) AS RangeStart, IIf(Max(Data)=Min(Data), Null, Max(Data)) AS RangeEnd
FROM (SELECT A.Data, Count(*) AS Rank FROM Table1 AS A INNER JOIN Table1 AS B ON A.Data >=B.Data GROUP BY A.Data) AS QueryRank
GROUP BY Data - Rank) AS Query1 WHERE Data BETWEEN RangeStart AND Nz(RangeEnd, RangeStart);


For the concatenation, review: http://allenbrowne.com/func-concat.html

[thebigthing313]

This worked wonderfully, thank you very much. Thank you for going in deeper into the solution, really appreciate it. As it turned out, I was applying the solution incorrectly, mostly syntactical errors, but I managed to get it to work. Thanks again!

[thebigthing313]

So I am now running into trouble trying to use Allen Browne's ConcatRelated function.

qry1 is used to pull out the records I want to report via criteria filter
qry2 is used to create the Start and End Ranges using qry1 results
qry3 is used to join each Range from qry2 with their identifying records from qry1

I am now trying to use the ConcatRelated function in a separate query to concatenate all ranges and keep them joined with their identifying records so that my report can be based solely off this query.

The new query has the following SQL code currently:

SELECT [qry1].InspectorID, [qry1].InspectionDate, [qry1].MunicipalID, [qry1].SectionID, ConcatRelated("Range","qry3","MunicipalID = "" & [MunicipalID] & "" AND SectionID = "" & [SectionID] & """) AS Expr1

FROM [qry1];

Upon execution, I get an "Error 3131: Syntax error in FROM clause."

When I temporarily commented out the module's On Error statement, the debugger highlights this line:

Set rs = DBEngine(0)(0).OpenRecordset(strSql, dbOpenDynaset)

I'm not entirely sure if it's my SQL syntax that's wrong or if I am using the function incorrectly.

[June7]

Would have to analyze the structure of all the queries. Why not the all-in-version?

If you want to provide db, follow instructions at bottom of my post.

[thebigthing313]

Hey,

So sorry for only getting back to this now. Was temporarily assigned a different project to work on lol. I did eventually figure it out. The query ConcatRelated uses as an argument cannot use parameters from forms it seems, and so I just moved the parameters to the query that actually uses the function and everything worked, but I appreciate your help!