Address Correction Dilemma 1

[losthorizon]

We are working on an address correction report, to track down "lost" members, and to bring our records up to date. We are running into the following problems: Some entries in our {member.address} database have been corrected or changed multiple times, either because a member has moved to a different address, or typographic errors have been corrected, (several times, in the case of some members.) An address1, address2, state & Zipcode (as an example) may ALL have been changed several times over the years, either because of "typos", or because of that member actually moving to a new address...BUT we are only interested in the MOST RECENT data. How do we go about filtering our data, to ONLY extract the "latest known address"? We are attempting to do this with Access 97, and, if possible, also creating a Crystal Report from the corrected data. The preliminary report we have right now shows a "current address" and a "former address" for comparison purposes, but it is not 100 % accurate.

 

[Guest_imported]

So how do you know which is the latest. If you have Address1, Address2, Address3, State and ZipCode, do you want Address3? What are the field names involved?
Paul

 

[losthorizon]

Thanks, I'll get back to your reply soon. I did locate some helpful information in the Win Update Office Assistance Center (as it pertains to Access 2002) called "Finding and Deleting Duplicate Records in Access 2002." There is also an article entitled "Finding the First or Last Record in a Query," which also sounds helpful.

 

[raskew]

Is there an "As Of" or "Date Entered" type field in all of this?

 

[losthorizon]

raskew, no, that information is currently not available in the data. That is something that we would have to build into the available Access 97 tables. That is a good thought, however, and something, I think, that we should strongly consider doing. It might be helpful to us in the future. Thanks!

 

[losthorizon]

raskew, okay, I stand corrected. A "posting date & time" stamp DOES show up in our "comments" field, once for every transaction recorded.

 

[raskew]

The way I might approach it is to:

(1) Add a date field (e.g., dtePosted) to the table (e.g. tblCorrectAddress)

(2) Create an update query to update dtePosted to the date portion of the date/time stamp in the comments field, use the cDate() or DateValue() function. How you extract the date from the comments field will depend on what else is in that field. In the example I created, I included only the date so it was easy (example below). If it's going to be a problem, please post back with examples of full Comments fields.

(3) Create a totals query, grouping on member name and selecting the max value of dtePosted, which will be the most current address. Once you're satisfied with the results, change the totals query to a make-table query and, voila, you've hopefully got a list of members and their most recent addresses.

Update Query:
UPDATE tblCorrectAddress SET tblCorrectAddress.dtePosted = CDate([Comment]);

Totals Query:
SELECT tblCorrectAddress.name, Max(tblCorrectAddress.dtePosted) AS MaxOfdtePosted
FROM tblCorrectAddress
GROUP BY tblCorrectAddress.name;

Make-table query:
SELECT tblCorrectAddress.name, Max(tblCorrectAddress.dtePosted) AS MaxOfdtePosted INTO tblCorrectAddress2
FROM tblCorrectAddress
GROUP BY tblCorrectAddress.name;

Note that in all of the above, I haven't included the address information, since the concern was to pick the most recent record and the actual address info didn't affect the outcome. If you elect to implement this solution, you should include address info.

 

[losthorizon]

Thank you!