Collate Field Content from multiple records

[MoreInterestingThanW]

I have a database with company records in one table and calls made to companies in another table; the two tables properly related on a CompanyID field. A query joining the tables returns a dataset with multiple instances of CompanyID's because each company may have received 0 - n calls.

I would like to collate the content of the 0 - n[/I] callnotes records for each company into a single 'CollectedNotes' entity. I have two questions please.

Can anyone think of a way to do this without using code?

If not, can anyone give me a helping hand with the code?

In pseudo-code terms, I anticipate something like accessing the recordset for CompanyID's related to CallID's looping through to write the content of each instance of a CallNote (identified by unique CallID) to a new 'CollectedNotes' object. Help gratefully received. Thanks. MITW

 

[Moniker]

In your query that pulls company names and number of calls, make it an aggregate query. Put a Group By on the Company Name field and a Count on the Number of Calls field.

 

[MoreInterestingThanW]

Thanks - but I think it has to be code

Thanks Moniker

Unfortunately the Calls table does not have any count of calls so each company is associated with a list of calls of indeterminate length with calls identified by autonumbered CallID numbers. So I think it has to be code... I now have some DAO code which does the job when called from a query and I am trying to adapt this for other similar tidying up tasks in this database and trying to reproduce it in ADO.

MITW

In your query that pulls company names and number of calls, make it an aggregate query. Put a Group By on the Company Name field and a Count on the Number of Calls field.

 

[neileg]

What are you doing with all the call data? Why does it have to be munged together? Why not use a report or a form/subform setup?