Query Help

[mbogan]

I have a data table in ACCESS with 2 Fields:

1) REPCODE
2) STATE

There are many duplicate REPCODES that list different STATES. HEre is the Example:

REPCODE, STATE

000111 | NJ
000111 | NY
000111 | PA
000222 | TX
000333 | NJ
000333 | NY

How can I write a query that will combine the STATE code values in to ONE text string with a comma seperating the States? EXAMPLE:

REPCODE | STATE
000111 | NJ,NY,PA
000222 | PA
000333 | NJ, NY

Any one have a suggestion??

 

[Joe4]

If you add a third field, you can use a CrossTab query. Here is what I did. Add a Counter field, where you number each state for each rep code, starting with 1 for each REPCODE (i.e. 000111 has three states, so have them list 1, 2, 3). Here is what the data would now look like in your table:

REPCODE, COUNTER, STATE

000111 | 1 | NJ
000111 | 2 | NY
000111 | 3 | PA
000222 | 1 | TX
000333 | 1 | NJ
000333 | 2 | NY

Now do, a Crosstab Query on this table, using REPCODE as the Row Header, COUNTER as Column Header, and selecting First as the function for the STATES. You can build this using the Crosstab Query wizard. If your table was named MyTable, the SQL would look something like:

TRANSFORM First(MyTable.STATE) AS FirstOfSTATE
SELECT MyTable.REPCODE
FROM MyTable
GROUP BY MyTable.REPCODE
PIVOT MyTable.COUNTER;

Now, build another table based off this CrossTab query which combines all the states with a series of IIF statements. Your first field would obviously be your REPCODE, then the calculated field would look something like:

ALLSTATES: Query_CrossTab![1] & IIf(Len(Query_CrossTab![2])>0,", " & Query_CrossTab![2],"") & IIf(Len(Query_CrossTab![3])>0,", " & Query_CrossTab![3],"")