MS Access Line_No

[rexie]

Hi all,

I have an access table where I would like to add Line Nos.
This is a Detail (Header/Detail) Table below
For eg below,

Customer ID Order No Invoice ID
1002 100 123
1002 100 233
1002 100 344

For the same CustomerID, for the same Order No, there can be multiple Invoice IDs, so when each Invoice ID's, a record should be written like below


Customer ID Order No Invoice ID Line No
1002 100 123 1
1002 100 233 2
1002 100 344 3

See, the Line Number should be added, so that we can make it unique in the Detail Table. SO, what I am looking for is how can I add a line number like 1,2,3 ...) to the table at run time.

Please let me know if anybody could throw some light,

Thanks a Lot in advance

Regards

 

[Dreamboat]

Does this number need to be hard-coded into the table? If NOT, you can do it with a query on the same table.

Create a query using all fields in the table. Insert the column where you want it. In the field name type:

Line No:=left([invoiceID],1)

http://www.TheWordExpert.com

 

[rexie]

Thanks a lot for the answer, could you also tell me what left([invoiceID],1) does.

Thanks a lot

 

[Dreamboat]

Sure. It goes from the left of the value under the InvoiceID and takes the 1st character. If you do:

=Right([InvoiceID],4)

it returns the last 4 characters of a value.

=Mid([InvoiceID],3,4)

will go to the 3rd character and give you that plus the next 3 (total 4) characters.

http://www.TheWordExpert.com

 

[rexie]

Hi Dreamboat,

Thanks once again, but the line number should be generated, there is no data in line number, we have to populate it. I want access to give me line numbers for the same customer_id, order_id for different invoices.

 

[Dreamboat]

Doh! I thought you already had them. Go to table design view. Insert a new FIRST field. Call it RecordID or something, and give it an autonumber field. That's all!

http://www.TheWordExpert.com

 

[rexie]

Hi Dreamboat,

Thanks , when the value of the key changes I want the Line number reset. Meaning to say as below


With Customer Number 100
-------------------------
Customer ID Order No Invoice ID Line No
1002 100 123 1
1002 100 233 2
1002 100 344 3


With Customer Number 101
========================
Customer ID Order No Invoice ID Line No
1002 101 123 1
1002 101 233 2
1002 101 344 3

IF I use autonumber will it keep on incrementing till the end of data. What I would like is to reset the numbers when the key value changes

Thanks a lot Dreamboat

 

[Dreamboat]

You cannot reset auto-numbers.

http://www.TheWordExpert.com