Web Development·11,944 discussions

Access

Locked

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.

Topic

Access

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.

Access

In reply to Access

Worked wonderfully! Thanks!

Access

In reply to Access

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

Access

In reply to Access

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

Access

In reply to Access

This question was closed by the author

[Author]

Replies