Complex Count and IIF with two columns

[JH40]

I'm using two fields from the same table, FieldCount and FieldID. If [FieldCount]=2, I want to count the number of IDs found in [FieldID]. And in another column, if [FieldCount]>2, I want to count the number of those IDs found in [FieldID]. Here is the best way I can think of to explain it:

DESIGN VIEW
My.Column1: Count(IIF([FieldCount]=2,Count[FieldID],0)
My.Column2: Count(IIF([FieldCount]>2,Count[FieldID],0)

I know this is terribly wrong, but am unable to determine the correct expression... I'm will do in SQL view if needed. Thanks in advance...

 

[jdraw]

Perhaps you can tell us what you are trying to do in plain english.
Can you give an example of what you are counting?

 

[pr2-eugin]

Hello JH40,

What you are thinking is correct in some way, just replace the count by 1.

My.Column1: Count(IIF([FieldCount]=2,1,Null))
My.Column2: Count(IIF([FieldCount]>2,1,Null))

 

[MSAccessRookie]

I'm using two fields from the same table, FieldCount and FieldID. If [FieldCount]=2, I want to count the number of IDs found in [FieldID]. And in another column, if [FieldCount]>2, I want to count the number of those IDs found in [FieldID]. Here is the best way I can think of to explain it:

DESIGN VIEW
My.Column1: Count(IIF([FieldCount]=2,Count[FieldID],0)
My.Column2: Count(IIF([FieldCount]>2,Count[FieldID],0)

I know this is terribly wrong, but am unable to determine the correct expression... I'm will do in SQL view if needed. Thanks in advance...

I don't know if I am using the correct names, but the following structure might work for you. (Pick only one of the two options in RED).

[B]SELECT[/B] Field1, Field2, { Other Fields }
[B]FROM[/B] YourTable
[B]GROUP BY [/B]{ Other Fields }
[B]HAVING[/B] ((COUNT(Field1) = 2) [COLOR=red][B]And/Or[/B][/COLOR] (COUNT(Field2) > 2))