Query to count between list of number (Predicting Start/End that may occur in data range)

Hi,

I have a below list of numbers.

566667
566668
566669
566665
566666
566671
566672
566680


I want a query that would return a count between start and end of
range.

Like

Start        End         Quantity
566665     566669     5

566671      566672     2

566680       566680     1


Thank you.
0
Angela
4/2/2010 5:17:50 AM
access 16762 articles. 3 followers. Follow

8 Replies
731 Views

Similar Articles

[PageSpeed] 1

On 2 apr, 07:17, Angela <ims...@gmail.com> wrote:
> Hi,
>
> I have a below list of numbers.
>
> 566667
> 566668
> 566669
> 566665
> 566666
> 566671
> 566672
> 566680
>
> I want a query that would return a count between start and end of
> range.
>
> Like
>
> Start =A0 =A0 =A0 =A0End =A0 =A0 =A0 =A0 Quantity
> 566665 =A0 =A0 566669 =A0 =A0 5
>
> 566671 =A0 =A0 =A0566672 =A0 =A0 2
>
> 566680 =A0 =A0 =A0 566680 =A0 =A0 1
>
> Thank you.

A query to do this could look like:

SELECT Count(YourNumber) AS CountOfNumbers
FROM YourTable
WHERE YourNumber>=3D[Enter Start] And YourNumber<=3D[Enter End];

Groeten,

Peter
http://access.xps350.com
0
XPS350
4/2/2010 6:22:30 AM
"Angela" <imsguy@gmail.com> kirjoitti 
viestiss´┐Ż:1acfa36e-8d83-455f-8e9b-9b561c95fdd4@10g2000yqq.googlegroups.com...
> Hi,
>
> I have a below list of numbers.
>
> 566667
> 566668
> 566669
> 566665
> 566666
> 566671
> 566672
> 566680
>
>
> I want a query that would return a count between start and end of
> range.
>
> Like
>
> Start        End         Quantity
> 566665     566669     5
>
> 566671      566672     2
>
> 566680       566680     1
>
>
> Thank you. 


0
Risse
4/2/2010 6:53:22 AM
Hi Angela,

I'm not sure how (or if, even) you could do that in a query.

Here is some (quickly tested) code that seems to give the results you want:
'********VBA code (somewhat tested)**********
Public Sub StartEnd()

Dim dbs As DAO.Database: Set dbs = DBEngine(0)(0)
Dim rstSource As DAO.Recordset
Dim rstTarget As DAO.Recordset
Dim lngCount As Long
Dim lngStart As Long
Dim lngEnd As Long
Dim lngPrev As Long
Dim strSQL As String

strSQL = "SELECT setField FROM tblStartEndTest ORDER BY setField"

Set rstSource = dbs.OpenRecordset(strSQL, dbOpenDynaset)
Set rstTarget = dbs.OpenRecordset("tblStartEndResults", dbOpenDynaset)

With rstSource
    .MoveFirst
    lngStart = !setField
    lngPrev = lngStart
    lngCount = 1
    .MoveNext
    Do
        If !setField = lngPrev + 1 Then
            lngPrev = !setField
            lngCount = lngCount + 1
        Else
            lngEnd = lngPrev
            With rstTarget
                .AddNew
                !serStart = lngStart
                !serEnd = lngEnd
                !serCount = lngCount
                .Update
            End With
            lngStart = !setField
            lngPrev = lngStart
            lngCount = 1
        End If
    .MoveNext
    Loop Until .EOF
    lngEnd = lngPrev
    With rstTarget
        .AddNew
        !serStart = lngStart
        !serEnd = lngEnd
        !serCount = lngCount
        .Update
    End With
End With

rstSource.Close
Set rstSource = Nothing
rstTarget.Close
Set rstTarget = Nothing
Set dbs = Nothing

End Sub
'********End VBA Code********************

Copy and paste this into a standard module.

If you need any help following what it does, or getting it to work, please 
post back.

Cheers,
Alex.


"Angela" wrote:

> Hi,
> 
> I have a below list of numbers.
> 
> 566667
> 566668
> 566669
> 566665
> 566666
> 566671
> 566672
> 566680
> 
> 
> I want a query that would return a count between start and end of
> range.
> 
> Like
> 
> Start        End         Quantity
> 566665     566669     5
> 
> 566671      566672     2
> 
> 566680       566680     1
> 
> 
> Thank you.
> .
> 
0
Utf
4/2/2010 8:51:01 AM
Hi Angela - are you always grouping in 10's?  If so then the partition 
function would do it in a query:

