access 2003

[snakedog]

I have two questions:
1. How do I create a formula in a table or in a form or both where I
can have a Yes, No, and an Na where anytime some leaves a question as
NA it is not counted in the total. For example if I have 6 questions
and someone answers 3 of them as yes, 2 as No and they leave one as Na
I want my total to be out off 5 not 6 as one was left Na meaning my
total would be 3 out of 5 so then I would be able to score the true
values average??? Please help on this one

2. How can I get a form to show: lets say I have three teams with
three coaches on a form for results I want my people to drop down a
coach name from list and only have the players under that coach to
show up under the player drop down...

 

[snakedog]

I have two questions:
1. How do I create a formula in a table or in a form or both where I
can have a Yes, No, and an Na where anytime some leaves a question as
NA it is not counted in the total. For example if I have 6 questions
and someone answers 3 of them as yes, 2 as No and they leave one as Na
I want my total to be out off 5 not 6 as one was left Na meaning my
total would be 3 out of 5 so then I would be able to score the true
values average??? Please help on this one

2. How can I get a form to show: lets say I have three teams with
three coaches on a form for results I want my people to drop down a
coach name from list and only have the players under that coach to
show up under the player drop down...

I am sorry I can not see any of your suggetstions

 

[John W. Vinson]

I have two questions:
1. How do I create a formula in a table or in a form or both where I
can have a Yes, No, and an Na where anytime some leaves a question as
NA it is not counted in the total. For example if I have 6 questions
and someone answers 3 of them as yes, 2 as No and they leave one as Na
I want my total to be out off 5 not 6 as one was left Na meaning my
total would be 3 out of 5 so then I would be able to score the true
values average??? Please help on this one

I would suggest using a Number... Integer field (you could use an Option Group
or a Combo Box to select the values); store 0 for No, 1 for Yes, and NULL for
n/a. A Totals query will let you sum the yesses; summing (1-[answer]) will sum
the nos; a Count on the field will count only the answered questions; the
Nulls won't be included in the totals query in any of the three sums.

2. How can I get a form to show: lets say I have three teams with
three coaches on a form for results I want my people to drop down a
coach name from list and only have the players under that coach to
show up under the player drop down...

Not sure I understand. How are your tables structured? Do you have three
coaches per team, or one for each? Do you have a Teams table related
one-to-many to a Players table? If so, you can put a combo box (on the players
subform) with a Rowsource being a query using the coach's ID from the mainform
as a criterion:

=Forms![TeamForm]![cboCoachID]

on the coach ID field (don't use the coach's name, it's not unique and she
might get married and change her name).

John W. Vinson [MVP]

 

[luis100ca]

I have two questions:
1. How do I create a formula in a table or in a form or both where I
can have a Yes, No, and an Na where anytime some leaves a question as
NA it is not counted in the total. For example if I have 6 questions
and someone answers 3 of them as yes, 2 as No and they leave one as Na
I want my total to be out off 5 not 6 as one was left Na meaning my
total would be 3 out of 5 so then I would be able to score the true
values average??? Please help on this one

2. How can I get a form to show: lets say I have three teams with
three coaches on a form for results I want my people to drop down a
coach name from list and only have the players under that coach to
show up under the player drop down...

 

[snakedog]

I have two questions:
1. How do I create a formula in a table or in a form or both where I
can have a Yes, No, and an Na where anytime some leaves a question as
NA it is not counted in the total. For example if I have 6 questions
and someone answers 3 of them as yes, 2 as No and they leave one as Na
I want my total to be out off 5 not 6 as one was left Na meaning my
total would be 3 out of 5 so then I would be able to score the true
values average??? Please help on this one

I would suggest using a Number... Integer field (you could use an Option Group
or a Combo Box to select the values); store 0 for No, 1 for Yes, and NULL for
n/a. A Totals query will let you sum the yesses; summing (1-[answer]) will sum
the nos; a Count on the field will count only the answered questions; the
Nulls won't be included in the totals query in any of the three sums.

2. How can I get a form to show: lets say I have three teams with
three coaches on a form for results I want my people to drop down a
coach name from list and only have the players under that coach to
show up under the player drop down...

Not sure I understand. How are your tables structured? Do you have three
coaches per team, or one for each? Do you have a Teams table related
one-to-many to a Players table? If so, you can put a combo box (on the players
subform) with a Rowsource being a query using the coach's ID from the mainform
as a criterion:

=Forms![TeamForm]![cboCoachID]

on the coach ID field (don't use the coach's name, it's not unique and she
might get married and change her name).

John W. Vinson [MVP]

Thanks John for your help
to answer your question on the teams part, I am sorry I did not word
my questions that clearly


