Record Number in a query

[QAVinyard]

Hi all

I just joined up but I have been using this site for some time now. Up until now I have found answers to all my questions, where somebody else has already asked. However, I now have one that I cannot find.

Does anyone know how to have a query provide the record number within the query. I know how to accomplish it with a form but not in a query.

Thanks

 

[scriverb]

Select *
FROM tblYourTableName;

If there is a field in the table that is the AutoNumber type(AutoIncrement) then it will be displayed as a column along with all other fields. You must have the field identified as such in your table first. Bob Scriver

 

[cmmrfrds]

Are you meaning a row sequence number or an actual number in the record that is in sequential order (this may have gaps).

see thread701-496708

 

[scriverb]

cmmrfrds: I think QAVinyard may be talking about a row sequence number afterall. You link to the thread should certain do the trick. Bob Scriver

 

[victory92]

I am trying to do the same thing!! That is I'm trying to obtain a row sequence number for all records in my query.

Currently I have a query (not a table) that I would like to get a record number -- or some sort of autonumber capability to assign sequential numbers to the records.

I don't understand the answer "You link to the thread should certain do the trick." Will you please elaborate?

Sorry I'm a newbie.

 

[scriverb]

My posting was to cmmrfrds who posted just above mine. He indicated a Thread# on this site where he had already posted a sample query to create a Seq# for the rows of a query. Just click on that Thread link and you will see the previous postings related to this subject. Here is the example query that was being referenced:

select count(*) as rownum, c1.name
from customer c1, customer c2
where c2.name <= c1.name
group by c1.name
order by rownum

You will have to make adjustments for table names and field names but this will give you a RowSeq#. Bob Scriver

 

[PaulBricker]

This may explain it a little more clearly. You can't ORDER BY recnum because Access doesn't recognize it as a legitimate name so you have to order by the Count(*). Also, the c1 and c2 were a little confusing for me. My Num field is just a Text field with Alpha/Numeric values in it.

SELECT Count(*) AS recnum, tblTemp.Num
FROM tblTemp, tblTemp AS tblTemp_1
WHERE (((tblTemp_1.Num)<=[tblTemp].[Num]))
GROUP BY tblTemp.Num
ORDER BY Count(*);

Paul

 

[scriverb]

PaulBricker is correct in the the Order by needs to reference the Count(*). In addition, I believe that the WHERE line references the Num fields from both has to be a unique identifier for the records. You can't just use name where there would be duplicates possibily. The AutoNumber field of the table would work. You see what you are doing is saying gather up and count all of the records from tblTemp_1 which have a Num field that is <= to the Num field in the primary table of your query. They must be unique and be the order sorted by the table.

WHERE (((tblTemp_1.Num)<=[tblTemp].[Num]))

Correct me in this if I am wrong Paul.
Bob Scriver

 

[PaulBricker]

Duplicates in the Num field produce duplicate recnum's so it would seem that unique field values are necessary. But what I don't know would fill more of a book that what I do know when it comes to subqueries. I follow you and cmmrfrds around picking up tidbits.

Paul

 

[QAVinyard]

Thanks everyone for taking the time to help me out. I havn't had a chance to try it out yet. I'll let you know

Thanks again
Paul