SELECT Min(Myfield) AS Start, Max(Myfield) AS [End], Count(Myfield) AS 
Quantity
FROM MyTable
GROUP BY Partition([myfield],0,1000000,10);


Hth

Stu

"Angela" wrote:

> Hi,
> 
> I have a below list of numbers.
> 
> 566667
> 566668
> 566669
> 566665
> 566666
> 566671
> 566672
> 566680
> 
> 
> I want a query that would return a count between start and end of
> range.
> 
> Like
> 
> Start        End         Quantity
> 566665     566669     5
> 
> 566671      566672     2
> 
> 566680       566680     1
> 
> 
> Thank you.
> .
> 
0
Utf
4/2/2010 9:07:01 AM
You might try this untested idea.

Find the beginning of a sequence
SELECT A.Number
FROM Table as A LEFT JOIN Table as B
ON A.Number = B.Number-1
WHERE B.Number is Null

Find the End of a sequence
SELECT A.Number
FROM Table as A LEFT JOIN Table as B
ON A.Number = B.Number+1
WHERE B.Number is Null

SELECT qBegin.Number as StartofRange,
Min(qEnd.Number) as EndOfRange,
1+QBeginNumber-Min(qEndNumber) as Quantity
FROM qBegin INNER JOIN qEnd
ON qBegin.Number <= qEnd.Number
GROUP BY qBegin.Number


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Angela wrote:
> Hi,
> 
> I have a below list of numbers.
> 
> 566667
> 566668
> 566669
> 566665
> 566666
> 566671
> 566672
> 566680
> 
> 
> I want a query that would return a count between start and end of
> range.
> 
> Like
> 
> Start        End         Quantity
> 566665     566669     5
> 
> 566671      566672     2
> 
> 566680       566680     1
> 
> 
> Thank you.
0
John
4/2/2010 8:11:21 PM
I should have tested first.  Here is a query that seems to work.

My test table was named tNumbers and my field was named Counter.


SELECT QStart.Counter as StartNumber,
Min(qEnd.Counter) as EndNumber,
Min(qEnd.Counter) - qStart.Counter + 1 as Amount
FROM
    (SELECT A.Counter
     FROM tNumbers as A LEFT JOIN tNumbers as B
     ON A.Counter = B.Counter+1
     WHERE B.Counter Is Null) As QStart
INNER JOIN
     (SELECT A.Counter
      FROM tNumbers as A LEFT JOIN tNumbers as B
      ON A.Counter = B.Counter-1
      WHERE B.Counter Is Null) As qEnd
ON qStart.Counter <=qEnd.Counter
GROUP BY qstart.Counter

When I tested I found that I had reversed the queries that got the begin and 
end of the sequence. AND I had the math to calculate quantity wrong.

If your table and field names don't follow the naming guidelines you will 
probably have to nest queries instead of using sub-queries in the from clause. 
  Table and field names should start with a letter and only contain letters, 
numbers, and the underscore - no spaces or other characters.


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

John Spencer wrote:
> You might try this untested idea.
> 
> Find the beginning of a sequence
> SELECT A.Number
> FROM Table as A LEFT JOIN Table as B
> ON A.Number = B.Number-1
> WHERE B.Number is Null
> 
> Find the End of a sequence
> SELECT A.Number
> FROM Table as A LEFT JOIN Table as B
> ON A.Number = B.Number+1
> WHERE B.Number is Null
> 
> SELECT qBegin.Number as StartofRange,
> Min(qEnd.Number) as EndOfRange,
> 1+QBeginNumber-Min(qEndNumber) as Quantity
> FROM qBegin INNER JOIN qEnd
> ON qBegin.Number <= qEnd.Number
> GROUP BY qBegin.Number
> 
> 
> John Spencer
> Access MVP 2002-2005, 2007-2010
> The Hilltop Institute
> University of Maryland Baltimore County
> 
> Angela wrote:
>> Hi,
>>
>> I have a below list of numbers.
>>
>> 566667
>> 566668
>> 566669
>> 566665
>> 566666
>> 566671
>> 566672
>> 566680
>>
>>
>> I want a query that would return a count between start and end of
>> range.
>>
>> Like
>>
>> Start        End         Quantity
>> 566665     566669     5
>>
>> 566671      566672     2
>>
>> 566680       566680     1
>>
>>
>> Thank you.
0
John
4/2/2010 8:38:07 PM
Hello Alex..

Apologies for late reply..

Well you code works. It creates a table "tblStartEndResults" and is
counting between start and end values from a list in table
"tblStartEndTest".

