How to merg fields in MS Access

[vahidam]

I've got a question about SQL query in MS Access, for example I have the following data:

Table1:
NAME ____ CITY ____ FAV
vahid ______ NY _______ A
vahid ______ NY _______ B
hamed _____ LA _______ A
hamed _____ LA _______ C

Do I able to merge them like this in MS Access 2003:
NAME ____ CITY ____ FAV
vahid ______ NY _____ A, B
hamed _____ LA _____ A, C

can anyone give any solution to it ? thanks

 

[bobinnc]

vahidam,

You could do a crosstab query as follows that would get you close...

TRANSFORM Max(Table1.FAV) AS MaxOfFAV
SELECT Table1.NAME, Table1.CITY
FROM Table1
GROUP BY Table1.NAME, Table1.CITY
PIVOT Table1.FAV;

I believe to get the Fav's in the same field would require VBA and an ADO database object.

 

[bobinnc]

If you want to go through VBA route, do the following:
1. Add the "Microsoft DAO 3.6 Object Library" (Or highest you have) in the VBA Tool Menu, References.
2. Paste in the following code:

Sub SummarizeTheFavs()
    Dim db As Database
    Dim rs As Recordset
    Dim rsout As Recordset
    Dim tempNAME As String
    Dim tempCITY As String
    Dim tempFAV As String
    Dim icnt As Long
    Set db = CurrentDb()
    On Error Resume Next
    db.Execute "SELECT * INTO Table2 FROM Table1;"
    On Error GoTo 0
    db.Execute "DELETE * FROM Table2;"
    Set rs = db.OpenRecordset("Select * from Table1 Order By NAME, CITY, FAV")
    Set rsout = db.OpenRecordset("Select * from Table2")
    rs.MoveLast
    rs.MoveFirst
    For icnt = 1 To rs.RecordCount
    If icnt = 1 Then
        tempNAME = rs![Name]
        tempCITY = rs![CITY]
        tempFAV = rs![FAV]
    ElseIf rs![Name] <> tempNAME Then
        rsout.AddNew
        rsout!Name = tempNAME
        rsout!CITY = tempCITY
        rsout!FAV = tempFAV
        rsout.Update
        tempNAME = rs![Name]
        tempCITY = rs![CITY]
        tempFAV = rs![FAV]
    Else
        tempFAV = tempFAV & ", " & rs![FAV]
    End If
    rs.MoveNext
    Next icnt
    rsout.AddNew
    rsout!Name = tempNAME
    rsout!CITY = tempCITY
    rsout!FAV = tempFAV
    rsout.Update
    Set db = Nothing
    Set rs = Nothing
End Sub

3. Run the Subroutine. It will create a Table 2 that is the summary of Table 1.

Caution: You would have to ensure that the text field FAV has a length long enough to hold all of the different values and their commas.

 

[james_lankford]

    db.Execute "SELECT * INTO Table2 FROM Table1;"
    On Error GoTo 0
    db.Execute "DELETE * FROM Table2;"

can be replaced with

    db.Execute "SELECT * INTO Table2 FROM Table1 where 1 = 0;"

that way you get the table structure you're looking for without doing all the inserts and deletes (which could be 10000s depending on how many rows are in the table)

 

[bobinnc]

vahidam,

James is correct that replacing:

db.Execute "SELECT * INTO Table2 FROM Table1;"

with

db.Execute "SELECT * INTO Table2 FROM Table1 where 1 = 0;"

will just copy the structure without doing the inserts. (Good catch James!)

However, I recommend that you do not delete the other two lines:

On Error GoTo 0
db.Execute "DELETE * FROM Table2;"

These lines allow you to rerun the code reusing the existing table2. - Bob

 

[vahidam]

My dear friends,
Thanks for your attention, but these are not my desired solution. Help me more ... Thanks