beginner query question

[Victoria]

hello to all

I would like to restate a question I posted a few days ago in different
terms. I received excellent advice from Allen Browne and John Vinson but, as
a beginner, I'm having trouble implementing their suggestions.

I have a form that shows a student's ID and name in the header. In the body
of the form are 40 textboxes that will each accept an integer from 1 to 5.
These are evaluation scores for 40 different criteria. The fields may also
be left blank if they do not pertain to that particular student. I've called
these 40 textboxes s1, s2, s3 .... s40. The form is bound to a query that
looks like this:

Value can be 1,2,3,4 or 5. Field runs from 1 to 40.

ID Value Field
bro_jon 4 1
bro_jon 3 2
bro_jon 5 3
bro_jon 5 4
bro_jon 5
.................... etc ...........
bro_jon 4 40
sam_tyl 2 1
sam_tyl 4 2
.................... etc ...........

QUESTION - For any given ID, say bro_jon, how do I get the value keyed into
textbox s1, s2, s3.... on the form to be recorded as the Value for Field
1,2,3... ? Am I doing this wrong?

appreciative of any help

 

[Amy Blankenship]

hello to all

I would like to restate a question I posted a few days ago in different
terms. I received excellent advice from Allen Browne and John Vinson but,
as
a beginner, I'm having trouble implementing their suggestions.

I have a form that shows a student's ID and name in the header. In the
body
of the form are 40 textboxes that will each accept an integer from 1 to 5.
These are evaluation scores for 40 different criteria. The fields may
also
be left blank if they do not pertain to that particular student. I've
called
these 40 textboxes s1, s2, s3 .... s40. The form is bound to a query that
looks like this:

Value can be 1,2,3,4 or 5. Field runs from 1 to 40.

ID Value Field
bro_jon 4 1
bro_jon 3 2
bro_jon 5 3
bro_jon 5 4
bro_jon 5
................... etc ...........
bro_jon 4 40
sam_tyl 2 1
sam_tyl 4 2
................... etc ...........

QUESTION - For any given ID, say bro_jon, how do I get the value keyed
into
textbox s1, s2, s3.... on the form to be recorded as the Value for Field
1,2,3... ? Am I doing this wrong?

Could you be a bit more clear about what the fields mean and what the values
mean?

 

[John W. Vinson]

hello to all

I would like to restate a question I posted a few days ago in different
terms. I received excellent advice from Allen Browne and John Vinson but, as
a beginner, I'm having trouble implementing their suggestions.

I have a form that shows a student's ID and name in the header. In the body
of the form are 40 textboxes that will each accept an integer from 1 to 5.
These are evaluation scores for 40 different criteria. The fields may also
be left blank if they do not pertain to that particular student. I've called
these 40 textboxes s1, s2, s3 .... s40. The form is bound to a query that
looks like this:

What do *YOU* mean by the term "criteria"? In Access that's a term usually
used to define an item being searched for.

Value can be 1,2,3,4 or 5. Field runs from 1 to 40.

ID Value Field
bro_jon 4 1
bro_jon 3 2
bro_jon 5 3
bro_jon 5 4
bro_jon 5
................... etc ...........
bro_jon 4 40
sam_tyl 2 1
sam_tyl 4 2
................... etc ...........

Is this the structure of your Table? Do you actually have fields named Value
and Field? If so they're both reserved words, and should be changed.

QUESTION - For any given ID, say bro_jon, how do I get the value keyed into
textbox s1, s2, s3.... on the form to be recorded as the Value for Field
1,2,3... ? Am I doing this wrong?

Well, by using 40 unbound (I presume - do textboxes S1 through S40 have
anything in their Control Source property?) textboxes on your form, you are
certainly making your job much more difficult! For one thing, you're casting
the structure of your evaluation in concrete. Suppose next month you come up
with a list of 45 criteria, 38 of them drawn from the existing 40, and 7 new
ones? You want to redesign your form, change a whole lot of labels, etc.?
ouch!!

If so, you will need to put a command button on the Form. It will open a
Recordset based on your table; it will need to poll through the 40 textboxes
looking for non-NULL values, and if it finds one, use the AddNew method to
create a new record in the table, copy the value from the ID and the textbox
to it... fairly complex code.


I would suggest a different database structure entirely. You could have three
tables such as

Students
StudentID
LastName
FirstName <etc.> <you presumably already have such a table>

Criteria
CriterionNo <Number, Long Integer, Primary Key>
Criterion <Text, what's on your textbox labels now>
<other info about this criterion, e.g. range of valid values; you might want
to have more flexibility to include yes/no criteria, date criteria, etc.>

Evaluations
StudentID <who's being evaluated>
CriterionNo <what they're being evaluated on>
Measurement <your 1-5 value, currently>

You could use a Form based on Students with a continuous Subform based on a
Query:

SELECT Criteria.CriterionNo, Criteria.Criterion, Evaluations.CriterionNo,
Evaluations.StudentID, Evaluations.Measurement
FROM Criteria LEFT JOIN Evaluations
ON Criteria.CriterionNo = Evaluations.CriteriaNo
ORDER BY Criteria.CriterionNo;

Have textboxes on the subform for Criteria.CriterionNo, Criteria.Criterion,
and Evaluations.Measurement, and use StudentID as the subform's master/child
link field. Set the Locked property of CriterionNo and Criterion to Yes - they
should be for display only, not for editing!

This subform will show you all 40 rows with the text meaning of each
criterion, and have a textbox in which you can enter the 1 - 5 value (or you
could use a Combo box instead to make controlled data entry easier).

The advantage here is that your criteria can now be edited simply by editing
the contents of the Criteria table; it's not necessary to change your Forms
design at all, you can add new criteria, delete criteria, change the text of
criteria at any time.

 

[Victoria]

john - thank you for your detailed reply. It is very clearly presented.

 

[Victoria]

hello to all

John - I've followed your advice to a "T" and have learned a lot. I have
one remaining question that is giving me trouble still.

My subform is based on the query you suggested, and it does indeed show 40
records with fields Criteria.CriterionNo, Criteria.CriterionDesc,
Evaluations.Measurement. But, when this subfom is placed in the main form
(frmStudents based on table Students) and linked by StudentID, then the
subform only shows those records where the student already has a measurement
in the Evaluations table. I really need it to show all 40 records so that I
can enter the student's values in the Measurement field. Any hints?

Much thanks

 

[tedmi]

Victoria: You really DON'T need to show all 40 evaluation records. If your
subform has the Allow Additions property set to Yes, you can add a new
evaluation record for the student displayed on the main form. Just scroll to
the bottom of the displayed rows, and type into the blank row there. Ideally,
you should make the control bound to the Criterion field a drop-down combo
box, whose row source is the query:
SELECT CriterionID, CriterionText FROM Criteria
WHERE CriterionID NOT IN
(SELECT CriterionID FROM Evaluation
WHERE StudentID=ThisStudentID)

This StudentID is a variable that contains the ID from the main student form.

 

[Amy Blankenship]

hello to all

John - I've followed your advice to a "T" and have learned a lot. I have
one remaining question that is giving me trouble still.

My subform is based on the query you suggested, and it does indeed show 40
records with fields Criteria.CriterionNo, Criteria.CriterionDesc,
Evaluations.Measurement. But, when this subfom is placed in the main form
(frmStudents based on table Students) and linked by StudentID, then the
subform only shows those records where the student already has a
measurement
in the Evaluations table. I really need it to show all 40 records so that
I
can enter the student's values in the Measurement field. Any hints?

You need to make another left join to the students table. The student
should be on the left side and your existing query should be on the right
side.

HTH;

Amy