Increment field in Query

[Mchel Boholano]

DocNum LineNum ItemCode
100001 0 B-22220
100001 1 B-22221
100001 2 B-22222
100001 3 B-22223
100002 0 C-33330
100002 1 C-33331
100002 2 C-33332

How can I create a query with an output like this?
DocNum and ItemCode are from the table named 'Table1'

[ADezii]

1. Are you looking to generate the next LineNum in sequence for each unique DocNum?
2. I'm assuming that Values in the ItemCode Field are manually entered, and are in strict sequence. Is this assumption correct?

[NeoPa]

SQL doesn't support that natively.

The best answer is not to. Use the Running Sum facility of report controls instead.

If you have another good reason for wanting this unconnected with reports then let us know . It's possible to create a function to help with this, but as that's only going to help you get the fundamental concepts all wrong, I don't want to take you there unless there's good reason.

[Narender Sagar]

Hi Boholano,
1. what about LineNum? Is it logical or coming from another table
2. If logical, what is your requirement?
3. If from another table, is there any relation between table1 and that Table2?
4. Do you want DocNum, LineNum, ItemCode in different column or in single column itself? because your question "How can I create a query with an output like this?"- does not indicates like what?

[NeoPa]

@Narender
The question is not asked well, but you can find the information you want from the title. The OP is after showing data records (including fields [DocNum] and [ItemCode]) as well as a separate field [LineNum], which is created automatically and is an incremental value reflecting the position of the record within those of the same [DocNum] (starting at zero) and sorted by [ItemCode].

[Mchel Boholano]

hey guys thanks for your interest in helping me.
actually, am creating a template needed for SAP B1
am pulling some data from ms access to be uploaded to SAP thru SAP B1 Templates.

@neopa
thanks you explained it well. i want to create a query that can automatically generate incremental value that links to DocNum field. is it possible in access? the values in the ItemCode are not manually entered. it just happened that my sample data looks like a sequential nos. too.

[Mchel Boholano]

@neopa

yes, i'd tried it already in reports...and running sum works.
but unfortunately, the LineNum (which is sequential) will be linking to another template am doing. that's why am trying to do it in a query (if its possible) so i can link the LineNum value to another template.

example :
template -> ar details
fields -> DocNum LineNum ItemCode Qty
sample -> 7379 0 N270 1
7379 1 N455 3
7379 2 WM66A 2
7377 0 N270 1
7377 1 N455 1
*** i can generate this template in a report using running sum (LineNum)then, output the report to excel file
but the problem is, i still have another template which is the "ar serial numbers", linking to LineNum in "ar details" template.
how can i output the "ar serial numbers" template like this :

fields -> DocNum LineNum DistNumber
7379 0 3446547
7379 1 3435466
7379 1 4365754
7379 1 5453498
7379 2 6546556
7379 2 8575455
7377 0 3466454
7377 1 4354589

hope u can help me....

[NeoPa]

Mchel Boholan:
the LineNum (which is sequential) will be linking to another template am doing

Unfortunately, linking to a template means nothing to me. I have no idea what you mean by a template so none of the rest of it makes sense, and I can't guess what you mean because I simply can't imagine any sensible set-up that could be close to what you're saying.