changing values in a query

  • Thread starter Thread starter mtress
  • Start date Start date
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.
 
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;
 
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];
 
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...
 
Back
Top