Counter in a Query

  • Thread starter Thread starter Dale Brown
  • Start date Start date
D

Dale Brown

I would like to know how to put a counter in my query so I have a record
number on each row. I want this to start at 1 and go to however many
rows there is. Also I do not want to use a table with one column for the
counter. Let me know if there is an easy way to do this.

Dale Brown
 
Well, if you sort on a column that has unique values (such as the Primary
Key), you can generate a "counter" like this:

SELECT MyTable.*, (SELECT Count(*) FROM MyTable As T2 WHERE T2.UniquField <=
MyTable.UniqueField) As RowNumber
FROM MyTable
ORDER BY UniqueField

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
 
John,

My tables primary key is made up of three fields how would you suggest
doing that?

Dale
 
Three fields in a Primary Key sounds like a design problem. Why do you want
to do this? If it's for a report, it's easy to generate a "record number"
using a text box with a Control Source set to =1 and RunningSum set to Over
All.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
 
Hi, Dale

Thanks for posting in the community. From your description, I understand
that you would like to have a record number on each row and do not want to
use a table with one column for the counter.

MVP John got the right answer and I supposed for three primary fields you
would better use the query like this:
(supposing p1,p2,p3 are three primary fields in yourTable)
select yourTable.*, count(SELECT Count(*) FROM yourTable As T2 WHERE T2.p1
<=yourTtable1.p1 and T2.p2 <=table1.p2 and T2.p3 <=table1.p3) as RowNumber
from yourTable
order by p1,p2,p3
(what's more, you could change the sequence in "order by" to customize your
own order)

and you can use the query below to get the max number of you row in the
yourTable:
select max(rownumber) as MaxRowNumber
from (select table1.*,count(SELECT Count(*) FROM table1 As T2 WHERE T2.p1
<=table1.p1 and T2.p2 <=table1.p2 and T2.p3 <=table1.p3) as rownumber
from table1)

Moreover, I found a Knowledge Base for you,
ACC2000: How to Rank Records Within a Query
http://support.microsoft.com/?id=208946
which lists three examples to show you how to rank records within a query

Hope these help.If you still have questions about it, please feel free to
post message here and I am glad to help.


Sincerely, yours
Michael Cheng
Microsoft Online Partner Support
************************************************
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
 
From: (e-mail address removed) (Michael Cheng)
Date: Mon, 22 Mar 2004 04:15:43 GMT
Subject: RE: Counter in a Query
Newsgroups: microsoft.public.access.queries

Hi, Dale

Thanks for posting in the community. From your description, I understand
that you would like to have a record number on each row and do not want to
use a table with one column for the counter.

MVP John got the right answer and I supposed for three primary fields you
would better use the query like this:
(supposing p1,p2,p3 are three primary fields in yourTable)
select yourTable.*, count(SELECT Count(*) FROM yourTable As T2 WHERE T2.p1
<=yourTtable.p1 and T2.p2 <=yourTable.p2 and T2.p3 <=yourTable.p3) as
RowNumber
from yourTable
order by p1,p2,p3
(what's more, you could change the sequence in "order by" to customize your
own order)

and you can use the query below to get the max number of you row in the
yourTable:
select max(rownumber) as MaxRowNumber
from (select yourTable.*,count(SELECT Count(*) FROM yourTable As T2 WHERE
T2.p1
<=yourTable.p1 and T2.p2 <=yourTable.p2 and T2.p3 <=yourTable.p3) as
rownumber
from yourTable)

Moreover, I found a Knowledge Base for you,
ACC2000: How to Rank Records Within a Query
http://support.microsoft.com/?id=208946
which lists three examples to show you how to rank records within a query

Hope these help.If you still have questions about it, please feel free to
post message here and I am glad to help.


Sincerely, yours
Michael Cheng
Microsoft Online Partner Support
************************************************
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
 
Michael,

This does not quite get me what I am wanting. It is close though. Here
is my query. I need a row number on each row so I can determine when to
do additional calculation with my time fields. Let me know what you think.