I have a table for teams I called them sites ie Ottawa, toronto,
hamilton now each site is broken down into teams so I have a table
for coaches ie Ottawa has 6 teams each team has one coach and about 20
players which brings me to my player table.
now the colums are as follow:
- sites only has auto key and site
- coaches have coach id(the key), site, coach full name, coach email,
coach devision
players have player id(the key), site, player full name and coach id
then I have a results table where I put all the stats for each player
to pull my reports that is where I have the problem! I have a lookup
wizard looking up and showing on a drop down all player full name and
id in 2 columns and same for coach full name and id but when I put
the coach name every player in the site shows up so I manually have to
find the player and match it to the proper coach every time I want to
input a result, that is my problem.

Lets not get into my other problem with the na that right now no
matter what I do my no and na are both showing as defult values of 0
which is driving me nuts! I have not used access in years and I know
it's something stupid that I am missing but it is driving me up the
wall.....


so far when I go to the coach table and hit the plus sign beside any
of my coaches I can see the proper player under each coach but that is
as far as I have gotten so far, I know you will laugh but I could
really use some direction..
Thank you again
Snake dog

 

[John W. Vinson]

players have player id(the key), site, player full name and coach id
then I have a results table where I put all the stats for each player
to pull my reports that is where I have the problem! I have a lookup
wizard looking up and showing on a drop down all player full name and
id in 2 columns and same for coach full name and id but when I put
the coach name every player in the site shows up so I manually have to
find the player and match it to the proper coach every time I want to
input a result, that is my problem.

Ah. You're yet another victim of Microsoft's misdesigned, misleading,
infuriating Lookup Wizard.

See http://www.mvps.org/access/lookupfields.htm for a critique.

The problem is that your table *APPEARS* to have a coach's name in it. It
doesn't. It has the coach's ID. This basic fact is concealed from your view by
the combo box!

Solution? 1. Don't use Lookup fields at all. 2. Don't use table datasheet view
for anything except debugging (use Forms for interaction with the data). 3.
Base your Reports, not on the Player table, but on a Query joining all three
tables. Pull the site name from the sites table, coach name from the coach
table, player name from the player table.

John W. Vinson [MVP]

 

[snakedog]

Ah. You're yet another victim of Microsoft's misdesigned, misleading,
infuriating Lookup Wizard.

Seehttp://www.mvps.org/access/lookupfields.htmfor a critique.

The problem is that your table *APPEARS* to have a coach's name in it. It
doesn't. It has the coach's ID. This basic fact is concealed from your view by
the combo box!

Solution? 1. Don't use Lookup fields at all. 2. Don't use table datasheet view
for anything except debugging (use Forms for interaction with the data). 3.
Base your Reports, not on the Player table, but on a Query joining all three
tables. Pull the site name from the sites table, coach name from the coach
table, player name from the player table.

John W. Vinson [MVP]

ahh I see I knew I was doing something silly! I will try that. thank
you! I will let you know how it turns out. one question without using
the lookup that means that my result table that I have I should not be
looking up (or even have) the coach field, the site field, or player
field at all? my question goes for a table where the players scores
are kept from their evaluations. I hope that previous statement does
not sound stupid, but the people running the teams are crunching un us
to come up with something easier than the excel sheet that they've
been using for years! and again we are a little rusty with access.

what is your take on the first problem of the yes=1 no=0 and Na=Null
(defaulting to 0) which then gives me a hard time on the form of
defaulting both the no field and na field here is an example
did the player wear approved gear yes no na

I have about 30 questions but not all are applicable to everyone
takeing the test. currently I have about 10 different worksheets
taylored to each department only showing the applicable questions and
it is working ok but now having more players and more teams my partner
and I got into access to manage less duplication and less work than in
excel. we beleave that by making one form with all the questions using
na, we can do a count of the yes and no leaving the Na out so the
questionare that touches all 30 would be marked and given a % out of
30, and on the same form the test that only touches 3 out of those
same 30 would be given a % out of 3 with the other 27 being left as
Na, we know there has to be a way to do that but every book we have
does not show us that example other than
using:=IIf(IsNull([fieldname]),"Unknown",Format([fieldname],"@;\ZLS"))
in a query but we can't seem to get that to work unless ofcoars we are
using the option group wrong what do you think?

 

[snakedog]

Ah. You're yet another victim of Microsoft's misdesigned, misleading,
infuriating Lookup Wizard.

Seehttp://www.mvps.org/access/lookupfields.htmfor a critique.

The problem is that your table *APPEARS* to have a coach's name in it. It
doesn't. It has the coach's ID. This basic fact is concealed from your view by
the combo box!

Solution? 1. Don't use Lookup fields at all. 2. Don't use table datasheet view
for anything except debugging (use Forms for interaction with the data). 3.
Base your Reports, not on the Player table, but on a Query joining all three
tables. Pull the site name from the sites table, coach name from the coach
table, player name from the player table.

John W. Vinson [MVP]

we understand that forms are just windows showing the data of
customers. I think we have been looking at our previous data sheets
that others before us have created and our minds have been stuck on
the excel mind frame grrrrrr! you are deffenatly giving us the kick in
our you know what to get in gear with access again, I know once we get
used to it again it will be no problems

 

