Combine Zipcodes into 1 field

[samiam07]

I have a table with states, countys and zips. Many counties have more than one zip. Some may have a hundred or more. This zip table has about 80k records.

I need to create a table that has a state field, a county field and one field that has all the zips for that county separated by commas.

How about creating an array for each state/county combo that has all the state/county zips. Each zip should only be in the array once and, of course, it needs to be sorted by zip.

I figure it'll be some kind of looping function to create the array, but I'm at a loss as to where to start it. Could anyone give me a whack on the side of the head and point me in the right direction?
Thanks

 

[Remou]

DHookom's concatenate function may suit. You will find it in the FAQs. However, what you intend to do is against the rules of normalization, which would dictate that you use two table, one for state and county and one with all the zip codes:

tblStateCounty
ID
State
County

tblZips
StateCountyID
Zip

 

[missinglinq]

As Remou hinted at, this is a terrible idea!Why do you "need" to have one field that has all the zips for a given county?

The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!

 

[SkipVought]

samiam07,

I happen to also agree that "one field that has all the zips for that county separated by commas" is a horrible design.

Unless you have some database background, you do not realize the disaster that that would be.

Remou has proposed a sound table design, that can accomplish what you need to do, associating all the zip codes in a county.

Believe me, you do NOT want to go where you envision!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]