NULL

[snakedog]

****HELP****** I have a question to ask you,, I am doing a marking
form to give me a total and a score in % using many fields and a combo
box with Yes=1, No=0, Na=Null, now here is my delema how on earth do
you get the table to show the sum of the Yes and minus the No's and
ignore the Nulls..... right now the option group does not like me
using Null.. This is what I want to do:

Did you check the corner store Yes No Na (for this example lets say
No)=0

do you have a car Yes No Na( for this one lets say Yes)=1
Do you have a bike Yes No Na( for this one lets say Na)=Na
Result = 50% possible 3 two are relevant so the total is 1out of 2= 1=
50%

pls help
Access
Option Group Control (Forms and Reports) [Access 2003 VBA Language
Reference]
An option group on a form or report displays a limited set of
alternatives. An option group makes selecting a value easy since you
can just click the value you want. Only one option in an option group
can be selected at a time.

An option group consists of a group frame and a set of check boxes,
toggle buttons, or option buttons.


Note This control should not be confused with the Dynamic HTML
option group control used on a data access page. For information about
a option group control on a data access page, see Option Group Control
(Data Access Pages).

Remarks
If an option group is bound to a field, only the group frame itself is
bound to the field, not the check boxes, toggle buttons, or option
buttons inside the frame. Instead of setting the ControlSource
property for each control in the option group, you set the OptionValue
property of each check box, toggle button, or option button to a
number that's meaningful for the field to which the group frame is
bound. When you select an option in an option group, Microsoft Access
sets the value of the field to which the option group is bound to the
value of the selected option's OptionValue property.


Note The OptionValue property is set to a number because the value of
an option group can only be a number, not text. Microsoft Access
stores this number in the underlying table. In the preceding example,
if you want to display the name of the shipper instead of a number in
the Orders table, you can create a separate table called Shippers that
stores shipper names, and then make the ShipVia field in the Orders
table a Lookup field that looks up data in the Shippers table.

An option group can also be set to an expression, or it can be
unbound. You can use an unbound option group in a custom dialog box to
accept user input and then carry out an action based on that input.

 

[storrboy]

****HELP****** I have a question to ask you,, I am doing a marking
form to give me a total and a score in % using many fields and a combo
box with Yes=1, No=0, Na=Null, now here is my delema how on earth do
you get the table to show the sum of the Yes and minus the No's and
ignore the Nulls..... right now the option group does not like me
using Null.. This is what I want to do:

I'm confused as to what you are asking. What does an option group have
to do with summing the values in a table? If Yes = 1 and No = 0, then
the sum of the yes's minus the no's is the sum of the yes's. Nulls
can't be summed so they are automatically ignored.

I assume the rest of your post was from the help files?

 

[Dirk Goldgar]

In

****HELP****** I have a question to ask you,, I am doing a marking
form to give me a total and a score in % using many fields and a combo
box with Yes=1, No=0, Na=Null, now here is my delema how on earth do
you get the table to show the sum of the Yes and minus the No's and
ignore the Nulls..... right now the option group does not like me
using Null.. This is what I want to do:

Did you check the corner store Yes No Na (for this example lets say
No)=0

do you have a car Yes No Na( for this one lets say Yes)=1
Do you have a bike Yes No Na( for this one lets say Na)=Na
Result = 50% possible 3 two are relevant so the total is 1out of 2= 1=
50%

I'm not sure I understand how you have this set up. An option group
stores one value (representing which option was selected). The normal
way to store the sort of survey information you describe would be in a
table of responses, with one record per question; a table structure
along the lines of:

Questions
-------------
QuestionID (autonumber primary key)
QuestionText (text)

Respondents
----------------
RespondentID (autonumber primary key)
FirstName
MiddleName
LastName
(other information about respondent, as needed)

Responses
--------------
RespondentID (compound primary key, ref. Respondents)
QuestionID (compound primary key, ref. Questions)
Response (number/integer, 1=yes, 0=no, Null=n/a)

Your forms would be set up to record each question's answer, along with
the respondent's ID, in table Responses.

Then you can see the percentage of "yes" answers to relevant questions
(excluding response "n/a") for a given respondent by the expression

Sum(Response=1)/Sum(Response Is Not Null)

On a continuous form (or subform) bound to Responses, you could have
that as a controlsource expression for a text box in the footer:

