Counting only Yes values from a Yes/No field.

[Guest]

I've asked this question before but the response I received went straight
over my head. I'm a bit of a novice with access and was wondering how I can
count only the positive responses from a yes no field.

I'd be vary grateful if somebody can explain this to me. Thanks alot Ian

 

[Guest]

You don't say where the fields are. I will go on the assumption that you are
using the correct terminology where a field is a property of a table. If you
are talking about a Form, then you would be asking about a control.

So, if it realy is a field in a table:

=DCount("[MyYNField]","MyTableName","[MyYNField] = True")

 

[fredg]

I've asked this question before but the response I received went straight
over my head. I'm a bit of a novice with access and was wondering how I can
count only the positive responses from a yes no field.

I'd be vary grateful if somebody can explain this to me. Thanks alot Ian

Since a Yes/No field's value can only be -1 or 0, all you need do is
add them.
=Abs(Sum([CheckBoxField]))

The ABS() displays the number as positive number.

If you wish to count No's, use:
=Sum([CheckBoxField]+1)

 

[Guest]

The Sum function is only for queries. This seems a hard way to get there.
The Dcount funtion I posted earlier is a better optin (IMHO)

I've asked this question before but the response I received went straight
over my head. I'm a bit of a novice with access and was wondering how I can
count only the positive responses from a yes no field.

I'd be vary grateful if somebody can explain this to me. Thanks alot Ian

Since a Yes/No field's value can only be -1 or 0, all you need do is
add them.
=Abs(Sum([CheckBoxField]))

The ABS() displays the number as positive number.

If you wish to count No's, use:
=Sum([CheckBoxField]+1)

 

[Marshall Barton]

I disagree.

