General discussion

Locked

Access

By vhansen86 ·
I have a table I want to combine records on:
Name Number Letter
John 1 A
John 2 B
John 3 C

Need to query to make a new table to look like this:

Name Number Letter
John 1,2,3 a,b,c

Any help would beappreciated. Current code is not efficient. Current code consists of deleting the new table each time and re-creating.

This conversation is currently closed to new comments.

5 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

Access

by andrew In reply to Access

Here's a script I wrote for SQL Server. I'm not sure if it will work in Access, but it demonstrates the logic (nested cursors) needed to loop through the data.

set nocount on
declare @Name varchar(20),@Number int,@Letter char(1)
declare @Numbers varchar(100),@Letters varchar(100)

select distinct Name into #names from table1

create table #final (Name varchar(20),Number varchar(100),Letter varchar(100))
declare curtmp cursor for select name from #names
open curtmp
Fetch Next From curtmp Into @Name
While (@@Fetch_Status = 0)
Begin
Set @Numbers=''
Set @Letters=''
declare curnames cursor for select Number,Letter from Table1 where name=@Name
open curnames
Fetch Next From curnames Into @Number,@Letter
While (@@Fetch_Status = 0)
Begin
Set @Numbers=@Numbers+cast(@Number as varchar(1))+','
Set @Letters=@Letters+@Letter+','
Fetch Next From curnames Into @Number,@Letter
End
insert into #final values(@Name,@Numbers,@Letters)
Close curnames
Deallocate curnames
Fetch Next From curtmp Into @Name
End

Close curtmp
Deallocate curtmp

select * from #final

drop table #names
drop table #final

Hope this helps.

Collapse -

Access

by vhansen86 In reply to Access

Worked wonderfully! Thanks!

Collapse -

Access

by BradHeld In reply to Access

INSERT INTO Table2 ( Combined )
SELECT (tbl1.Name & tbl1.Number & tbl1.Letter) as Combined INTO Table2
FROM tbl1

Collapse -

Access

by vhansen86 In reply to Access

Thanks for the suggestion but it did not help my situation.

Collapse -

Access

by vhansen86 In reply to Access

This question was closed by the author

Back to Web Development Forum
5 total posts (Page 1 of 1)  

Related Discussions

Related Forums