=Sum([Response]=1)/Sum([Response] Is Not Null)

In a query, you would use the expression as the basis of a calculated
field.

Now, this is quite simple, and all based on the idea that you are using
a structure that stores the response to each question in a separate
record in a table. From the way you phrased your question, it may be
that you are storing the response to each question as a separate field
in the same record. That's much hard to manage, and requires that you
write a long expression that adds up the results for all the individual
fields; e.g.,

=Nz(Q1,0)+Nz(Q2,0)+Nz(Q3,0)+Nz(Q4,0) /
4+IsNull(Q1)+IsNull(Q2)+IsNull(Q3)+IsNull(Q4)

The above would be for 4 question fields. The number 4 in the above
expression must be adjusted to the total number of question fields.

I strongly recommend the first, multi-record approach over the second,
multi-field approach.

 

[Arvin Meyer [MVP]]

Dirk's suggestion is the correct way to redesign and you should do so. In
the meantime, if you've already done one of these surveys, you can get the
results by running separate queries to get the count of answers to a
question then subtracting one count from the other. Totals (or aggregate)
queries are created when you use the Sigma button to group by, then change
group by to count. It should look like this in SQL view:

SELECT Question1, Count(Question1) AS CountOfQuestion1
FROM tblMyData
GROUP BY Question1;

You should get 3 answers to each question looking something like:

Question1 CountOfQuestion1
0
0 4
1 5

The Null will always have a count of 0 because nulls can't be counted like
that. You need to run a query for each question, then you can subtract the
results in another query, or in a report.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

****HELP****** I have a question to ask you,, I am doing a marking
form to give me a total and a score in % using many fields and a combo
box with Yes=1, No=0, Na=Null, now here is my delema how on earth do
you get the table to show the sum of the Yes and minus the No's and
ignore the Nulls..... right now the option group does not like me
using Null.. This is what I want to do:

Did you check the corner store Yes No Na (for this example lets say
No)=0

do you have a car Yes No Na( for this one lets say Yes)=1
Do you have a bike Yes No Na( for this one lets say Na)=Na
Result = 50% possible 3 two are relevant so the total is 1out of 2= 1=
50%

pls help
Access
Option Group Control (Forms and Reports) [Access 2003 VBA Language
Reference]
An option group on a form or report displays a limited set of
alternatives. An option group makes selecting a value easy since you
can just click the value you want. Only one option in an option group
can be selected at a time.

An option group consists of a group frame and a set of check boxes,
toggle buttons, or option buttons.


Note This control should not be confused with the Dynamic HTML
option group control used on a data access page. For information about
a option group control on a data access page, see Option Group Control
(Data Access Pages).

Remarks
If an option group is bound to a field, only the group frame itself is
bound to the field, not the check boxes, toggle buttons, or option
buttons inside the frame. Instead of setting the ControlSource
property for each control in the option group, you set the OptionValue
property of each check box, toggle button, or option button to a
number that's meaningful for the field to which the group frame is
bound. When you select an option in an option group, Microsoft Access
sets the value of the field to which the option group is bound to the
value of the selected option's OptionValue property.


Note The OptionValue property is set to a number because the value of
an option group can only be a number, not text. Microsoft Access
stores this number in the underlying table. In the preceding example,
if you want to display the name of the shipper instead of a number in
the Orders table, you can create a separate table called Shippers that
stores shipper names, and then make the ShipVia field in the Orders
table a Lookup field that looks up data in the Shippers table.

An option group can also be set to an expression, or it can be
unbound. You can use an unbound option group in a custom dialog box to
accept user input and then carry out an action based on that input.

 

[Allen Browne]

You have a couple of answers explaining how to set up this data structure.
Here's how to set up a Yes/No/Not Applicable field. (This may not be useful
for what you need, but it hopefully helps answer what you asked.)

Create a field of type Number, with these properties:
Size Integer
Required No
Validation Rule Is Null Or Between -1 And 0

You can now interface this field with a check box that has its Triple State
property set. The check box now cycles between 3 states: checked (yes),
unchecked (no), and greyed (null.) In Access 2003, you may not be able to
differentiate the 3rd state unless you uncheck the box under:
Tools | Options | Forms/Reports | Use Windows Themed Controls.