I have start and end values as text since some figures start with a
zero. With your script, we are dealing the values as numbers and not
text.

What I think, we can do the calculation based on your fields which
should be read from the text fields as numbers and in the
"tblStartEndResults", we can show the corresponding original text
fields also.

Like  for instance...

setfield    txtfield
34               034
35               035
36               036
37               037
41               041
42               042
40               040

-----------------------------------
Result
serstart	serend	textfieldstart	textfieldend	      sercount
34	          37			034                037                      4
40	          42			040                042                      3


We could also just use serstart & serend for calculation and hide them
in result query by just showing the rest of the fields.
textfieldstart	textfieldend	      sercount
034                037                      4
040                042                      3


Hope I have managed to explain it clearly.
0
Angela
4/25/2010 8:30:42 AM
Hey John,

Your query approach is good but I'm still checking it with the text
field output.

Will get back to you on this one.
0
Angela
4/25/2010 8:39:32 AM
Reply:

Similar Artilces:

Edit Pie Chart Data Label
I am using a pie chart to detail a subset of data and I am having trouble getting the chart to display the data I need. For example: Fruit equals 25% of overall diet. Within fruit I ate: Fruit # Eaten % of Overall Diet Apples 10 10% Oranges 8 8% Pears 7 7% I want to graph fruit consumption in pie chart and add all information to the data label using a link. I get this to work (click on data label and add location of cells), but I can't get it to display properly. I am getting Apples 10 10% but I...

How do I create a combination chart and table with different data.
I am using Windows XP, and Microsoft Excel 2003. I have been asked to create scorecards with charts displaying rates graphically, and an attached table underneath with numerator and denominator data. The combination graphs in the custom charts try to graph and display all the data. Can I create a combination chart with the rates graphically displayed and the underlying numerator/denominator data presented in a linked table underneath the chart? Or do I need to create the graph and table separately? You would do better to make a separate chart and table, because chart data tables ...

Use first day of month formula in a drop down list
I'm using the formula =DATE(YEAR(B4),MONTH(B4),1) where cell B4 = Today() and this works to give me the 1st day of the current month. My question is: How do I use this in a drop down list on a separate worksheet in the same workbook? I have tried to name the cell containing the 'date' formula, but it looks like Excel does not allow naming a cell with a formula. Each time I name the cell (select the cell, click on the 'name box', type name, enter) when I re-select the cell it hasn't changed the cell name. It defaults back to the row/column name. Than...

Count off calculated fields
We run Access 2003 I have a report for salespersons which lists all salepersons and the total sales each one made for each week of the month. There are 5 fields on the line, one per week of the month. E.g. John Doe Wk1Sales Wk2Sales Wk3Sales Wk4Sale Wk5Sales Each of the Wk#Sales textboxes get fed by a function that runs a dsum function over the database. All sales persons will appear whether or not they met the quota for every week of the month How do I calculate the number of salespersons who met their weekly quota for ALL weeks of the month. Using the average o...

Can a mouseover reveal associated data in charts?
I want the associated cost display on the chart when you mouseover each data point (representing number of units). Does anyone know how to do this? -- JLC ------------------------------------------------------------------------ JLC's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=28014 View this thread: http://www.excelforum.com/showthread.php?threadid=475576 Hi! Mouseover on my (XL2003) charts always shows values (and a bit more: x-coordinate and series name). I seem to recall it did in XL200 and XL97, too. Alf -- AlfD --------------------------------...

Related Data Between 2 Different Workbooks
Hello everyone, I need to create 2 workbooks, which have data relationships. I don't know how to get data connect when I change data in the first workbook, this data will also be automatically changed in the second workbook. Please give me a hand. Thank you very much and I am looking forward to hearing from you. Regards, Peter -- freeecoom ------------------------------------------------------------------------ freeecoom's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=27507 View this thread: http://www.excelforum.com/showthread.php?threadid=470378 On...

Report List not displayed
Hi, When I go to the reports menu - Reports Section -> Account Reports - the report list is not displayed. There is just a blank page. Does anyone know why the reports are not displayed? Thanks Emma Its OK, they are back. I had to restart the Crystal Services. Emma >-----Original Message----- >Hi, >When I go to the reports menu - Reports Section -> >Account Reports - the report list is not displayed. >There is just a blank page. > >Does anyone know why the reports are not displayed? > >Thanks >Emma >. > ...

