how to combine each record into a range?

how to combine each record into a range?
Table1
Day Value
1       0
2       0
3       0
4       1
5       1
6       1
7       0
8       0
9       0
10     0
11     1
12     1
13     0
14     1
15     0
16     0
17     1
18     1
19     0
20     1
..        .
..        .
..        .
1000  1

how to covert the above table into:
DayStart  DayEnd  Value
1                   3      0
4                   6      1
7                   10     0
11                 12     1
13                 13     0
14                 14     1
15                 16     0
17                 18     1
19                 19     0
.... 

thanks,

pemt    
0
Utf
3/23/2010 3:13:02 PM
access 16762 articles. 3 followers. Follow

3 Replies
449 Views

Similar Articles

[PageSpeed] 45

Use these two queries --
     pemt_1 --
SELECT Q.Day, Q.Value,  (SELECT COUNT(*) FROM [pemt] Q1 
WHERE Q1.[Day] > Q.[Day] 
AND Q1.[Value] <> Q.[Value] )+1 AS Rank
FROM pemt AS Q
ORDER BY Q.Day;

SELECT Min(pemt_1.Day) AS DayStart, Max(pemt_1_1.Day) AS DayEnd, pemt_1.Value
FROM pemt_1 INNER JOIN pemt_1 AS pemt_1_1 ON (pemt_1.Rank = pemt_1_1.Rank) 
AND (pemt_1.Value = pemt_1_1.Value)
GROUP BY pemt_1.Value, pemt_1.Rank
ORDER BY Min(pemt_1.Day);

-- 
Build a little, test a little.


"pemt" wrote:

> how to combine each record into a range?
> Table1
> Day Value
> 1       0
> 2       0
> 3       0
> 4       1
> 5       1
> 6       1
> 7       0
> 8       0
> 9       0
> 10     0
> 11     1
> 12     1
> 13     0
> 14     1
> 15     0
> 16     0
> 17     1
> 18     1
> 19     0
> 20     1
> .        .
> .        .
> .        .
> 1000  1
> 
> how to covert the above table into:
> DayStart  DayEnd  Value
> 1                   3      0
> 4                   6      1
> 7                   10     0
> 11                 12     1
> 13                 13     0
> 14                 14     1
> 15                 16     0
> 17                 18     1
> 19                 19     0
> ... 
> 
> thanks,
> 
> pemt    
0
Utf
3/23/2010 7:15:01 PM
Karl,

Thanks for your great help always!

pemt

"KARL DEWEY" wrote:

> Use these two queries --
>      pemt_1 --
> SELECT Q.Day, Q.Value,  (SELECT COUNT(*) FROM [pemt] Q1 
> WHERE Q1.[Day] > Q.[Day] 
> AND Q1.[Value] <> Q.[Value] )+1 AS Rank
> FROM pemt AS Q
> ORDER BY Q.Day;
> 
> SELECT Min(pemt_1.Day) AS DayStart, Max(pemt_1_1.Day) AS DayEnd, pemt_1.Value
> FROM pemt_1 INNER JOIN pemt_1 AS pemt_1_1 ON (pemt_1.Rank = pemt_1_1.Rank) 
> AND (pemt_1.Value = pemt_1_1.Value)
> GROUP BY pemt_1.Value, pemt_1.Rank
> ORDER BY Min(pemt_1.Day);
> 
> -- 
> Build a little, test a little.
> 
> 
> "pemt" wrote:
> 
> > how to combine each record into a range?
> > Table1
> > Day Value
> > 1       0
> > 2       0
> > 3       0
> > 4       1
> > 5       1
> > 6       1
> > 7       0
> > 8       0
> > 9       0
> > 10     0
> > 11     1
> > 12     1
> > 13     0
> > 14     1
> > 15     0
> > 16     0
> > 17     1
> > 18     1
> > 19     0
> > 20     1
> > .        .
> > .        .
> > .        .
> > 1000  1
> > 
> > how to covert the above table into:
> > DayStart  DayEnd  Value
> > 1                   3      0
> > 4                   6      1
> > 7                   10     0
> > 11                 12     1
> > 13                 13     0
> > 14                 14     1
> > 15                 16     0
> > 17                 18     1
> > 19                 19     0
> > ... 
> > 
> > thanks,
> > 
> > pemt    
0
Utf
3/23/2010 7:43:01 PM
Hi Karl,

One more question: this code run very slow when there are over 1000 ranges, 
is it possible to run every 20 or 100 ranges and combine all ranges finally? 
how to do that?
Thanks,

pemt

"KARL DEWEY" wrote:

