Join Query

[mongaro]

Hello,
I have a query with 2 fields:

IDRich Email
001/08 aa@a.com
001/08 bb@b.com
001/08 cc@c.com
002/08 uu.u@com
..... ......
I would like to join the data of "Email" field like this:

IDRich Email
001/08 aa@a.com;bb@b.com;cc@c.com
002/08 uu.u@com
..... ........

Is it possible?
Many thanks
Ciao

Marco

 

[ajetrumpet]

horizontal actions like this are not possible with the Access query, but maybe a function to create another table would do the trick? You could always export the data to excel too, and simply transpose it there.

but, in access:

copy the current table.

[B][U]Write a function for the copied table[/U][/B]

on error resume next

dim rs as recordset, str as string, prevID as string, prevEMAIL as string
  set rs = currentdb.openrecordset("nameofCopyTable", [I]dbopendynaset[/I])

with rs
  .movefirst
    while not .eof
      prevID = !IDrich
      prevEMAIL = !email
        .movenext
          if !IDrich = prevID then
            !email = !email & ";" & prevEMAIL
              .moveprevious
                .delete
                  .movenext
          end if
    wend
  .close
end with

This will only work of course, if the table is sorted in ASC order.