Sequential Number in Query

[JAQ]

Hi

I am looking for a solution as i haven't any ID field in my query what i need is a sequential number from 1 to so on which shall count rows only in query

 

[theDBguy]

No ID, but do you have another unique column available? If not, you will need a custom counter function.

 

[The_Doc_Man]

There is a conceptual problem here. If you have no ID (or any other type of field) that you can use to provide order to your query, you can number the rows but those numbers will mean nothing. If you update any field in any record in that table, the numbers will be different the next time you open that query.

This happens because your underlying table has no inherent order - particularly since you denied having a usable ID field. Therefore, unless you can provide at least some semblance of a field to support a sort order, your query-based number will be useful only for picking random lottery numbers. Look through the articles linked below in the "Similar Threads" section to see if you get any ideas.

Try this Google search: "Can I add an autonumber field to an access table?"

 

[JAQ]

There is a conceptual problem here. If you have no ID (or any other type of field) that you can use to provide order to your query, you can number the rows but those numbers will mean nothing. If you update any field in any record in that table, the numbers will be different the next time you open that query.

This happens because your underlying table has no inherent order - particularly since you denied having a usable ID field. Therefore, unless you can provide at least some semblance of a field to support a sort order, your query-based number will be useful only for picking random lottery numbers. Look through the articles linked below in the "Similar Threads" section to see if you get any ideas.

Try this Google search: "Can I add an autonumber field to an access table?"

Dear thanks, I just need a column in query which shows row numbers for that particular records in query

 

[oleronesoftwares]

Dear thanks, I just need a column in query which shows row numbers for that particular records in query

You haven't answered the questions raised by @theDBguy and @The_Doc_Man with regards to whether a unique field exists, without understanding how records in the table are organised, it might be difficult to advise well.

 

[Uncle Gizmo]

Have a look at This Blog:-

How do I create a Numbered Query in Access?

Thoughts, opinions, samples, tips, and tricks about Microsoft Access

rogersaccessblog.blogspot.com

 

[MajP]

Microsoft Access tips:

How to rank records in Microsoft Access

allenbrowne.com

 

[Pat Hartman]

Regarding Uncle's suggestion ----

Both of these methods require a unique column in the table to create the sequence on.

Maj's suggestion ALSO requires something on which to order the set of records.

That brings us back to the initial questions On what do you propose to sequence the rows?

!. Having no uniqueID for a table is simply poor practice. If you don't have a unique candidate key, use an autonumber.
2. Without a "seed", you need a sequential process to come up with a sequence. This is trivial in a report because reports are produced sequentially. If the number is to be used in a report, this is the best solution. Use the Over All or Over group property to "count" the rows to print.

Relational tables are unordered sets. They do NOT have the concept of a record number. Neither does a query.

 

[MajP]

If you don't have a unique candidate key, use an autonumber.

The mileage is both unique and ordered and makes a good natural PK. It is always increasing. AFAIK you cannot drive a car and then fill up without the mileage increasing and being unique. I guess you can worry about the case where the odometer fails and you reset it or get a new odometer. At that point it is probably a new query.

 

[Pat Hartman]

The mileage is both unique and ordered and makes a good natural PK.

Do you know something we don't know about the OP's database

 

[MajP]

Do you know something we don't know about the OP's database

No. I just read what the OP wrote.

 

[The_Doc_Man]

@MajP - are you perhaps confusing this person with someone else? I just looked at all four of JAQ's posts and NONE of them mention mileage. Can you point to the post with the word mileage? Because your point is well taken, but either you have a PM from this person or your crystal ball is working and mine is on the fritz.

 

[Gasman]

That is another thread
This one perhaps? https://www.access-programmers.co.u...ence-between-two-figures.321059/#post-1802760

 

[MajP]

@MajP - are you perhaps confusing this person with someone else? I just looked at all four of JAQ's posts and NONE of them mention mileage. Can you point to the post with the word mileage? Because your point is well taken, but either you have a PM from this person or your crystal ball is working and mine is on the fritz.

Thanks. Definitely the wrong post.