Update table field based on another field.

freud52

New member
Local time
Yesterday, 16:13
Joined
Mar 3, 2008
Messages
4
Ok. I've got a table named SIPATable and some of the fields contain summed values. Let's say for instance I have a field named "GaDRaw", which represents a raw score. now...I need to update a field called "GaDPer", based upon the value in "GaDRaw". So...If "GaDRaw" <=16, then I would want "GaDPer" to be updated to = 35. Further, if "GaDRaw" = 17. then I want "GaDPer" to = 37. and so on....

I think it's an If Then Else statement, but not sure how or where to place it. Id like it to be as code under a command button, and update the table all at once.
Any help would be appreciated.
Thanks in advance,
Freud52
 
Are you storing calculated fields in your tables? That tends to be frowned upon as bad practice. But whatever, you could search the forum for examples of case Select or Iif statements, although the experts here might favour a function of some sort.
 
Firstly I agree with Barry's first 2 sentences, then I need to ask how far does the "and so on " go. Is there an arithmetic relationship between the 2 fields after gadraw >16.

brian
 
yes, the "GaDRaw" field, or raw score, is related to the "GaDPer" field, or percentile score. the formula to calculate the percentile from the raw score escapes me. i'm using a graph i have that has the raw scores and %tile scores for reference. and yes, i know it's bad form to work off of a calculated field, but i'm desperate to get it to work. the raw scores can be anywhere from 8 to 40, and the %tile start at 35 and go to 99. so...whatever is calculated in the GaDRaw field will determine what gets placed in the GaDPer field. Any ideas would be greatly appreciated!
 
Since the GadPer cannot be calculated from the GadRaw wouldn't the simplest thing be to put them togather in a Table?

Brian
 
do you mean make another table, and say, call it GadTable, and have 2 columns, one for GaDRaw and one for GaDPer? OK, but how do i pull out the correct GaDPer value, based upon the value of GaDRaw - then populate the field in the first table? can you show me an example of what you are talking about?
 
If its a one off a simple update 1 table by values in another, else it will be a Dlookup, both are covered in ACCESS help.

Brian
 
I'm going to assume you can compute the percentile by hand. (If you can't, Access will probably not do it for you.) I'll show you the way I would approach it, but in the final analysis, you are the one who will have to make the formulas work. OK? Let's start...

You can help yourself by reading up on DSum, DMax, DMin, DLookup, and DCount in the Help Files including taking a quick peek at the examples. This forum also has threads containing the function names. Write a parameter query (q.v. in the Help Files) to select AND SORT (!!!) the subset of your total data set (if that's the way you are keeping things). Then later, you will run the Dxx functions on the query rather than the whole table. Should be faster.

You said you were willing to have a button do this for you. If you haven't done so by now, read up on the OnClick event on a form. When building a button on a form, have the form's control wizards enabled at the time. One of the "presets" for building buttons is "Run Code" - and if you have the entry point already defined, you will be allowed to run a subroutine. (NOT a function! It should not return a value.)

For the code, read up on opening recordsets in code segments. In the code, do a DCount on the parameter query before you open it in order to determine the total population. Keep that in a code-local variable declared through a Dim statement. Open the recordset and do a .MoveFirst on it.

In a loop, read through the recordset represented by the query until the .EOF property becomes true. For each value you find in the recordset, do a DCount of the number of entries less than or equal to the current value. Then your percentile is (something like)

pctile = CINT( DCount(values <= this value)*100 / DCount(total) )

but... remember that you never have percentiles of 0 or 100.

For very large groups, this forumula COULD give you a percentile < 1 (say, at least 201 members in population and a single value equal to DMin(query). I'm also guessing that for really big populations and a unique value equal to DMax(query), you might get a percentile of 100. So you will need some "IF" statements in the code to catch these two cases. NOTA BENE: This is also similar to rank-ordering, the difference being that for rank-order, you DON'T divide by the DCount(total) AND you have to show the ranges where there is a tie. But a similar approach for rank-ordering is also valid.

OK, having computed this value, you already have the recordset open. Sorting and searching don't necessarily make a query non-updateable. So do a .EDIT of the recordset, copy/set the computed percentile in a field that you include in the recordset, and do the .UPDATE of the recordset. Then step to the next record (.MoveNext) and loop until you reach the exit condition of the loop. Close the recordset and you are done.

This is a misleading situation from the DB purist standpoint. Mathematically, the percentile formula as I just described SHOULD be computable on the fly, but the syntax of Access makes that a somewhat tricky proposition. Normally, you would say NEVER store a computed value. However, there are exceptions to every rule. If you were willing to work with a pug-ugly formula, I would say don't store it, just compute it as you need it. But since the values depend on the population as a whole of a parameter query that could select subsets, the nastiness of the formula is an EXCUSE for violating an otherwise solid concept, "don't store a computable value." And not a bad excuse as such things go.
 

Users who are viewing this thread

Back
Top Bottom