Consecutive Number (1 Viewer)

[sajarac]

Hello there, I would like to have an advice from you guys, please check the enclosed file and let me know if this is possible:

As you can see I have two records and 3 orders, 2 orders for the record 1 and one order for the record two, is possible to add a field that count a new record starting in 1?

for instance if I have 3 record, the first one have 5 orders, second 7 orders and third 4 orders, I would like to see the subform like this:

OrderID Consecutive Order Date
1 1
1 2
1 3
1 4
1 5
2 1
2 2
2 3
2 4
2 5
2 6
2 7
3 1
3 2
3 3
3 4


thanks in advance for any response

 

[Minty]

You can calculate this for viewing purposes, but I don't see any logical reason for storing it, what would you think its purpose would be?

 

[plog]

Agree with Minty about not storing it, do not agree that this can be calculated. At least not with the information you have provided thus far.

You want to apply an order to your data (record 1, record 2, record 3, record 4...) but you haven't precisely defined how that order is to be achieved. How do you determine the order of your data to know which record is in what number?

Before you say Order date, let me tell you that method alone isn't good enough. If 2 records of the same order have the same Order date they will both have the same ordinal number. You need a way to uniquely order your data if you want to avoid 2 or more records both being numbered the same.

 

[sajarac]

Hi thanks both for your quick reply. I want the consecutive in order to know of how many rows were added. row 1 = consecutive 1, row 2 = consecutive 2, and so on

 

[plog]

Yes, we understand that. How you get there is a different manner. There is inherently no order in a table. It's just a big pile of data. There is no 1st record, no 2nd record, no next record, no prior record, no 143rd record in a table. Order exists on your data only when you tell it to exist (most likely in a query with an ORDER BY clause).

So, you need to be more explicit in how to apply order to your data. "row 2 = record 2" means nothing unless you tell us how to determine which row is row #2

 

[sajarac]

Thanks again for your reply, sorry I am not good in access as you guys, I am using the old sample "Orders", and I would like to see rather that the consecutive 1,2,3,4. one to x for every order I mean how many orders this customer has?

See the screenshot Record 1 has 3 buildings, and record 2 has 1 building, but the building # is 4, I want that number = 1

 

[Minty]

I'm guessing the Building number is a unique autonumber field and used to reference a whole heap of other things, and therefore should be left as such.

If you simply want to know how many buildings there are for that customer then you can display it on the form as a count of the subform records. An additional field with that customers consecutive building number in it will have no use to you and need a great deal of maintenance. What if that building changes owner - you would have to renumber everything otherwise your numbers won't be consecutive. and if the customer says I need you to do something to our building records 9 but it's now building 8 because you removed one who would know, and how would your customer?

Stick to giving the customer a totally unique reference for the building. It can only ever be that record. It doesn't matter that his buildings are number 2,7,12, 3434, 120098 in your system, only that you can quickly find them and know which building he is looking for.

 

[sajarac]

Thanks, could you please explain how to achieve the count of the display records? I would like to try that maybe is my solution

 

[Minty]

Place a textbox in the footer of the subform. Give it the ControlSource:
=Count(*)

Place a textbox on the main form. Give it the ControlSource for the one you added to the subform:
=subformname.Form.controlname

Blatently stolen from Galaxioms post about 3 years ago...