Merging City state zip code table into query single list no duplicate city state

Question

I have a table in access of 42,000 plus rows which contains

column1 = City Column2 = State Column3 = zip

Now it has multiple rows for cities with multiple zips. SO you could have have four rows as such: (Note Raleigh is Column1, NC is Column2, and 29706 is Column3)

Raleigh NC 29706
Raleigh NC 29707
Raleigh NC 29708
Raleigh NC 29709

I want to search the table and return the City State one time as such "Raleigh NC" with all the zips that are related to it in a single column in a query (IE: result being like such)

Raleigh NC 29706 29707 29708 29709

I have been trying to accomplish this for a week now and have found tons of concats and such but none seem to remove the duplicate cities and list the zips in the same cell with the city state as one column record.

Any help would be greatly appreciatted.

Answer 1

Good problem. I don't know Access, but this article seems to tackle the problem in SQL rather nicely. Perhaps you can inspire yourself with some of the techniques if you familiar with it.

Anyhow, the solution appear to revolve around two things:

1. First have a unique list of cities (in SQL this is done by select city, state group by city, state). Then find a way to create a query that assigns a unique row number (1,2,3,) to every zip for a given city, and then query that query to concatenate using case(1) + case(2), etc. This is the first solution in the article.
2. Use VBA to make a function for it.