Let me translate that query for you - first Access adds needless sets of
parentheses, so I'll simplify it
SELECT Count(*) AS NumPairs
FROM Datarange AS t1
WHERE Exists (select * from datarange t2
where t2.idx = t1.idx+1
and t2.ColA = "Xxx"<>False) AND t1.colA="Aaa";
The outer part is
SELECT Count(*) AS NumPairs
FROM Datarange AS t1
WHERE Exists (...) AND t1.colA="Aaa";
So we want to count the # of times Aaa shows up. However, we want to limit
it to the times it is FOLLOWED by Xxx, so the inner part says WHERE Exists
(...) and what must exist is this
select * from datarange t2
where t2.idx = t1.idx+1
and t2.ColA = "Xxx"<>False
which means that anytime Aaa appears, we want to only consider the times
that the row with the next higher Idx (i.e., the next row) must have an Xxx
in it
For your purposes, change DataRange to whatever you name your table. You
have to have a column that numbers the riws. If you don't name it Idx, then
change all the Idx entries to whatever you name your column. Finally, change
all the references to ColA to whatever you name your column of actual data.
Duke Carey said:
OK, so do you know the two values that you want to find in sequence, eh?
I created a new first column for some sample data containing row numbers and
titled Idx, titled your first column ColA, and then saved the file. Then I
opened Access and imported your data into a table named DataRange. Then I
created a query like so using SQL mode:
SELECT Count(*) AS NumPairs
FROM Datarange AS t1
WHERE (((Exists (select * from datarange t2
where t2.idx = t1.idx+1
and t2.ColA = "Xxx"))<>False) AND ((t1.colA)="Aaa"));
Piotrek said:
I'll try to be more precise
I *do not know* how many times Aaa is folowed
by Xxx - that's what I want to know
The fact the sequence appears twice
is just because I gave such example
I have lots of different pairs and I
would like to know how many times given pair occurs within the given range
(it can be more than 8 cells - in fact the matrix has [you - just kidding
] several rows and columns filled with the values).
--
Piotrek
So...you want to know how many times the same 2 values appear
consecutively
exactly twice? In your example, you are counting Aaa followed by Xxx
because
that sequence appears 2 times, is that right?
:
Hello,
I have a little problem with counting pairs of values in vertically
adjacent
cells, hope you can help...
Cells from A1 to A8 contain Aaa, Xxx, Aaa, Xxx, Aaa, Yyy, Bbb, Yyy. I
have
24 similar columns.
Generally, odd rows contain Aaas or Bbbs and even ones - only Xxxs or
Yyys.
Is there a way to count the amount of *pairs* of e.g. Aaas and Xxxs?
(Here:
2 pairs)
I'll be very grateful for any clues...
