Row Number in a Query

[Leo_Polla_Psemata]

I don't understand, why in a simple query, the row number, in access,
looks like rocket science. Too difficult.

Well
I have a query, one field, say [Bol_reference], may return the below

ABC123
ABC234
ABC345
ABC345
BCD123
BCD123

I would like to have a row number so that query will look like this

1 ABC123
2 ABC234
3 ABC345
4 ABC345
5 BCD123
6 BCD123

And then, a row number for each record, the query would look like this

1 ABC123 1
2 ABC234 1
3 ABC345 1
4 ABC345 2
5 BCD123 1
6 BCD123 2
7 BCD123 3

The record 1,2, are unique
The records 3, and 4 are duple so, a row number for ABC345
The records 5,6,7 are listed three times, so, a new row number for BCD123

This query could return up to 600 records, average 40% may appear more than one time.

Could someone help me in this by using simple functions?

 

[CJ_London]

there is not enough data in your table to provide a rownumber as you require - you need a unique value in another column such as an PK field - at least unique to each BolReference (i.e. ABC345 + some other value=unique). Once you have that you can either use a subquery for speed or the dcount function for 'relative' simplicity but slow

 

[Leo_Polla_Psemata]

Hi CJ London
Thanks for your comments.
In fact, the purpose of this query is to make the [Bol_reference] a unique value.
I want to make the

BCD123
BCD123
BCD123

look like

BCD123/1
BCD123/2
BCD123/3

The [Bol_reference] belongs in a table which is relation is one to many.
[Bol_reference] is in the many side.
In the query, it may appear more than one times.
I retrieve as excel and I change manually by adding /1, /2, /3 etc where is is necessary.
Then i convert to XML and upload to another application.
The problem is that if i do it manually in 600 lines, for sure a couple of records will skip my attention and then I get errors and need to go back and forward, so, I tried to see if this could come ready from the main query.

 

[CJ_London]

This is not excel, you still need another unique ID - otherwise how do you differentiate between each?

BCD123
BCD123
BCD123

 

[delowardidar]

This is not excel, you still need another unique ID - otherwise how do you differentiate between each?

BCD123
BCD123
BCD123

Thats why we need yours help. me also in same ruble. :banghead:

 

[CJ_London]

in the table design, you will need to add an autonumber field

 

[Galaxiom]

Once you have that you can either use a subquery for speed or the dcount function for 'relative' simplicity but slow

Actually, for adding a row number (or running totals), there are no really fast solutions. Subqurey techniques are slow while DCount techniques are incredibly slow, in database performance terms.

Since tables are unordered lists, unlike Excel, an ordered number is not part of the data and must be imposed on each record, involving a serious individual calculation for each record.

Either way, vast numbers of iterations run in the background. The subquery (or more precisely in this case, a correlated subquery), involves running a query for every record returned from the main query. At least it doesn't involve a call back to VBA for every record as is required with a DCount.

It is a huge subject best not engaged if you don't have a day to spare, but such tasks are best performed with a join to a Tally table.

A wise database developer would want a really good reason to include a row number in a query result unless only small numbers of users would ever be engaged. Honestly the overheads are horrendous.

 

[gemma-the-husky]

the reason this is so difficult is that access gives you an un-ordered set. effectively it gives you the records you want in no particular order.

so if you want to make the order significant, and then allocate row numbers, you have a difficult job. eg - what if you resort or filter the query - what happens to your row numbers.

often the best solution is to try to work with what access (any database) does give you, which is discrete sets of data.

 

[The_Doc_Man]

This is an example of my Old Programmer's Rule #2 - Access won't tell you anything you didn't tell it first.

If you wanted sequence numbers output, you should have either input the sequence numbers or allowed an autonumbering scheme. As noted by Dave, Greg, and CJ, Access record presentation is based on SET theory, so you get a SET of records. In theory, you get all of the selected records at once. It is the graphic interface that imposes the row structure to show them to you in a human-readable format.

That means that there ARE no row numbers unless you DECLARED a field for row numbers. And if you want consecutive numbering from a query of the raw data, you are going to have to compute it yourself. It is NOT a pretty sight because SQL is SET-oriented, not ROW-oriented. Therefore, the computations needed to do this will depend on order of presentation or sorting. There is no simple answer.