Report with row data as columns

[KTM parked]

Everyone at my work loves spreadsheets and so anything that comes out of a DB needs to look like a spreadsheet.

They use a spreadsheet to keep track of comments on individual items such as Tender Clarifications, so column A would have the title and then columns B,C, etc would have the sequential comments for each item.

I have created a DB that has two tables:

Table 1: List of Tender Clarifications
[ID_Clarification]
[title]

Table 2: List of Comments by Tender Calrification Item
[ID_Comment]
[ID_Clarification]
[Comment]
[Comment made By]

The two tables are linked by [ID_Clarification]

I now want to create a report that in column A has [Title]
Column B: first Comment
Column C: second comment

This is not a cross tabe query becuase it is not summarising.

So how do I do this?

I have all comments for all tender clarifications in one table, but it would seem that I would need to add a 1,2,3,4 etc for each set of comments for each tender clarification item that would then be the columns.

Thanks in advance

 

[Peter Reid]

The problem here is where you say you 'need to add a 1,2,3,4 etc for each set of comments'

You could use a crosstab query as you are actually summarising the data and the data in [ID_Comment] is incremental (1, 2, 3, 4 etc) [ID_Clarification] although I suspect it's incremental across the whole table.

When I've been tasked to do something similar, I've created a custom query to concatenate the data using either vbcrlf, which will then show all comments in one cell, or a 'pipe' (or another character that's unlikely to be in the comment) so the users can use the 'text to columns' to get what they need.

Alternatively, you can use VBA to create the Excel file exactly how they want it (not using the 'export to excel' functionality)

 

[JRickman]

How does one get the Totals (Sum, Average, Count, etc.) at the bottom of the query into the Report based on that query?
Thanks, Jim

 

[Peter Reid]

How does one get the Totals (Sum, Average, Count, etc.) at the bottom of the query into the Report based on that query?
Thanks, Jim

Come again?