Create a unique group

[emjay]

Here's what I'm trying to do: I need a unique list of zip codes from a mailing list. That is, I need 1 record for each unique zip code as opposed to multiple records per zip.

I have a mailing list with a mess o' records. I can sort them, no problem. I can create a layout that counts the number of times a unique zip code appears and display that in Preview mode no problem.

What I need to do is somehow get those unique zip codes in a table so I can do something like select them individually and the do some relationship stuff to it.

How can I accomplish this?

Thanks for any input.

MJ

[LaRetta]

Run the script in this file called Create Unique Zips. It's a technique learned from Comment and it's become one of my favorites.

UPDATE: In truth, another table may not be necessary but you haven't given us anything to work with. But I pretended that this was a flat file of customers and their invoices and I needed to create a new customer table ( from the CustomerID ). Principle is the same ...

DOUBLE UPDATE: One of the particular beauties of this process is that, now that you have it established, you can add zip codes into your main table and easily 'update' your unique list at any time - it will NEVER pull in duplicates. If you fire it again, it will find no new records. But add a few zips into Zip Codes that aren't in Unique and watch same script pull them over. It is a nice way of 'sychronizing' your unique table ( or Customers table ). And it is very fast.

Gotta love it ...

zip_codes.zip

Edited June 8, 2008 by Guest
Added two updates

[LaRetta]

BTW, some might ask why we don't just use 'Update and Add to'. There are three reasons I can think of:

1) Update and Add To only works if there are records in the target table. Otherwise it won't let you even map it - major PIB when you first create the script because you must put dummy record in the table FIRST. Once it is written, you can delete the record back out but it stinks.

2) It takes twice as long as using only 'Add' and field-level validation. This may be partly because you must include a 'Show All Records' in the Target but that doesn't account for it taking twice as long.

3) Update and Add To requires at least one other field or it won't work. You can't run it with only one field (the field you will map by).

It is also faster than Smart Summaries according to my tests and doesn't require a relationship to do it.

[emjay]

thank you very much.

Very clear answer and very very helpful.

Cheers,

MJ