Web Development

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.

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

This question was closed by the author