Since we do not tend to include the country name when addressing domestic mail, users tend to forget to enter this into the Constituent address. Having the country specified can enable you to perform certain queries, such as what is the geographic distribution for outgoing loans in a given year, and enables better formatting of the display address. Here are a few scripts I use for managing periodic maintenance on addresses in my database.

This first script will show you which records are missing the country, but which have a State/Province value that infers a domestic address. Uncomment the UPDATE to correct the values, but remember to verify the CountryID in your database is correct.

/* Add Missing Country Value to Domestic Addresses*/ 
SELECT * FROM [ConAddress]
--UPDATE [ConAddress] SET CountryID = 3
WHERE [STATE] IN ('Alabama',  'AL',
	'Alaska', 'AK',
	'Arizona', 'AZ',
	'Arkansas', 'AR',
	'California', 'CA',
	'Colorado', 'CO',
	'Connecticut', 'CT',
	'Delaware', 'DE',
	'District of Columbia', 'DC', 'D.C.',
	'Florida', 'FL',
	'Georgia', 'GA',
	'Hawaii', 'HI',
	'Idaho', 'ID',
	'Illinois', 'IL',
	'Indiana', 'IN',
	'Iowa', 'IA',
	'Kansas', 'KS',
	'Kentucky', 'KY',
	'Louisiana', 'LA',
	'Maine', 'ME',
	'Maryland', 'MD',
	'Massachusetts', 'MA',
	'Michigan', 'MI',
	'Minnesota', 'MN',
	'Mississippi', 'MS',
	'Missouri', 'MO',
	'Montana', 'MT',
	'Nebraska', 'NE',
	'Nevada', 'NV',
	'New Hampshire', 'NH',
	'New Jersey', 'NJ',
	'New Mexico', 'NM',
	'New York', 'NY',
	'North Carolina', 'NC',
	'North Dakota', 'ND',
	'Ohio', 'OH',
	'Oklahoma', 'OK',
	'Oregon', 'OR',
	'Pennsylvania', 'PA',
	'Rhode Island', 'RI',
	'South Carolina', 'SC',
	'South Dakota', 'SD',
	'Tennessee', 'TN',
	'Texas', 'TX',
	'Utah', 'UT',
	'Vermont', 'VT',
	'Virginia', 'VA',
	'Washington', 'WA',
	'West Virginia', 'WV',
	'Wisconsin', 'WI',
	'Wyoming', 'WY') 
AND CountryID = 0

Another situation that occurs is that users will occassionally write out the name of the State, instead of using the appropriate postal abbreviations. Here is a series of scripts for correcting those.

