Counting of Check boxes

[Guest]

Just wanted to state I have looked all over this discussion group for an
answer before I posted this question, but to no avail.

I have a contacts inforamtaion form. On this main form there is a subform
that is called Projects. The projects form is from its very own table.
Everything on the Projects form are yes/no boxes. The user is supposed to
check each box that applies to their contact. This part is working fine. My
problem is trying to get each Projects field to calculate. I have tried the
many ideas that are posted on this site, but they are not working.
I need for this information to be in a report based on a monthly critiera.
I am not good a the whole code part of access, so is there a simple way to
write this in a query?
Your help is greatly appreciated.

 

[tina]

your question isn't clear. do you want a total of the number of projects
selected for each contact? as in: contact1 has projectA, projectC,
projectD, projectF - so there are three projects total for contact1. or do
you want a count of the total number of contacts associated with projectA,
and a count of the total number of contacts associated with projectB, a
count of the total number of contacts associated with projectC, etc, etc?

and forget the forms for a minute. data is not stored in forms. if you want
to summarize the data for a report, you need to answer the above question,
and also tell us how the data is stored in the *table(s)*, because the query
must pull the raw data from the table(s) in order to manipulate it.

hth

 

[Guest]

I am so sorry for not making my self clearer.
I only need to run a query for all of the Projects that are marked yes. It
does not need to be based off of the contact in this case. Like I stated the
projects table is linked to the contacts table. When the user is putting the
information into the form they are at this time to check all boxes that apply
to their contact. However, my report does not need to be linked to the
contact. Just a total of projects marked yes for the month. That's it.
Like I stated in my earlier post, I am not too familiar with writing code, so
if this can be done in a query that would be great, but if not, I am more
than willing to try and get the code to work.
Thank you for your help

 

[tina]

okay, it's still not clear how you're storing the data in the table -
normalized, or as a flat file. i'll take a guess that the data is *not*
normalized, but rather that each Project is used as a field name (which is a
flat file structure), and each project field is a Yes/No data type. if that
is the case, you may be able to base a query on the Projects table, pull
each project field into the design grid, then change the query to a Totals
query. change each project field to a calculated field, as

ProjA: Sum(Abs([ProjectA]))
ProjB: Sum(Abs([ProjectB]))
etc, etc.

change the default "GroupBy" for each field to "Expression".

hth

 

[Guest]

Tina,
You are correct, each project in the project table is its own Yes/No Field.
I guess I didn't understand the question you were asking. I will try your
suggestions and let you know how it turns out.


Thanks again

okay, it's still not clear how you're storing the data in the table -
normalized, or as a flat file. i'll take a guess that the data is *not*
normalized, but rather that each Project is used as a field name (which is a
flat file structure), and each project field is a Yes/No data type. if that
is the case, you may be able to base a query on the Projects table, pull
each project field into the design grid, then change the query to a Totals
query. change each project field to a calculated field, as

ProjA: Sum(Abs([ProjectA]))
ProjB: Sum(Abs([ProjectB]))
etc, etc.

change the default "GroupBy" for each field to "Expression".

hth

I am so sorry for not making my self clearer.
I only need to run a query for all of the Projects that are marked yes. It
does not need to be based off of the contact in this case. Like I stated the
projects table is linked to the contacts table. When the user is putting the
information into the form they are at this time to check all boxes that apply
to their contact. However, my report does not need to be linked to the
contact. Just a total of projects marked yes for the month. That's it.
Like I stated in my earlier post, I am not too familiar with writing code, so
if this can be done in a query that would be great, but if not, I am more
than willing to try and get the code to work.
Thank you for your help

 

[Guest]

Tina,
I have written the query as you suggested and it is calculating the yes/no
boxes for me, however, It is calculating as a total for each field based on
the date. So it give you the number for that particular yes/no box, but
break it down per date. Is there a way to just calulate it as whole for the
entire month?

okay, it's still not clear how you're storing the data in the table -
normalized, or as a flat file. i'll take a guess that the data is *not*
normalized, but rather that each Project is used as a field name (which is a
flat file structure), and each project field is a Yes/No data type. if that
is the case, you may be able to base a query on the Projects table, pull
each project field into the design grid, then change the query to a Totals
query. change each project field to a calculated field, as

ProjA: Sum(Abs([ProjectA]))
ProjB: Sum(Abs([ProjectB]))
etc, etc.

change the default "GroupBy" for each field to "Expression".

hth

