IIF Statement

[Guest]

I need help with a query. One field is "Code", the other field is "Account".
What I want to do is enter the criteria [Enter Code: ], then write an IIF
statement ; if the [Enter code:] is in the database then count them, but if
the Code has a duplicate "Account" then count it as one entry. Also, want
another query to be able to [Enter Code: ], then [Enter incidents: ], so the
query will count the code that has more than one incident and so on. Hope
someone can help with this one. I've written several IIF statements, but
this one is stumping me.

 

[Guest]

Susan,

If I understand you correctly, it sounds like you need a Totals query. Make
a query with the columns Code and Account, or your applicable equivalents.
Select View>Totals to show the Totals row. The Totals row will appear with
the default "Group By" in each column. For your case, leave this default
setting. Enter the text [Enter Code:] for Criteria in the Code column. Run
the query. I actually don't see the need for an iif function here.

For your second problem, try this: In a new query insert the Code column and
2 columns containing your Incident field. Select View>Totals again to show
the Totals row. Leave "Group By" under Code, set the first Incident column
to "Where" and enter whatever criteria makes it an incident (true for
example). In the second Incident column set Totals to "Count". If you like
to be prompted, then enter [Enter Code:] under criteria for the Code column.

Cliff

 

[Guest]

Thanks, the info did help some, but here is the real issue.

I have the field "code" and the field "Account". Counting the records is
easy enough, but what I need is this.

Say there are 10 records for code 123, but 5 of the records have the same
account number, so basically, there are only 6 incidents. So if I enter
code 123 into [Enter code: ] I want it to count the incidents not the
records. Or if I have [Enter Incidents: ] then I want it to count any code
that has more than [Enter Incidents: ] I hope this information helps. I'm
just having a heck of a time. Thanks!

Susan,

If I understand you correctly, it sounds like you need a Totals query. Make
a query with the columns Code and Account, or your applicable equivalents.
Select View>Totals to show the Totals row. The Totals row will appear with
the default "Group By" in each column. For your case, leave this default
setting. Enter the text [Enter Code:] for Criteria in the Code column. Run
the query. I actually don't see the need for an iif function here.

For your second problem, try this: In a new query insert the Code column and
2 columns containing your Incident field. Select View>Totals again to show
the Totals row. Leave "Group By" under Code, set the first Incident column
to "Where" and enter whatever criteria makes it an incident (true for
example). In the second Incident column set Totals to "Count". If you like
to be prompted, then enter [Enter Code:] under criteria for the Code column.

Cliff

I need help with a query. One field is "Code", the other field is "Account".
What I want to do is enter the criteria [Enter Code: ], then write an IIF
statement ; if the [Enter code:] is in the database then count them, but if
the Code has a duplicate "Account" then count it as one entry. Also, want
another query to be able to [Enter Code: ], then [Enter incidents: ], so the
query will count the code that has more than one incident and so on. Hope
someone can help with this one. I've written several IIF statements, but
this one is stumping me.

 

[Guest]

Ah ha,

Make one query with columns Code and Account. Enable Totals and leave the
default setting "Group By" in both columns. Type [Enter Code:] for Criteria
in the Code column. Save this query.

Make a second query and place only the above query in the Show Table area.
Do not include the original table. Enter two columns both with the Code
field. Enable Totals again. Leave the first Code column as "Group By", but
change the second Code column to "Count". Also, in the second Code column,
type >[Enter Incidents:] under Criteria. Save and run this query and I think
that should work.

Now if you need to need additional information (fields) associated with any
Code, make a third query. Include the second query mentioned above and the
original table. Link the Code column from the query to the Code column from
the original table.

Cliff

Thanks, the info did help some, but here is the real issue.

I have the field "code" and the field "Account". Counting the records is
easy enough, but what I need is this.

Say there are 10 records for code 123, but 5 of the records have the same
account number, so basically, there are only 6 incidents. So if I enter
code 123 into [Enter code: ] I want it to count the incidents not the
records. Or if I have [Enter Incidents: ] then I want it to count any code
that has more than [Enter Incidents: ] I hope this information helps. I'm
just having a heck of a time. Thanks!

Susan,

If I understand you correctly, it sounds like you need a Totals query. Make
a query with the columns Code and Account, or your applicable equivalents.
Select View>Totals to show the Totals row. The Totals row will appear with
the default "Group By" in each column. For your case, leave this default
setting. Enter the text [Enter Code:] for Criteria in the Code column. Run
the query. I actually don't see the need for an iif function here.

For your second problem, try this: In a new query insert the Code column and
2 columns containing your Incident field. Select View>Totals again to show
the Totals row. Leave "Group By" under Code, set the first Incident column
to "Where" and enter whatever criteria makes it an incident (true for
example). In the second Incident column set Totals to "Count". If you like
to be prompted, then enter [Enter Code:] under criteria for the Code column.

Cliff

I need help with a query. One field is "Code", the other field is "Account".
What I want to do is enter the criteria [Enter Code: ], then write an IIF
statement ; if the [Enter code:] is in the database then count them, but if
the Code has a duplicate "Account" then count it as one entry. Also, want
another query to be able to [Enter Code: ], then [Enter incidents: ], so the
query will count the code that has more than one incident and so on. Hope
someone can help with this one. I've written several IIF statements, but
this one is stumping me.