1

In access I have a table that is like 

ID| ROOMS | NOTES|
78|    234|      |
3 |    231|  key |
78|    195|      |
3 |    164|      |

I want to a sql query that will take the ID and combine them into one row each so it is like

78 -> 234,195
3->231, 164 -> key

i just want to combine the rows only in the query no into a new table

Tony Toews
  • 7,850
  • 1
  • 22
  • 27
italiano40
  • 496
  • 9
  • 18
  • Where are you getting 192 from? – Brian Webster Jul 17 '11 at 03:51
  • I don't understand how you would get the 195 in the 3 result, is that supposed to be 164?. And the 192 in the 78 result is supposed to be 195, right? – mu is too short Jul 17 '11 at 04:13
  • @hamlin11 @mu is too short, Yes it was a mistake sorry but i just a query that works in access that will combine those 2 rows and give you just one row instead of a bunch of rows together it is for a report – italiano40 Jul 17 '11 at 22:13
  • possible duplicate of [is there a group_concat function in ms-access?](http://stackoverflow.com/questions/2852892/is-there-a-group-concat-function-in-ms-access) – onedaywhen Jul 18 '11 at 14:16

1 Answers1

0

Unfortunately you'll have to build a function to do this, but thankfully access supports the use of VBA functions inside of your SQL query.

For example function to concatenate the rim's together based on a given ID would be as follows:

Public Function MyRooms(lngID As Variant) As Variant

   Dim rstRooms         As DAO.Recordset

   If IsNull(lngID) Then Exit Function

   Set rstRooms = "select Rooms from tblBookings where id = " & lngID
   Do While rstRooms.EOF
      If MyRooms <> "" Then MyRooms = MyRooms & "->"
      MyRooms = MyRooms & rstRooms!Rooms
      rstRooms.MoveNext
   Loop
   rstRooms.Close

End Function

Now your query can look like this:

Select id, MyRooms([id]) as Rooms, Notes from some table.

You can also make an identical function much the same for the notes column, and again simply place that in the above query.

Albert D. Kallal
  • 42,205
  • 3
  • 34
  • 51
  • This is not a macro. Access has macros and also has VBA (they are separate concepts and have separate meanings in Access). So, the above is VBA code that would be placed inside of a standard code module. You thus Insert a code module from the main Access menu or ribbon. You then paste in the above VBA code into that module. – Albert D. Kallal Jul 19 '11 at 21:43