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:
   [CODE=vb]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.SetWarnin gs False
   DoCmd.RunSQL "DELETE * From tblResults;"
   DoCmd.SetWarnin gs 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.OpenRecord set(strSQL, dbOpenSnapshot)
   Set rstResults = MyDB.OpenRecord set("tblResults ", dbOpenDynaset)
   
   Do While Not MyRS_ID.EOF
   strRestrictID = "SELECT * From Info Where [ProjectID] =" & MyRS_ID![ProjectID]
   Set MyRS_Info = MyDB.OpenRecord set(strRestrict ID, dbOpenSnapshot)
   Do While Not MyRS_Info.EOF
   strConsultants = strConsultants & MyRS_Info!Consu ltantName & ","
   MyRS_Info.MoveN ext
   Loop
   strConsultants = Left(strConsult ants, Len(strConsulta nts) - 1)
   rstResults.AddN ew
   rstResults![ProjectID] = MyRS_ID![ProjectID]
   rstResults![Consultant(s)] = strConsultants
   rstResults.Upda te
   strConsultants = "" 'Reset
   MyRS_Info.MoveF irst
   MyRS_ID.MoveNex t
   Loop
   
   MyRS_ID.Close: Set MyRS_ID = Nothing
   rstResults.Clos e: Set rstResults = Nothing
   MyRS_Info.Close : Set MyRS_Info = Nothing[/CODE]
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