General discussion

Locked

Access

By vhansen86 ·
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.

This conversation is currently closed to new comments.

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

All Comments

Collapse -

Access

by andrew In reply to Access

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.

Collapse -

Access

by vhansen86 In reply to Access

Poster rated this answer

Collapse -

Access

by vhansen86 In reply to Access

This question was closed by the author

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

Related Discussions

Related Forums