Increment Change On Invoice Number? 1

[supportsvc]

Hello,
So there's an odd incrementing that needs to occur for change on invoice number

Need to increment in a 14 "digit" required LineSeqNo field like so:
00000100000000
00000200000000

Invoice Numbers repeat for each line and the LineSeqNo increments for each line detail per invoice but not the typical 1, 2, 3, 4, etc ...

but as shown, it starts in the middle of a 14 "digit" LineSeqNo field.

How do I do a Make Table to auto assign the LineSeqNo for each change on InvoiceNo?

 

[MajP]

What happens after?
00000900000000

 

[Andrzejek]

If you know how to get the "typical 1, 2, 3, 4, etc.", why not simply keep the value as 1, 2, 3, 4, but for display only - multiply your value by 100 000 000 and format it to whatever you want:

Dim x As Integer
Dim i As Integer

For x = 1 To 10
    i = i + 1
    
    Debug.Print Format(i * 100000000, "00000000000000")
Next x

---- Andy

There is a great need for a sarcasm font.

 

[supportsvc]

MajP

after 00000900000000
it's 00000100000000

Andy,
Actually I don't know how to get the 1,2,3,4, etc ... just know that's the typical increment logic

could you elaborate, rather, provide how to do the incrementing as typical and applying your suggestion?
there's the leading zeros then the numbering with tailing zeros


Thank you

 

[Andrzejek]

Typically Access users establish a field in a table and set it as an AutoNumber (and have it as a Primary Key field in a table). Access will increase it for you when a new record is inserted into that table.

Unless you need something else...


---- Andy

There is a great need for a sarcasm font.

 

[supportsvc]

I am looking for incrementing in the 14 "digit" required field in this back office SQL table on change of InvoiceNo and set all other records without an InvoiceNo to 00000000000000

Where the numbering starts @ middle of the 14 "digit" required field

InvoiceNo: 1234
LineSeqNo: 00000100000000
LineSeqNo: 00000200000000

InvoiceNo: 2345
LineSeqNo: 00000100000000
LineSeqNo: 00000200000000

InvocieNo: NULL
LineSeqNo: 00000000000000

etc ...

 

[Andrzejek]

If we would forget (for now) about your '14 "digit" required field' requirement, would you know how to accomplish this: increase a simple number (LineSeqNo) per InvoiceNo?

[pre]
InvoiceNo LineSeqNo
1234 1
1234 2
2345 1
2345 2
NULL 0[/pre]


---- Andy

There is a great need for a sarcasm font.

 

[supportsvc]

Unfortunately I do not

 

[Andrzejek]

So let's say you know your InvoiceNo of 1234 and you want to Insert a new record. You can simply execute this statement:
[tt]
INSERT INTO MyTable (InvoiceNo, LineSeqNo)
VALUES (1234, (SELECT MAX(LineSeqNo) + 1
FROM MyTable WHERE InvoiceNo = 1234))[/tt]

You need another INSERT statement where you can Insert a record without InvoiceNo so you can deal with a NULL, something like:
[tt]
INSERT INTO MyTable (InvoiceNo, LineSeqNo) VALUES (NULL, 0)[/tt]



---- Andy

There is a great need for a sarcasm font.

 

[supportsvc]

Ok, sorry

Here's the situation
There's a table with information that includes everything EXCEPT for the LineSeqNo

So trying to Extract from that table and do a Make Table to assign the LineSeqNo in the 14 "digit" format that'll then be used to Append to the table that requires this 14 "digit" LineSeqNo along with the rest of the information from the originating table.

It doesn't have to be a Make Table, it can be the Append table as long as the query will assign the LineSeqNo as required and described.

 

[Andrzejek]

