Assign a value of 1 for Yes

[Chuck W]

Hi,
I have a query based on a table called HAPU. There is a field called Wound
that has the values Yes, No or Null. The query is used for several other
things and I cannot restrict it or add new queries. What I want to do is to
create a new field called YesCount and have a 1 appear in this field if the
answer to Wound is "Yes". If it is null or No then leaving WoundCount null
is fine. I am exporting this to a system that requires a numeric field to
count the Yeses. Can someone help?

Thanks,
Chuck

 

[Arvin Meyer [MVP]]

Values for a Boolean (yes/no) field in Access are: -1 and 0. If you really
need to use a 1, you can either use (ABS(-1) also ABS([The field value]) or
just multiply the field value by -1 (-1*-1=1)

 

[Al Campagna]

Chuck,Do you mean possible string values of "Yes" or "No" or just blank?

A better solution would be a Boolean (True/False) field, represented by
a checkbox.

As Boolean... True = -1 and False = 0.
And that field can be added directly...
= Sum(Wound) * -1
or
=Abs(Sum(Wound))

No need to pass through another "evaluating" intermediary field.
And no need to mess with nulls...
--
hth
Al Campagna
Microsoft Access MVP 2007-2009
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

 

[Chuck W]

Thanks everyone. I forgot mention that the Wound data is text (Yes or No)
rather than 1 and 0. Kens note addressed this which solved me issue.
Chuck

Chuck:

Assuming the Wound field is a text data type with possible values 'Yes' or
'No', enter the following in the 'field' row of a blank column in query
design view:

YesCount: IIf([Wound] = "Yes", 1, 0)

returning a zero for No or Null is probably a safer bet than returning a Null,
as the latter could possibly cause problems in the system to which its being
exported.

BTW it is possible to count the number of instances of a 'Yes' directly in a
'totals' query with:

SUM(IIf([Wound] = "Yes", 1, 0))

or:

COUNT(IIf([Wound] = "Yes", 1, NULL)

Ken Sheridan
Stafford, England

Hi,
I have a query based on a table called HAPU. There is a field called Wound
that has the values Yes, No or Null. The query is used for several other
things and I cannot restrict it or add new queries. What I want to do is to
create a new field called YesCount and have a 1 appear in this field if the
answer to Wound is "Yes". If it is null or No then leaving WoundCount null
is fine. I am exporting this to a system that requires a numeric field to
count the Yeses. Can someone help?

Thanks,
Chuck

 

[Al Campagna]

Chuck,
Ken is correct... for the way you set it up. But, the way you set it up
is
not the most efficient way to accomplish what you want.
I think most folks (and if I might presume... Ken) would use a Boolean
field to accomplish this function. It's exactly what Boolean types are
for, and
I'd rather simply check a box True or False than have to type "Yes" or "No"
in a
text control each time.

Your not wrong... but it's like using a knife to eat peas... :-D
Good luck,
--
hth
Al Campagna
Microsoft Access MVP 2007-2009
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

Thanks everyone. I forgot mention that the Wound data is text (Yes or No)
rather than 1 and 0. Kens note addressed this which solved me issue.
Chuck

Chuck:

Assuming the Wound field is a text data type with possible values 'Yes'
or
'No', enter the following in the 'field' row of a blank column in query
design view:

YesCount: IIf([Wound] = "Yes", 1, 0)

returning a zero for No or Null is probably a safer bet than returning a
Null,
as the latter could possibly cause problems in the system to which its
being
exported.

BTW it is possible to count the number of instances of a 'Yes' directly
in a
'totals' query with:

SUM(IIf([Wound] = "Yes", 1, 0))

or:

COUNT(IIf([Wound] = "Yes", 1, NULL)

Ken Sheridan
Stafford, England

Hi,
I have a query based on a table called HAPU. There is a field called
Wound
that has the values Yes, No or Null. The query is used for several
other
things and I cannot restrict it or add new queries. What I want to do
is to
create a new field called YesCount and have a 1 appear in this field if
the
answer to Wound is "Yes". If it is null or No then leaving WoundCount
null
is fine. I am exporting this to a system that requires a numeric field
to
count the Yeses. Can someone help?

Thanks,
Chuck

 

[Al Campagna]

Ken,
Agreed... and I understand why you chose to answer within the
structure Steve has currently.
I wasn't sure of Chuck's experience level, and wanted him to consider
Boolean the next time he encountered the same "logical" function in a new
situation.

Your solution is very workable, and he should be good to go...

As always... good chatting with you Ken,
Al Campagna

Al:

I'd agree that a Boolean column would appear to make sense here. The only
reservation I have is that Chuck does have Nulls in the column, which a
Boolean column can't have of course. It doesn't look to me as though
semantically Null differs from No here, but I don't want to presume too
much
as Chuck knows his job far better than I and it may be that there is some
implied difference between No and Null in this case. It there isn't
then,
even if the present structure is adhered to it would be a good idea to
update
all Nulls to No with a simple update query, and then set the column's
Required property to True to disallow Nulls, maybe giving it a
DefaultValue
property of 'No' at the same time. This would remove the semantic
ambiguity
which is a characteristic of allowing Null. The example I always cite of
this is a Null Credit Limit; does the customer have zero credit, unlimited
credit or what? We just can't tell.

Ken Sheridan
Stafford, England

Chuck,
Ken is correct... for the way you set it up. But, the way you set it
up
is
not the most efficient way to accomplish what you want.
I think most folks (and if I might presume... Ken) would use a Boolean
field to accomplish this function. It's exactly what Boolean types are
for, and
I'd rather simply check a box True or False than have to type "Yes" or
"No"
in a
text control each time.

Your not wrong... but it's like using a knife to eat peas... :-D
Good luck,

Thanks everyone. I forgot mention that the Wound data is text (Yes or
No)
rather than 1 and 0. Kens note addressed this which solved me issue.

[quoted text clipped - 45 lines]

Thanks,
Chuck

 

[Al Campagna]

Ken,
Agreed...

I must admit though... that I'm "street" trained in database "theory."
I just got in there and started designing many years ago. I made my
mistakes, and learned from them. I've also broken/bent some rules when
I decided it was... given what I had to work with... the best way to go.
Chuck is probably in that same situation here...

I'll also admit that the "Zen" of Cobb and Date is lost on me.
At times they appear to discuss how many angels can fit on the head of a
pin.

I really only have one "Normalization" or datbase design rule...
"Does it hurt when you do this?"
"Then... don't do that!"
(insert rim shot here!)

I tend to use a more crude method to make decisions of how data should
be organized and controlled.

Given Chuck's example... I find it incongruous that someone...
"might be wounded..." :-D

Regards Ken,
Al

Its good talking with you too, Al. Your posts are always on the ball and
a
pleasure to read.

Another possible option here of course would be to stick with the '3-way
logic' but implement it differently. Its worth remembering that Codd did
support '3-way logic', proposing logical values of True, False and Maybe.
Date points out, somewhat grudgingly, that this implicitly supports the
use
of Null. So if in this case Null is regarded as having a meaning along
the
lines of 'maybe' a column of integer data type could be used, with a
ValidationRule property of:

Is Null Or -1 Or 0

This would mean a check box, with its TripleState property set to true
could
be used as the bound control.

Ken Sheridan
Stafford, England

Ken,
Agreed... and I understand why you chose to answer within the
structure Steve has currently.
I wasn't sure of Chuck's experience level, and wanted him to consider
Boolean the next time he encountered the same "logical" function in a new
situation.

Your solution is very workable, and he should be good to go...

As always... good chatting with you Ken,
Al Campagna

Al:

[quoted text clipped - 41 lines]

Thanks,
Chuck

 

[Al Campagna]

guess that would be 12 Nulls and 1 True in 3-way logic.
Ha!
They were some "hard dudes" in those days!
Al

Al:

I hope you're right about the definition of 'wounded' in the modern army.
But in the First World War my grandfather was hit 13 times in one night
before they decided he was 'wounded' enough to be discharged from duty! I
guess that would be 12 Nulls and 1 True in 3-way logic. He had scars
everywhere, which fascinated me as a kid. He had one right in the middle
of
his hand where a bullet went through and killed his loader (he was a
machine
gunner), and two either side of his neck where a bullet went in one side
and
out the other and missed everything vital. Somebody was sure on his side
that night!

Ken Sheridan
Stafford, England

Ken,
Agreed...

I must admit though... that I'm "street" trained in database "theory."
I just got in there and started designing many years ago. I made my
mistakes, and learned from them. I've also broken/bent some rules when
I decided it was... given what I had to work with... the best way to go.
Chuck is probably in that same situation here...

I'll also admit that the "Zen" of Cobb and Date is lost on me.
At times they appear to discuss how many angels can fit on the head of
a
pin.

I really only have one "Normalization" or datbase design rule...
"Does it hurt when you do this?"
"Then... don't do that!"
(insert rim shot here!)

I tend to use a more crude method to make decisions of how data should
be organized and controlled.

Given Chuck's example... I find it incongruous that someone...
"might be wounded..." :-D

Regards Ken,
Al

Its good talking with you too, Al. Your posts are always on the ball
and
a

[quoted text clipped - 36 lines]

Thanks,
Chuck

 

[David W. Fenton]

As Boolean... True = -1 and False = 0.

In most contexts, True is actually NOT FALSE. And if you write your
code and your SQL to test for NOT FALSE instead of TRUE, it will be
portable since it won't matter if you're using 1 or -1 for True.

 

[Al Campagna]

David
Good point.
Thanks for that info...
Al Campagna

 

[James A. Fortune]

I'll also admit that the "Zen" of Cobb and Date is lost on me.
At times they appear to discuss how many angels can fit on the head of a
pin.

"Eight if they're skinny, four if they're fat." -- Linus (from
Peanuts)

James A. Fortune
(e-mail address removed)