It is possible (though messy) to use an unbound option group to handle the 3
choices, with a hidden control for the field. Use the AfterUpdate event of
the option group to write the 3 values to the true field. Use the Current
event of the form to assign the values to the option group. Use the Undo
event of the form to reset the option group based on the OldValue of the
hidden control.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

****HELP****** I have a question to ask you,, I am doing a marking
form to give me a total and a score in % using many fields and a combo
box with Yes=1, No=0, Na=Null, now here is my delema how on earth do
you get the table to show the sum of the Yes and minus the No's and
ignore the Nulls..... right now the option group does not like me
using Null.. This is what I want to do:

Did you check the corner store Yes No Na (for this example lets say
No)=0

do you have a car Yes No Na( for this one lets say Yes)=1
Do you have a bike Yes No Na( for this one lets say Na)=Na
Result = 50% possible 3 two are relevant so the total is 1out of 2= 1=
50%

pls help
Access
Option Group Control (Forms and Reports) [Access 2003 VBA Language
Reference]
An option group on a form or report displays a limited set of
alternatives. An option group makes selecting a value easy since you
can just click the value you want. Only one option in an option group
can be selected at a time.

An option group consists of a group frame and a set of check boxes,
toggle buttons, or option buttons.


Note This control should not be confused with the Dynamic HTML
option group control used on a data access page. For information about
a option group control on a data access page, see Option Group Control
(Data Access Pages).

Remarks
If an option group is bound to a field, only the group frame itself is
bound to the field, not the check boxes, toggle buttons, or option
buttons inside the frame. Instead of setting the ControlSource
property for each control in the option group, you set the OptionValue
property of each check box, toggle button, or option button to a
number that's meaningful for the field to which the group frame is
bound. When you select an option in an option group, Microsoft Access
sets the value of the field to which the option group is bound to the
value of the selected option's OptionValue property.


Note The OptionValue property is set to a number because the value of
an option group can only be a number, not text. Microsoft Access
stores this number in the underlying table. In the preceding example,
if you want to display the name of the shipper instead of a number in
the Orders table, you can create a separate table called Shippers that
stores shipper names, and then make the ShipVia field in the Orders
table a Lookup field that looks up data in the Shippers table.

An option group can also be set to an expression, or it can be
unbound. You can use an unbound option group in a custom dialog box to
accept user input and then carry out an action based on that input.

 

[snakedog]

You have a couple of answers explaining how to set up this data structure.
Here's how to set up a Yes/No/Not Applicable field. (This may not be useful
for what you need, but it hopefully helps answer what you asked.)

Create a field of type Number, with these properties:
Size Integer
Required No
Validation Rule Is Null Or Between -1 And 0

You can now interface this field with a check box that has its Triple State
property set. The check box now cycles between 3 states: checked (yes),
unchecked (no), and greyed (null.) In Access 2003, you may not be able to
differentiate the 3rd state unless you uncheck the box under:
Tools | Options | Forms/Reports | Use Windows Themed Controls.

It is possible (though messy) to use an unbound option group to handle the 3
choices, with a hidden control for the field. Use the AfterUpdate event of
the option group to write the 3 values to the true field. Use the Current
event of the form to assign the values to the option group. Use the Undo
event of the form to reset the option group based on the OldValue of the
hidden control.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

****HELP****** I have a question to ask you,, I am doing a marking
form to give me a total and a score in % using many fields and a combo
box with Yes=1, No=0, Na=Null, now here is my delema how on earth do
you get the table to show the sum of the Yes and minus the No's and
ignore the Nulls..... right now the option group does not like me
using Null.. This is what I want to do:

Did you check the corner store Yes No Na (for this example lets say
No)=0

do you have a car Yes No Na( for this one lets say Yes)=1
Do you have a bike Yes No Na( for this one lets say Na)=Na
Result = 50% possible 3 two are relevant so the total is 1out of 2= 1=
50%

pls help
Access
Option Group Control (Forms and Reports) [Access 2003 VBA Language
Reference]
An option group on a form or report displays a limited set of
alternatives. An option group makes selecting a value easy since you
can just click the value you want. Only one option in an option group
can be selected at a time.

An option group consists of a group frame and a set of check boxes,
toggle buttons, or option buttons.

Note This control should not be confused with the Dynamic HTML
option group control used on a data access page. For information about
a option group control on a data access page, see Option Group Control
(Data Access Pages).