If you want to sum the data displayed in a form/report, it
is difficult to apply the same criteria (used to filter the
form/report's data) to the DSum function. The Sum function
in a text box is far easier and much faster than any other
way of calculating the same result.
--
Marsh
MVP [MS Access]

The Sum function is only for queries. This seems a hard way to get there.
The Dcount funtion I posted earlier is a better optin (IMHO)

Since a Yes/No field's value can only be -1 or 0, all you need do is
add them.
=Abs(Sum([CheckBoxField]))

The ABS() displays the number as positive number.

If you wish to count No's, use:
=Sum([CheckBoxField]+1)

 

[Guest]

Where did the DSum come into the conversation. I originally posted a DCount
solution which will work just fine.

I disagree.

If you want to sum the data displayed in a form/report, it
is difficult to apply the same criteria (used to filter the
form/report's data) to the DSum function. The Sum function
in a text box is far easier and much faster than any other
way of calculating the same result.
--
Marsh
MVP [MS Access]

The Sum function is only for queries. This seems a hard way to get there.
The Dcount funtion I posted earlier is a better optin (IMHO)

On Tue, 21 Jun 2005 08:36:03 -0700, NoviceIan wrote:
I've asked this question before but the response I received went straight
over my head. I'm a bit of a novice with access and was wondering how I can
count only the positive responses from a yes no field.

I'd be vary grateful if somebody can explain this to me. Thanks alot Ian

Since a Yes/No field's value can only be -1 or 0, all you need do is
add them.
=Abs(Sum([CheckBoxField]))

The ABS() displays the number as positive number.

If you wish to count No's, use:
=Sum([CheckBoxField]+1)

 

[Marshall Barton]

It doesn't matter which function.

In a filtered form/report, any aggregate function is better
than its corresponding domain aggregate function for the
same reasons.
--
Marsh
MVP [MS Access]

Where did the DSum come into the conversation. I originally posted a DCount
solution which will work just fine.

I disagree.

If you want to sum the data displayed in a form/report, it
is difficult to apply the same criteria (used to filter the
form/report's data) to the DSum function. The Sum function
in a text box is far easier and much faster than any other
way of calculating the same result.

The Sum function is only for queries. This seems a hard way to get there.
The Dcount funtion I posted earlier is a better optin (IMHO)


On Tue, 21 Jun 2005 08:36:03 -0700, NoviceIan wrote:
I've asked this question before but the response I received went straight
over my head. I'm a bit of a novice with access and was wondering how I can
count only the positive responses from a yes no field.

I'd be vary grateful if somebody can explain this to me. Thanks alot Ian

:
Since a Yes/No field's value can only be -1 or 0, all you need do is
add them.
=Abs(Sum([CheckBoxField]))

The ABS() displays the number as positive number.

If you wish to count No's, use:
=Sum([CheckBoxField]+1)

 

[Guest]

True, but in this case, there is no mention of a form. His basic question was:

how I can
count only the positive responses from a yes no field.

We don't know where he wants to use this. If it is a query or a textbox on
a form or report, then the Sum() would be better than a Domain Aggregate
function, but the Count() would be better because it is straight forward and
does not require an addition function or statement. like:
Abs(Sum([checkfield]))
or
Sum([checkfield]) * -1

I know we all have different styles, so everyone should do what works best
for them, provided it is also easy for the next poor slob that has to
maintain our code to read and understand.

One of my very best friends and I have a long running debate over the use of
IIf.
I like them, provided they are not nested, and he thinks they are evil and
only dastards of the worst sort would sink so low.

It doesn't matter which function.

In a filtered form/report, any aggregate function is better
than its corresponding domain aggregate function for the
same reasons.
--
Marsh
MVP [MS Access]

Where did the DSum come into the conversation. I originally posted a DCount
solution which will work just fine.

I disagree.

If you want to sum the data displayed in a form/report, it
is difficult to apply the same criteria (used to filter the
form/report's data) to the DSum function. The Sum function
in a text box is far easier and much faster than any other
way of calculating the same result.


Klatuu wrote:
The Sum function is only for queries. This seems a hard way to get there.
The Dcount funtion I posted earlier is a better optin (IMHO)


On Tue, 21 Jun 2005 08:36:03 -0700, NoviceIan wrote:
I've asked this question before but the response I received went straight
over my head. I'm a bit of a novice with access and was wondering how I can
count only the positive responses from a yes no field.

I'd be vary grateful if somebody can explain this to me. Thanks alot Ian

:
Since a Yes/No field's value can only be -1 or 0, all you need do is
add them.
=Abs(Sum([CheckBoxField]))

The ABS() displays the number as positive number.

If you wish to count No's, use:
=Sum([CheckBoxField]+1)

 

[Marshall Barton]

True, but in this case, there is no mention of a form. His basic question was:

how I can count only the positive responses from a yes no field.

We don't know where he wants to use this. If it is a query or a textbox on
a form or report, then the Sum() would be better than a Domain Aggregate
function, but the Count() would be better because it is straight forward and
does not require an addition function or statement. like:
Abs(Sum([checkfield]))
or
Sum([checkfield]) * -1

Well, I assumed that since the question was asked in a Forms
newsgroup, the question related to a form situation, but,
you're right, it was never explicitly stated.

As for counting records with True values, you can not use a
simple Count(field) because Count will count all non-Null
values, which includes the records with a value of False as
well as True. If you want to use Count, then one way is to
use an expression such as:
Count(IIf(field, x, Null))
where x can be any non-Null literal.

Personally I think it's clearer, easier and faster to use
Sum as Fred suggested, but that's your call.

 

[Guest]

Marshall,

My original suggestion was a DCount. Are you saying that if a boolean field
were bound to a tri-state control and thus had a null value, that those rows
would be counted with the Trues? What about the Falses?

We all have our preferences and styles. It is good that there is this group
to share and debate information. I have learned a lot here and it has
improved what I do.

True, but in this case, there is no mention of a form. His basic question was:

how I can count only the positive responses from a yes no field.

We don't know where he wants to use this. If it is a query or a textbox on
a form or report, then the Sum() would be better than a Domain Aggregate
function, but the Count() would be better because it is straight forward and
does not require an addition function or statement. like:
Abs(Sum([checkfield]))
or
Sum([checkfield]) * -1

Well, I assumed that since the question was asked in a Forms
newsgroup, the question related to a form situation, but,
you're right, it was never explicitly stated.

As for counting records with True values, you can not use a
simple Count(field) because Count will count all non-Null
values, which includes the records with a value of False as
well as True. If you want to use Count, then one way is to
use an expression such as:
Count(IIf(field, x, Null))
where x can be any non-Null literal.

Personally I think it's clearer, easier and faster to use
Sum as Fred suggested, but that's your call.

 

[Marshall Barton]

A DCount is just a function wrapper around a temporary
query. Except for the criteria issues, the items that are
counted are the same. If performance is not an issue and
leaving aside the complex problems of applying a form's
filtering criteria, the only advantage of using DCount is
that you can use a criteria to select the only the True
values to count.

As with all aggregation functions, except Count(*),
Count(field) and DCount("field"," table") will both count
all non-Null values. So, in the case of a field displayed
as a triple state check box, Count(field) will count both
the True and False values. It will not count the Null
records, nor will it count only the True records (zero is
not the same as Null).

Note that a field displayed as a triple state check box must
be a numeric type such as Integer, because a boolean field
can never be Null.
--
Marsh
MVP [MS Access]

My original suggestion was a DCount. Are you saying that if a boolean field
were bound to a tri-state control and thus had a null value, that those rows
would be counted with the Trues? What about the Falses?

We all have our preferences and styles. It is good that there is this group
to share and debate information. I have learned a lot here and it has
improved what I do.

True, but in this case, there is no mention of a form. His basic question was:

how I can count only the positive responses from a yes no field.

We don't know where he wants to use this. If it is a query or a textbox on
a form or report, then the Sum() would be better than a Domain Aggregate
function, but the Count() would be better because it is straight forward and
does not require an addition function or statement. like:
Abs(Sum([checkfield]))
or
Sum([checkfield]) * -1

Well, I assumed that since the question was asked in a Forms
newsgroup, the question related to a form situation, but,
you're right, it was never explicitly stated.

As for counting records with True values, you can not use a
simple Count(field) because Count will count all non-Null
values, which includes the records with a value of False as
well as True. If you want to use Count, then one way is to
use an expression such as:
Count(IIf(field, x, Null))
where x can be any non-Null literal.

Personally I think it's clearer, easier and faster to use
Sum as Fred suggested, but that's your call.

 

[Guest]

You statement regarding Tri-state is not correct. A tri-state check box will
return -1 for true, 0 for false, and Null for no selection.

I think I may be misunderstanding what you are saying about what will and
will not be counted. If your criteria requests only True, then you will get
a count of records with true in the field. It will not count True and Null
or any other value.

I will let the comment about 0 and null being different go. I'm a bit more
advanced than that.

A DCount is just a function wrapper around a temporary
query. Except for the criteria issues, the items that are
counted are the same. If performance is not an issue and
leaving aside the complex problems of applying a form's
filtering criteria, the only advantage of using DCount is
that you can use a criteria to select the only the True
values to count.

As with all aggregation functions, except Count(*),
Count(field) and DCount("field"," table") will both count
all non-Null values. So, in the case of a field displayed
as a triple state check box, Count(field) will count both
the True and False values. It will not count the Null
records, nor will it count only the True records (zero is
not the same as Null).

Note that a field displayed as a triple state check box must
be a numeric type such as Integer, because a boolean field
can never be Null.
--
Marsh
MVP [MS Access]

My original suggestion was a DCount. Are you saying that if a boolean field
were bound to a tri-state control and thus had a null value, that those rows
would be counted with the Trues? What about the Falses?

We all have our preferences and styles. It is good that there is this group
to share and debate information. I have learned a lot here and it has
improved what I do.

Klatuu wrote:
True, but in this case, there is no mention of a form. His basic question was:

how I can count only the positive responses from a yes no field.

We don't know where he wants to use this. If it is a query or a textbox on
a form or report, then the Sum() would be better than a Domain Aggregate
function, but the Count() would be better because it is straight forward and
does not require an addition function or statement. like:
Abs(Sum([checkfield]))
or
Sum([checkfield]) * -1

Well, I assumed that since the question was asked in a Forms
newsgroup, the question related to a form situation, but,
you're right, it was never explicitly stated.

As for counting records with True values, you can not use a
simple Count(field) because Count will count all non-Null
values, which includes the records with a value of False as
well as True. If you want to use Count, then one way is to
use an expression such as:
Count(IIf(field, x, Null))
where x can be any non-Null literal.

Personally I think it's clearer, easier and faster to use
Sum as Fred suggested, but that's your call.

 

[Marshall Barton]

TriState is a property of a check box control, it does not
apply to a field in a table/query. Furthermore a TriState
checkbox can have any value, limited only by the type of the
numeric (or even a date) field it is bound to, any non-zero,
non-Null value in the bound field will display a check in
the check box.

No, you are not misunderstanding about what will be counted,
I'm just not saying it very well. What I said applies to
both Count(field) and DCount("field", "table") without
criteria. Using criteria on a boolean field, and, once
again, ignoring the complexity of a filtered form/report
recordset,
DCount("field", "table", "field = True")
is equivalent to any of these commonly used aggregates:
Count(IIf(field, x, Null)
Sum(IIf(field, 1, 0)
Sum(IIf(field, 1, Null)
Abs(Sum(field))

I know that you understand all that. The point I have been
trying to address is that it can be very difficult to
specify the proper criteria in a Domain Aggregate function
to match the form/report/query filtering.

And I was really aiming the comment about zero and Null at
other people who may be following this discussion that may
not have made that distinction yet. I apologize, I should
have stated that instead of assuming it would be understood.
--
Marsh
MVP [MS Access]

You statement regarding Tri-state is not correct. A tri-state check box will
return -1 for true, 0 for false, and Null for no selection.

I think I may be misunderstanding what you are saying about what will and
will not be counted. If your criteria requests only True, then you will get
a count of records with true in the field. It will not count True and Null
or any other value.

I will let the comment about 0 and null being different go. I'm a bit more
advanced than that.

A DCount is just a function wrapper around a temporary
query. Except for the criteria issues, the items that are
counted are the same. If performance is not an issue and
leaving aside the complex problems of applying a form's
filtering criteria, the only advantage of using DCount is
that you can use a criteria to select the only the True
values to count.

As with all aggregation functions, except Count(*),
Count(field) and DCount("field"," table") will both count
all non-Null values. So, in the case of a field displayed
as a triple state check box, Count(field) will count both
the True and False values. It will not count the Null
records, nor will it count only the True records (zero is
not the same as Null).

Note that a field displayed as a triple state check box must
be a numeric type such as Integer, because a boolean field
can never be Null.

My original suggestion was a DCount. Are you saying that if a boolean field
were bound to a tri-state control and thus had a null value, that those rows
would be counted with the Trues? What about the Falses?

We all have our preferences and styles. It is good that there is this group
to share and debate information. I have learned a lot here and it has
improved what I do.


Klatuu wrote:
True, but in this case, there is no mention of a form. His basic question was:

how I can count only the positive responses from a yes no field.

We don't know where he wants to use this. If it is a query or a textbox on
a form or report, then the Sum() would be better than a Domain Aggregate
function, but the Count() would be better because it is straight forward and
does not require an addition function or statement. like:
Abs(Sum([checkfield]))
or
Sum([checkfield]) * -1


:
Well, I assumed that since the question was asked in a Forms
newsgroup, the question related to a form situation, but,
you're right, it was never explicitly stated.

As for counting records with True values, you can not use a
simple Count(field) because Count will count all non-Null
values, which includes the records with a value of False as
well as True. If you want to use Count, then one way is to
use an expression such as:
Count(IIf(field, x, Null))
where x can be any non-Null literal.

Personally I think it's clearer, easier and faster to use
Sum as Fred suggested, but that's your call.

 

[Guest]

Okay, Gotcha.
I have never used a check box for other than Boolean fields. If you use it
on a different data type, how will it know when to show checked or unchecked
and what value will it assign for a new record in either case? Would it have
to been done via the contor's Before Update?

TriState is a property of a check box control, it does not
apply to a field in a table/query. Furthermore a TriState
checkbox can have any value, limited only by the type of the
numeric (or even a date) field it is bound to, any non-zero,
non-Null value in the bound field will display a check in
the check box.

No, you are not misunderstanding about what will be counted,
I'm just not saying it very well. What I said applies to
both Count(field) and DCount("field", "table") without
criteria. Using criteria on a boolean field, and, once
again, ignoring the complexity of a filtered form/report
recordset,
DCount("field", "table", "field = True")
is equivalent to any of these commonly used aggregates:
Count(IIf(field, x, Null)
Sum(IIf(field, 1, 0)
Sum(IIf(field, 1, Null)
Abs(Sum(field))

I know that you understand all that. The point I have been
trying to address is that it can be very difficult to
specify the proper criteria in a Domain Aggregate function
to match the form/report/query filtering.

And I was really aiming the comment about zero and Null at
other people who may be following this discussion that may
not have made that distinction yet. I apologize, I should
have stated that instead of assuming it would be understood.
--
Marsh
MVP [MS Access]

You statement regarding Tri-state is not correct. A tri-state check box will
return -1 for true, 0 for false, and Null for no selection.

I think I may be misunderstanding what you are saying about what will and
will not be counted. If your criteria requests only True, then you will get
a count of records with true in the field. It will not count True and Null
or any other value.

I will let the comment about 0 and null being different go. I'm a bit more
advanced than that.

A DCount is just a function wrapper around a temporary
query. Except for the criteria issues, the items that are
counted are the same. If performance is not an issue and
leaving aside the complex problems of applying a form's
filtering criteria, the only advantage of using DCount is
that you can use a criteria to select the only the True
values to count.

As with all aggregation functions, except Count(*),
Count(field) and DCount("field"," table") will both count
all non-Null values. So, in the case of a field displayed
as a triple state check box, Count(field) will count both
the True and False values. It will not count the Null
records, nor will it count only the True records (zero is
not the same as Null).

Note that a field displayed as a triple state check box must
be a numeric type such as Integer, because a boolean field
can never be Null.


Klatuu wrote:
My original suggestion was a DCount. Are you saying that if a boolean field
were bound to a tri-state control and thus had a null value, that those rows
would be counted with the Trues? What about the Falses?

We all have our preferences and styles. It is good that there is this group
to share and debate information. I have learned a lot here and it has
improved what I do.


Klatuu wrote:
True, but in this case, there is no mention of a form. His basic question was:

how I can count only the positive responses from a yes no field.

We don't know where he wants to use this. If it is a query or a textbox on
a form or report, then the Sum() would be better than a Domain Aggregate
function, but the Count() would be better because it is straight forward and
does not require an addition function or statement. like:
Abs(Sum([checkfield]))
or
Sum([checkfield]) * -1


:
Well, I assumed that since the question was asked in a Forms
newsgroup, the question related to a form situation, but,
you're right, it was never explicitly stated.

As for counting records with True values, you can not use a
simple Count(field) because Count will count all non-Null
values, which includes the records with a value of False as
well as True. If you want to use Count, then one way is to
use an expression such as:
Count(IIf(field, x, Null))
where x can be any non-Null literal.

Personally I think it's clearer, easier and faster to use
Sum as Fred suggested, but that's your call.

 

[Marshall Barton]

Any non-zero, non-Null value will be displayed with a check.

If you click on a check box it will set its value to
True (-1), False (0) or, if it's TripleState property is
set, Null. However, the value of the control can be set
using code the same way as any other control, subject to the
constraints imposed by its bound field's data type. (Note
that this can get rather silly when binding a text field to
a check box ;-)
--
Marsh
MVP [MS Access]

Okay, Gotcha.
I have never used a check box for other than Boolean fields. If you use it
on a different data type, how will it know when to show checked or unchecked
and what value will it assign for a new record in either case? Would it have
to been done via the contor's Before Update?

TriState is a property of a check box control, it does not
apply to a field in a table/query. Furthermore a TriState
checkbox can have any value, limited only by the type of the
numeric (or even a date) field it is bound to, any non-zero,
non-Null value in the bound field will display a check in
the check box.

No, you are not misunderstanding about what will be counted,
I'm just not saying it very well. What I said applies to
both Count(field) and DCount("field", "table") without
criteria. Using criteria on a boolean field, and, once
again, ignoring the complexity of a filtered form/report
recordset,
DCount("field", "table", "field = True")
is equivalent to any of these commonly used aggregates:
Count(IIf(field, x, Null)
Sum(IIf(field, 1, 0)
Sum(IIf(field, 1, Null)
Abs(Sum(field))

I know that you understand all that. The point I have been
trying to address is that it can be very difficult to
specify the proper criteria in a Domain Aggregate function
to match the form/report/query filtering.

And I was really aiming the comment about zero and Null at
other people who may be following this discussion that may
not have made that distinction yet. I apologize, I should
have stated that instead of assuming it would be understood.

You statement regarding Tri-state is not correct. A tri-state check box will
return -1 for true, 0 for false, and Null for no selection.

I think I may be misunderstanding what you are saying about what will and
will not be counted. If your criteria requests only True, then you will get
a count of records with true in the field. It will not count True and Null
or any other value.

I will let the comment about 0 and null being different go. I'm a bit more
advanced than that.

:
A DCount is just a function wrapper around a temporary
query. Except for the criteria issues, the items that are
counted are the same. If performance is not an issue and
leaving aside the complex problems of applying a form's
filtering criteria, the only advantage of using DCount is
that you can use a criteria to select the only the True
values to count.

As with all aggregation functions, except Count(*),
Count(field) and DCount("field"," table") will both count
all non-Null values. So, in the case of a field displayed
as a triple state check box, Count(field) will count both
the True and False values. It will not count the Null
records, nor will it count only the True records (zero is
not the same as Null).

Note that a field displayed as a triple state check box must
be a numeric type such as Integer, because a boolean field
can never be Null.


Klatuu wrote:
My original suggestion was a DCount. Are you saying that if a boolean field
were bound to a tri-state control and thus had a null value, that those rows
would be counted with the Trues? What about the Falses?

We all have our preferences and styles. It is good that there is this group
to share and debate information. I have learned a lot here and it has
improved what I do.


Klatuu wrote:
True, but in this case, there is no mention of a form. His basic question was:

how I can count only the positive responses from a yes no field.

We don't know where he wants to use this. If it is a query or a textbox on
a form or report, then the Sum() would be better than a Domain Aggregate
function, but the Count() would be better because it is straight forward and
does not require an addition function or statement. like:
Abs(Sum([checkfield]))
or
Sum([checkfield]) * -1


:
Well, I assumed that since the question was asked in a Forms
newsgroup, the question related to a form situation, but,
you're right, it was never explicitly stated.

As for counting records with True values, you can not use a
simple Count(field) because Count will count all non-Null
values, which includes the records with a value of False as
well as True. If you want to use Count, then one way is to
use an expression such as:
Count(IIf(field, x, Null))
where x can be any non-Null literal.

Personally I think it's clearer, easier and faster to use
Sum as Fred suggested, but that's your call.

 

[Guest]

Sorry guys I always post in the wrong place for some reason when I post a
comment it defaults to Forms and not the subject I was viewing.

The original comment was supposed to be in queries. I apologise as well for
the lack of detail. I have a form which records details of an appointment
and a subform which records activities to be taken following that appointment.

The query I'm trying to make will count all the true values for each
activity in order for us to understand what we do most etc. So I basically
want the query to work out how many times each activity is done following an
appointment.

 

[Marshall Barton]

Sorry guys I always post in the wrong place for some reason when I post a
comment it defaults to Forms and not the subject I was viewing.

The original comment was supposed to be in queries. I apologise as well for
the lack of detail. I have a form which records details of an appointment
and a subform which records activities to be taken following that appointment.

The query I'm trying to make will count all the true values for each
activity in order for us to understand what we do most etc. So I basically
want the query to work out how many times each activity is done following an
appointment.

I don't unerstand why you would want to do that in a query.
The activities subform can do that in a footer text box
using one of the expressions posted elsewhere in this
thread. Note that if you do it in the query, you will have
to use DCount (slow) or make it a totals type query (not
updatable).

 

[Guest]

I work for the NHS and we have to account for everything these days which is
way wee need to know how many of everything we do. I used the

=Abs(Sum([CheckBoxField])) that fred suggested and it works just fine now.
What you mean wont update?

 

[Marshall Barton]

A Totals type (Group By) query is not updatable, because
each record in the query's result set may represent multiple
records in its base table.

Do you want to be able to enter/edit the values in the form
or is this form strictly for viewing the data values? The
answer to that determines if the form needs an updatable
record source query or not.
--
Marsh
MVP [MS Access]

I work for the NHS and we have to account for everything these days which is
way wee need to know how many of everything we do. I used the

=Abs(Sum([CheckBoxField])) that fred suggested and it works just fine now.
What you mean wont update?

I don't unerstand why you would want to do that in a query.
The activities subform can do that in a footer text box
using one of the expressions posted elsewhere in this
thread. Note that if you do it in the query, you will have
to use DCount (slow) or make it a totals type query (not
updatable).

 

[Guest]

What happens is a nurse will have an appointment with a patient in their own
home then when she returns to base she'll hand a form to admin and they'll
simply enter details name address etc then check the relevent boxes for the
activites they did with the patient.

The record wont need to be altered after this initial entry. However every
quarter we have to provide details on what we've been doing etc. This is
where this query comes in we just want to supply the beginning and end dates
of the last quarter and have the details returned of how many of this and
that etc.

A Totals type (Group By) query is not updatable, because
each record in the query's result set may represent multiple
records in its base table.

Do you want to be able to enter/edit the values in the form
or is this form strictly for viewing the data values? The
answer to that determines if the form needs an updatable
record source query or not.
--
Marsh
MVP [MS Access]

I work for the NHS and we have to account for everything these days which is
way wee need to know how many of everything we do. I used the

=Abs(Sum([CheckBoxField])) that fred suggested and it works just fine now.
What you mean wont update?

NoviceIan wrote:
Sorry guys I always post in the wrong place for some reason when I post a
comment it defaults to Forms and not the subject I was viewing.

The original comment was supposed to be in queries. I apologise as well for
the lack of detail. I have a form which records details of an appointment
and a subform which records activities to be taken following that appointment.

The query I'm trying to make will count all the true values for each
activity in order for us to understand what we do most etc. So I basically
want the query to work out how many times each activity is done following an
appointment.

I don't unerstand why you would want to do that in a query.
The activities subform can do that in a footer text box
using one of the expressions posted elsewhere in this
thread. Note that if you do it in the query, you will have
to use DCount (slow) or make it a totals type query (not
updatable).