Need to "denormalize" records for client mailing

[Scott A]

Hey Everyone -



I don't know that this is the right place to post this so if not please feel free to point me in the right area.

I have been given a list from a client where all individuals are each in their own records. I need to perform a mailing for them where I combine the names based on the address - 1 per household.

Fields are typical:
id, FirstName, LastName, Address, City, State, Zip + others that make the records unique.

Records are like this:

1, John, Smith, 123 main st, anywhere, WY, 99999, +
2, Judy, Smith, 123 main st, anywhere, WY, 99999, +
3, Owen, Everybody, 4 bankers blvd, washington, dc, 01432
4, Meto, Everybody, 4 bankers blvd, washington, dc, 01432

I need to be able to bring the records together without changing original data for the mailing like this:

John and Judy Smith....
Owen and Meto Everybody....

Can this be done with a query? Do I need to make some temp tables? OR ????

Sure could use some ideas. TIA for any help you can provide!

Scott

[orange]

What is the format of the list?? Text file?
Do you have more sample data?
How many records in total?
Is this your first database?
What do you plan on mailing? Where is that info located?

It is quite rare to have such data without typos, extra commas... etc.

[Micron]

Any chance you could add a table field (e.g. HoushldID) as a long type and manually pair the households and group by that ID? Going forward, you'd make this pairing excercise part of your input form. The reason being is that the only other way I can think of is (in code) to loop through your records and do comparisons. Kind of If last name in record2 <> record1, move to record3 and compare to 2. If the same, check the address (then the other fields in turn). The first method would be much more reliable because these two do not live at the same address according to that logic:
John, Smith, 123 main st, anywhere, WY, 99999
Judy, Smith, 123 main st., anywhere, WY, 99999
yet you would want them to be paired.

[Scott A]

Hi Orange

I actually am a sole proprietor mailer / mail house. I typically have the client give me their files in a .csv format. I'm fortunate in that the software I use standardizes the address elements to postal standards which makes searching the data for dupes much more easy. I don't have to worry about Ave vs Avenue or ST vs Street. Periods, extra spaces, etc all take care of.

I did run the data through CASS Certification, as well as the the National Change of Address (NCOA), through the PO and have received back the moves for the people in the list which I've managed to run through an update query successfully. All of the data is currently in the Access db.


There are 19,045 records of which there 6,432 duplicates - based on LastName+address+zip (5 digit). I have noticed that there are some addresses that are more than just "pairs" - in other words more than 2 at a house hold. To throw another monkey wrench into the mix I've also seen records where there are more than one last name at an address.

No not my first db rodeo but I'm used to working in other systems. I'm not a real 'whiz' (yet! LOl) in Access. I've just got to play the hand I've been dealt.

I'm not sure what relevance the type of mailing has but the first one is just an oversize post card. In the future I will be doing matched mailings - Merged letters with data personalized matched to the same address on the outside of the envelope. I have a high speed direct impression printer that sprays the address and bar code directly on the envelope or post cards.
The match mailings are a bear all on their own. Keeping them in postal order all while not getting the Jone's letter in the Smith's envelope is a real joy.

Not quite sure what you mean by "Where is that info located?"

The data for the list is property of the client but I could probably cobble together a sample data set with "names changed to protect the innocent".

Hope that clarifies things. I really appreciate the fast response!

Scott

[Scott A]

Micron -

See my previous reply to Orange that will fill in a lot of detail.
In answer to you question regarding adding fields, tables, etc. pretty much anything goes. I just have to be able to return their original data back to them with the updates.
The data sets will be changing for ME. The client will be giving me new data, depending on their marketing needs, on an ongoing basis. I'm just trying to build a tool in Access where I don't have to reinvent the wheel every time. I want to be able to import the data, manipulate it for the current need, an export it back out.

Manually pairing house holds would not be practical in this case. To costly in time for me and $$ for the client.

[orange]

Scott A,

Very interesting. When you said you had a list, I was wondering---in Access, in some other software, a file somewhere...

Seems you have much of the "production facility" in place and operational. I have seen software that "adapts/adjusts/configures" mailing address data to postal standards.

It seems one of your concerns is related to what makes a duplicate. I've worked on applications where we looked for duplicates in companies --we would use things like Operating Name, Legal Name, Alternate/Alias Names along with Mailing Address, Physical location Address, Shipping/receiving Address and then Postal Code, phone number in an attempt to get an algorithm with weighting that worked (most of the time).

I have also seen mailing systems that would take almost any name and address and send marketing literature. I recall one where Mr. Thomas was someone's cat!!! The owner questioned where the list originated based on a feedback questionnaire.

I have also seen systems where addresses and customized letters were not merged as such. The letters were configured to show an address through a "window envelop".

What rules do you/will you have to deal with multiple names at a given address? You can make some guesses as you have shown with John and Judy Smith. How do you envisage handling another name at that address, say
Paul Johnson? Also, in today's society it could easily be John Smith and Jane Doe who constitute a couple, that defies positive groupings based on address.
Banks and marketing companies typically mail to any name with an account. I know people with 3 or 4 bank accounts, and they will get 3 or 4 marketing blurbs during a campaign. So they are not "aggregating" based on address.

I agree generally with micron --every record in the system should have a unique identifier. And as you may mix and match some data you will keep the unique identifiers with the pieces. Also, as you have mentioned, this is not your first rodeo, so I would say map out what you plan to do, test it, and rejig as necessary.

I have a colleague on one forum who has been doing collecting, refining, mixing, matching, interpreting, interpolating facts from gazillions of records in multiple databases related to people, their ages, their likes, dislikes, pets, sports, travel, education, vehicle, profession ...... His clients are looking to sell or promote things to "potential customers". So he often has to manipulate files with such parameters as, has pets, not dogs, travels by air, vacationed more than twice in the last X years and an annual income >Y dollars. Next campaign may be tangential to the previous. He has spent years on this and his software hardware and design have evolved sufficiently to make it "more of a well paid pastime" now.

Anyway, good luck. It seems you have things organized and the forum is here if you have Access/database questions as you proceed.

[Micron]

Maybe try this: if you can add a text flag field to the table, use a find duplicates query as a table for an update query that gets the First record from the dupes (not 100% sure but I think you can make such a connection between the two queries) and update the field to "cmbd" (combined) based on your criteria for the address being exactly the same. Then dump that recordset into a local table for mailings. Then get the rest of the records with a new select query where txtFlag <> 'cmbd' and dump those into the mailing table.

Of course, I'm assuming you'd be content to choose the first name as the addressee for a combined household. If not, you'll need to modify the strategy and if need be, deal with any cases where the mailing address is not exact, and you say you have that covered. Don't try this on your master db copy