Web Development

Ask something
Discussion Locked

Access

0 helpful
3 replies

by vhansen86

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

Access
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 be appreciated. Currentcode is not efficient. Current code consists of deleting the new table each time and re-creating.

3 replies

by andrew

hmmm...deja vu...

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.

This discussion is closed to new replies.