Update / Select query

  • Thread starter Thread starter AnotherNewGuy
  • Start date Start date
A

AnotherNewGuy

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


Jeff Boyce said:
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
Click to expand...
Click to expand...
 
Missed a closing parentheses on the update query.

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")

You are probably much better off using the conversion table. And with
it you could even do the update query if needed. And the query would be
much simpler although it would populate the

UPDATE [CRA Download] INNER JOIN tblConvert
ON [CRA Download].[Type] = [tblConvert].[ValIn]
SET [CRA DownLoad].[Type] = [tblConvert].[ValOut]





'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================


John said:
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


Jeff Boyce said:
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?
Click to expand...
Click to expand...
Click to expand...
 
Thanks John. That's a much better solution. I was so intent on an update
query that I overlooked the obvious.

John Spencer said:
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


Jeff Boyce said:
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

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?
Click to expand...
Click to expand...
Click to expand...
 
Another plus to John's approach is if the coding "cross-walk" ever gets
modified (added/removed/altered), you can make a simple change in the
table...

Regards

Jeff Boyce
Microsoft Office/Access MVP

AnotherNewGuy said:
Thanks John. That's a much better solution. I was so intent on an update
query that I overlooked the obvious.

John Spencer said:
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?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top