I am so sorry for not making my self clearer.
I only need to run a query for all of the Projects that are marked yes. It
does not need to be based off of the contact in this case. Like I stated the
projects table is linked to the contacts table. When the user is putting the
information into the form they are at this time to check all boxes that apply
to their contact. However, my report does not need to be linked to the
contact. Just a total of projects marked yes for the month. That's it.
Like I stated in my earlier post, I am not too familiar with writing code, so
if this can be done in a query that would be great, but if not, I am more
than willing to try and get the code to work.
Thank you for your help

 

[tina]

well, you must have included a date field in the fields you added to the
"grid" in query design, with the default "Group By". try changing that field
to a calculated field also, as

MyMonth: Month([datefield])

and leave the default "Group By" as is.

hth

Tina,
I have written the query as you suggested and it is calculating the yes/no
boxes for me, however, It is calculating as a total for each field based on
the date. So it give you the number for that particular yes/no box, but
break it down per date. Is there a way to just calulate it as whole for the
entire month?

okay, it's still not clear how you're storing the data in the table -
normalized, or as a flat file. i'll take a guess that the data is *not*
normalized, but rather that each Project is used as a field name (which is a
flat file structure), and each project field is a Yes/No data type. if that
is the case, you may be able to base a query on the Projects table, pull
each project field into the design grid, then change the query to a Totals
query. change each project field to a calculated field, as

ProjA: Sum(Abs([ProjectA]))
ProjB: Sum(Abs([ProjectB]))
etc, etc.

change the default "GroupBy" for each field to "Expression".

hth

I am so sorry for not making my self clearer.
I only need to run a query for all of the Projects that are marked

yes.
It

does not need to be based off of the contact in this case. Like I

stated
the

projects table is linked to the contacts table. When the user is

putting
the

information into the form they are at this time to check all boxes

that
apply

to their contact. However, my report does not need to be linked to the
contact. Just a total of projects marked yes for the month. That's it.
Like I stated in my earlier post, I am not too familiar with writing

code,
so

if this can be done in a query that would be great, but if not, I am more
than willing to try and get the code to work.
Thank you for your help

:

your question isn't clear. do you want a total of the number of projects
selected for each contact? as in: contact1 has projectA, projectC,
projectD, projectF - so there are three projects total for contact1.

or
do

you want a count of the total number of contacts associated with projectA,
and a count of the total number of contacts associated with projectB, a
count of the total number of contacts associated with projectC, etc, etc?

and forget the forms for a minute. data is not stored in forms. if

you
want

to summarize the data for a report, you need to answer the above question,
and also tell us how the data is stored in the *table(s)*, because

the
query

must pull the raw data from the table(s) in order to manipulate it.

hth


Just wanted to state I have looked all over this discussion group

for
an

answer before I posted this question, but to no avail.

I have a contacts inforamtaion form. On this main form there is a subform
that is called Projects. The projects form is from its very own table.
Everything on the Projects form are yes/no boxes. The user is supposed to
check each box that applies to their contact. This part is

working
fine.

My
problem is trying to get each Projects field to calculate. I have tried
the
many ideas that are posted on this site, but they are not working.
I need for this information to be in a report based on a monthly critiera.
I am not good a the whole code part of access, so is there a

simple
way to

write this in a query?
Your help is greatly appreciated.

 

[Guest]

Tina,
Thanks so much for all of your help. All of your suggestions have worked
wonderfully. For the date problem. I needed to pull the query based on
paramerters of a date. I changed the "Group By" to Expression and it works
perfectly.

well, you must have included a date field in the fields you added to the
"grid" in query design, with the default "Group By". try changing that field
to a calculated field also, as

MyMonth: Month([datefield])

and leave the default "Group By" as is.

hth

Tina,
I have written the query as you suggested and it is calculating the yes/no
boxes for me, however, It is calculating as a total for each field based on
the date. So it give you the number for that particular yes/no box, but
break it down per date. Is there a way to just calulate it as whole for the
entire month?

okay, it's still not clear how you're storing the data in the table -
normalized, or as a flat file. i'll take a guess that the data is *not*
normalized, but rather that each Project is used as a field name (which is a
flat file structure), and each project field is a Yes/No data type. if that
is the case, you may be able to base a query on the Projects table, pull
each project field into the design grid, then change the query to a Totals
query. change each project field to a calculated field, as

ProjA: Sum(Abs([ProjectA]))
ProjB: Sum(Abs([ProjectB]))
etc, etc.

change the default "GroupBy" for each field to "Expression".

hth


