DO you have an active flag in the table which provides this value? If so, a technique I have seen, from Armen at J Street
, is to change the way such "inactive" records display and sort.
Assumption: you do have an inactive flag which identifies inactive records (true if they are inactive).
Select CountryID, Iif([InActiveFlag]=True,"*" & [CountryName], [CountryName]) AS Country
ORDER BY InActiveFlag DESC, CountryName
This places an asterisk "*" in front of the name of each inactive countryname, alerting users that they are different. Because Access uses -1 for "True" and 0 for False, it also sorts these names to the bottom of the list so users have to scroll down to see them.
If you have an Inactive date instead of an inactive flag:
Select CountryID, Iif(IsNull([InActiveDate]),[CountryName], "*" & [CountryName]) AS Country
ORDER BY InactiveDate, CountryName
Sorting is not quite so neat, but it as the same overall effect.
Edited by: GroverParkGeorge on Wed Nov 11 10:33:19 EST 2009.