Solved

Howto: SELECT rownumber as unique key.

Posted on 2004-04-16
9
1,414 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

920 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

17 Experts available now in Live!

Get 1:1 Help Now