Query Help

• Dhaval-190627

  Old Hand

  Points: 323

  More actions

  January 7, 2005 at 11:18 am

  #87770

  I have two table and they have joint ID to Id

  Table A          Table B

  Id    Name       Id   Salary

  1      A          1    100

  2      B          2    200

  3      C      

  4      D          4    300   

  8    E            6     500

                     8      800                                                                             

  My query results are

  1 A 100

  2 B 200

  4 D 300

  8 E 800

  I would like to create report look like this.

  Id      Name     Salary    Sequence

  1         A         100       1

  2         B         200       2

  4         D         300       3

  8         E         800        4

  How can i create sequence field which carry auto number in my reports.If i add other record in tabel A and B then it create new sequence number. 

  I created query like

  SELECT [Table A].ID, [Table A].Name, [Table B].Salary, (Select Count (*) FROM [Table B] as Temp

  WHERE [Temp].[ID] < [Table B].[ID])+1 AS Expr1

  FROM [Table A] INNER JOIN [Table B] ON [Table A].ID = [Table B].ID;

  But it gave me this

  1 A 100 1

  2 B 200 2

  4 D 300 3

  8 E 500 5

  Need help.

   

• babuganesh2000

  Hall of Fame

  Points: 3123

  More actions

  January 7, 2005 at 12:01 pm

  #536501

  use this function in the place of temp: IDENTITY(int, 1,1)

  insert into a temporary table and select the temporary table.

  I hope that will solve your problem.

  Cheers,

  Ganesh