Count Formula

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I would like to check if this formula is working well:

SPEC COUNT1: Count(IIf([PCP] In ("Dual - PCP & Specialitst","SPEC -
Specialist Provider") And ([SPEC1] Not In
("OBSTETRICS/GYNECOLOGY","OBSTETRICS","GYNECOLOGY","OBSTETRICS and
GYNECOLOGY","OBSTETRICS & GYNECOLOGY") And [SPECDIR1]="Y"),[PROVNUM],Null))

I should count PROVNUM when PCP feild equal to the above cretieria and Spec1
not like the disgnated specs.
 
It looks ok. Does it give you the expected results?

The only way to know if it is working well is to use it. You might try
limiting the set of records that is returned and hand-counting to see if you
get the expected results.

You could also run a query that gets just the records that match the
criteria in the IIF and see if that number is the same as the what you get
with the expression.

SELECT *
FROM YourTable
WHERE [PCP] In ("Dual - PCP & Specialitst","SPEC - > Specialist Provider")
And ([SPEC1] Not In
("OBSTETRICS/GYNECOLOGY","OBSTETRICS","GYNECOLOGY","
OBSTETRICS and GYNECOLOGY","OBSTETRICS & GYNECOLOGY")
And [SPECDIR1]="Y"

Max said:
I would like to check if this formula is working well:
Click to expand...
SPEC COUNT1:
Count(
IIf([PCP] In ("Dual - PCP & Specialitst","SPEC - > Specialist Provider")
And ([SPEC1] Not In ("OBSTETRICS/GYNECOLOGY","OBSTETRICS","GYNECOLOGY","
OBSTETRICS and GYNECOLOGY","OBSTETRICS & GYNECOLOGY")
And [SPECDIR1]="Y"),[PROVNUM],Null))
 
I do not think its' working? Its off bye 1500 records

John Spencer said:
It looks ok. Does it give you the expected results?

The only way to know if it is working well is to use it. You might try
limiting the set of records that is returned and hand-counting to see if you
get the expected results.

You could also run a query that gets just the records that match the
criteria in the IIF and see if that number is the same as the what you get
with the expression.

SELECT *
FROM YourTable
WHERE [PCP] In ("Dual - PCP & Specialitst","SPEC - > Specialist Provider")
And ([SPEC1] Not In
("OBSTETRICS/GYNECOLOGY","OBSTETRICS","GYNECOLOGY","
OBSTETRICS and GYNECOLOGY","OBSTETRICS & GYNECOLOGY")
And [SPECDIR1]="Y"

Max said:
I would like to check if this formula is working well:
Click to expand...
SPEC COUNT1:
Count(
IIf([PCP] In ("Dual - PCP & Specialitst","SPEC - > Specialist Provider")
And ([SPEC1] Not In ("OBSTETRICS/GYNECOLOGY","OBSTETRICS","GYNECOLOGY","
OBSTETRICS and GYNECOLOGY","OBSTETRICS & GYNECOLOGY")
And [SPECDIR1]="Y"),[PROVNUM],Null))
I should count PROVNUM when PCP feild equal to the above cretieria and
Spec1
not like the disgnated specs.
Click to expand...
Click to expand...
 
Did you try creating a separate query that returns the records you expect?

"Dual - PCP & Specialitst" <-- is this a valid value or is it a typo? If it is
a typo, does it occur in your database or only in your posting?

1500 too many or 1500 too few records?
I do not think its' working? Its off bye 1500 records

John Spencer said:
It looks ok. Does it give you the expected results?

The only way to know if it is working well is to use it. You might try
limiting the set of records that is returned and hand-counting to see if you
get the expected results.

You could also run a query that gets just the records that match the
criteria in the IIF and see if that number is the same as the what you get
with the expression.

SELECT *
FROM YourTable
WHERE [PCP] In ("Dual - PCP & Specialist","SPEC - > Specialist Provider")
And ([SPEC1] Not In
("OBSTETRICS/GYNECOLOGY","OBSTETRICS","GYNECOLOGY","
OBSTETRICS and GYNECOLOGY","OBSTETRICS & GYNECOLOGY")
And [SPECDIR1]="Y"

Max said:
I would like to check if this formula is working well:
Click to expand...
SPEC COUNT1:
Count(
IIf([PCP] In ("Dual - PCP & Specialitst","SPEC - > Specialist Provider")
And ([SPEC1] Not In ("OBSTETRICS/GYNECOLOGY","OBSTETRICS","GYNECOLOGY","
OBSTETRICS and GYNECOLOGY","OBSTETRICS & GYNECOLOGY")
And [SPECDIR1]="Y"),[PROVNUM],Null))
I should count PROVNUM when PCP feild equal to the above cretieria and
Spec1
not like the disgnated specs.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top