Getting a report from a table

[Shakss2]

Hello all,

I have a table name "info" which looks like,

ProjectID Consultantname
1 Alpha
1 Beta
1 Charlie
2 James
2 Mario
3 Shak

Now im getting a excel report in the below format.

ProjectID Consultantname
1 Alpha
1 Beta
1 Charlie
2 James
2 Mario
3 Shak

I want a excel report which gives the result as below:

ProjectID Consultantname
1 Alpha, Beta, Charlie
2 James, Mario
3 Shak

im struck here n cant proceed further.
Pls help

Thanks,
Shaq

[ADezii]

Hello all,

I have a table name "info" which looks like,

ProjectID Consultantname
1 Alpha
1 Beta
1 Charlie
2 James
2 Mario
3 Shak

Now im getting a excel report in the below format.

ProjectID Consultantname
1 Alpha
1 Beta
1 Charlie
2 James
2 Mario
3 Shak

I want a excel report which gives the result as below:

ProjectID Consultantname
1 Alpha, Beta, Charlie
2 James, Mario
3 Shak

im struck here n cant proceed further.
Pls help

Thanks,
Shaq

Just subscribing, I'll have your answer either this afternoon or this evening if no one gets to it before me.

[ADezii]

Hello all,

I have a table name "info" which looks like,

ProjectID Consultantname
1 Alpha
1 Beta
1 Charlie
2 James
2 Mario
3 Shak

Now im getting a excel report in the below format.

ProjectID Consultantname
1 Alpha
1 Beta
1 Charlie
2 James
2 Mario
3 Shak

I want a excel report which gives the result as below:

ProjectID Consultantname
1 Alpha, Beta, Charlie
2 James, Mario
3 Shak

im struck here n cant proceed further.
Pls help

Thanks,
Shaq

1. Create a Table named tblResults. This Table will consist of only 2 Fields with no Primary Key:
   1. [ProjectID] - [NUMBER] {LONG}
   2. [Consultant(s)] - [TEXT 255]
2. Copy and Paste, then Run the following code from anywhere you like:
   

   Dim MyDB As DAO.Database, MyRS_ID As DAO.Recordset, strSQL As String
   Dim MyRS_Info As DAO.Recordset, strInfo As String, rstResults As DAO.Recordset
   Dim strRestrictID As String, strConsultants As String
    
   'DELETE any pre-existing Records in tblResults
   DoCmd.SetWarnings False
     DoCmd.RunSQL "DELETE * From tblResults;"
   DoCmd.SetWarnings False
    
    
   'Get a listing of Unique ProjectIDs
   strSQL = "SELECT Distinct [ProjectID] From Info Order By [ProjectID];"
   strInfo = "Select * From Info;":
    
   Set MyDB = CurrentDb()
   Set MyRS_ID = MyDB.OpenRecordset(strSQL, dbOpenSnapshot)
   Set rstResults = MyDB.OpenRecordset("tblResults", dbOpenDynaset)
    
   Do While Not MyRS_ID.EOF
    strRestrictID = "SELECT * From Info Where [ProjectID] =" & MyRS_ID![ProjectID]
    Set MyRS_Info = MyDB.OpenRecordset(strRestrictID, dbOpenSnapshot)
     Do While Not MyRS_Info.EOF
       strConsultants = strConsultants & MyRS_Info!ConsultantName & ","
       MyRS_Info.MoveNext
     Loop
       strConsultants = Left(strConsultants, Len(strConsultants) - 1)
         rstResults.AddNew
           rstResults![ProjectID] = MyRS_ID![ProjectID]
           rstResults![Consultant(s)] = strConsultants
         rstResults.Update
         strConsultants = ""     'Reset
     MyRS_Info.MoveFirst
     MyRS_ID.MoveNext
   Loop
    
   MyRS_ID.Close: Set MyRS_ID = Nothing
   rstResults.Close: Set rstResults = Nothing
   MyRS_Info.Close: Set MyRS_Info = Nothing

3. The data that you requested, in the proper format, will now be contained in tblResults in Ascending ProjectID Order. Since the data is contained within a Table, you can do virtually anything you like with it.

[Shakss2]

Thanks a lot, that works...

But im having another issue.. which looks similar but a bit messed up.

I have a table as below

ProjID b c d e f
2 12 3 1 2 1
3 10 3 0 2 4
4 5 5 6 5 5
5 4 2 0 0 0
6 1 0 0 1 0
8 0 6 0 7 2
10 0 0 0 0 0

I want a o/p in excel like below...

1st column projID
2nd column should be like this:
b, 12,
c, 3,
d, 1,
e, 2,
f, 1 (in one cell)
like previously we got all Consultants in one cell.

If U can help with this.. I will run short of words to thank :)

Shaq

[Shakss2]

I got it working... :)

Thanks for helping me with the 1st result.

Regards,
Shaq