add yes/no's in access database

[Guest]

I created a database that use's the check box(yes/no) my question is "How can
I
tabulte the yes and no's. example: show if 5 or more meetings were attended,
for use in a query
thanks

 

[Duane Hookom]

I am a bit skeptical about your table structure. However, you can count the
number of Yes values in a Yes/No field using an expression like:
=Abs(Sum([YesNoField]))

 

[Guest]

Duane,
Thanks. I'll figure it out now. This is the first database I attempted do
create..LOL
feeling my way through it .ok so far.
Thanks again

I am a bit skeptical about your table structure. However, you can count the
number of Yes values in a Yes/No field using an expression like:
=Abs(Sum([YesNoField]))

--
Duane Hookom
MS Access MVP
--

I created a database that use's the check box(yes/no) my question is "How
can
I
tabulte the yes and no's. example: show if 5 or more meetings were
attended,
for use in a query
thanks

 

[Guest]

hey there - I'll give you a real simply approach....

a. make a new query in Design view and just add the Name Field and the
yes/no field

b. select the greek sum icon from the tool bar (looks like greek E)

this adds a "Total" row to your query builder
'GroupBy' is the default entry - leave this as-is for the Name field

c. in the yes/no field click into right side of 'Groupby' and a drop-down
will appear - - go ahead and change that to 'Count'

then hit the red ! ......and the result will be the name of all and their
count of Yes

easy to scan down it to see whom is above your minimum criteria......hope
this approach helps....

 

[Guest]

I am a bit skeptical about your table structure. However, you can count the
number of Yes values in a Yes/No field using an expression like:
=Abs(Sum([YesNoField]))

--
Duane Hookom
MS Access MVP
--

I created a database that use's the check box(yes/no) my question is "How
can
I
tabulte the yes and no's. example: show if 5 or more meetings were
attended,
for use in a query
thanks

 

[Guest]

can't seem to get it to function the way I want it to.here's what I have,
name followed by 9 months with yes/no box. I want to add the total months
someone was present at a meeting.
Thanks

 

[Duane Hookom]

Looks like you are "committing spreadsheet". A normalized table would create
one record per meeting attended so you could use a simple SUM or COUNT in a
totals query.

If you can't change your table structure, you can use something like:
Attended: Abs(Nz([Jan],0))+Abs(Nz([Feb],0)) + etc for each month field

 

[John Spencer]

Since these are all yes/no fields wouldn't
Attended: Abs([Jan]+[Feb]+[Mar]+...)
work just as well

Looks like you are "committing spreadsheet". A normalized table would
create one record per meeting attended so you could use a simple SUM or
COUNT in a totals query.

If you can't change your table structure, you can use something like:
Attended: Abs(Nz([Jan],0))+Abs(Nz([Feb],0)) + etc for each month field

--
Duane Hookom
MS Access MVP
--

can't seem to get it to function the way I want it to.here's what I have,
name followed by 9 months with yes/no box. I want to add the total months
someone was present at a meeting.
Thanks

 

[John Spencer]

Pardon me, but your simple approach won't work on yes/no (boolean) fields.
This field type always has a value - either true or false. Since Count
counts the number of times ANY value exists in a field, the count would the
same as the number of rows in the group.

To count true, you can use a formula
Abs(Sum([YesNoField]))

 

[Guest]

Greetings John,
I tried the formula you posted and recieved this pop up msg..calculation is
to complex.try using variable...I'm just an old dog trying to learn a new
trick, this is the first database I attempted to build. to say that I am
confused is putting it mildly..Here is what I want it to do for me.
I have a base of 85 members and we have 9 meetings a year.jan thru may. I
would like to see who(last name) attended how many meetings. they need to
attend at least 5 mtg to be eligble for a scholarship fund drawing.I would
like to see ie:
Blow,Joe 7. is this possible.
Thanks for any assistance you can give. Is there a book on this subject that
would help me?
thanks again
Rob M

Pardon me, but your simple approach won't work on yes/no (boolean) fields.
This field type always has a value - either true or false. Since Count
counts the number of times ANY value exists in a field, the count would the
same as the number of rows in the group.

To count true, you can use a formula
Abs(Sum([YesNoField]))

 

[Duane Hookom]

Because:
1) I was concerned about possible null values
2) I wanted to apply a keystroke tax for creating an un-normalized table
structure ;-)

--
Duane Hookom
MS Access MVP
--

Since these are all yes/no fields wouldn't
Attended: Abs([Jan]+[Feb]+[Mar]+...)
work just as well

