General discussion

  • Creator
    Topic
  • #2344761

    Access

    Locked

    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.

All Comments

  • Author
    Replies
    • #3691521

      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.

      • #3711153

        Access

        by vhansen86 ·

        In reply to Access

        Worked wonderfully! Thanks!

    • #3721213

      Access

      by bradheld ·

      In reply to Access

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

      • #3711154

        Access

        by vhansen86 ·

        In reply to Access

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

    • #3711152

      Access

      by vhansen86 ·

      In reply to Access

      This question was closed by the author

Viewing 2 reply threads