Dale


SELECT EBEntry.EntryBlankID, EBEntry.HorseID, EBEntry.ClassID,
ClassInfo.USDFStartTime, ClassInfo.USDFRideTime,
ClassInfo.USDFBreakTime, ClassInfo.USDFBreakHorses,
rnd(right(ebentry.riderid,2))+rnd(now()) AS rnd,
dateadd("n",CStr(usdfridetime),CStr(classinfo.usdfstarttime)) AS ridetime
FROM (ActiveShow INNER JOIN ClassInfo ON ActiveShow.ShowID =
ClassInfo.ShowID) INNER JOIN EBEntry ON ClassInfo.ClassID = EBEntry.ClassID
WHERE ebentry.status = "a"
ORDER BY ebentry.classid, rnd(right(ebentry.riderid,2))+rnd(now());
 
Dale-

How will a row number help you decide to do "additional calculation?" What
is the final result you're trying to achieve?

It's not clear why you included ActiveShow in the query - you're not
fetching any fields from that table or using and criteria on that table.
Also, you appear to be sorting by ClassID and a random number. If you want
a "row number" based on these two values, it is impossible. Each time the
query calls Rnd, it gets a different value. In fact, it will sort on the
Rnd values generated on an initial pass, but will most likely return
different values in the RND column in the recordset or query datasheet. If
you scroll up and down in the datasheet, you can watch the query generate
*new* values in the rows. Because this is happening, there won't be any
constant RND value per row on which to calculate the relative row number.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
 
Hi, Dale


Thanks for your update.

I think MVP John's reply is very useful.

First of all, Refer to MS Access Help, you will find that rnd(number)
funtion will return a random value between 0 and 1, if number is greater
than zero. So I supposed you'd better change the ORDER BY in your query as
"ORDER BY ebentry.classid, md". Try to see whether it will work and meet
your demand?

Secondly, From your description, however, I'm unsure of the usage you
define the row number and how will it work to help you determine when to do
additional calculations. Would you please give further descriptions in
detail ?


Thanks and hope these help. Please feel free to post message here and I
will be glad to help ASAP


Sincerely, yours
Michael Cheng
Microsoft Online Partner Support
************************************************
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
 
Hi, Dale


Thanks for your update.

I think MVP John's reply is very useful.

First of all, Refer to MS Access Help, we will find that rnd(number)
funtion will return a random value between 0 and 1, if number is greater
than zero.

Secondly, from your description, however, I'm unsure of the usage you
define the row number and how will it work to help you determine when to do
additional calculations. Would you please give me further descriptions
about what you want in
detail ?


Thanks and please feel free to post message here and I will be glad to help
ASAP


Sincerely, yours
Michael Cheng
Microsoft Online Partner Support
************************************************
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
 
Hi Dale,

I am reviewing your post and I have not heard from you for some time. In my
last post, I asked you to give me some more information which would be
very helpful for our assistance to the issue. It is much appreciated if you
could post back in the newsgroup.

If you have any questions, please feel free to reply in the Newsgroup and
let us know you need further assistance.



Sincerely, yours
Michael Cheng
Microsoft Online Partner Support
************************************************
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
 
Michael
I think he gave up, but I have the same need
What I am doing is creating a query that combines data from several tables in multiple databases.
I also am using expressions to add some additional fixed data, and then concatenate some of the fields together as well. I also address field name changes in the query. The query is being used for data migration efforts, and I have an existing set of tables I would like to append to.
The structure of the resulting data set is different and I require a data sequence field when it is appended, in the order my query creates. Via a selection form I wanted to set filter criteria on the query, change some of the fixed data, which drives information of a new location for where data is appended to, then simply append the data to this new existing data fields
I am doing this because the process will need to be done many times to sort the data as required. I was hoping to eliminate the steps of make tables with Auto Id's and deleting them every time
 
Back
Top