Total count of multiple fields?... Is that possible?

  • Thread starter Thread starter TomP
  • Start date Start date
T

TomP

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.
 
Try this --
SELECT Sum(IIf([Closed] Is Not Null Or [Terminated]=-1,1,0)) AS Expr1
FROM TomP;
 
I entered the info in the query design mode and could not run it.

KARL DEWEY said:
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


TomP said:
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.
Click to expand...
Click to expand...
 
Try:



SELECT COUNT(*)
FROM tableName
WHERE (Closed IS NULL) OR Terminated


in SQL view, not in graphical view.



Vanderghast, Access MVP



TomP said:
I entered the info in the query design mode and could not run it.

KARL DEWEY said:
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


TomP said:
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.
Click to expand...
Click to expand...
Click to expand...
 
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,

Michel Walsh said:
Try:



SELECT COUNT(*)
FROM tableName
WHERE (Closed IS NULL) OR Terminated


in SQL view, not in graphical view.



Vanderghast, Access MVP



TomP said:
I entered the info in the query design mode and could not run it.

KARL DEWEY said:
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.
Click to expand...
Click to expand...
Click to expand...
 
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


TomP said:
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,

Michel Walsh said:
Try:



SELECT COUNT(*)
FROM tableName
WHERE (Closed IS NULL) OR Terminated


in SQL view, not in graphical view.



Vanderghast, Access MVP



TomP said:
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.
Click to expand...
Click to expand...
Click to expand...
 
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,

Michel Walsh said:
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


TomP said:
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,

Michel Walsh said:
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.
Click to expand...
Click to expand...
Click to expand...
 
You get a result of four as in 2 records with a not null date in Closed, and
2 records (one of them is already included in the first count) which have
checked under terminate?


SELECT COUNT(closed) - SUM(terminate)
FROM yourTableName


The count(fieldName) works as already explained, and the SUM works because
checked == -1 and uncheck == 0, so, -SUM( checked) returns the number of
records where terminate has the value checked.
 
Is this the NEW, NEW math? A count of 4 with only 3 records?
--
KARL DEWEY
Build a little - Test a little


TomP said:
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,

Michel Walsh said:
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


TomP said:
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.
Click to expand...
Click to expand...
Click to expand...
 
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


TomP said:
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,

Michel Walsh said:
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.
Click to expand...
Click to expand...
Click to expand...
 
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


TomP said:
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.
Click to expand...
Click to expand...
Click to expand...
 
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.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Count text values in a Access Field 0
Access Dcount (multiple criteria) 3
counting Null fields 3
Crosstab query totals 3
count number of specific entries but over multiple fields! 9
Instant Totals on Continuous Forms 1
Using one calculation in another 1
Count <>0 not working 3

Back
Top