Query to identify sequences of data

[TheWolfster]

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.

[TheShabz]

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.

[TheWolfster]

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?

[TheShabz]

if youre using it in the wizard, the Like will need () around the "*11111*"

[TheWolfster]

That doesn't seem to be working either.

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

[TheShabz]

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*")

[TheWolfster]

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.

[pbaldy]

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.

[TheShabz]

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

[TheWolfster]

1
0
0
1
1
1
0

Sorry if I didn't make that clear!

[TheShabz]

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

[pbaldy]

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.

[TheWolfster]

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.

[TheWolfster]

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)

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";