help counting how many values are within a group

[fbm2themex]

I am trying to count/find the page count. or more like to find how many pages(N's) are within Y(each Y)..

example:

(Col1) (Col2) (what im trying to get in the nxt col)
1 Y 3
2 N
3 N
4 Y 1
5 Y 5
6 N
7 N
8 N
9 N
10 C 1

in excel..the formula is:
=IF(B1="Y",MATCH("Y",B2:$B$10,0),"")
now, im trying to figure out a query in access...not sure how tho...

 

[Remou]

Do you have any other columns that indicate groups?

[tt]
C1 C2 S Group
1 Y 3 1
2 N 1
3 N 1
4 Y 1 2[/tt]

If not, you will have to write a sub to loop through the recordset, which is more spreadsheet than database.

 

[fbm2themex]

no, just C2.
so i cant do this in access?

 

[Remou]

You can, but it is quite clunky. A database should not be used in the same way that a spreadsheet is used. You may wish to read

http://www.r937.com/relational.html

The logic of doing what you want would run something like:

Open recordset ordered by col1
While not eof
If col2=y, counter = 1
If col2=n, counter = counter+1

 

[fbm2themex]

not to sound like a noob, but where do i go to open recordset?

 

[Remou]

You will find it in Help under OpenRecordset.