Compare a range of cells
How can i compare a range of cells an have them "reorganized" so that the one that correspond is listed on the same Row? AAA-AAA BBB-CCC CCC-DDD EEE-EEE become: AAA-AAA BBB- CCC-CCC -DDD EEE-EEE Two columns? Add a header to row 1 and try this macro that I've saved this from a few previous posts: Option Explicit Sub testme() Application.ScreenUpdating = False Dim wks As Worksheet Dim ColA As Range Dim ColB As Range Dim iRow As Long Dim myCols As Long Set wks = Worksheets("sheet1") wks.DisplayPageBreaks = False Wit...

Data Label Format
I have to data labels on a chart that overlap. Is it possible to wrap the text or change the data label box size so both labels are readable? Thanks for any help. Cody ...

Adding in data based on dates
If I have a historical file in access, and then I recieve a file every week that has the past 17 days. Is there a way to get access to merge JUST the past days that are not included in the history. So you have a report that is from 2/7/05 through 2/7/08. I recieve a report for 1/28/08 through 2/14/08. I want to just add on the 2/8/08 through 2/14/08 data. Is that possible? How can I do this? Schwimms, You should be able to determine the latest date in your History table. DMax("[HistoryDate]", "tblHistory") Then filter your import with a Date criteria...

input a signal into a cell and count
I wish to detect a signal in to an excel cell via a parallel port, hopefully I can then count the number of pulses over a period of time. Any help would be much appreciated RL, Get a driver/Excel template from your hardware vendor, or use software like http://www.softwarewedge.com/ HTH, Bernie MS Excel MVP "RL" <RL@discussions.microsoft.com> wrote in message news:34C41879-7972-49EA-9402-C8317DE34F38@microsoft.com... >I wish to detect a signal in to an excel cell via a parallel port, hopefully > I can then count the number of pulses over a period of time. Any help...

How to export/import an existing Junk email list.
Does anyone know how to export/copy and existing Junk email senders list from one computer to another. I have looked in the registry and on the hard drive and cannot determine where they store the addresses added to this list. Thanks. Thanks Chris. I should have searched the entire drive reather than "Program Files". Thanks for your help. >-----Original Message----- >Using FileMon and Regmon from www.sysinternals, I was >able to monitor both the file system and the registry >while adding a user to my Junk Email list. It appears >that the entry in added to ...