/* Abbreviate the State/Province field */
UPDATE [ConAddress] SET [State] = 'AL' WHERE [State] = 'Alabama';
UPDATE [ConAddress] SET [State] = 'AK' WHERE [State] = 'Alaska';
UPDATE [ConAddress] SET [State] = 'AZ' WHERE [State] = 'Arizona';
UPDATE [ConAddress] SET [State] = 'AR' WHERE [State] = 'Arkansas';
UPDATE [ConAddress] SET [State] = 'CA' WHERE [State] = 'California';
UPDATE [ConAddress] SET [State] = 'CO' WHERE [State] = 'Colorado';
UPDATE [ConAddress] SET [State] = 'CT' WHERE [State] = 'Connecticut';
UPDATE [ConAddress] SET [State] = 'DE' WHERE [State] = 'Delaware';
UPDATE [ConAddress] SET [State] = 'DC' WHERE [State] = 'District of Columbia';
UPDATE [ConAddress] SET [State] = 'FL' WHERE [State] = 'Florida';
UPDATE [ConAddress] SET [State] = 'GA' WHERE [State] = 'Georgia';
UPDATE [ConAddress] SET [State] = 'HI' WHERE [State] = 'Hawaii';
UPDATE [ConAddress] SET [State] = 'ID' WHERE [State] = 'Idaho';
UPDATE [ConAddress] SET [State] = 'IL' WHERE [State] = 'Illinois';
UPDATE [ConAddress] SET [State] = 'IN' WHERE [State] = 'Indiana';
UPDATE [ConAddress] SET [State] = 'IA' WHERE [State] = 'Iowa';
UPDATE [ConAddress] SET [State] = 'KS' WHERE [State] = 'Kansas';
UPDATE [ConAddress] SET [State] = 'KY' WHERE [State] = 'Kentucky';
UPDATE [ConAddress] SET [State] = 'LA' WHERE [State] = 'Louisiana';
UPDATE [ConAddress] SET [State] = 'ME' WHERE [State] = 'Maine';
UPDATE [ConAddress] SET [State] = 'MD' WHERE [State] = 'Maryland';
UPDATE [ConAddress] SET [State] = 'MA' WHERE [State] = 'Massachusetts';
UPDATE [ConAddress] SET [State] = 'MI' WHERE [State] = 'Michigan';
UPDATE [ConAddress] SET [State] = 'MN' WHERE [State] = 'Minnesota';
UPDATE [ConAddress] SET [State] = 'MS' WHERE [State] = 'Mississippi';
UPDATE [ConAddress] SET [State] = 'MO' WHERE [State] = 'Missouri';
UPDATE [ConAddress] SET [State] = 'MT' WHERE [State] = 'Montana';
UPDATE [ConAddress] SET [State] = 'NE' WHERE [State] = 'Nebraska';
UPDATE [ConAddress] SET [State] = 'NV' WHERE [State] = 'Nevada';
UPDATE [ConAddress] SET [State] = 'NH' WHERE [State] = 'New Hampshire';
UPDATE [ConAddress] SET [State] = 'NJ' WHERE [State] = 'New Jersey';
UPDATE [ConAddress] SET [State] = 'NM' WHERE [State] = 'New Mexico';
UPDATE [ConAddress] SET [State] = 'NY' WHERE [State] = 'New York';
UPDATE [ConAddress] SET [State] = 'NC' WHERE [State] = 'North Carolina';
UPDATE [ConAddress] SET [State] = 'ND' WHERE [State] = 'North Dakota';
UPDATE [ConAddress] SET [State] = 'OH' WHERE [State] = 'Ohio';
UPDATE [ConAddress] SET [State] = 'OK' WHERE [State] = 'Oklahoma';
UPDATE [ConAddress] SET [State] = 'OR' WHERE [State] = 'Oregon';
UPDATE [ConAddress] SET [State] = 'PA' WHERE [State] = 'Pennsylvania';
UPDATE [ConAddress] SET [State] = 'RI' WHERE [State] = 'Rhode Island';
UPDATE [ConAddress] SET [State] = 'SC' WHERE [State] = 'South Carolina';
UPDATE [ConAddress] SET [State] = 'SD' WHERE [State] = 'South Dakota';
UPDATE [ConAddress] SET [State] = 'TN' WHERE [State] = 'Tennessee';
UPDATE [ConAddress] SET [State] = 'TX' WHERE [State] = 'Texas';
UPDATE [ConAddress] SET [State] = 'UT' WHERE [State] = 'Utah';
UPDATE [ConAddress] SET [State] = 'VT' WHERE [State] = 'Vermont';
UPDATE [ConAddress] SET [State] = 'VA' WHERE [State] = 'Virginia';
UPDATE [ConAddress] SET [State] = 'WA' WHERE [State] = 'Washington';
UPDATE [ConAddress] SET [State] = 'WV' WHERE [State] = 'West Virginia';
UPDATE [ConAddress] SET [State] = 'WI' WHERE [State] = 'Wisconsin';
UPDATE [ConAddress] SET [State] = 'WY' WHERE [State] = 'Wyoming';

Finally, if users have not selected a country, then the address format probably has not been set either. This is especially true in new database conversions, but it is relatively easy to set the format based upon the country value. Under Maintenance » Authorities » Countries, set the default format for each country in your system.

Once this is completed, the following script , then run the following script.

/* Set Address Format to Country Default */
UPDATE CA SET CA.AddressFormatID = C.DefaultAddrFormatID
FROM ConAddress CA
INNER JOIN Countries C ON CA.CountryID = C.CountryID

If you use any of these scripts, remember that you will also need to update the ConAddress Display field. There is a function for this in DBConfig under Maintenance » Database » Update ConAddress Display which will take care of all that for you.