> Use these two queries --
>      pemt_1 --
> SELECT Q.Day, Q.Value,  (SELECT COUNT(*) FROM [pemt] Q1 
> WHERE Q1.[Day] > Q.[Day] 
> AND Q1.[Value] <> Q.[Value] )+1 AS Rank
> FROM pemt AS Q
> ORDER BY Q.Day;
> 
> SELECT Min(pemt_1.Day) AS DayStart, Max(pemt_1_1.Day) AS DayEnd, pemt_1.Value
> FROM pemt_1 INNER JOIN pemt_1 AS pemt_1_1 ON (pemt_1.Rank = pemt_1_1.Rank) 
> AND (pemt_1.Value = pemt_1_1.Value)
> GROUP BY pemt_1.Value, pemt_1.Rank
> ORDER BY Min(pemt_1.Day);
> 
> -- 
> Build a little, test a little.
> 
> 
> "pemt" wrote:
> 
> > how to combine each record into a range?
> > Table1
> > Day Value
> > 1       0
> > 2       0
> > 3       0
> > 4       1
> > 5       1
> > 6       1
> > 7       0
> > 8       0
> > 9       0
> > 10     0
> > 11     1
> > 12     1
> > 13     0
> > 14     1
> > 15     0
> > 16     0
> > 17     1
> > 18     1
> > 19     0
> > 20     1
> > .        .
> > .        .
> > .        .
> > 1000  1
> > 
> > how to covert the above table into:
> > DayStart  DayEnd  Value
> > 1                   3      0
> > 4                   6      1
> > 7                   10     0
> > 11                 12     1
> > 13                 13     0
> > 14                 14     1
> > 15                 16     0
> > 17                 18     1
> > 19                 19     0
> > ... 
> > 
> > thanks,
> > 
> > pemt    
0
Utf
3/26/2010 6:06:01 PM
Reply:

Similar Artilces:

Merge records function
Is it possible to reuse the record merge function in CRM. Fx to call the function with a valid Account GUID with a URL-call to mergerecords ? I want this to open the merge windows with the GUID as the master record. ...

Advanced Search Criteria
Just an FYI, since there doesn't seem to be a solution posted anywhere on the web, at least that I could find. If you want to search using dynamic dates, at least in the case of finding items older than a certain relative date, such as more than 30 days old, I used the following criteria successfully in Outlook 2007: Advanced Find > Advanced (tab) > Field: Sent Condition: On or after Value: 30 days ago ...

-1026 JET_errRecordTooBig, Record larger than maximum size
I administer a single Windows 2003 domain, Native Mode only W3k servers and use Exchange 2003. I am seeing this error in all DC Directory Service Event Logs. Event Type: Error Event Source: NTDS SDPROP Event Category: Internal Processing Event ID: 2008 Date: 28/07/2004 Time: 6:38:04 PM User: NT AUTHORITY\ANONYMOUS LOGON Computer: TLSAD1 Description: Internal error: The security descriptor propagation task encountered an error while processing the following object. The propagation of security descriptors may not be possible until the problem is corrected. Object: CN=internal 29176...

Previous Record
How do I access a field from the previous record in a form To programmatically read the value from the previous record, regardless of how the form is sorted or filtered, use the RecordsetClone of the form. This kind of thing: Function GetPreviousValue(frm As Form, strField As String) As Variant On Error GoTo Err_Handler 'Purpose: Return the value from the previous row of the form. 'Keywords: PriorRow PreviousValue Dim rs As DAO.Recordset Set rs = frm.RecordsetClone rs.Bookmark = frm.Bookmark rs.MovePrevious GetPreviousValue = rs(strField) Set rs...

Sheet range reference
I have a workbook with a number of sheets, let's say named sheet01, sheet02 ......sheet10. These sheets are of the same structure and are representing some development in time. So now I would like to make a chart (or separate table) the where the X coordinate is the sheet number and the Y some cell on each of the sheets. Is this possible with a simple reference like the ranges within the same chart or do I really have to create a macro or do it by hand? Jens. Each series must reside on a single sheet. You can create a summary sheet which has something like: A1: =Sheet1!A1 A2: ...

combine 2 vbs into one
on.vbs: Dim fsoSet Set fso = CreateObject("Scripting.FileSystemObject") fso.MoveFile "c:\windows\system32\drivers\etc\POSTS", "c:\windows \system32\drivers\etc\HOSTS" off.vbs: Dim fsoSet Set fso = CreateObject("Scripting.FileSystemObject") fso.MoveFile "c:\windows\system32\drivers\etc\HOSTS", "c:\windows \system32\drivers\etc\POSTS" how do i combine this 2 vbs into 1? "xmd" <fwdsd45@gmail.com> wrote in message news:dcba46df-b0ac-475f-b4fc-8c4ef78b56a3@q22g2000yqm.googlegroups.com... > on.vbs: ...