I am so sorry for not making my self clearer.
I only need to run a query for all of the Projects that are marked yes.
It
does not need to be based off of the contact in this case. Like I stated
the
projects table is linked to the contacts table. When the user is putting
the
information into the form they are at this time to check all boxes that
apply
to their contact. However, my report does not need to be linked to the
contact. Just a total of projects marked yes for the month. That's it.
Like I stated in my earlier post, I am not too familiar with writing code,
so
if this can be done in a query that would be great, but if not, I am more
than willing to try and get the code to work.
Thank you for your help

:

your question isn't clear. do you want a total of the number of projects
selected for each contact? as in: contact1 has projectA, projectC,
projectD, projectF - so there are three projects total for contact1. or
do
you want a count of the total number of contacts associated with
projectA,
and a count of the total number of contacts associated with projectB, a
count of the total number of contacts associated with projectC, etc,
etc?

and forget the forms for a minute. data is not stored in forms. if you
want
to summarize the data for a report, you need to answer the above
question,
and also tell us how the data is stored in the *table(s)*, because the
query
must pull the raw data from the table(s) in order to manipulate it.

hth


Just wanted to state I have looked all over this discussion group for
an
answer before I posted this question, but to no avail.

I have a contacts inforamtaion form. On this main form there is a
subform
that is called Projects. The projects form is from its very own
table.
Everything on the Projects form are yes/no boxes. The user is
supposed to
check each box that applies to their contact. This part is working
fine.
My
problem is trying to get each Projects field to calculate. I have
tried
the
many ideas that are posted on this site, but they are not working.
I need for this information to be in a report based on a monthly
critiera.
I am not good a the whole code part of access, so is there a simple
way to
write this in a query?
Your help is greatly appreciated.

 

[tina]

you're welcome

Tina,
Thanks so much for all of your help. All of your suggestions have worked
wonderfully. For the date problem. I needed to pull the query based on
paramerters of a date. I changed the "Group By" to Expression and it works
perfectly.

well, you must have included a date field in the fields you added to the
"grid" in query design, with the default "Group By". try changing that field
to a calculated field also, as

MyMonth: Month([datefield])

and leave the default "Group By" as is.

hth

Tina,
I have written the query as you suggested and it is calculating the yes/no
boxes for me, however, It is calculating as a total for each field

based
on

the date. So it give you the number for that particular yes/no box, but
break it down per date. Is there a way to just calulate it as whole

for
the

entire month?

:

okay, it's still not clear how you're storing the data in the table -
normalized, or as a flat file. i'll take a guess that the data is *not*
normalized, but rather that each Project is used as a field name

(which
is a

flat file structure), and each project field is a Yes/No data type.

if
that

is the case, you may be able to base a query on the Projects table, pull
each project field into the design grid, then change the query to a Totals
query. change each project field to a calculated field, as

ProjA: Sum(Abs([ProjectA]))
ProjB: Sum(Abs([ProjectB]))
etc, etc.

change the default "GroupBy" for each field to "Expression".

hth


I am so sorry for not making my self clearer.
I only need to run a query for all of the Projects that are marked yes.
It
does not need to be based off of the contact in this case. Like I stated
the
projects table is linked to the contacts table. When the user is putting
the
information into the form they are at this time to check all boxes that
apply
to their contact. However, my report does not need to be linked to the
contact. Just a total of projects marked yes for the month.

That's
it.

Like I stated in my earlier post, I am not too familiar with

writing
code,

so
if this can be done in a query that would be great, but if not, I

am
more

than willing to try and get the code to work.
Thank you for your help

:

your question isn't clear. do you want a total of the number of projects
selected for each contact? as in: contact1 has projectA, projectC,
projectD, projectF - so there are three projects total for

contact1.
or

do
you want a count of the total number of contacts associated with
projectA,
and a count of the total number of contacts associated with projectB, a
count of the total number of contacts associated with projectC, etc,
etc?

and forget the forms for a minute. data is not stored in forms.

if
you

want
to summarize the data for a report, you need to answer the above
question,
and also tell us how the data is stored in the *table(s)*,

because
the

query
must pull the raw data from the table(s) in order to manipulate it.

hth


Just wanted to state I have looked all over this discussion

group
for

an
answer before I posted this question, but to no avail.

I have a contacts inforamtaion form. On this main form there is a
subform
that is called Projects. The projects form is from its very own
table.
Everything on the Projects form are yes/no boxes. The user is
supposed to
check each box that applies to their contact. This part is working
fine.
My
problem is trying to get each Projects field to calculate. I have
tried
the
many ideas that are posted on this site, but they are not working.
I need for this information to be in a report based on a monthly
critiera.
I am not good a the whole code part of access, so is there a simple
way to
write this in a query?
Your help is greatly appreciated.