Looks like you are "committing spreadsheet". A normalized table would
create one record per meeting attended so you could use a simple SUM or
COUNT in a totals query.

If you can't change your table structure, you can use something like:
Attended: Abs(Nz([Jan],0))+Abs(Nz([Feb],0)) + etc for each month field

--
Duane Hookom
MS Access MVP
--

can't seem to get it to function the way I want it to.here's what I
have,
name followed by 9 months with yes/no box. I want to add the total
months
someone was present at a meeting.
Thanks

:

hey there - I'll give you a real simply approach....

a. make a new query in Design view and just add the Name Field and the
yes/no field

b. select the greek sum icon from the tool bar (looks like greek E)

this adds a "Total" row to your query builder
'GroupBy' is the default entry - leave this as-is for the Name field

c. in the yes/no field click into right side of 'Groupby' and a
drop-down
will appear - - go ahead and change that to 'Count'

then hit the red ! ......and the result will be the name of all and
their
count of Yes

easy to scan down it to see whom is above your minimum
criteria......hope
this approach helps....
--
NTC


:

I created a database that use's the check box(yes/no) my question is
"How can
I
tabulte the yes and no's. example: show if 5 or more meetings were
attended,
for use in a query
thanks

 

[John Spencer]

I'm sure it is possible, but what is your table structure? Is it something
like the following (bad table design)?
MemberLastName
MemberFirstName
Meeting1YN
Meeting2YN
Meeting3YN
....
Meeting9YN

Or is it something else? If it is the way I show above, then the solution I
posted earlier would be what you wanted. In a field in the query grid

Field: NumberAttended: Abs([Meeting1YN] + [Meeting2YN] + [Meeting3YN] +
.... + [Meeting9YN])
Criteria: >=4

That assumes that Meeting1YN, etc are all YesNo (or boolean) fields.

Greetings John,
I tried the formula you posted and recieved this pop up msg..calculation
is
to complex.try using variable...I'm just an old dog trying to learn a new
trick, this is the first database I attempted to build. to say that I am
confused is putting it mildly..Here is what I want it to do for me.
I have a base of 85 members and we have 9 meetings a year.jan thru may. I
would like to see who(last name) attended how many meetings. they need to
attend at least 5 mtg to be eligble for a scholarship fund drawing.I would
like to see ie:
Blow,Joe 7. is this possible.
Thanks for any assistance you can give. Is there a book on this subject
that
would help me?
thanks again
Rob M

Pardon me, but your simple approach won't work on yes/no (boolean)
fields.
This field type always has a value - either true or false. Since Count
counts the number of times ANY value exists in a field, the count would
the
same as the number of rows in the group.

To count true, you can use a formula
Abs(Sum([YesNoField]))

hey there - I'll give you a real simply approach....

a. make a new query in Design view and just add the Name Field and the
yes/no field

b. select the greek sum icon from the tool bar (looks like greek E)

this adds a "Total" row to your query builder
'GroupBy' is the default entry - leave this as-is for the Name field

c. in the yes/no field click into right side of 'Groupby' and a
drop-down
will appear - - go ahead and change that to 'Count'

then hit the red ! ......and the result will be the name of all and
their
count of Yes

easy to scan down it to see whom is above your minimum
criteria......hope
this approach helps....
--
NTC


:

I created a database that use's the check box(yes/no) my question is
"How
can
I
tabulte the yes and no's. example: show if 5 or more meetings were
attended,
for use in a query
thanks

 

[Guest]

John,
I have to Bad design I guess.
lastname
firstname
sept
oct
nov
dec
jan
feb
mar
apr
may
the months are all yes/no.
what I would like is this:
have a report that list the names and number of meetings attened such as:
jacobs,henry 7.
only interested in people that attended 5 or more meetings.
I am grateful for your patients in this matter John. when I try the formulas
you suggest it ask me for paramiters ,I'm lost.
thanks again
Rob m

I'm sure it is possible, but what is your table structure? Is it something
like the following (bad table design)?
MemberLastName
MemberFirstName
Meeting1YN
Meeting2YN
Meeting3YN
....
Meeting9YN

Or is it something else? If it is the way I show above, then the solution I
posted earlier would be what you wanted. In a field in the query grid

Field: NumberAttended: Abs([Meeting1YN] + [Meeting2YN] + [Meeting3YN] +
.... + [Meeting9YN])
Criteria: >=4

