changing values in a query

M

mtress

I have an append query based on an imported table. Would
like to change the values in certain fields e.g. 'R&D'
to 'Research and Development' and so on. Trying to make
subsequent report more readable. I'm limited in my SQL
but I'm looking to improve. Thanks.
 
K

Ken Snell [MVP]

Let's assume that your current append query is adding single-field records:

INSERT INTO TargetTableName ( FieldName )
SELECT SourceTableName.FieldTitle
FROM SourceTableName;

What you do is change the source field to be a calculated field that uses
nested IIf functions to select the replacement text:

INSERT INTO TargetTableName ( FieldName )
SELECT IIf(SourceTableName.FieldTitle = "R&D",
"Research and Development",
IIf(SourceTableName.FieldTitle="EOM", "End Of Month",
SourceTableName.FieldTitle))
FROM SourceTableName;

If you have a lot of possible replacements, then create a table (name it
ReplaceText) with two fields:
InputString
OutputString

Put the various pairings of original and resulting strings in this table as
individual records. Then use an append query that links to that table:

INSERT INTO TargetTableName ( FieldName )
SELECT IIf(ReplaceText.OutputString Is Null,
SourceTableName.FieldTitle, ReplaceText.OutputString)
FROM SourceTableName LEFT JOIN
ReplaceText ON SourceTableName.FieldTitle =
ReplaceText.InputString;
 
M

mtress

I'm beginning to see. Where do I put the statement (I
will be creating table ReplaceText)? Here is my append
query I have now. Its the SBU field I'm trying to
replace text in. Do I need to insert this new statement
someplace in the old?

INSERT INTO INVtotals ( SBU, [First Name], [Last Name],
[Sum Of Qty], [Sum Of Sq Ft], [Sum Of Cost] )
SELECT DISTINCTROW OWNERS.SBU, OWNERS.[First Name],
OWNERS.[Last Name], Sum(INVothers.Qty) AS [Sum Of Qty],
Sum(INVothers.[Sq Ft]) AS [Sum Of Sq Ft], Sum
(INVothers.Cost) AS [Sum Of Cost]
FROM OWNERS INNER JOIN INVothers ON OWNERS.Owner =
INVothers.Owner
GROUP BY OWNERS.SBU, OWNERS.[First Name], OWNERS.[Last
Name];
 
K

Ken Snell [MVP]

Try this (not tested!):

INSERT INTO INVtotals ( SBU, [First Name], [Last Name],
[Sum Of Qty], [Sum Of Sq Ft], [Sum Of Cost] )
SELECT DISTINCTROW IIf(ReplaceText.OutputString Is Null,
OWNERS.SBU, ReplaceText.OutputString),
OWNERS.[First Name],
OWNERS.[Last Name], Sum(INVothers.Qty) AS [Sum Of Qty],
Sum(INVothers.[Sq Ft]) AS [Sum Of Sq Ft], Sum
(INVothers.Cost) AS [Sum Of Cost]
FROM INVothers INNER JOIN
(OWNERS LEFT JOIN ReplaceText ON
OWNERS.SBU = ReplaceText.OutputString) ON
INVothers.Owner = OWNERS.Owner
GROUP BY OWNERS.SBU, OWNERS.[First Name],
OWNERS.[Last Name];


--

Ken Snell
<MS ACCESS MVP>

mtress said:
I'm beginning to see. Where do I put the statement (I
will be creating table ReplaceText)? Here is my append
query I have now. Its the SBU field I'm trying to
replace text in. Do I need to insert this new statement
someplace in the old?

INSERT INTO INVtotals ( SBU, [First Name], [Last Name],
[Sum Of Qty], [Sum Of Sq Ft], [Sum Of Cost] )
SELECT DISTINCTROW OWNERS.SBU, OWNERS.[First Name],
OWNERS.[Last Name], Sum(INVothers.Qty) AS [Sum Of Qty],
Sum(INVothers.[Sq Ft]) AS [Sum Of Sq Ft], Sum
(INVothers.Cost) AS [Sum Of Cost]
FROM OWNERS INNER JOIN INVothers ON OWNERS.Owner =
INVothers.Owner
GROUP BY OWNERS.SBU, OWNERS.[First Name], OWNERS.[Last
Name];
-----Original Message-----
Let's assume that your current append query is adding single-field records:

INSERT INTO TargetTableName ( FieldName )
SELECT SourceTableName.FieldTitle
FROM SourceTableName;

What you do is change the source field to be a calculated field that uses
nested IIf functions to select the replacement text:

INSERT INTO TargetTableName ( FieldName )
SELECT IIf(SourceTableName.FieldTitle = "R&D",
"Research and Development",
IIf(SourceTableName.FieldTitle="EOM", "End Of Month",
SourceTableName.FieldTitle))
FROM SourceTableName;

If you have a lot of possible replacements, then create a table (name it
ReplaceText) with two fields:
InputString
OutputString

Put the various pairings of original and resulting strings in this table as
individual records. Then use an append query that links to that table:

INSERT INTO TargetTableName ( FieldName )
SELECT IIf(ReplaceText.OutputString Is Null,
SourceTableName.FieldTitle, ReplaceText.OutputString)
FROM SourceTableName LEFT JOIN
ReplaceText ON SourceTableName.FieldTitle =
ReplaceText.InputString;

--

Ken Snell
<MS ACCESS MVP>






.
Click to expand...
Click to expand...
 
Top