Code to combine workbooks
Hi, I need to combine several workbooks into one workbook. Say, individual workbooks are named A, B and C, the master workbook is Master. They all have the same format and are stored in directory c:\Data. Please can anyone help and send me the code Thanks Andy Sub GrabData() Dim varr As Variant Dim rng As Range Dim i As Long Dim sh As Worksheet Dim wkbk as Workbook varr = Array("A.xls", "B.xls", "c.xls") Set sh = Workbooks("Master.xls").Worksheets(1) For i = LBound(varr) To UBound(varr) Set wkbk = Workbooks.Open("c:\Data\" & var...

substraction of sequential records
I need to calculate moving ranges in either a report or query. Currently my query returns TestTime (ascending) and TestResult. The moving range is the absolute difference between two consecutive samples. TestTime TestResult MovingRange 8am 20 -- 9am 24 4 10am 21 3 11am 26 5 Once all the moving ranges are calculated, I need to report the average of the moving ranges. ...

Pivot Table Data Ranges
I just upgraded to excel2000, and am finding out that keyboard shortcuts used in 97 do not exist in 2000. Does anyone know of a KEYBOARD shortcut to modify, expand and or create data ranges in the dialog box for entering the range for the pivot. I have many data 'lists' which are added to each month, and when I want to update the corresponding pivot table, I used to simply hit "shift" + "end" + "down arrow" to automatically go to the last populated cell in the list. In 2000, I either have to enter in the cell addy's manually or use the mouse...

How to combine text from 3 cells into 1 cell with space and ", bet
How to combine text from 3 cells into 1 cell with space and ", between. Is it a formala I can use? HI Try this =A1&" "&""""&B1&" "&""""&C1, adjust range to your needs HTH John "JOF" <JOF@discussions.microsoft.com> wrote in message news:3BD2A42E-BCB9-4504-8454-727E366E25DD@microsoft.com... > How to combine text from 3 cells into 1 cell with space and ", between. Is > it > a formala I can use? ...

Field with running count of records
Hi, I have a query with fields such as date, Item Number and Quantity. I want to create an additional field called Count that will act like an autonumber. It will assign a value of 1 for the first record, 2 for the second number etc. Can someone tell me how to do this? Thanks, -- Chuck W There are tons of posts on how to create a "ranking" query. You must have a field or fields that uniquely identify the sort order. If you provided significant table and field names as well as your desired sort order, someone could create the SQL for you if you can't search for and fi...

?-Change data series range as data is entered?
I have a chart which shows a prediction curve and an actual curve. The prediction curve runs out until the end of a project. The actual curve is populated as data is entered. I will be generating a number of these charts from a datatable. I would like to plot the prediction curve for the duration of the project (easy). I would then like to plot the actual curve with only the existing data and not have the line drop to the x-axis at the end. Can I put a formula in the data series "x values" field? I hope that my description makes sense..... Thanks, Carl -- isofuncurves ---...

total of a range of times
This should be simple for some of you, but not for me. I am a runner, I have 4 ind. cells w/my split times. How do I get a total for the tallied times. I'm far from being experienced but am trying to learn..any and all help will be much appreciated. Thanks, Roger Roger To help you in your quest to "try to learn" I will direct you to Chip Pearson's site so's you can learn just about all you'll ever need for Time Calculations. http://www.cpearson.com/excel/datetime.htm#AddingTimes Gord Dibben Excel MVP On Sat, 13 Nov 2004 15:19:02 -0800, "Roger WG" &...

Advance filter not filtering unique records only
Hi, I have a user who has four large lists, he wants to combine them into two master lists; but hide or delete duplicates. I showed him how to use Advance Filter and he said it worked perfectly for combination; but the second worksheet did not filter only unique records. He assured me he followed the directions and click "Unique Records" in the Advance filter dialog box; but it didn't work. Can you guys give me a few more avenues he can pursue: (I suggested misspelled entries, and double-checking his work.) Thanks Kim I agree with you. My bet is that there is a typo or even ...

How do I automatically plot different ranges of data in different.
I need to chart parameters that are a function of three variables in the 2002 version of Excel. I have used the x and y axis to represent 2 variables on an 'xy chart', but require an automatic method to represent the value of the third variable. I have tried the 'bubble-plot', but cannot get sufficient resolution between the data. Is their any way that I may plot the third variable in different colours/shades, that automatically changes for different ranges of the the third variable? THis might help you: http://www.peltiertech.com/Excel/Charts/format.html#CondChart &q...

Default Public View has records but columns are blank
A custom entity has a default list view but the entries in the list do not show any values. There are a number of lines in the list with an icon only. If I try to delete one the message says select an entry before attempting operation. I cannot select the lijne either. Have created many views before and never seen this. Other views are ok? Hi Steve, Try publishing the entity and see if this resolves the problem. HTH, Niths "Steve" wrote: > A custom entity has a default list view but the entries in the list do not > show any values. There are a number of lines in the...

Getting the longest lengh in range of cells
I am trying to get the length of the longest cell in a range and use the array {=len(a1:a1)}. What happens is that it picks up the length of cell A1. Is there a way in a formula to determine the longest cell length in a range, or through VBA. Thanks in advance Hi Try this formula array (please amend with last row number): {=MAX(LEN(A1:A7))} HTH Cordially Pascal "Geoff" <gh@bob.com> a �crit dans le message de news:uMKoCQiSFHA.1176@TK2MSFTNGP10.phx.gbl... > I am trying to get the length of the longest cell in a range and use the > array {=len(a1:a1)}. What happen...

synchronized combo boxes moving between records problem
Sorry if this has been answered, I couldn't find the exact problem elsewhere! I'm only a beginner at this but when i select the right ID from the first combo box, i got it to come up with the correct related IDs in the second combo box , like it's meant to! but when I move to the next record (a new one, or the last one, or anything), the second combo box doesn't retain the record. As in, as you scroll through each record, none of the second combo box 'records' are kept, they are all blank until you click on them, then they come up with your choices, UNLESS you ...

My code stops after the record saves....form never closes
Users are going into to update a number, and they want to close the form before the record gets saved, thus losing their changes. I created an Exit button, which should save the record change and close the form. Here's the code: If Me.Dirty Then RunCommand acCmdSaveRecord End If Forms![frmRMSHours]![Act Hours] = Me![Text9] DoCmd.Close If the record is dirty, they press Exit and the record saves. They have to press Exit again and the form closes. If the record is not dirty, the form will close when pressing Exit. What am I doing wrong? To save ...

Combining certain matrices
Can anyone think of a way to add certain matrices together, depending on some matrix criteria? Situation: X Y Z Bob A 2 3 1 Bob B 4 1 5 Bob C 3 2 6 X Y Z Sue A 4 3 3 Sue B 4 1 5 Sue C 2 2 5 X Y Z Bob A 2 3 1 Bob B 3 5 5 Bob C 5 2 1 X Y Z Sue A 3 3 1 Sue B 4 1 2 Sue C 2 5 6 I want to combine all the matrices that belong to Bob, etc (ie. add all A-X together, all B-X, e...

Select Range
Having trouble selecting several non-contiguous ranges. The spreadsheet I am working with is apprx. 300 rows by 110 columns. The sheet is subtotaled. I need to copy data and move to a summary worksheet. There is a subtotaled section entitled 'Risk'. Within this section, the only data required is Country, Customer, 3Q (orders, revenue, gross margin), and 4Q (orders, revenue, gross margin); columns B, E, AQ, AR, AS, BK, BL, and BM - respectively. The section title is in cell A162 and the data is in A163:CY200. I would prefer to select all of the data from the section at once, but ...

creation of combinations
Divisions Old BA Old CC OLD GL No. New BA NEW CC New GL XYZ 123 1000 37000 500B 10000 700000 ABC 235 2000 47500 600C 20008 800000 DEF 370 2500 50000 700D 26007 905250 runs to about 600 lines in each column From the above i need to get all combinations like: Divisions OLD CC & GL NEW CC & GL XYZ 100037000 1000700000 Can anyone advise pls ...

Iterate named range & clear contents
Excel 03 Hi all I have a bunch of named ranges (Insert > Name > Define) which I wan to iterate through, & then delete their contents. I'm trying this : For Each nm in ThisWorkbook.Names nm.ClearContents Next nm can someone please show me the correct way to do this. thanks paul For Each nm In ThisWorkbook.Names On Error Resume Next Range(nm.Name).ClearContents On Error GoTo 0 Next nm -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Paul Wagstaff" <paulwagstaff@blueyonder.co.uk> wrote in mes...

report date range
I have a report that will display data from an inventory transaction history form. There I would enter in the date and several other criteria. After doing so I filter the form to my selection and then print my report. I wanted the date range that is on the form to print on the report. I hvae done this in several other reports and never had a problem until now. I'm getting #NAME. The form is still open upon viewing the report. On the report I have a text box with the logic of: ="Report Date Between: " & [Forms]![frmInvTransactionHistory]![BegtDate] & " and &...

Calculating greatest number in a range
This is a little tricky to explain, but hopefully will make sense. A series of data is produced that monitors concurrent users on a system. This is returned every five minutes of every day. This is on a work book which has a month for each tab. Data typically comes in the following, simple form: Wed Nov 26 10:50:01 GMT 2008 0 Wed Nov 26 10:55:00 GMT 2008 0 Wed Nov 26 11:00:00 GMT 2008 1 Wed Nov 26 11:05:00 GMT 2008 1 Wed Nov 26 11:10:00 GMT 2008 1 Wed Nov 26 11:15:00 GMT 2008 1 Wed Nov 26 11:20:00 GMT 2008 1 Essentially, I need a way of differentiating between indivi...