Remarks
If an option group is bound to a field, only the group frame itself is
bound to the field, not the check boxes, toggle buttons, or option
buttons inside the frame. Instead of setting the ControlSource
property for each control in the option group, you set the OptionValue
property of each check box, toggle button, or option button to a
number that's meaningful for the field to which the group frame is
bound. When you select an option in an option group, Microsoft Access
sets the value of the field to which the option group is bound to the
value of the selected option's OptionValue property.

Note The OptionValue property is set to a number because the value of
an option group can only be a number, not text. Microsoft Access
stores this number in the underlying table. In the preceding example,
if you want to display the name of the shipper instead of a number in
the Orders table, you can create a separate table called Shippers that
stores shipper names, and then make the ShipVia field in the Orders
table a Lookup field that looks up data in the Shippers table.

An option group can also be set to an expression, or it can be
unbound. You can use an unbound option group in a custom dialog box to
accept user input and then carry out an action based on that input.- Hide quoted text -

- Show quoted text -

Problem

While we can get the Is Null to show as default in the form and it
will also
leave the field blank in the table (perfrectit's just what we need!),
selecting a check results in a -1 in the field rather than +1...is
there a
way to set the Valdation Rule in the table so that the Is Null Or
Between -1
And 0 shows a +1 or 0 rather than a -1 on the table field?

 

[Allen Browne]

Response in the new thread you started.

 

[Todos Menos [MSFT]]

Arvin is a baby retarded programmer
don't listen to him or else you'll be stuck in the 1st grade with him,
making $12/hour

Dirk's suggestion is the correct way to redesign and you should do so. In
the meantime, if you've already done one of these surveys, you can get the
results by running separate queries to get the count of answers to a
question then subtracting one count from the other. Totals (or aggregate)
queries are created when you use the Sigma button to group by, then change
group by to count. It should look like this in SQL view:

SELECT Question1, Count(Question1) AS CountOfQuestion1
FROM tblMyData
GROUP BY Question1;

You should get 3 answers to each question looking something like:

Question1 CountOfQuestion1
0
0 4
1 5

The Null will always have a count of 0 because nulls can't be counted like
that. You need to run a query for each question, then you can subtract the
results in another query, or in a report.
--
Arvin Meyer, MCP, MVPhttp://www.datastrat.comhttp://www.mvps.org/accesshttp://www.accessmvp.com

****HELP****** I have a question to ask you,, I am doing a marking
form to give me a total and a score in % using many fields and a combo
box with Yes=1, No=0, Na=Null, now here is my delema how on earth do
you get the table to show the sum of the Yes and minus the No's and
ignore the Nulls..... right now the option group does not like me
using Null.. This is what I want to do:

Did you check the corner store Yes No Na (for this example lets say
No)=0

do you have a car Yes No Na( for this one lets say Yes)=1
Do you have a bike Yes No Na( for this one lets say Na)=Na
Result = 50% possible 3 two are relevant so the total is 1out of 2= 1=
50%

pls help
Access
Option Group Control (Forms and Reports) [Access 2003 VBA Language
Reference]
An option group on a form or report displays a limited set of
alternatives. An option group makes selecting a value easy since you
can just click the value you want. Only one option in an option group
can be selected at a time.

An option group consists of a group frame and a set of check boxes,
toggle buttons, or option buttons.

Note This control should not be confused with the Dynamic HTML
option group control used on a data access page. For information about
a option group control on a data access page, see Option Group Control
(Data Access Pages).

Remarks
If an option group is bound to a field, only the group frame itself is
bound to the field, not the check boxes, toggle buttons, or option
buttons inside the frame. Instead of setting the ControlSource
property for each control in the option group, you set the OptionValue
property of each check box, toggle button, or option button to a
number that's meaningful for the field to which the group frame is
bound. When you select an option in an option group, Microsoft Access
sets the value of the field to which the option group is bound to the
value of the selected option's OptionValue property.

Note The OptionValue property is set to a number because the value of
an option group can only be a number, not text. Microsoft Access
stores this number in the underlying table. In the preceding example,
if you want to display the name of the shipper instead of a number in
the Orders table, you can create a separate table called Shippers that
stores shipper names, and then make the ShipVia field in the Orders
table a Lookup field that looks up data in the Shippers table.

An option group can also be set to an expression, or it can be
unbound. You can use an unbound option group in a custom dialog box to
accept user input and then carry out an action based on that input.