Solved

Howto: SELECT rownumber as unique key.

Posted on 2004-04-16
9
1,420 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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
 
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

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

705 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