So the way I understand your statements:
1. You already have a table with the data (EXCEPT for the LineSeqNo)
2. If so, could you show me a sample of your table? (table name, fields' names, some sample data. If you do, please use proper formatting so the information is readable)
3. You want to add to that table a field named LineSeqNo
4. You want to populate the LineSeqNo field with data (let's make it simple at this point and use just simple numbers, like 1,2,3,4, forget at this point the 14 digits 0 filled data)
5. If the previous statements are true, what will drive the ORDER of the records for any specific InvoiceNo?


---- Andy

There is a great need for a sarcasm font.

 

[supportsvc]

Not sure what you mean / asking on this

5. If the previous statements are true, what will drive the ORDER of the records for any specific InvoiceNo?

As long as each invoiceno has 1-x then reset on the next invoiceno and repeat, no invoiceno gets the 00000000000000

Sample data from table without the LineSeqNo

PostingDate	BatchNo	RegisterNo	DocumentNo	DebitAmount	CreditAmount
4/24/2018	MULTI	004903		233299.38	0
4/24/2018	00424	004903	E000136	0	11.75
4/24/2018	00424	004903	E000136	0	11.75
4/24/2018	00424	004903	E000136	0	11.75
4/24/2018	00424	004903	E000136	0	11.75
4/24/2018	00424	004903	E000136	0	11.75
4/24/2018	00424	004903	E000136	0	11.75
4/24/2018	00424	004903	E000137	0	11.75
4/24/2018	00424	004903	E000138	0	11.75
4/24/2018	00424	004903	E000138	0	11.75
4/24/2018	00424	004903	E000138	0	11.75
4/24/2018	00424	004903	E000139	0	11.75
4/24/2018	00424	004903	E000140	0	11.75
4/24/2018	00424	004903	E000140	0	11.75
4/24/2018	00424	004903	E000141	0	11.75
4/24/2018	00424	004903	E000141	0	11.75
4/24/2018	00424	004903	E000141	0	11.75
4/24/2018	00424	004903	E000141	0	11.75
4/24/2018	00424	004903	E000142	0	11.75

Need it to

PostingDate	BatchNo	RegisterNo	DocumentNo	LineSeqNo	DebitAmount	CreditAmount
4/24/2018	MULTI	004903		00000000000000	233299.38	0
4/24/2018	00424	004903	E000136	00000100000000	0	11.75
4/24/2018	00424	004903	E000136	00000200000000	0	11.75
4/24/2018	00424	004903	E000136	00000300000000	0	11.75
4/24/2018	00424	004903	E000136	00000400000000	0	11.75
4/24/2018	00424	004903	E000136	00000500000000	0	11.75
4/24/2018	00424	004903	E000136	00000600000000	0	11.75
4/24/2018	00424	004903	E000137	00000100000000	0	11.75
4/24/2018	00424	004903	E000138	00000100000000	0	11.75
4/24/2018	00424	004903	E000138	00000200000000	0	11.75
4/24/2018	00424	004903	E000138	00000300000000	0	11.75
4/24/2018	00424	004903	E000139	00000100000000	0	11.75
4/24/2018	00424	004903	E000140	00000100000000	0	11.75
4/24/2018	00424	004903	E000140	00000200000000	0	11.75
4/24/2018	00424	004903	E000141	00000100000000	0	11.75
4/24/2018	00424	004903	E000141	00000200000000	0	11.75
4/24/2018	00424	004903	E000141	00000300000000	0	11.75
4/24/2018	00424	004903	E000141	00000400000000	0	11.75
4/24/2018	00424	004903	E000142	00000100000000	0	11.75

 

[Andrzejek]

Is InvoiceNo field the same as DocumentNo field? And if it IS the same field, do you care about the ORDER of the records for the same InvoiceNo / DocumentNo ? Does it matter if the record with LineSeqNo 00000[blue]5[/blue]00000000 be switched with the record 00000[blue]2[/blue]00000000 ? (That's what I meant in my quoted statement)

[pre]
PostingDate BatchNo RegisterNo DocumentNo LineSeqNo DebitAmount CreditAmount
4/24/2018 MULTI 004903 00000[blue]0[/blue]00000000 233299.38 0
4/24/2018 00424 004903 E000136 00000[blue]1[/blue]00000000 0 11.75
4/24/2018 00424 004903 E000136 00000[blue]2[/blue]00000000 0 11.75
4/24/2018 00424 004903 E000136 00000[blue]3[/blue]00000000 0 11.75
4/24/2018 00424 004903 E000136 00000[blue]4[/blue]00000000 0 11.75
4/24/2018 00424 004903 E000136 00000[blue]5[/blue]00000000 0 11.75
4/24/2018 00424 004903 E000136 00000[blue]6[/blue]00000000 0 11.75
[/pre]
And there is no Primary Key in this table?


---- Andy

There is a great need for a sarcasm font.

 

[supportsvc]

Ah, yea, was thinking that's what you might be inquiring about

No, it's not necessary from what I can tell.

Unfortunately there is no PrimaryKey :/

 

[Andrzejek]

In my opinion...
Without the PK in your table (or any other way to point to a single record) it is impossible to update your records with code - because there is no way to point to any particular one record.

You would use an Update statement as long as you would be able to say which record to update:
[tt]Update MyTbale
Set LineSeqNo = 1234
Where.... ???[/tt]
and there is no Where statement that would define one record :-(

Maybe somebody smarter than me would have a way to do it.... Let's hope.

But I would, again, concentrate on using simple numbers, like 1, 2, 3, 4, etc. for your LineSeqNo and forget the 14 digits zero filled. That's just formatting the data, which is easy in code or in a query.


---- Andy

There is a great need for a sarcasm font.

 

[supportsvc]

SeqNo is unique

however, it's reset incrementing when change on invoiceno

 

[Andrzejek]

SeqNo is unique" - you mean: LineSeqNo ?
Per "nvoiceno" (Is InvoiceNo field the same as DocumentNo field? I've asked this before but never got any answer...)

Well, this 'combination of 2 fields' would be unique as soon as you get the value in LineSeqNo field - which you are trying to populate. So you have a "Catch 22" (good book, by the way)


---- Andy

There is a great need for a sarcasm font.

 

[supportsvc]

No there's a SequenceNo and a LineSeqNo
Assigning the SequenceNo was easy to do in Excel
The LineSeqNo when change on InvoiceNo is the difficult one
Doing the LineSeqNo in Excel is too manual whereas the SequenceNo, after couple of entries, you just double click on the corner that auto populates consecutively to the end of the row.
You have to visually look to see when there's a change in InvoicNo to do it in Excel and will take too long to do with the number of records in the file.

Easy to import back in with the SequenceNo assigned.

 

[MajP]

I am a little lost, and coming in late. At first I thought you needed to come up with a numbering strategy but now you first need to update your data. If you have data in your db and you want to renumber as described in your sample data, I would think you can just do that in code.
So you would order your query by batchnumber, registerno, documentnumber, date and then loop the records in a recordset. Start increment and then once you get to the next documentNo start the increment over. Not sure if that is what you are asking, but this seems different from the initial posts.

 

[supportsvc]

MajP
The code is what I'm seeking

To assign LineSeqNo when InvoiceNo changes within the 14 "digit" requirement