Hello all,
I am trying to replace a macro in Access that makes a series of SQL queries to update a table used in more processing afterwards. I am pretty new to creating work benches and wanted to get some input as far as how I might construct a workbench to do this and the transformers I would use. I am getting lost in whether I can create a more linear process with transformers in sequential order, or if it will take a different way of thinking to build it that incorporates different pieces at different times. I am more used to ArcGIS Modelbuilder and this is just different enough get me second guessing all ideas.
Here are all of the SQL queries the macro executes, in order with a couple of notes I added for clarification:
NOTE: I know this part can be done with the truncate option in the writer:
DELETE StagingTable.*
FROM StagingTable;
________
INSERT INTO StagingTable (PIN, MailingTo, FullAddress, CityState, ZipCode)
SELECT SourceTable1.PIN, "" AS MailingTo, SourceTable1.StreetAddress AS FullAddress, "DES MOINES WA" AS CityState, SourceTable1.ZipCode
FROM SourceTable1
ORDER BY SourceTable1.PIN;
________
NOTE: This is SourceTable2Query mentioned in the statement after it:
SELECT SourceTable2.Pin, Trim([TaxpayerName] & " " & [AttnLine]) AS MailingTo, SourceTable2.AddrLine AS FullAddress, SourceTable2.CityState, SourceTable2.ZipCode
FROM SourceTable2;
__
UPDATE StagingTable INNER JOIN SourceTable2Query ON StagingTable.PIN = SourceTable2Query.Pin SET StagingTable.MailingTo = [SourceTable2Query].[MailingTo]
WHERE (((SourceTable2Query.CityState) Like "DES MOINES*"));
________
UPDATE StagingTable SET StagingTable.MailingTo = "Current Occupant"
WHERE (((StagingTable.MailingTo)=""));
________
INSERT INTO StagingTable ( Pin, MailingTo, FullAddress, CityState, ZipCode )
SELECT SourceTable2.Pin, Trim([TaxpayerName] & " " & [AttnLine]) AS MailingTo, SourceTable2.AddrLine AS FullAddress, SourceTable2.CityState, SourceTable2.ZipCode
FROM SourceTable2;
________
UPDATE StagingTable SET StagingTable.CityState = Replace([CityState]," "," ")
WHERE (((StagingTable.CityState) Like "* *"));
__
UPDATE StagingTable SET StagingTable.CityState = Replace([CityState]," "," ")
WHERE (((StagingTable.CityState) Like "* *"));
__
UPDATE StagingTable SET StagingTable.CityState = Replace([CityState]," "," ")
WHERE (((StagingTable.CityState) Like "* *"));
________
UPDATE StagingTable SET StagingTable.Major = Left([PIN],6);
________
UPDATE StagingTable SET StagingTable.JoinPin = [PIN]
WHERE (((StagingTable.JoinPin) Is Null));
________
UPDATE StagingTable INNER JOIN SourceTable3 ON StagingTable.JoinPin = SourceTable3.PIN SET StagingTable.JoinPin = [SourceTable3].[Pin], StagingTable.MailingTo = "Current Occupant";
________
UPDATE StagingTable INNER JOIN SourceTable4 ON StagingTable.PIN = SourceTable4.PIN SET StagingTable.JoinPin = [SourceTable4].[Pin], StagingTable.MailingTo = "Current Occupant";
________
SELECT DISTINCT StagingTable.PIN, StagingTable.JoinPin, StagingTable.MailingTo, StagingTable.FullAddress, StagingTable.CityState, StagingTable.ZipCode INTO FinalTable IN '\\DatabasePath\Database\DataTable'
FROM StagingTable
ORDER BY StagingTable.PIN;Any help is much appreciated!