That assumes that Meeting1YN, etc are all YesNo (or boolean) fields.

Greetings John,
I tried the formula you posted and recieved this pop up msg..calculation
is
to complex.try using variable...I'm just an old dog trying to learn a new
trick, this is the first database I attempted to build. to say that I am
confused is putting it mildly..Here is what I want it to do for me.
I have a base of 85 members and we have 9 meetings a year.jan thru may. I
would like to see who(last name) attended how many meetings. they need to
attend at least 5 mtg to be eligble for a scholarship fund drawing.I would
like to see ie:
Blow,Joe 7. is this possible.
Thanks for any assistance you can give. Is there a book on this subject
that
would help me?
thanks again
Rob M

Pardon me, but your simple approach won't work on yes/no (boolean)
fields.
This field type always has a value - either true or false. Since Count
counts the number of times ANY value exists in a field, the count would
the
same as the number of rows in the group.

To count true, you can use a formula
Abs(Sum([YesNoField]))


hey there - I'll give you a real simply approach....

a. make a new query in Design view and just add the Name Field and the
yes/no field

b. select the greek sum icon from the tool bar (looks like greek E)

this adds a "Total" row to your query builder
'GroupBy' is the default entry - leave this as-is for the Name field

c. in the yes/no field click into right side of 'Groupby' and a
drop-down
will appear - - go ahead and change that to 'Count'

then hit the red ! ......and the result will be the name of all and
their
count of Yes

easy to scan down it to see whom is above your minimum
criteria......hope
this approach helps....
--
NTC


:

I created a database that use's the check box(yes/no) my question is
"How
can
I
tabulte the yes and no's. example: show if 5 or more meetings were
attended,
for use in a query
thanks

 

[John Spencer]

Well, I can't see what you are doing, so this is tough to work out.

In the SQL view, I would expect to see

SELECT LastName, FirstName,
Abs([Sept] + [Oct] + [Nov] + [Dec] + [Jan] + [Feb] + [Mar] + [Apr] + [May]) As [MeetingsAttended]
FROM [YourTableName]
WHERE Abs([Sept] + [Oct] + [Nov] + [Dec] + [Jan] + [Feb] + [Mar] + [Apr] +
[May]) > 4

If you are being asked for parameters, that indicates that you have a field name
or table name that Access doesn't understand - misspelling, different spelling,
field not really in table, etc. What exactly is the parameter asking you for?

Troubleshooting technique is to try building the query a bit at a time.

Select LastName, FirstName
FROM [YourTableName]

If that works then add in
Abs([Sept])
If that works then add in + [Oct]
If that works then try adding the rest of the months.
If that works try adding the condition in.

John,
I have to Bad design I guess.
lastname
firstname
sept
oct
nov
dec
jan
feb
mar
apr
may
the months are all yes/no.
what I would like is this:
have a report that list the names and number of meetings attened such as:
jacobs,henry 7.
only interested in people that attended 5 or more meetings.
I am grateful for your patients in this matter John. when I try the formulas
you suggest it ask me for paramiters ,I'm lost.
thanks again
Rob m

I'm sure it is possible, but what is your table structure? Is it something
like the following (bad table design)?
MemberLastName
MemberFirstName
Meeting1YN
Meeting2YN
Meeting3YN
....
Meeting9YN

Or is it something else? If it is the way I show above, then the solution I
posted earlier would be what you wanted. In a field in the query grid

Field: NumberAttended: Abs([Meeting1YN] + [Meeting2YN] + [Meeting3YN] +
.... + [Meeting9YN])
Criteria: >=4

That assumes that Meeting1YN, etc are all YesNo (or boolean) fields.

Greetings John,
I tried the formula you posted and recieved this pop up msg..calculation
is
to complex.try using variable...I'm just an old dog trying to learn a new
trick, this is the first database I attempted to build. to say that I am
confused is putting it mildly..Here is what I want it to do for me.
I have a base of 85 members and we have 9 meetings a year.jan thru may. I
would like to see who(last name) attended how many meetings. they need to
attend at least 5 mtg to be eligble for a scholarship fund drawing.I would
like to see ie:
Blow,Joe 7. is this possible.
Thanks for any assistance you can give. Is there a book on this subject
that
would help me?
thanks again
Rob M

:

Pardon me, but your simple approach won't work on yes/no (boolean)
fields.
This field type always has a value - either true or false. Since Count
counts the number of times ANY value exists in a field, the count would
the
same as the number of rows in the group.

