473,320 Members | 2,092 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Increment field in Query

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'
Aug 26 '11 #1
7 2744
ADezii
8,834 Expert 8TB
  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?
Aug 26 '11 #2
NeoPa
32,556 Expert Mod 16PB
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.
Aug 26 '11 #3
Narender Sagar
189 100+
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?
Aug 26 '11 #4
NeoPa
32,556 Expert Mod 16PB
@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].
Aug 26 '11 #5
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.
Sep 7 '11 #6
@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....
Sep 7 '11 #7
NeoPa
32,556 Expert Mod 16PB
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.
Sep 7 '11 #8

Sign in to post your reply or Sign up for a free account.

Similar topics

2
by: Kajol | last post by:
Hi All, I am using server version: 4.1.11-nt on windows 2000. I want to create a table with ID as auto increment and initialise it with a starting value of 1000. I am trying to do following...
0
by: Santosh | last post by:
Hi, I have a requirement in which I need to create an auto increment column in a file which will be unique. The following is what I am trying to do. 1) I need to use DDS to define the file 2)...
5
by: Javier | last post by:
I have a field type Date/Time that automatically defaults to Now(). I'm having a problem writing a query that will retrieve all the records for a specific date the user to enters when prompted. I...
9
by: Eitan M | last post by:
Hello, How can I find the last indentity of auto-increment field ? (If I inserted a record, then the auto-increment field is build automatically, and I want to find its value). Thanks :)
2
by: gomzi | last post by:
hi, I have an auto-increment field. I would like to know as to how I could reset it back to zero. thanks, gomzi.
0
by: Michele 'xjp' | last post by:
Hi, I need a data structure (better if in STL) what give me: - an auto generated auto-increment integer field ('id') - various string fields This structure data must be searchable: I must be...
0
chumlyumly
by: chumlyumly | last post by:
Hello scripters - OS: Mac OSX Language: PHP w/ MySQL database I've created an insert page where a user inputs his info, which then goes to four different tables in a MySQL database. The...
5
by: jnice814 | last post by:
I am trying to create an AutoNumber that will begin at a number that I specify. I have followed all instructions in MS Access Help, but it is still not working. I continue getting an error message...
11
by: ZaphodBBB | last post by:
Hi I have a table that has as its Primary Key the Auto-Increment Field: Equipment_Number. In M.S. Access is there any way to define a starting number for the field? OR another way in which...
5
by: David Wright | last post by:
Hello Everyone I would be grateful if someone could help me with the automatic increment of a field on my subform called ‘Test_SrNo’. I am Using Microsoft Office 2000. The auto entry of the...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.