For some reason, this script was able to give me a total of cases closed
and/or terminated. I could use that and thank you for your help.
I'm going back to the basics with 1 field and hopefully I will overcome this
.....
To start things small. I created a new query with one field which is the
"DATE" field. The format is set to Long Date. It will spell out the day of
the week, month, day, & year. Using the window, I selected "total by count"
drop down and as a criteria I typed "Is Null". I know I have over 500
records that have no entries and when I run it, it doesn't show anything.
I also have the same issue with the "Yes/No", "On/Off", etc... I need to
count blanks on that one also.
Eventually, I like to combine those two, but I can not even get one to work
for me.
Thank you!
Michel Walsh said:
SELECT COUNT(*)
FROM tableName
WHERE (Closed IS NULL) AND Terminated
would do. Note the use of AND instead of OR. If you need the two counts in
one query, use two iffs:
SELECT SUM(iif( (Closed IS NULL) OR Terminated,1, 0) ) AS
NotClosedORchecked,
SUM(iif( (Closed IS NULL) AND Terminated,1, 0) ) AS
NotClosedANDchecked
FROM tableName
Hoping it may help,
Vanderghast, Access MVP
TomP said:
Woops!!!! I meant to say the total should be 3. Overall, I was hoping to
present a visual of what I want to explain. The rule should be to count
only the checkmarks that do not have a date entered in the other field.
Thank you
KARL DEWEY said:
Is this the NEW, NEW math? A count of 4 with only 3 records?
--
KARL DEWEY
Build a little - Test a little
:
Hopefully, I can give you a visual on what I'm trying to explain. Here
it
goes....
Closed Terminate
12/3/2007 not checked
11/1/2007 checked
checked
My official total count should be four (4). Even though there is no
date
on one record, the check mark indicates that the record was terminated
via
other explanation. Is there a better method than what I set up?
Thank you,
:
SELECT COUNT(*), COUNT(*) - COUNT(Closed)
FROM tableName
WHERE (Closed IS NULL) OR Terminated
COUNT(*) returns the number of records. COUNT(fieldName) returns the
number
of row having a NOT NULL value for the given field name (after the
WHERE
clause elimination of some records, as usual). So... COUNT(*) -
COUNT(fieldName) returns the number of records having a null value
in the
given field name.
Hoping it may help,
Vanderghast, Access MVP
I'm almost there. What I like to add on top of that statement is
to count
the yes/no "Terminated" fields that have no date in the closed
field tabs.
Thank you,
:
Try:
SELECT COUNT(*)
FROM tableName
WHERE (Closed IS NULL) OR Terminated
in SQL view, not in graphical view.
Vanderghast, Access MVP
I entered the info in the query design mode and could not run it.
:
Try this --
SELECT Sum(IIf([Closed] Is Not Null Or [Terminated]=-1,1,0)) AS
Expr1
FROM TomP;
--
KARL DEWEY
Build a little - Test a little
:
Here are the fields I need to manipulate
Field 1: Account (Text field with validation rule)
Field 2: Closed (Date field)
Field 3: Termination (Yes/No)
I want to be able to get a total count of records not closed
or
terminated.
The condition should be to count everything except for the
ones that
are
"Closed" and also marked for "Termination".
My concern is that if the termination field is "checked" ...
it may
or
may
not have an entry in the closed field which may count the
records
twice.
The termination field is used because the case may have been
forwarded
or
terminated for other reasons which is entered in a separate
field
for
"comments".
Any help would be appreciated.
