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

 

[Dennisk]

I would perform this using 2 recordsets

firstly I would create a select statement for you first recordset so only a distinct list of dates are present.

then for each date open another recordset selecting all rows with that date, then append each email address to the date then append each new row to a tempory table.

 

[mongaro]

I would perform this using 2 recordsets

firstly I would create a select statement for you first recordset so only a distinct list of dates are present.

then for each date open another recordset selecting all rows with that date, then append each email address to the date then append each new row to a tempory table.

Ciao Dennisk,
first many thanks for your reply.
I'm not an expert of access, could you kindly post an example, please?
Many thanks

Marco

 

[namliam]

I cannot seem to find it just this quickly, but I know there is a good sample on the forum in here someplace
http://www.access-programmers.co.uk/forums/forumdisplay.php?f=26

Have a look if you can find it... This question has been asked about 24.000 times this year allready.

 

[Dennisk]

Marco,

Firstly create a query (use the QBE) select the date field, open the properties for the query and set Unique Values to yes.
or if you can write sql

SELECT DISTINCT YourDate FROM YourTable

Dim rs as DAO.Recordset
 
Dim rsRecords as DAO.Recordset

Set rs=CurrentDB.OpenRecordset("SELECT DISTINCT YourDate FROM YourTable")

DO While not rs.EOF
     set rsRecords = CurrentDB.OpenRecordset("SELECT * FROM YourOtherTable WHERE [YourOtherTable.YourDate =#" & rs("YourDate") & "#")
         DO While not rsRecords.EOF
             ' Concatenate here
              rsRecords.Movenext
        LOOP
        rsRecords.Close
    rs.movenext
LOOP