How to move multiple records into a single row

[Guest]

Access03/WinXP

I have a very simple query that I need to rework via another query or VBA
that takes multiple records of the same CompanyID and arranges the data into
a single row.

Essentially, I need to take the query results:

CompanyID MemberName
1000 John Smith
1000 Jane Smith
etc.

and turn it into
CompanyID Member1 Member2 ..... Member10 (maximum number)
1000 John Smith Jane Smith .....

Can this be done with a Crosstab Query? Haven't quite figured out how so
help would be appreciated.

I've written many routines in VBA to roll through records and combine
multiple fields into a single comma-separated field (like John Smith, Jane
Smith, etc.) but am stuck on how to configure the routine to roll through the
record and if the CompanyID is the same, add the next record into the next
Member(x) field.

I have a temp table set up ("TempCompanyMembers") that has the fields
CompanyID and ten separate text fields for the ten possible member names.

Any help is appreciated, as always.

 

[Guest]

You will be limiting the membership to 254. The maximum number of fields in
a table or query is 255 and you are using 1 for the company. This is not an
assumption I would be comfortable with.

 

[Guest]

Sorry, I think you misunderstood, or more likely, I wasn't clear. There are
only 11 fields in the table. The first is CompanyID, the remaining will be
Member1, Member2, etc., through Member10. Each CompanyID will be a separate
record in this table. I need to take all of the members of a company and put
them into a horizontal row/record.

The number of members by company will vary but will never exceed 10.

 

[Guest]

After much searching, I found something that works. In another post, I saw a
reference to using a variable and the .Fields collection.

I set one recordset (rs) to a standard query to gather my data. I set
another recordset (rsCount) that grouped the first recordset by CompanyID and
gave a count on the MemberName field.

In my code, I used a Do While Not EOF Loop in which was a For Next loop
utilizing the rsCount("MemberNameCount") as the upper bound, and then
progessively moved across the field list using the .Fields() with the
variable set to "Member" & X (from the For Next loop).

I know that's probably not clear, so if anyone is interested in seeing it,
I'll post the code.