Update query - multiple criterias

  • Thread starter Thread starter Carlos
  • Start date Start date
C

Carlos

I want to populate a new field for all records in a table
based on the values in another field. How does one do
this in a single query when different values in the other
field should produce a different value in the new field?
e.g. If field "letter" has "A" update field number
to "1". If field "letter" has "B" update field "number"
to "2".
I know how to do it for one, but not for both in the same
query.
Thank you,

Carlos
 
Dear Carlos:

Your new column can be derived from another column in the same table.
Is that right.

Then don't store it. Derive it. Whatever rule you have that allows
you to derive it should be used to derive it any time the derived
value is needed.

Think of this. You say that whenever the existing column is A the
new, derived column should say "1". If it is a column, then someone
could change it to "2" while the existing column still says "A". Is
this the behavior you want? Is setting it to "1" based on the
existing column being "A" just an initial, default value? If so, then
create the new column in your table.

But if you want the new column to always be 1 when the existing column
is A, then it is not new data, and should not be stored.

If we are to help you, please explain fully the rule that makes A into
1, B into 2, etc. Is the existing column always a letter? To what
value in the new column does "R" become? Or, is "R" even in the
domain of the existing column? What range of values do you expect for
the new column?

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Hi,


In theory you do not update something that can be compute, you compute
it as needed. The reason is that is someone modify the first field,
forgiving to ALSO modify the dependant field, you got inconsistency and, to
be sure you have consistent data, you have to run the computation! exactly
as if you were not storing any "dependant" data, anyhow... so.


SELECT letterField,
1+ASC(UCASE(letterField)) - ASC("A") As ComputedNumberField
FROM ...


and use that query, when you need data with the "computed" expression.



Hoping it may help,
Vanderghast, Access MVP
 
Thanks Tom,
My goal here is simply to create a one time
table/datasheet that I can export in Excel format for a
manager who has requested some data that I keep. I have
to merge this data from several tables which is easy
enough to do except that one piece of info he's asked for
I don't have in my database but is related to what is
there. The A = 1, B = 2 was just a simple analogy. The
actual case is the database lists routers connected to a
network. The same 4 router ids appear frequently in one
field. I simply want to add a "circuit id" (cctid) field
to match these 4 router ids. So for every record where
router1 appears, append cctid1; for every record where
router2 appears, append cctid2 ... for these 4 routers.
I initialy did a make table query to bring all the data
together, then added the cctid field and was going to
populate it with an update query. I can do this easily
enough with 4 different update queries (one for each
router id). I was wondering if there was a way to do all
this more efficiently, for this time, and any similar
requests in the future.

Thank you,

Carlos
 
Dear Carlos:

First of all, adding a column to the table would be extra work and a
bad idea. In order to be able to update the table, you would need a
query to create the values you are going to want. That query could
export to Excel and you'd be done.

If you have a way to code the change for the CircuitID you can just
put that in the query. Otherwise, it might be best to create a table
that gives the equivalence from RouterID to CircuitID. A join on this
table using RouterID (which should be the Primary Key of this new
table) would "lookup" the CircuitID for you in this query, and for any
future needs (assuming the table is kept up-to-date).

Does this help?

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Back
Top