table relation problem

[craigK]

I have two tables related. I need to know the total of records in table "A" when a certain condition is met.. Like this..

Table A Table B
Status RowID RowID
A 1 1
B 2 1
C 3 1
2
3
3

I want to know how many records in table &quot;A&quot; <> value C.
In my example it should be 2.. But when I relate the tables I get 4.. One for every matching record in table &quot;B&quot;.
I know there must be an easy way to do this, but I can't see the answer..

 

[mshetti]

Dear craigk

Can you clarify this a bit more...

&quot;I want to know how many records in table &quot;A&quot; <> value C.&quot;

Bye
Mahesh
mshetti@yahoo.com

 

[craigK]

In my example. There would be 2.. Record values A & B.

 

[rogar]

You could use a Running Total field on {TableA.RowID} with Type = &quot;distinct count&quot; and Evaluate on Use a Formula: {Status} <> 'C'. This assumes that there are no duplicate values of RowID in Table A. If this is not true, you need to distinct-count a field in Table A which is unique.

 

[craigK]

Thanks, that worked perfectly...