Howto: SELECT rownumber as unique key.

Does anyone know how one would SELECT a Sequential Rownumber/Recordnumber in a select query?

e.g. (1-5)
Row#        Field2
1               a
2               b
3               c
4               d
5               e
africaohAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
nico5038Connect With a Mentor Commented:
As long as you have a unique key (and for two tables you can use two fields combined) you can use this.
Even the query could be used like:
select dcount("Field2","yourqueryname","keyfield1 & keyfield2<=" & chr(34) & [keyfield1] & [keyfield2] & chr(34) ) as Sequence, Field2 from yourqueryname;

Getting the idea ?

Nic;o)
0
 
LordSparhawkCommented:
What kind of rownumber do you mean? If your table does not have an 'ID' column or something similar then there is no sequential number to SELECT.

As far as I know Access does not have any internal numbers for each row.
0
 
phileocaCommented:
SELECT yourtable.*, *
FROM yourtable
ORDER BY yourtable.row;

<>< kT
0
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
africaohAuthor Commented:
phileoca,

Im not sure how your solution will give me an extra column with the rownumber?
Can you maybe explain?

Thank you
0
 
africaohAuthor Commented:
I just need to select the rownumber in my SELECT statement.


The reason why I need this...
I have two queries that both does a SELECT TOP 5, there is no common key between the two queries, hence I need to create my own somehow, I was hoping I could use rownumbers 1->5.

I need to select * (all 5 rows) from both these two queries, but instead of having 25 rows returned I only need a total of 5 rows (2 columns) in order to do that I need a key between the two.

Does any of the above make sense to anyone?

0
 
nico5038Commented:
Try:

select dcount("Field2","yourtablename","field2<=" & chr(34) & [field2] & chr(34) ) as Sequence, Field2 from yourtablename;

Nic;o)
0
 
africaohAuthor Commented:
Hi nico,

Your solution works for a simple select on one table but I cant get it working on my query (I use a query to join two existing queries).

Thanks for you help though, appreciate it
0
 
africaohAuthor Commented:
Nico,

I got help from another expert via sql-server sp, I figured I have to move my Access queries over to sp anyway might as well get started...

I really appreciate all the help from everyone, Nico Im sure if I tried your final solution earlier we would have gotten that working, hence I will go ahead and accept your answer.

Thanks again everyone
0
 
africaohAuthor Commented:
For those who wanted to know how to do this via sql-server sp: (BulZEye helped me with this)




sorry, extra comma..:

CREATE PROCEDURE dbo.prGetDefectNums
(
  @inStartDate DATETIME,
  @inEndDate   DATETIME
)
AS
BEGIN

  SET NOCOUNT ON
 
  SELECT Top 5
         IDENTITY(int, 1,1) AS ID_Num
        ,[tblFinalDefect].[DefectsMMO]
        ,COUNT([tblFinalDefect].[DefectsMMO]) AS countMMO
    into #tblMMODefects
    FROM tblFinalOK
   INNER JOIN tblFinalDefect
      ON [tblFinalOK].[Buildseq]=[tblFinalDefect].[Buildseq]
   WHERE [tblFinalOK].[FullDate] BETWEEN @inStartDate AND @inEndDate
  GROUP BY [tblFinalDefect].[DefectsMMO]
  ORDER BY COUNT([tblFinalDefect].[DefectsMMO]) DESC
 
  SELECT Top 5
         IDENTITY(int, 1,1) AS ID_Num
        ,[tblFinalDefect].[DefectsEC]
        ,COUNT([tblFinalDefect].[DefectsEC]) AS countEC
    into #tblECDefects
    FROM tblFinalOK
   INNER JOIN tblFinalDefect
      ON [tblFinalOK].[Buildseq]=[tblFinalDefect].[Buildseq]
   WHERE [tblFinalOK].[FullDate]  BETWEEN @inStartDate AND @inEndDate
  GROUP BY [tblFinalDefect].[DefectsEC]
  ORDER BY COUNT([tblFinalDefect].[DefectsEC]) DESC
 
  SELECT t1.ID_Num
        ,t1.DefectsMMO AS MMO
        ,t1.countMMO AS MMOCnt
        ,t1.DefectsEC AS EC
        ,t1.countEC AS ECCnt
    FROM #tblECDefects t1
   INNER JOIN #tblMMODefects t2
      ON t1.ID_Num = t2.ID_Num


END
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.