Re: beginner query question

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

On Sat, 8 Mar 2008 16:08:00 -0800, Victoria
<Victoria@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:

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.
--

John W. Vinson [MVP]
.

Relevant Pages

  • CCWAPSS : a Comprehensive security scoring method for web applications
    ... We are pleased to release our first public release of the Common Criteria Web Application Security Scoring. ... This scale does not aim at replacing other evaluation standards but suggests a simple way of evaluating the security level of a web application. ... Offering a solution to interpretation problems between different auditors by providing clear and 11 well documented criteria. ... buy it or download a solution FREE today! ...
    (Pen-Test)
  • RE: IDS Common Criteria
    ... efforts to develop criteria for evaluation of IT security that are ... In the early 1980's the Trusted Computer System Evaluation ... I.B.M. Security & Privacy Services ... Subject: IDS Common Criteria ...
    (Focus-IDS)
  • Re: beginner query question
    ... john - thank you for your detailed reply. ... These are evaluation scores for 40 different criteria. ... it will need to poll through the 40 textboxes ...
    (microsoft.public.access.queries)
  • Valid vs. Correct
    ... But the conclusion is not known to be any more "correct" than the criteria and evaluation are correct. ... If you don't agree with the idea that all blue paintings are good, then you may rightly consider the conclusion to be incorrect even though the evaluation itself is a valid one based on the given criteria. ... Same as if you disagree only with the idea that the painting actually is blue. ... When it comes to art, the choice of criteria always has a degree of subjectivity to it, as does the evaluation against any realistically meaningful criteria. ...
    (rec.arts.fine)
  • Re: Query from an unbound form
    ... which would allow users to enter criteria into textboxes and pull up ... criteria for the query. ... were left blank every record from tblClients would show up. ...
    (microsoft.public.access.queries)