Transpose Date

[SlimSquirrel]

I have a couple of tables in a database and following the rules of normalisation have separated the contacts from the companies. I have managed to display the data in many different ways using standard queries but cannot do a simple “transpose” like you can in excel. My tables are set out as follows:

CompanyTable:
CompanyID
CompanyName

ContactTable:
ContactID
CompanyID (as a foreign key)
ContactName

I now have several companies and several hundred contacts and would like to display the data with the company names as rows followed by the relevant contacts to that company in the next field across. Please see example below:

CompanyName1 ContactName1ContactName2ContactName3ContactName4
CompanyName2 ContactName5ContactName6
CompanyName3 ContactName7ContactName8ContactName9
CompanyName4 ContactName10

The amount of columns needs to be variable as some companies have several contacts and others have very few.
What I am trying to achieve is not too dissimilar to a crosstab query (or a pivot table in excel) but without the numeric totals / values.
Is this something simple that I am missing?

Thanks in advance.

Slim

 

[spikepl]

Define

would like to display the data

In a report you should be able to do this with appropriate grouping, having selected mutiple columns for layout

Alternatively, search for concatrelated

 

[SlimSquirrel]

spikepl,
Sorry I should have been more clear, it is not a report I am after but rather a query or table.
Concatrelated looks interesting though, I will look into that.
Thanks.

 

[jdraw]

Here is a link to Allen Browne's ConcatRelated

 

[Galaxiom]

Access does CrossTab queries.

There is an example of a similar requirement to your in post 10 of this thread.

Note however that crosstab queries are not updateable.