Concatenate in Access 2000

Question

I have searched 'concatenate' topics and have been unable to find the answer I need. This posting came close access sql query to concatenate rows but my attempts to make it work for my purpose failed.

What I have is a table like this

Lic# | Permit  | Year     
------------------------
1    | NS1     |   2003   
1    | NS1     |   2004  
1    | NS2     |   2004  
2    | TR      |   2012  
2    | NS2     |   2012  
3    | OR      |   2008   
2    | OR      |   2011  
2    | NS1     |   2011  
2    |  TR     |   2011

....And so forth. This table has many unique license numbers with permit type and year (from 2003-2012) listed for each.

What I would like is to create a table that would display the information like this

Lic#  | Permit      | Year  
-----------------------------
1     |NS1          | 2003  
1     | NS1, NS2    | 2004  
2     | TR, NS2     | 2012  
3     | OR          | 2008  
2     | OR, NS1, TR | 2011

Are you trying to create a new table or create a query that shows that information? — PW Kad, Apr 09 '13 at 23:40
It's something like the `group_concat()` function in MySQL... however I think there's no similar function in MS Access — Barranka, Apr 09 '13 at 23:44
Ideally create a new table, but if I can do a select that produces similar results is datasheet view I can make a new table out of that. I should also add that the data I am working with I queried from a partner agency's DB that I am know analyzing, if that makes a difference. — Andre831, Apr 11 '13 at 17:44

Barranka · Answer 1 · 2013-04-11T21:37:34.850

As I posted in my comment, this is easy using the group_concat() function in MySQL, but if you want to do it in MS Access, I think you have to deal with this using VBA.

I propose you this function:

public function concatenatePermits(licNo as integer, year as integer)
    dim db as DAO.database, rec as DAO.recordset, strSQL as string
    dim ans as string

    set db = currentdb()
    strSQL = "select permit from [your table] " & _
             "where [lic#]=" & licNo & " and year=" & year & ";"
    set rec = db.openrecordset(strSQL, dbOpenDynaset, dbReadOnly)
    ans = ""
    with rec
        .moveFirst
        do
            if ans = "" then
                ans = !permit
            else
                ans = ans & "," & !permit
            end if
        loop until .EOF
    end with
    rec.close
    db.close
    concatenatePermits = ans
end function

This function can be used in any query. Downside: If your table is really big, the execution of a query that uses this function can be really slow. I think the better approach would be to create an empty table and then fill it row by row using VBA.

Hope this helps you.

Adding rows using VBA

In your comment you ask how to add rows to a table with VBA. Assuming the table exists and you have the data you want to feed into this table, I suggest you something like this:

public sub addData()
    dim db as dao.database, recOut as dao.recordset
    ' Declare all the variables you need for your code'
    set db = currentdb()

    ' recOut will be the table where you want to store your data '
    set recIn = db.openRecordset("tblYourOutTable",dbOpenDynaset,dbEditAdd)

    ' At some point in your code you will need to store data in your table: '
    with recOut
        .addNew
            ![A_Field] = value1
            ![Another_field] = value2
            ![Yet_another_field] = value3
        .update
    end with

    ' Close the recordset and the database objects '
    rec.close
    db.close
end sub

Forgive me, but I am still trying to teach myself and am not that experienced yet. How would one do as you suggested filling in a new table row by row using VBA. In the mean time I will give this one a shot. I greatly appreciate your time and help! — Andre831, Apr 11 '13 at 17:27
I tried your suggestion and get the error "invalid SQL statement; expected 'DELETE','INSERT','SELECT', or 'UPDATE'... any suggestions??? — Andre831, Apr 11 '13 at 17:34
@Andre831 You'll need to debug the code and find where the error is generated. Try to understand what causes the error and try to correct it. If you used the code just as I've written it (and if my assumptions about the data types of your fields are accurate), there should be no problem. The places where I think the error can be is in the definition of the query (`strSQL=...`) or in the initialization of the recordset (`set rec=...`). Can you give more info about it? — Barranka, Apr 11 '13 at 19:01
I copied it just as you have except for included the table name. I have been trying to figure it out, but have been unable to so far....Is there something easy that I am over looking? Ill continue to try to figure it out. Thanks again for your help!!! — Andre831, Apr 11 '13 at 23:37

Concatenate in Access 2000

1 Answers1