I can't open Office Data File from CDs #2
Dear technical experts, I have been facing a security problem with Microsoft Outlook files. I exported Outlook items into Office outlook files (with the extension is .pst). The exported files were firstly saved on my computer (different path and location in comparison with default path of Office Outlook files in Documents and Settings). I can open my exported files easily. Because the size of outlook items is very large, I would like to save Office outlook files on another storage device in stead of using hard drives of my computer. I made copy these files to a flash drive (USB dri...

Values in List Box
Hello, I have a multi select list box control on a form and I need to return the values of highlighted multi select items. Code With Me.lstExtractRouteMulti For Each varItem In .ItemsSelected If Not IsNull(varItem) Then Debug.Print End If Next End With In this, I need to return the values that are selected in the list box. Using Me.lstExtractRouteMulti.Value returns Null Thanks. Using Me.lstExtractRouteMulti.Value returns Null Yes it does because it is a multi select list box. In the code you posted, yo...

Charting Selective Data
I am trying to create a chart of monthly percent gain across a given year, and I have separated the data into two rows: Jan Feb Mar Apr ........... Dec #N/A #N/A 5% 12% .......... 0% The #N/A signifies that there is no data for the given months, and the 0% signifies that the month is not completed. They are a result of the formula being used to grab the data. I'm not sure of what method is the best, but I simply want a chart that only displays the months that contain a percent value (other than 0%). Any help would be greatly appreciated with this problem. Than...

Identify Whole Number
How can excel identify a number when it is whole or it has decimals. I am doing an IF function. E.I. If(A1=WHOLE NUMBER, YES, NO) Is there some way? =IF(A1=INT(A1),TRUE,FALSE) -- Best Regards, Luke M "ileanardz" <ileanardz@gmail.com> wrote in message news:cf438d6d-b2c8-4191-961f-69909be1a53a@g28g2000yqh.googlegroups.com... > How can excel identify a number when it is whole or it has decimals. > > I am doing an IF function. > > E.I. > If(A1=WHOLE NUMBER, YES, NO) > > Is there some way? =IF(INT(A1)=A1,"YES",&...

Payables MGmnt GP 10 year end close
HI All, Does anyone know what the steps are to clsoe AP, I don't think you need to do a hard close, my problem is this I set up 2010 fiscal periods and had they a/p clerk post a/pto 2010, problem is when i go into financial inquiry detail or summary I have no transactions for 2010 even though posting date was 2010, is there another steop i must do in order for 2010 transactions to show up? Can some on help us, my email; is jims@em-powered.com any help would be great, Thanks Hi Jim, Have you looked into any unposted batch in GL? The resulting GL batch remained unpost...

import data from goldmine
Hi, Does anyone know how to import data (customer contact) from goldmine to CRM? Thank you very much. Yes, we used Scribe data importing tool. www.scrobesoft.com Cheers Kyaw "Qnguyen" <Qnguyen@discussions.microsoft.com> wrote in message news:809E43DE-7D38-45A7-95B4-BD24B66A2434@microsoft.com... > Hi, > Does anyone know how to import data (customer contact) from goldmine to > CRM? > Thank you very much. Hi Qnguyen, A bit of a typo.it is www.scribesoft.com . What are the things which you want imported from Goldmine? If it is not too exhaustive then you...

Word was unable to open the data source
My problem is that my Excel data is not being "mail merged" into Word. I followed the suggestion of prompting the data source before opening and I can choose "MS Excel Worksheet via DDE (*.xls) when prompted. Next I choose th named or cell range which is "Entire Spreadsheet". Then in lowr left task bar it briefly says "Opening file . . . ." but then I am prompted with Word error "Word was unable to open the data source." If I try Office 2007 I use "OLE DB Database Files" when prompted to open data source. Then I select t...

Converting mysql query result to xml
Hi all, I'm a newbie in .net xml programming, so please be patient. And sorry for my uncorrect english, too. I'm going to explain my problem: I've built a web service which responds to ferries timetable requests. It receives an xml document with this format: <TimeTableRequest> <Routes> <Route> <Company></Company> <DeparturePort>ptf</DeparturePort> <ArrivalPort>pio</ArrivalPort> <Date>2006-12-20T13:00:00</Date> </Route> <Route> <Company></Company> ...

number entry
when i enter a number in a cell in excel 2003 worksheet appear in the cell as the number divide par 1000 e.g. I enter the number 1 in a cell when I <ENTER>, in the cell appear 0,001 Who can my help Goto Tools>Options and on the Edit tab there is a checkbox for Fixed Decimal. Uncheck that box. -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "nieuws" <frits.vandezande@telenet.be> wrote in message news:xBTah.208347$d36.3120290@phobos.telenet-ops.be... > when i enter a number in a cell in excel 2003 worksheet appear in...

Excel changes the values of my numbers
I have coded a simple user form in Excel that calculates a balance an assigns the proper values to cells in a worksheet. What I don' understand is that when I enter a number into the user form, the valu of that number changes once it gets assigned to the cell in th worksheet. For example, if I enter the number 542.73 ,the user for assigns the number to the proper cell in the worksheet but it shows u as 542.72998046875. It should show up as 542.73 because that's what entered. I am calculating a running balance so my calculations aren' coming out right. Sometimes they are off ...

Data Entry problem #2
When I enter numbers into spreadsheets they get changed to two decimal points (e.g. 250 = 2.5). I know there must be some global setting, but I don't know where. Can anyone help? one way; Ctrl+A to select the entire sheet. Then format>cells>number tab Alternative; Tools>options>Edit tab>uncheck the fixed decimals box HTH "Bob Shepard" wrote: > When I enter numbers into spreadsheets they get changed to two decimal points > (e.g. 250 = 2.5). I know there must be some global setting, but I don't know > where. Can anyone help? Hi Bob Just had...

Best practice to modify list during iteration
Hi, I'm writing a scheduling class. On each clock tick, I scan the trigger list to see which if any triggers have been activated. If so, execute the callback and if it is a periodic trigger to reinsert it in the list at some future time. So it's a matter of removing and possibly adding the trigger within the same function. I don't that you can modify the list while enumerating it - ie: psuedocode foreach (trigger in triggers) { if (reachedtime) deleteit if (trigger is Periodic) reinsert in list) } so I scan the list, find the active triggers, use trigg...

Add Item to Data List that has Dynamic Source
I have a dynamic drop down list that refers to a long column of names, sorted alphabetically. The list will size itself depending on the letters entered into a reference cell (A1). ResList is a named range that references to the column origin, and ResName is the dynamic named range of the column of names. The source equation is this: =OFFSET(ResList,MATCH(A1&"*",ResName,0),0,COUNTIF(ResName,A1&"*"),1) Question: is it possible to insert a blank as the first selectable item in the resulting list - without VB? -- Kind regards Rik A possible o...