[John W. Vinson]

ahh I see I knew I was doing something silly! I will try that. thank
you! I will let you know how it turns out. one question without using
the lookup that means that my result table that I have I should not be
looking up (or even have) the coach field, the site field, or player
field at all? my question goes for a table where the players scores
are kept from their evaluations. I hope that previous statement does
not sound stupid, but the people running the teams are crunching un us
to come up with something easier than the excel sheet that they've
been using for years! and again we are a little rusty with access.

There's nothing wrong with using Lookups (combo boxes). They're indispensible;
I use them ALL the time.

The question is *where* do you use them.

The Lookup Wizard assumes you will be opening a table Datasheet and using it
to enter data, do searches, etc. Admittedly, the lookup wizard makes it easier
to do so.

BUT... there is an alternative. Use the Table *only* as a data storage
repository. Use a Form (based on the table), perhaps with a Subform (instead
of a subdatasheet) or two, for your data entry; use the form toolbox Combo Box
wizard to put combo boxes on the form to look up the site, the coach, etc.

The Combo Box can store the meaningless numeric siteID (so the computer is
happy) while displaying the site name on the screen (so the user is happy).
There's no need for the user to ever even SEE the numeric SiteID or the
CoachID or the PlayerID - and in general they shouldn't; there is likewise no
need for the user to ever open the Table.

As noted, your Reports should be based on queries joining whichever tables
contain the information that you want printed.

John W. Vinson [MVP]

 

[snakedog]

There's nothing wrong with using Lookups (combo boxes). They're indispensible;
I use them ALL the time.

The question is *where* do you use them.

The Lookup Wizard assumes you will be opening a table Datasheet and using it
to enter data, do searches, etc. Admittedly, the lookup wizard makes it easier
to do so.

BUT... there is an alternative. Use the Table *only* as a data storage
repository. Use a Form (based on the table), perhaps with a Subform (instead
of a subdatasheet) or two, for your data entry; use the form toolbox Combo Box
wizard to put combo boxes on the form to look up the site, the coach, etc.

The Combo Box can store the meaningless numeric siteID (so the computer is
happy) while displaying the site name on the screen (so the user is happy).
There's no need for the user to ever even SEE the numeric SiteID or the
CoachID or the PlayerID - and in general they shouldn't; there is likewise no
need for the user to ever open the Table.

As noted, your Reports should be based on queries joining whichever tables
contain the information that you want printed.

John W. Vinson [MVP]

ok we will try that any advise on the marking that we are trying to
implement with the yes no na(null)

 

[John W. Vinson]

ok we will try that any advise on the marking that we are trying to
implement with the yes no na(null)

Other than what I posted before? To reiterate:

I would suggest using a Number... Integer field (you could use an Option Group
or a Combo Box to select the values); store 0 for No, 1 for Yes, and NULL for
n/a. A Totals query will let you sum the yesses; summing (1-[answer]) will sum
the nos; a Count on the field will count only the answered questions; the
Nulls won't be included in the totals query in any of the three sums.

A Yes/No field type will NOT work: it doesn't allow NULL values.

John W. Vinson [MVP]

 

[snakedog]

ok we will try that any advise on the marking that we are trying to
implement with the yes no na(null)

Other than what I posted before? To reiterate:

I would suggest using a Number... Integer field (you could use an Option Group
or a Combo Box to select the values); store 0 for No, 1 for Yes, and NULL for
n/a. A Totals query will let you sum the yesses; summing (1-[answer]) will sum
the nos; a Count on the field will count only the answered questions; the
Nulls won't be included in the totals query in any of the three sums.

A Yes/No field type will NOT work: it doesn't allow NULL values.
, ,,
John W. Vinson [MVP]

ok here is where I am stuck when I create a number box All I get is
Null defaulting to zero!!! in the combo box, or my drop down menu

Question on the initial table where the results are stored the field
types are all set to number,,, on the table with the results all
applicagle asre ste toi number and the default value option is set to
null, on the form to submit this table i created an option group with
3 option lables are yes no and na,,... the problem is that access
askes for numeric value for na.. hope you understand the question..
pls help

 

[John W. Vinson/MVP]

ok here is where I am stuck when I create a number box All I get is

Null defaulting to zero!!! in the combo box, or my drop down menu

Question on the initial table where the results are stored the field
types are all set to number,,, on the table with the results all
applicagle asre ste toi number and the default value option is set to
null, on the form to submit this table i created an option group with
3 option lables are yes no and na,,... the problem is that access
askes for numeric value for na.. hope you understand the question..
pls help

An Option Group control *will simply not work* in the way you want.

An Option Group control has a numeric value. NULL is not a numeric value.

See the other answers in your new thread entitled NULL. You may need to
either change your user presentation - e.g. using a Listbox or a Combo Box
rather than an Option Group control - or write some rather complex code.
Your choice!