Updating a table

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I have a question regarding updating a table. I am currently converting
dbase to access but do not know how you would update a table directly before
it's seen in a form. In dbase i can run a program(which evaluates each
record with a do while not eof() and replaces blank information) to directly
change a table before a form is viewed. How do you do this in access?
dbase program example:
USE WEBCPT **opens table
DO WHILE .NOT.EOF()
RCSCORE=0 ***variable
RECOMMEND=" "
rscre=""
DO CASE
CASE RCSCORE=0
RSCRE="NO"
CASE (RCSCORE>=20.0.AND.RCSCORE< 34.0)
RSCRE="RLO"
CASE (RCSCORE>=34.0 .AND.RCSCORE<53.0)
RSCRE="BR"
CASE (RCSCORE>=53.0 .AND.RCSCORE< 68.0)
RSCRE="WAIT/WRIT"
*CASE (RCSCORE>=64.0 .AND.RCSCORE< 68.0)
* RSCRE="CRREQ"
CASE (RCSCORE>=68.0.AND.RCSCORE< 120)
RSCRE="ELE"
ENDCASE
IF RSCRE="RLO"
RECOMMEND="RETEST"
ENDIF
IF RSCRE="BR"
RECOMMEND="BR"
ENDIF
IF RSCRE="WAIT/WRIT"
RECOMMEND="WAIT/WRIT"
ENDIF
IF RSCRE="CRREQ"
RECOMMEND="CRREQ"
ENDIF
IF RSCRE="ELE"
RECOMMEND="ELECT OPT"
ENDIF
REPLACE READPL WITH RECOMMEND
SKIP
ENDDO
REPL READPL WITH 'DNT' FOR RCSCORE=0
RETURN
 
How do you do this in access?
Click to expand...

By basing the form on a Query doing the necessary calculations. No code is
needed at all, and one would generally not need to change the data in the
table at all. If you do need to do so, an Update query would be appropriate.

dBase requires that you write code to do almost anything interesting. Access
lets you write code, of course, but there are a great many things that simply
don't NEED code, and/or can be better done using Queries instead. They are
very different programming environments and require a different mindset!

John W. Vinson [MVP]
 
Would you be able to do this in one update query(as this is one of the
shorter programs the others are much more involved)? What I need to do is
based on the scores that are imported evaluate and make a course name so that
when someone opens the form and looks up the student the course placement is
already there.
 
Would you be able to do this in one update query(as this is one of the
shorter programs the others are much more involved)? What I need to do is
based on the scores that are imported evaluate and make a course name so that
when someone opens the form and looks up the student the course placement is
already there.
Click to expand...

The course name should exist *only* in the Courses table. It should certainly
not be updated or imported into the scores table!

Unlike dBase, Access uses the relational paradigm. Information is stored once,
and once only, and is NOT copied from table to table. Instead you can use
(say) a Combo Box on a form, bound to the CourseID field in your scores table,
but displaying the course name. In a Report you would use a Query joining the
scores table to the courses table, pulling the course name from one table, the
score from another table, and the student's name from a third table.

Stop, step back, and relearn "the way things are always done". Being skilled
in dBase may be doing you more harm than good, because you have an expectation
of how things should work - *and they don't work that way!*

To directly answer your question... yes, you can update the entire table with
a single update query. No code is needed.

John W. Vinson [MVP]
 
I'm not trying to copy the courses from table to table. The course
placements are determined by certain raw scores which are imported then
evaluated to come up with a course placement.
Much like saying if raw score is 68 then the course placement should be "no
reading required" and that information should be put into a courseplacement
field.

I did do a update query which worked but I could only put in one evaluation
and I have quite a few which is why I was wondering if there was a way around
that.

I think I have a fair understanding on how Access is different from Dbase
which is why I'm looking at alternative ways to achieve the results needed.
 
I'm not trying to copy the courses from table to table. The course
placements are determined by certain raw scores which are imported then
evaluated to come up with a course placement.
Much like saying if raw score is 68 then the course placement should be "no
reading required" and that information should be put into a courseplacement
field.
Click to expand...

Sorry I misunderstood the problem.

One way to handle this kind of translation from a range to a value is to add a
lookup table named Ranges, with fields Low, High, and Placement:

0; 20.0; "NO"
20.0; 34.0; "RLO"
34.0; 53.0 "BR"

and so on.

Create a "Non Equi Join" query like

SELECT yourtable.<whatever>, Ranges.Placement
FROM <yourtable>
INNER JOIN Ranges
ON yourtable.RCSCORE >= Ranges.Low
AND yourtable.RCSCORE < Ranges.High;

Since the placement value can be looked up at will, it's probably neither
necessary nor appropriate to store it.

John W. Vinson [MVP]
 
Back
Top