To count true, you can use a formula
Abs(Sum([YesNoField]))


hey there - I'll give you a real simply approach....

a. make a new query in Design view and just add the Name Field and the
yes/no field

b. select the greek sum icon from the tool bar (looks like greek E)

this adds a "Total" row to your query builder
'GroupBy' is the default entry - leave this as-is for the Name field

c. in the yes/no field click into right side of 'Groupby' and a
drop-down
will appear - - go ahead and change that to 'Count'

then hit the red ! ......and the result will be the name of all and
their
count of Yes

easy to scan down it to see whom is above your minimum
criteria......hope
this approach helps....
--
NTC


:

I created a database that use's the check box(yes/no) my question is
"How
can
I
tabulte the yes and no's. example: show if 5 or more meetings were
attended,
for use in a query
thanks

 

[Guest]

John, I did it, but I cheated.. I copied and pasted your sql
statement...works fine
Thank You so much
Rob m

Well, I can't see what you are doing, so this is tough to work out.

In the SQL view, I would expect to see

SELECT LastName, FirstName,
Abs([Sept] + [Oct] + [Nov] + [Dec] + [Jan] + [Feb] + [Mar] + [Apr] + [May]) As [MeetingsAttended]
FROM [YourTableName]
WHERE Abs([Sept] + [Oct] + [Nov] + [Dec] + [Jan] + [Feb] + [Mar] + [Apr] +
[May]) > 4

If you are being asked for parameters, that indicates that you have a field name
or table name that Access doesn't understand - misspelling, different spelling,
field not really in table, etc. What exactly is the parameter asking you for?

Troubleshooting technique is to try building the query a bit at a time.

Select LastName, FirstName
FROM [YourTableName]

If that works then add in
Abs([Sept])
If that works then add in + [Oct]
If that works then try adding the rest of the months.
If that works try adding the condition in.

John,
I have to Bad design I guess.
lastname
firstname
sept
oct
nov
dec
jan
feb
mar
apr
may
the months are all yes/no.
what I would like is this:
have a report that list the names and number of meetings attened such as:
jacobs,henry 7.
only interested in people that attended 5 or more meetings.
I am grateful for your patients in this matter John. when I try the formulas
you suggest it ask me for paramiters ,I'm lost.
thanks again
Rob m

I'm sure it is possible, but what is your table structure? Is it something
like the following (bad table design)?
MemberLastName
MemberFirstName
Meeting1YN
Meeting2YN
Meeting3YN
....
Meeting9YN

Or is it something else? If it is the way I show above, then the solution I
posted earlier would be what you wanted. In a field in the query grid

Field: NumberAttended: Abs([Meeting1YN] + [Meeting2YN] + [Meeting3YN] +
.... + [Meeting9YN])
Criteria: >=4

That assumes that Meeting1YN, etc are all YesNo (or boolean) fields.

Greetings John,
I tried the formula you posted and recieved this pop up msg..calculation
is
to complex.try using variable...I'm just an old dog trying to learn a new
trick, this is the first database I attempted to build. to say that I am
confused is putting it mildly..Here is what I want it to do for me.
I have a base of 85 members and we have 9 meetings a year.jan thru may. I
would like to see who(last name) attended how many meetings. they need to
attend at least 5 mtg to be eligble for a scholarship fund drawing.I would
like to see ie:
Blow,Joe 7. is this possible.
Thanks for any assistance you can give. Is there a book on this subject
that
would help me?
thanks again
Rob M

:

Pardon me, but your simple approach won't work on yes/no (boolean)
fields.
This field type always has a value - either true or false. Since Count
counts the number of times ANY value exists in a field, the count would
the
same as the number of rows in the group.

To count true, you can use a formula
Abs(Sum([YesNoField]))


hey there - I'll give you a real simply approach....

a. make a new query in Design view and just add the Name Field and the
yes/no field

b. select the greek sum icon from the tool bar (looks like greek E)

this adds a "Total" row to your query builder
'GroupBy' is the default entry - leave this as-is for the Name field

c. in the yes/no field click into right side of 'Groupby' and a
drop-down
will appear - - go ahead and change that to 'Count'

then hit the red ! ......and the result will be the name of all and
their
count of Yes

easy to scan down it to see whom is above your minimum
criteria......hope
this approach helps....
--
NTC


:

I created a database that use's the check box(yes/no) my question is
"How
can
I
tabulte the yes and no's. example: show if 5 or more meetings were
attended,
for use in a query
thanks