Results 1 to 14 of 14
  1. #1
    TheWolfster is offline Novice
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Feb 2010
    Posts
    25

    Question Query to identify sequences of data


    Hi guys!

    We have a column of data that contains 0's and 1's, we're hoping to write a query that would identify occasions where there are 5 consecutive 1's, one after the other (not interrupted by 0's).

    Could this accomplished with the select statement? If so, would anyone know how to go about that?

    Thanks in advance.

  2. #2
    TheShabz is offline Court Jester
    Windows XP Access 2003
    Join Date
    Feb 2010
    Posts
    1,368
    SELECT *
    FROM myTable
    WHERE myField Like "*11111*"

    That looks for records in myField that contains 5 1's in a row regardless of where in the record it is. Obviously, you're going to replace myTable and myField with your relevant table and field.

  3. #3
    TheWolfster is offline Novice
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Feb 2010
    Posts
    25
    Hmm, I'm getting a

    The syntax of the subquery in this expression is incorrect.

    Ceck the subquery's syntax and enclose the subquery in parentheses.
    I've also tried it as

    SELECT *
    FROM [myTable]
    WHERE [myTable].[myField] Like "*11111*"

    while using the build wizard, but get the same message.

    Thoughts?

  4. #4
    TheShabz is offline Court Jester
    Windows XP Access 2003
    Join Date
    Feb 2010
    Posts
    1,368
    if youre using it in the wizard, the Like will need () around the "*11111*"

  5. #5
    TheWolfster is offline Novice
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Feb 2010
    Posts
    25
    That doesn't seem to be working either.

    I swear, Access' SQL has a mind of its own.

  6. #6
    TheShabz is offline Court Jester
    Windows XP Access 2003
    Join Date
    Feb 2010
    Posts
    1,368
    Odd. Is this embedded within another query? or are there other components to the query? I just made a mock db and tried it. Worked fine for me. try
    WHERE (myTable.MyField) Like ("*11111*")

  7. #7
    TheWolfster is offline Novice
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Feb 2010
    Posts
    25
    It's as simple as can be: it's just querying a table. I too just made a mock db and its giving me the same error.

    Would it be too much to ask for you to send me your mock db?

    I'm beginning to think its an issue with Access 2010. I have 2007 lying around here somewhere.

  8. #8
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Can you clarify if your records looks like:

    1
    0
    0
    1
    1
    1
    0

    or:

    1011101
    0011111
    1100110

    In other words, a single 0 or 1 in each record vs a group of 1's and 0's in each record. My initial thought was the former, the method posted is designed to work with the latter.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  9. #9
    TheShabz is offline Court Jester
    Windows XP Access 2003
    Join Date
    Feb 2010
    Posts
    1,368
    here's the db. I made 2 columns to test against both text and number datatypes. worked for both.

    PBaldy, I'm under the assumption its
    10101101
    rather than
    1
    0
    0
    1
    0

  10. #10
    TheWolfster is offline Novice
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Feb 2010
    Posts
    25
    1
    0
    0
    1
    1
    1
    0

    Sorry if I didn't make that clear!

  11. #11
    TheShabz is offline Court Jester
    Windows XP Access 2003
    Join Date
    Feb 2010
    Posts
    1,368
    SADFACE!!!!!

    If that's the case I think you'd be best suited doing that in Excel. The concept of order as far as records go is irrelevant in Access. A table of data is just that, a table. The order that the data is displayed is arbitrary.

    1
    0
    1
    is the same as
    1
    1
    0

  12. #12
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    You would need to have some field that you could order the records on, and even then I suspect you're looking at VBA code to determine whether there have been 5 in a row or not. I can't think of an SQL method offhand.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  13. #13
    TheWolfster is offline Novice
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Feb 2010
    Posts
    25
    Thanks for the help guys, we're gonna try to do it in Excel. I'll let you know what happens.

    Although, I'm thinking there might be an advance count function of some kind that doesn't require VBA. Like Max and Frequency in Excel.

  14. #14
    TheWolfster is offline Novice
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Feb 2010
    Posts
    25
    So I realize now I didn't mention I had a field that defined the time in a second-by-second manner. Given that, the following bit of SQL does what I intended. Hope it helps others!

    The fields are: ID , TIME, and SEQUENCE (which has all the 1's and 0's)

    Code:
    SELECT x.ID, Min(x.TIME) AS ["FirstPd"], Max(x.TIME) AS ["LastPd"]
    FROM (
    SELECT dat1.[ID], dat1.[SEQUENCE], dat1.[TIME], dat1.[SEQUENCE ], (  
    SELECT count(*)         
    FROM [YOURTABLE] AS dat2        
    WHERE dat2.[ID]  = dat1.[ID]           
    AND dat2.[TIME] <= dat1.[TIME]           
    AND  dat2.[SEQUENCE] <> dat1.[SEQUENCE]      ) 
    AS rungroup 
    FROM  [YOURTABLE] 
    AS dat1)  
    AS x
    WHERE (((x.Expr1001)=1))
    GROUP BY x.ID, x.rungroup
    HAVING (((Count(*))>=3))
    ORDER BY x.ID, "FirstPd";
    Last edited by TheWolfster; 05-25-2010 at 12:30 PM.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Query doesn't return all data
    By hawzmolly in forum Queries
    Replies: 6
    Last Post: 03-26-2010, 09:12 AM
  2. Excluding data in a query
    By lpdds in forum Queries
    Replies: 1
    Last Post: 12-16-2009, 08:06 PM
  3. Replies: 3
    Last Post: 12-10-2009, 02:16 PM
  4. Using lbl file to identify user
    By lynchoftawa in forum Access
    Replies: 1
    Last Post: 06-08-2009, 08:18 PM
  5. data query question
    By mrayray09 in forum Queries
    Replies: 0
    Last Post: 01-20-2009, 08:41 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums