 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Modules / DAO / VBA / November 2006How to move multiple records into a single row
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. 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. > Access03/WinXP > [quoted text clipped - 26 lines] > > Any help is appreciated, as always. 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. > 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 [quoted text clipped - 30 lines] > > > > Any help is appreciated, as always. 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. > 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 [quoted text clipped - 30 lines] > > > > Any help is appreciated, as always. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.