Numbering of records within groups (1 Viewer)

[oddbir]

Hi All!

I'm new on this forum, and have an issue where I need some help.

I have a table with two fields like this:

F1 F2
110 1
110 1
111 1
111 1
111 1
112 1
112 1
.....

I need to change the number in F2 like tihis


F1 F2
110 1
110 2
111 1
111 2
111 3
112 1
112 2
.....

Probably simple but I'm stuck!

Kind regards
oddbir

 

[rzw0wr]

Appears you need to start by reading the following.

http://symplebyte.com/microsoft_office/access/how_to_normalize_data_access.html

Dale

 

[oddbir]

Hi,

I checked this but did not quite get the message?

oddbir

 

[rzw0wr]

I think you need to normalize your tables.

Data is appearing more than 1 time in your tables.

Dale

 

[oddbir]

Sorry, there are several other fileds in the form, product info, amount, etc.
F1 is the order ID and F2 shuld be a sequential numbering of the order lines connected to each order.

Does this make more sence?

oddbir

 

[Pat Hartman]

The ongoing solution is to use DMax() to find the highest sequence number for a given order number and then add 1 to it. To convert the existing data, you will need to create a query that sorts the data into sequence by order number and then something like a dateTime field or autonumber that sequences the items into the order in which they were entered. Then write a code loop that reads this query and assigns the appropriate sequence number.

PS - I wouldn't bother with this at all since you really don't need a user-visible unique identifier for the item records. Just use an autonumber and keep it hidden.