Solved

Howto: SELECT rownumber as unique key.

Posted on 2004-04-16
9
1,413 Views
Last Modified: 2007-12-19
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
0
Comment
Question by:africaoh
9 Comments
 

Expert Comment

by:LordSparhawk
ID: 10844252
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
 
LVL 11

Expert Comment

by:phileoca
ID: 10844257
SELECT yourtable.*, *
FROM yourtable
ORDER BY yourtable.row;

<>< kT
0
 

Author Comment

by:africaoh
ID: 10844331
phileoca,

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

Thank you
0
 

Author Comment

by:africaoh
ID: 10844376
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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 54

Expert Comment

by:nico5038
ID: 10844756
Try:

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

Nic;o)
0
 

Author Comment

by:africaoh
ID: 10844943
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
 
LVL 54

Accepted Solution

by:
nico5038 earned 250 total points
ID: 10845041
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
 

Author Comment

by:africaoh
ID: 10845683
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
 

Author Comment

by:africaoh
ID: 10845685
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

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now