Easier way would be to build a conversion table.
tblConvert
ValIn: Has values such as C1, F1, H1 etc
ValOut: Has values 01, 02, or 03
Then you just add this table to your query and join Type to ValIn. Then
the correct ValOut is available.
If you actually want to use an update query then you need something like
the following.
UPDATE [CRA download]
SET [CRA download].TYPE =
IIf([TYPE] in ("C1","F1","H1","H2","H5","H7"),"01",
IIf([TYPE] in ("G1","G5","D1"),"02",
IIf([TYPE] in ("A1","E3"),"03","")))
WHERE [CRA download].TYPE IN
("C1","F1","H1","H2","H5","H7","G1","G5","D1","A1","E3"
'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================
I do realize that. The data comes from an AS400 query, and I keep a
copy of
the original. I'm running the query against it to isolate the 11 types
we
need, then I'm importing it into yet another application, which needs
the
different type identifiers.
I've done the following, and if finding the correct records, but it
replaces
everything with "01."
UPDATE [CRA download] SET [CRA download].TYPE = IIf("C1" Or "F1" Or
"H1" Or
"H2" Or "H5" Or "H7","01",IIf("G1" Or "G5" Or "D1","02",IIf("A1" Or
"E3","03"," ")))
WHERE ((([CRA download].TYPE)="C1" Or ([CRA download].TYPE)="F1" Or
([CRA
download].TYPE)="H1" Or ([CRA download].TYPE)="H2" Or ([CRA
download].TYPE)="H5" Or ([CRA download].TYPE)="H7" Or ([CRA
download].TYPE)="G1" Or ([CRA download].TYPE)="G5" Or ([CRA
download].TYPE)="D1" Or ([CRA download].TYPE)="A1" Or ([CRA
download].TYPE)="E3"));
I'll try updating a different field and see how that goes.
thx
:
You do realize, right, that once you change the value in the table,
there's
no going back?
Instead of dumping the input exactly as received, then altering the
values,
another approach would be to import the raw data as-is, then use
queries to
"parse" it into a more useful (?and better normalized) data structure
(i.e.,
"permanent" tables). That way, if you ever need to, you could step
back to
the raw input data and re-convert it...
Good Luck!
Regards
Jeff Boyce
Microsoft Office/Access MVP
message
Weekly, we get a large number of records with a Type field. There
are
many
types -- A1, H1, H3, etc. Most we won't use, but there are 11 that
we
need
to pull. That's easy enough, except that if it's "A1" or "C1" or
"D2", it
needs to change to "01". There are other combinations, but
ultimately,
the
11 different types will consist of "01", "02", or "03".
The only thing I can think of doing is to use an Update query against
the
original table with a long, convoluted iif() statement in the Update
to
update the applicable fields. Then use a select query to select
types
"01",
"02", and "03."
Am I on the right track? Or is there a better way?
